hankin
423baff73b
- Docker bridge 网络隔离（8000 端口封死） - Gunicorn 4 Worker 多进程 - Alembic 数据库迁移基线 - 日志轮转 20m×3 - JWT 密钥 + DB 密码 + CORS 收紧 - 3-2-1 备份链路（NAS + R740-B 冷备） - 连接池 pool_pre_ping + pool_recycle=3600
54 lines
3.3 KiB
SQL
54 lines
3.3 KiB
SQL
-- ============================================================
|
|
-- Phase 4 数据库迁移脚本
|
|
-- 执行方式: psql -U postgres -d crm_erp -f migration_phase4.sql
|
|
-- ============================================================
|
|
|
|
-- ────── 1. pg_trgm 扩展（客户模糊搜索） ──────
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
|
|
-- 为客户名称建 pg_trgm GIN 索引
|
|
CREATE INDEX IF NOT EXISTS idx_cust_name_trgm
|
|
ON crm_customers USING gin (name gin_trgm_ops);
|
|
|
|
-- ────── 2. 销项发票表 finance_sales_invoices ──────
|
|
CREATE TABLE IF NOT EXISTS finance_sales_invoices (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
issuer VARCHAR(200) NOT NULL, -- 开票方/我方主体
|
|
receiver_customer_id UUID NOT NULL REFERENCES crm_customers(id), -- 受票方
|
|
invoice_number VARCHAR(100) NOT NULL UNIQUE, -- 发票号
|
|
amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 票面金额
|
|
billing_date DATE NOT NULL, -- 开票时间
|
|
payment_status VARCHAR(20) NOT NULL DEFAULT '未回款'
|
|
CHECK (payment_status IN ('未回款', '部分回款', '已结清')),
|
|
payment_date DATE, -- 回款时间
|
|
payment_amount NUMERIC(14,2) DEFAULT 0, -- 已回款金额
|
|
remark TEXT,
|
|
created_by UUID REFERENCES sys_users(id),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
is_deleted BOOLEAN DEFAULT FALSE
|
|
);
|
|
|
|
COMMENT ON TABLE finance_sales_invoices IS '销项发票表（AR 应收账款核心）';
|
|
COMMENT ON COLUMN finance_sales_invoices.issuer IS '开票方/我方主体名称';
|
|
COMMENT ON COLUMN finance_sales_invoices.receiver_customer_id IS '受票方，关联 crm_customers';
|
|
COMMENT ON COLUMN finance_sales_invoices.invoice_number IS '发票号码（全局唯一）';
|
|
COMMENT ON COLUMN finance_sales_invoices.amount IS '票面金额';
|
|
COMMENT ON COLUMN finance_sales_invoices.billing_date IS '开票日期';
|
|
COMMENT ON COLUMN finance_sales_invoices.payment_status IS '回款状态: 未回款/部分回款/已结清';
|
|
COMMENT ON COLUMN finance_sales_invoices.payment_date IS '回款日期';
|
|
COMMENT ON COLUMN finance_sales_invoices.payment_amount IS '已回款金额（用于部分回款场景）';
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_fsi_receiver ON finance_sales_invoices(receiver_customer_id) WHERE is_deleted = FALSE;
|
|
CREATE INDEX IF NOT EXISTS idx_fsi_number ON finance_sales_invoices(invoice_number) WHERE is_deleted = FALSE;
|
|
CREATE INDEX IF NOT EXISTS idx_fsi_billing_date ON finance_sales_invoices(billing_date) WHERE is_deleted = FALSE;
|
|
CREATE INDEX IF NOT EXISTS idx_fsi_payment_status ON finance_sales_invoices(payment_status) WHERE is_deleted = FALSE;
|
|
|
|
CREATE TRIGGER trg_fsi_updated
|
|
BEFORE UPDATE ON finance_sales_invoices
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- ────── 完成 ──────
|
|
-- 新增: finance_sales_invoices (1 张表)
|
|
-- 新增: pg_trgm 扩展 + crm_customers.name GIN 索引
|