04 - 数据库设计¶

organization
├─ organization_member ─ user（better-auth）
└─ workspace
   └─ workspace_member ─ user
      └─ invoice / attachment / parse_task / batch / ...

CREATE TABLE organization (
    id          uuid PRIMARY KEY,
    name        text NOT NULL,
    slug        text NOT NULL UNIQUE,
    status      text NOT NULL DEFAULT 'active', -- active / suspended
    created_at  timestamptz NOT NULL,
    updated_at  timestamptz NOT NULL
);

CREATE TABLE organization_member (
    organization_id uuid NOT NULL REFERENCES organization(id),
    user_id         text NOT NULL REFERENCES "user"(id),
    employee_no     text,
    department_name text,
    status          text NOT NULL DEFAULT 'active', -- active / disabled
    created_at      timestamptz NOT NULL,
    PRIMARY KEY (organization_id, user_id)
);

CREATE TABLE workspace (
    id              uuid PRIMARY KEY,
    organization_id uuid NOT NULL REFERENCES organization(id),
    name            text NOT NULL,
    kind            text NOT NULL DEFAULT 'personal', -- personal / shared
    owner_user_id   text NOT NULL REFERENCES "user"(id),
    status          text NOT NULL DEFAULT 'active', -- active / archived
    created_at      timestamptz NOT NULL,
    updated_at      timestamptz NOT NULL,
    UNIQUE (organization_id, id)
);

CREATE TABLE workspace_member (
    workspace_id uuid NOT NULL REFERENCES workspace(id) ON DELETE CASCADE,
    user_id      text NOT NULL REFERENCES "user"(id),
    role         text NOT NULL, -- owner / manager / member / viewer
    status       text NOT NULL DEFAULT 'active',
    joined_at    timestamptz NOT NULL,
    PRIMARY KEY (workspace_id, user_id)
);
CREATE INDEX idx_workspace_member_user ON workspace_member(user_id, workspace_id);

CREATE TABLE file_blob (
    id                  uuid PRIMARY KEY,
    sha256              char(64) NOT NULL UNIQUE,
    storage_key         text NOT NULL UNIQUE, -- blobs/<2位>/<sha256>，不可直接作为公网 URL
    size_bytes          bigint NOT NULL,
    mime_type           text,
    preview_storage_key text, -- previews/<2位>/<sha256>.png（PDF 首页缩略图，服务端渲染）
    preview_mime_type   text,
    preview_width       integer,
    preview_height      integer,
    created_at          timestamptz NOT NULL
);

CREATE TABLE workspace_file (
    id                 uuid PRIMARY KEY,
    workspace_id       uuid NOT NULL REFERENCES workspace(id),
    blob_id            uuid NOT NULL REFERENCES file_blob(id),
    original_name      text NOT NULL,
    source             text NOT NULL DEFAULT 'upload', -- 文件来源：upload / mail / ocr
    archive_path       text, -- 虚拟归档路径（Archive/... 前缀，无物理目录对应）
    created_by_user_id text NOT NULL REFERENCES "user"(id),
    created_at         timestamptz NOT NULL,
    UNIQUE (workspace_id, blob_id),
    UNIQUE (workspace_id, id)
);

CREATE TABLE parse_task (
    id                   uuid PRIMARY KEY,
    workspace_id         uuid NOT NULL REFERENCES workspace(id),
    workspace_file_id    uuid NOT NULL,
    requested_by_user_id text NOT NULL REFERENCES "user"(id),
    status               text NOT NULL DEFAULT 'pending',
                         -- pending / processing / done / failed / duplicate / skipped
    worker_id            text,
    retry_count          integer NOT NULL DEFAULT 0,
    next_retry_at        timestamptz,
    error_code           text,
    error_msg            text,
    invoice_id           uuid,
    attachment_id        uuid,
    created_at           timestamptz NOT NULL,
    updated_at           timestamptz NOT NULL,
    FOREIGN KEY (workspace_id, workspace_file_id)
      REFERENCES workspace_file(workspace_id, id),
    UNIQUE (workspace_id, workspace_file_id),
    UNIQUE (workspace_id, id)
);
CREATE INDEX idx_task_claim ON parse_task(status, next_retry_at, created_at);
CREATE INDEX idx_task_workspace ON parse_task(workspace_id, created_at DESC);

CREATE TABLE invoice (
    id                     uuid PRIMARY KEY,
    organization_id        uuid NOT NULL,
    workspace_id           uuid NOT NULL,
    source_file_id         uuid NOT NULL,
    submitted_by_user_id   text NOT NULL REFERENCES "user"(id),
    created_by_user_id     text NOT NULL REFERENCES "user"(id),

    invoice_num            text NOT NULL,
    invoice_code           text,
    invoice_type           text,
    invoice_type_org       text,
    invoice_kind           text, -- 规范化票种：专票/普票/卷票/全电/通行费/货运/机动车/铁路/航空（docs/12 §2.4）
    service_type           text,
    invoice_tag            text,
    invoice_date           varchar(10), -- YYYY-MM-DD
    check_code             text,
    invoice_num_digit      text,
    seller_name            text,
    seller_register_num    text,
    seller_address         text,
    seller_bank            text,
    purchaser_name         text,
    purchaser_register_num text,
    amount_fen             integer NOT NULL,
    total_amount_fen       integer,
    total_tax_fen          integer,
    amount_in_words        text,
    items_raw              text,
    commodity_count        integer,
    detail                 jsonb, -- 票种专属字段（车辆信息/通行费起讫/铁路乘客…），按 invoice_kind 解释（docs/12 §3）
    ocr_raw_json           text NOT NULL, -- 完整原始响应；不转 jsonb，避免重排或丢失字面内容

    category               text NOT NULL DEFAULT '未分类',
    category_source        text,               -- 分类结论来源：service_type / rule:user|workspace|organization / model:*
    status                 text NOT NULL DEFAULT 'parsed',
                            -- parsed / archived / in_batch / reimbursed
    batch_id               uuid,
    batch_order            integer,
    archive_path           text, -- 交付包内虚拟路径（docs/03 §10），物理文件不动
    user_remark            text,
    is_manual_edited       boolean NOT NULL DEFAULT false,
    created_at             timestamptz NOT NULL,
    updated_at             timestamptz NOT NULL,
    deleted_at             timestamptz, -- 软删；列表过滤 deleted_at IS NULL
    FOREIGN KEY (organization_id, workspace_id)
      REFERENCES workspace(organization_id, id),
    FOREIGN KEY (workspace_id, source_file_id)
      REFERENCES workspace_file(workspace_id, id),
    UNIQUE (workspace_id, id),
    UNIQUE (organization_id, id)
);

CREATE TABLE reimburse_batch (
    id                 uuid PRIMARY KEY,
    workspace_id       uuid NOT NULL REFERENCES workspace(id),
    seq                integer NOT NULL, -- 工作区内建批序号，仅用于路径前缀稳定
    batch_name         text NOT NULL,
    total_limit_fen    integer,          -- 目标额度（分）：智能组单提示，不硬拦截
    status             text NOT NULL DEFAULT 'collecting',
                       -- collecting / paid（归集中 → 已打款单步终态，无审批环节）
    kind               text NOT NULL DEFAULT 'daily',
                       -- daily / trip / fixed_asset（日常报销 / 出差 / 固定资产，docs/02 §4）
    note               text,
    created_by_user_id text NOT NULL REFERENCES "user"(id),
    created_at         timestamptz NOT NULL,
    updated_at         timestamptz NOT NULL,
    UNIQUE (workspace_id, id)
);

ALTER TABLE invoice
  ADD CONSTRAINT fk_invoice_batch_same_workspace
  FOREIGN KEY (workspace_id, batch_id)
  REFERENCES reimburse_batch(workspace_id, id);

CREATE TABLE attachment (
    id                 uuid PRIMARY KEY,
    workspace_id       uuid NOT NULL REFERENCES workspace(id),
    invoice_id         uuid,
    workspace_file_id  uuid NOT NULL,
    status             text NOT NULL DEFAULT 'matched', -- matched / unmatched
    attachment_type    text NOT NULL DEFAULT 'other',
    source             text NOT NULL DEFAULT 'upload', -- mail / upload / ocr
    ocr_raw_text       text,
    ocr_raw_json       jsonb,                  -- 结构化端点完整原始返回（红线 5）
    documents          jsonb,                  -- 归一化 RecognizedDocument[]（判别键 kind；混贴多张多项，docs/12 §3）
    receipt_kind       text,                   -- 主文档种类（docs/12 §3）；发票时为 null
    amount_fen         integer,                -- 锚点金额（分）：金额精确相等才进候选集
    match_detail       jsonb,
    note               text,
    created_by_user_id text NOT NULL REFERENCES "user"(id),
    created_at         timestamptz NOT NULL,
    deleted_at         timestamptz,            -- 软删：删附件或删主发票时置位；同文件重传可恢复
    FOREIGN KEY (workspace_id, workspace_file_id)
      REFERENCES workspace_file(workspace_id, id),
    UNIQUE (workspace_id, id)
);

ALTER TABLE attachment
  ADD CONSTRAINT fk_attachment_invoice_same_workspace
  FOREIGN KEY (workspace_id, invoice_id)
  REFERENCES invoice(workspace_id, id);

ALTER TABLE parse_task
  ADD CONSTRAINT fk_task_invoice_same_workspace
  FOREIGN KEY (workspace_id, invoice_id)
  REFERENCES invoice(workspace_id, id),
  ADD CONSTRAINT fk_task_attachment_same_workspace
  FOREIGN KEY (workspace_id, attachment_id)
  REFERENCES attachment(workspace_id, id);

CREATE VIEW v_batch_summary WITH (security_invoker = true) AS
SELECT b.workspace_id, b.id, b.batch_name, b.status,
       COUNT(i.id) AS invoice_count,
       COALESCE(SUM(i.amount_fen), 0) AS current_total_fen
FROM reimburse_batch b
LEFT JOIN invoice i ON i.batch_id = b.id
                   AND i.workspace_id = b.workspace_id
                   AND i.deleted_at IS NULL
                   AND i.status = 'in_batch'
GROUP BY b.workspace_id, b.id, b.batch_name, b.status;

CREATE TABLE export_job (
    id                   uuid PRIMARY KEY,
    workspace_id         uuid NOT NULL REFERENCES workspace(id),
    batch_id             uuid NOT NULL,
    kind                 text NOT NULL,    -- xlsx / pdf / zip
    status               text NOT NULL DEFAULT 'processing',
                         -- processing / done / failed
    requested_by_user_id text NOT NULL REFERENCES "user"(id),
    error_code           text,
    error_message        text,
    result_file_id       uuid,             -- 产物 workspace_file（done 时非空）
    finished_at          timestamptz,
    created_at           timestamptz NOT NULL,
    updated_at           timestamptz NOT NULL,
    UNIQUE (workspace_id, id),
    FOREIGN KEY (workspace_id, batch_id)
      REFERENCES reimburse_batch(workspace_id, id),
    FOREIGN KEY (workspace_id, result_file_id)
      REFERENCES workspace_file(workspace_id, id)
);
CREATE INDEX idx_export_job_batch ON export_job(workspace_id, batch_id, created_at DESC);

CREATE TABLE invoice_dedup_key (
    organization_id   uuid NOT NULL REFERENCES organization(id),
    invoice_num_norm  text NOT NULL,
    invoice_code_norm text NOT NULL DEFAULT '',
    invoice_id        uuid NOT NULL UNIQUE,
    created_at        timestamptz NOT NULL,
    PRIMARY KEY (organization_id, invoice_num_norm, invoice_code_norm)
);

ALTER TABLE invoice_dedup_key
  ADD CONSTRAINT fk_dedup_invoice_same_organization
  FOREIGN KEY (organization_id, invoice_id)
  REFERENCES invoice(organization_id, id) ON DELETE CASCADE;

CREATE TABLE notification (
    id                 uuid PRIMARY KEY,
    recipient_user_id  text NOT NULL REFERENCES "user"(id),
    workspace_id       uuid REFERENCES workspace(id) ON DELETE SET NULL,
    level              text NOT NULL DEFAULT 'info',  -- info / warning / critical
    key                text NOT NULL,                 -- 聚合键（hub 10 分钟窗口去重）
    title              text NOT NULL,
    body               text NOT NULL,
    read_at            timestamptz,
    created_at         timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_notification_recipient ON notification(recipient_user_id, created_at DESC);

ALTER TABLE notification ENABLE ROW LEVEL SECURITY;
ALTER TABLE notification FORCE ROW LEVEL SECURITY;
-- 个人维度：收件人本人可读写自己的通知；写入由 worker 服务角色完成
CREATE POLICY notification_recipient_self ON notification
USING (app_is_service_role() OR recipient_user_id = current_setting('app.user_id', true));

ALTER TABLE invoice ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoice FORCE ROW LEVEL SECURITY;

CREATE POLICY invoice_workspace_member ON invoice
USING (EXISTS (
  SELECT 1 FROM workspace_member m
  WHERE m.workspace_id = invoice.workspace_id
    AND m.user_id = current_setting('app.user_id', true)
    AND m.status = 'active'
));

CREATE TABLE access_audit (
    id                  uuid PRIMARY KEY,
    actor_user_id       text NOT NULL REFERENCES "user"(id),
    target_workspace_id uuid NOT NULL REFERENCES workspace(id),
    action              text NOT NULL,
    resource_type       text,
    resource_id         text,
    reason              text NOT NULL,
    created_at          timestamptz NOT NULL
);
CREATE INDEX idx_access_audit_workspace
  ON access_audit(target_workspace_id, created_at DESC);

CREATE TABLE usage_event (
    id              uuid PRIMARY KEY,
    organization_id uuid NOT NULL,
    workspace_id    uuid NOT NULL,
    user_id         text REFERENCES "user"(id) ON DELETE SET NULL,
    provider        text NOT NULL,            -- baidu / jev / clef / ddddocr
    operation       text NOT NULL,            -- ocr.vat_invoice / model.systemone ...
    unit_kind       text NOT NULL,            -- call / input_token / output_token
    units           integer NOT NULL DEFAULT 1,
    outcome         text NOT NULL DEFAULT 'success', -- success / failed / blocked
    source_type     text,                     -- parse_task / invoice_list ...
    source_id       text,
    dedupe_key      text NOT NULL UNIQUE,     -- 幂等：重复上报只记一次
    cost_micros     bigint,                   -- 供应商成本（最小货币单位）
    price_version   text,
    captured_at     timestamptz NOT NULL DEFAULT now(),
    FOREIGN KEY (organization_id, workspace_id)
      REFERENCES workspace(organization_id, id)
);
CREATE INDEX idx_usage_event_org ON usage_event(organization_id, captured_at);
CREATE INDEX idx_usage_event_workspace ON usage_event(workspace_id, captured_at);
CREATE INDEX idx_usage_event_provider ON usage_event(provider, operation, captured_at);

CREATE TABLE provider_price (
    id          uuid PRIMARY KEY,
    provider    text NOT NULL,
    operation   text NOT NULL,
    unit_kind   text NOT NULL,
    currency    text NOT NULL DEFAULT 'CNY',
    cost_micros bigint NOT NULL,
    valid_from  timestamptz NOT NULL,
    valid_to    timestamptz,
    UNIQUE (provider, operation, unit_kind, currency, valid_from)
);

CREATE TABLE usage_entitlement (
    id              uuid PRIMARY KEY,
    organization_id uuid NOT NULL REFERENCES organization(id) ON DELETE CASCADE,
    provider        text NOT NULL,
    period          varchar(7) NOT NULL,      -- 计费周期 YYYY-MM（UTC）
    included_units  integer NOT NULL,
    used_units      integer NOT NULL DEFAULT 0, -- reserve 事务内原子自增
    overage_policy  text NOT NULL DEFAULT 'block', -- block / allow
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL,
    UNIQUE (organization_id, provider, period)
);