wangsli
f527633716
P0 MVP： - 认证：login/refresh/logout/forgot/reset + tokenVersion - RBAC：角色到权限映射 + requirePermission - 发票池：upload/bind/signed-url + 判重（VALID_003/VALID_005） - 报销：create/view/submit + 单号生成 - 工作流：approve + department_manager 解析 P1 扩展： - 动作：reject/return/withdraw（含 comment） - transfer：任务转审 + 链路上限 3 - pay：approved -> paid + 发票归档 used_complete - 动态审批链：approval_rules 驱动 回归脚本： - smoke-test.sh / regression-dedup.sh / regression-workflow-actions.sh
696 行
25 KiB
SQL
696 行
25 KiB
SQL
-- CreateEnum
|
||
CREATE TYPE "RoleCode" AS ENUM ('employee', 'manager', 'ceo', 'finance', 'finance_mgr', 'administration', 'hr', 'admin');
|
||
|
||
-- CreateEnum
|
||
CREATE TYPE "EmployeeStatus" AS ENUM ('active', 'archived');
|
||
|
||
-- CreateEnum
|
||
CREATE TYPE "DelegationGrantScope" AS ENUM ('reimbursement_submit', 'all');
|
||
|
||
-- CreateEnum
|
||
CREATE TYPE "ReimbursementStatus" AS ENUM ('draft', 'reviewing', 'approved', 'paid', 'rejected', 'cancelled');
|
||
|
||
-- CreateEnum
|
||
CREATE TYPE "InvoicePoolStatus" AS ENUM ('pending_ocr', 'ocr_failed', 'available', 'bound', 'archived');
|
||
|
||
-- CreateEnum
|
||
CREATE TYPE "InvoiceArchivedReason" AS ENUM ('used_complete', 'unused_timeout', 'manual');
|
||
|
||
-- CreateEnum
|
||
CREATE TYPE "OcrProvider" AS ENUM ('tencent');
|
||
|
||
-- CreateEnum
|
||
CREATE TYPE "OcrType" AS ENUM ('vat', 'train', 'flight', 'taxi', 'general');
|
||
|
||
-- CreateEnum
|
||
CREATE TYPE "DocumentResetPeriod" AS ENUM ('none', 'daily', 'monthly', 'yearly');
|
||
|
||
-- CreateEnum
|
||
CREATE TYPE "WorkflowInstanceStatus" AS ENUM ('pending', 'approved', 'rejected', 'cancelled', 'terminated');
|
||
|
||
-- CreateEnum
|
||
CREATE TYPE "WorkflowTaskStatus" AS ENUM ('pending', 'completed', 'rejected', 'cancelled', 'skipped');
|
||
|
||
-- CreateEnum
|
||
CREATE TYPE "WorkflowTaskOutcome" AS ENUM ('approved', 'rejected', 'returned', 'start', 'transfer', 'skipped');
|
||
|
||
-- CreateEnum
|
||
CREATE TYPE "ApprovalTransferStatus" AS ENUM ('active', 'cancelled');
|
||
|
||
-- CreateEnum
|
||
CREATE TYPE "ExpenseCategory" AS ENUM ('travel', 'rd', 'production', 'office', 'business', 'assets', 'other');
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "departments" (
|
||
"id" UUID NOT NULL,
|
||
"name" TEXT NOT NULL,
|
||
"parent_id" UUID,
|
||
"archived_at" TIMESTAMP(3),
|
||
"archived_by" UUID,
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"created_by" UUID,
|
||
"updated_at" TIMESTAMP(3) NOT NULL,
|
||
"updated_by" UUID,
|
||
|
||
CONSTRAINT "departments_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "employees" (
|
||
"id" UUID NOT NULL,
|
||
"employee_no" TEXT NOT NULL,
|
||
"name" TEXT NOT NULL,
|
||
"phone" TEXT NOT NULL,
|
||
"email" TEXT NOT NULL,
|
||
"department_id" UUID NOT NULL,
|
||
"title" TEXT NOT NULL,
|
||
"direct_manager_id" UUID NOT NULL,
|
||
"hire_date" DATE NOT NULL,
|
||
"status" "EmployeeStatus" NOT NULL DEFAULT 'active',
|
||
"bank_account_encrypted" TEXT NOT NULL,
|
||
"bank_name" TEXT NOT NULL,
|
||
"id_number_encrypted" TEXT,
|
||
"password_hash" TEXT NOT NULL,
|
||
"token_version" INTEGER NOT NULL DEFAULT 0,
|
||
"archived_at" TIMESTAMP(3),
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"created_by" UUID,
|
||
"updated_at" TIMESTAMP(3) NOT NULL,
|
||
"updated_by" UUID,
|
||
|
||
CONSTRAINT "employees_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "employee_roles" (
|
||
"employee_id" UUID NOT NULL,
|
||
"role_code" "RoleCode" NOT NULL,
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"created_by" UUID,
|
||
|
||
CONSTRAINT "employee_roles_pkey" PRIMARY KEY ("employee_id","role_code")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "delegation_grants" (
|
||
"id" UUID NOT NULL,
|
||
"principal_employee_id" UUID NOT NULL,
|
||
"delegate_employee_id" UUID NOT NULL,
|
||
"scope" "DelegationGrantScope" NOT NULL,
|
||
"valid_from" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"valid_to" TIMESTAMP(3),
|
||
"revoked_at" TIMESTAMP(3),
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"created_by" UUID,
|
||
|
||
CONSTRAINT "delegation_grants_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "auth_sessions" (
|
||
"id" UUID NOT NULL,
|
||
"employee_id" UUID NOT NULL,
|
||
"refresh_token_hash" TEXT NOT NULL,
|
||
"device_fingerprint" TEXT NOT NULL,
|
||
"ip_address" TEXT NOT NULL,
|
||
"user_agent" TEXT NOT NULL,
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"last_seen_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"expires_at" TIMESTAMP(3) NOT NULL,
|
||
"revoked_at" TIMESTAMP(3),
|
||
|
||
CONSTRAINT "auth_sessions_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "audit_logs" (
|
||
"id" UUID NOT NULL,
|
||
"request_id" TEXT NOT NULL,
|
||
"actor_id" UUID NOT NULL,
|
||
"action" TEXT NOT NULL,
|
||
"target_type" TEXT NOT NULL,
|
||
"target_id" UUID,
|
||
"ip_address" TEXT,
|
||
"user_agent" TEXT,
|
||
"extra" JSONB NOT NULL,
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT "audit_logs_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "expense_types" (
|
||
"id" UUID NOT NULL,
|
||
"code" TEXT NOT NULL,
|
||
"name" TEXT NOT NULL,
|
||
"description" TEXT,
|
||
"category" "ExpenseCategory" NOT NULL,
|
||
"sort_order" INTEGER NOT NULL,
|
||
"account_code_default" TEXT,
|
||
"is_system_default" BOOLEAN NOT NULL DEFAULT false,
|
||
"enabled" BOOLEAN NOT NULL DEFAULT true,
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"created_by" UUID,
|
||
"updated_at" TIMESTAMP(3) NOT NULL,
|
||
"updated_by" UUID,
|
||
|
||
CONSTRAINT "expense_types_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "document_sequences" (
|
||
"document_type" TEXT NOT NULL,
|
||
"prefix" TEXT NOT NULL,
|
||
"date_format" TEXT NOT NULL,
|
||
"separator" TEXT NOT NULL,
|
||
"number_length" INTEGER NOT NULL DEFAULT 4,
|
||
"reset_period" "DocumentResetPeriod" NOT NULL,
|
||
"current_number" INTEGER NOT NULL DEFAULT 0,
|
||
"last_reset_date" TIMESTAMP(3),
|
||
"updated_at" TIMESTAMP(3) NOT NULL,
|
||
"updated_by" UUID,
|
||
|
||
CONSTRAINT "document_sequences_pkey" PRIMARY KEY ("document_type")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "reimbursement_forms" (
|
||
"id" UUID NOT NULL,
|
||
"form_number" TEXT,
|
||
"submitter_id" UUID NOT NULL,
|
||
"owner_id" UUID NOT NULL,
|
||
"department_id" UUID NOT NULL,
|
||
"reimbursement_date" DATE NOT NULL,
|
||
"total_amount" DECIMAL(14,2) NOT NULL DEFAULT 0,
|
||
"status" "ReimbursementStatus" NOT NULL DEFAULT 'draft',
|
||
"description" TEXT,
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"created_by" UUID,
|
||
"updated_at" TIMESTAMP(3) NOT NULL,
|
||
"updated_by" UUID,
|
||
|
||
CONSTRAINT "reimbursement_forms_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "reimbursement_items" (
|
||
"id" UUID NOT NULL,
|
||
"reimbursement_id" UUID NOT NULL,
|
||
"expense_type_id" UUID NOT NULL,
|
||
"expense_date" DATE NOT NULL,
|
||
"amount" DECIMAL(14,2) NOT NULL,
|
||
"description" TEXT NOT NULL,
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"created_by" UUID,
|
||
"updated_at" TIMESTAMP(3) NOT NULL,
|
||
"updated_by" UUID,
|
||
|
||
CONSTRAINT "reimbursement_items_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "ocr_results" (
|
||
"id" UUID NOT NULL,
|
||
"file_sha256" TEXT NOT NULL,
|
||
"ocr_provider" "OcrProvider" NOT NULL DEFAULT 'tencent',
|
||
"ocr_type" "OcrType" NOT NULL,
|
||
"result_json" JSONB NOT NULL,
|
||
"confidence" INTEGER,
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT "ocr_results_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "invoice_pool" (
|
||
"id" UUID NOT NULL,
|
||
"owner_employee_id" UUID NOT NULL,
|
||
"uploaded_by_employee_id" UUID NOT NULL,
|
||
"storage_path" TEXT NOT NULL,
|
||
"original_name" TEXT NOT NULL,
|
||
"mime_type" TEXT NOT NULL,
|
||
"file_size" INTEGER NOT NULL,
|
||
"file_sha256" TEXT NOT NULL,
|
||
"status" "InvoicePoolStatus" NOT NULL DEFAULT 'pending_ocr',
|
||
"archived_reason" "InvoiceArchivedReason",
|
||
"archived_at" TIMESTAMP(3),
|
||
"linked_reimbursement_id" UUID,
|
||
"linked_item_id" UUID,
|
||
"remarks" TEXT,
|
||
"ocr_provider" "OcrProvider" NOT NULL DEFAULT 'tencent',
|
||
"ocr_type" "OcrType" NOT NULL DEFAULT 'general',
|
||
"ocr_result_id" UUID,
|
||
"ocr_attempts" INTEGER NOT NULL DEFAULT 0,
|
||
"ocr_last_error" TEXT,
|
||
"ocr_completed_at" TIMESTAMP(3),
|
||
"invoice_code" TEXT,
|
||
"invoice_number" TEXT,
|
||
"invoice_date" DATE,
|
||
"seller_name" TEXT,
|
||
"buyer_name" TEXT,
|
||
"amount" DECIMAL(14,2),
|
||
"tax_amount" DECIMAL(14,2),
|
||
"total_amount" DECIMAL(14,2),
|
||
"is_duplicate_override" BOOLEAN NOT NULL DEFAULT false,
|
||
"override_ref_form_number" TEXT,
|
||
"override_reason" TEXT,
|
||
"overridden_by" UUID,
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"created_by" UUID,
|
||
"updated_at" TIMESTAMP(3) NOT NULL,
|
||
"updated_by" UUID,
|
||
|
||
CONSTRAINT "invoice_pool_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "workflow_definitions" (
|
||
"id" UUID NOT NULL,
|
||
"name" TEXT NOT NULL,
|
||
"entity_type" TEXT NOT NULL,
|
||
"definition_json" JSONB NOT NULL,
|
||
"version" INTEGER NOT NULL,
|
||
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"created_by" UUID,
|
||
|
||
CONSTRAINT "workflow_definitions_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "workflow_instances" (
|
||
"id" UUID NOT NULL,
|
||
"workflow_id" UUID NOT NULL,
|
||
"entity_type" TEXT NOT NULL,
|
||
"entity_id" UUID NOT NULL,
|
||
"submitter_id" UUID NOT NULL,
|
||
"current_status" "WorkflowInstanceStatus" NOT NULL DEFAULT 'pending',
|
||
"current_step_id" TEXT NOT NULL,
|
||
"context_data" JSONB NOT NULL,
|
||
"started_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"completed_at" TIMESTAMP(3),
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"created_by" UUID,
|
||
"updated_at" TIMESTAMP(3) NOT NULL,
|
||
"updated_by" UUID,
|
||
"context_department_id" UUID,
|
||
|
||
CONSTRAINT "workflow_instances_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "workflow_tasks" (
|
||
"id" UUID NOT NULL,
|
||
"instance_id" UUID NOT NULL,
|
||
"step_id" TEXT NOT NULL,
|
||
"step_name" TEXT NOT NULL,
|
||
"assignee_id" UUID,
|
||
"assignee_role" "RoleCode",
|
||
"status" "WorkflowTaskStatus" NOT NULL DEFAULT 'pending',
|
||
"outcome" "WorkflowTaskOutcome",
|
||
"comment" TEXT,
|
||
"due_date" TIMESTAMP(3),
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"completed_at" TIMESTAMP(3),
|
||
"completed_by" UUID,
|
||
|
||
CONSTRAINT "workflow_tasks_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "workflow_history" (
|
||
"id" UUID NOT NULL,
|
||
"instance_id" UUID NOT NULL,
|
||
"task_id" UUID,
|
||
"actor_id" UUID NOT NULL,
|
||
"action" TEXT NOT NULL,
|
||
"comment" TEXT,
|
||
"payload" JSONB,
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
CONSTRAINT "workflow_history_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "approval_rules" (
|
||
"id" UUID NOT NULL,
|
||
"name" TEXT NOT NULL,
|
||
"min_amount" DECIMAL(14,2) NOT NULL,
|
||
"max_amount" DECIMAL(14,2),
|
||
"level1_role" "RoleCode" NOT NULL,
|
||
"level2_role" "RoleCode",
|
||
"level3_role" "RoleCode",
|
||
"enabled" BOOLEAN NOT NULL DEFAULT true,
|
||
"priority" INTEGER NOT NULL,
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"created_by" UUID,
|
||
"updated_at" TIMESTAMP(3) NOT NULL,
|
||
"updated_by" UUID,
|
||
|
||
CONSTRAINT "approval_rules_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateTable
|
||
CREATE TABLE "approval_transfers" (
|
||
"id" UUID NOT NULL,
|
||
"reimbursement_id" UUID NOT NULL,
|
||
"from_approver_id" UUID NOT NULL,
|
||
"to_approver_id" UUID NOT NULL,
|
||
"reason" TEXT NOT NULL,
|
||
"status" "ApprovalTransferStatus" NOT NULL DEFAULT 'active',
|
||
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
"created_by" UUID,
|
||
|
||
CONSTRAINT "approval_transfers_pkey" PRIMARY KEY ("id")
|
||
);
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "departments_parent_id_idx" ON "departments"("parent_id");
|
||
|
||
-- CreateIndex
|
||
CREATE UNIQUE INDEX "employees_employee_no_key" ON "employees"("employee_no");
|
||
|
||
-- CreateIndex
|
||
CREATE UNIQUE INDEX "employees_phone_key" ON "employees"("phone");
|
||
|
||
-- CreateIndex
|
||
CREATE UNIQUE INDEX "employees_email_key" ON "employees"("email");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "employees_department_id_idx" ON "employees"("department_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "employees_direct_manager_id_idx" ON "employees"("direct_manager_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "delegation_grants_principal_employee_id_idx" ON "delegation_grants"("principal_employee_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "delegation_grants_delegate_employee_id_idx" ON "delegation_grants"("delegate_employee_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "auth_sessions_employee_id_idx" ON "auth_sessions"("employee_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "auth_sessions_expires_at_idx" ON "auth_sessions"("expires_at");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "auth_sessions_revoked_at_idx" ON "auth_sessions"("revoked_at");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "audit_logs_request_id_idx" ON "audit_logs"("request_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "audit_logs_actor_id_created_at_idx" ON "audit_logs"("actor_id", "created_at");
|
||
|
||
-- CreateIndex
|
||
CREATE UNIQUE INDEX "expense_types_code_key" ON "expense_types"("code");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "expense_types_enabled_sort_order_idx" ON "expense_types"("enabled", "sort_order");
|
||
|
||
-- CreateIndex
|
||
CREATE UNIQUE INDEX "reimbursement_forms_form_number_key" ON "reimbursement_forms"("form_number");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "reimbursement_forms_owner_id_idx" ON "reimbursement_forms"("owner_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "reimbursement_forms_submitter_id_idx" ON "reimbursement_forms"("submitter_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "reimbursement_forms_department_id_idx" ON "reimbursement_forms"("department_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "reimbursement_forms_status_idx" ON "reimbursement_forms"("status");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "reimbursement_forms_reimbursement_date_idx" ON "reimbursement_forms"("reimbursement_date");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "reimbursement_items_reimbursement_id_idx" ON "reimbursement_items"("reimbursement_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "reimbursement_items_expense_type_id_idx" ON "reimbursement_items"("expense_type_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "reimbursement_items_expense_date_idx" ON "reimbursement_items"("expense_date");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "ocr_results_file_sha256_idx" ON "ocr_results"("file_sha256");
|
||
|
||
-- CreateIndex
|
||
CREATE UNIQUE INDEX "ocr_results_file_sha256_ocr_provider_ocr_type_key" ON "ocr_results"("file_sha256", "ocr_provider", "ocr_type");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "invoice_pool_owner_employee_id_idx" ON "invoice_pool"("owner_employee_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "invoice_pool_uploaded_by_employee_id_idx" ON "invoice_pool"("uploaded_by_employee_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "invoice_pool_file_sha256_idx" ON "invoice_pool"("file_sha256");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "invoice_pool_invoice_code_invoice_number_idx" ON "invoice_pool"("invoice_code", "invoice_number");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "invoice_pool_status_idx" ON "invoice_pool"("status");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "invoice_pool_linked_reimbursement_id_idx" ON "invoice_pool"("linked_reimbursement_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "workflow_definitions_entity_type_is_active_idx" ON "workflow_definitions"("entity_type", "is_active");
|
||
|
||
-- CreateIndex
|
||
CREATE UNIQUE INDEX "workflow_definitions_entity_type_version_key" ON "workflow_definitions"("entity_type", "version");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "workflow_instances_workflow_id_idx" ON "workflow_instances"("workflow_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "workflow_instances_entity_type_entity_id_idx" ON "workflow_instances"("entity_type", "entity_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "workflow_instances_current_status_idx" ON "workflow_instances"("current_status");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "workflow_instances_submitter_id_idx" ON "workflow_instances"("submitter_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "workflow_instances_context_department_id_idx" ON "workflow_instances"("context_department_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "workflow_tasks_instance_id_idx" ON "workflow_tasks"("instance_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "workflow_tasks_status_idx" ON "workflow_tasks"("status");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "workflow_tasks_assignee_id_idx" ON "workflow_tasks"("assignee_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "workflow_tasks_assignee_role_idx" ON "workflow_tasks"("assignee_role");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "workflow_history_instance_id_created_at_idx" ON "workflow_history"("instance_id", "created_at");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "workflow_history_task_id_idx" ON "workflow_history"("task_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "workflow_history_actor_id_idx" ON "workflow_history"("actor_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "approval_rules_enabled_priority_idx" ON "approval_rules"("enabled", "priority");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "approval_transfers_reimbursement_id_idx" ON "approval_transfers"("reimbursement_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "approval_transfers_from_approver_id_idx" ON "approval_transfers"("from_approver_id");
|
||
|
||
-- CreateIndex
|
||
CREATE INDEX "approval_transfers_to_approver_id_idx" ON "approval_transfers"("to_approver_id");
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "departments" ADD CONSTRAINT "departments_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "departments"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "employees" ADD CONSTRAINT "employees_department_id_fkey" FOREIGN KEY ("department_id") REFERENCES "departments"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "employees" ADD CONSTRAINT "employees_direct_manager_id_fkey" FOREIGN KEY ("direct_manager_id") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "employee_roles" ADD CONSTRAINT "employee_roles_employee_id_fkey" FOREIGN KEY ("employee_id") REFERENCES "employees"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "delegation_grants" ADD CONSTRAINT "delegation_grants_principal_employee_id_fkey" FOREIGN KEY ("principal_employee_id") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "delegation_grants" ADD CONSTRAINT "delegation_grants_delegate_employee_id_fkey" FOREIGN KEY ("delegate_employee_id") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "auth_sessions" ADD CONSTRAINT "auth_sessions_employee_id_fkey" FOREIGN KEY ("employee_id") REFERENCES "employees"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "audit_logs" ADD CONSTRAINT "audit_logs_actor_id_fkey" FOREIGN KEY ("actor_id") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "reimbursement_forms" ADD CONSTRAINT "reimbursement_forms_submitter_id_fkey" FOREIGN KEY ("submitter_id") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "reimbursement_forms" ADD CONSTRAINT "reimbursement_forms_owner_id_fkey" FOREIGN KEY ("owner_id") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "reimbursement_forms" ADD CONSTRAINT "reimbursement_forms_department_id_fkey" FOREIGN KEY ("department_id") REFERENCES "departments"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "reimbursement_items" ADD CONSTRAINT "reimbursement_items_reimbursement_id_fkey" FOREIGN KEY ("reimbursement_id") REFERENCES "reimbursement_forms"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "reimbursement_items" ADD CONSTRAINT "reimbursement_items_expense_type_id_fkey" FOREIGN KEY ("expense_type_id") REFERENCES "expense_types"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "invoice_pool" ADD CONSTRAINT "invoice_pool_owner_employee_id_fkey" FOREIGN KEY ("owner_employee_id") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "invoice_pool" ADD CONSTRAINT "invoice_pool_uploaded_by_employee_id_fkey" FOREIGN KEY ("uploaded_by_employee_id") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "invoice_pool" ADD CONSTRAINT "invoice_pool_overridden_by_fkey" FOREIGN KEY ("overridden_by") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "invoice_pool" ADD CONSTRAINT "invoice_pool_ocr_result_id_fkey" FOREIGN KEY ("ocr_result_id") REFERENCES "ocr_results"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "invoice_pool" ADD CONSTRAINT "invoice_pool_linked_reimbursement_id_fkey" FOREIGN KEY ("linked_reimbursement_id") REFERENCES "reimbursement_forms"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "invoice_pool" ADD CONSTRAINT "invoice_pool_linked_item_id_fkey" FOREIGN KEY ("linked_item_id") REFERENCES "reimbursement_items"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "workflow_instances" ADD CONSTRAINT "workflow_instances_workflow_id_fkey" FOREIGN KEY ("workflow_id") REFERENCES "workflow_definitions"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "workflow_instances" ADD CONSTRAINT "workflow_instances_entity_id_fkey" FOREIGN KEY ("entity_id") REFERENCES "reimbursement_forms"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "workflow_instances" ADD CONSTRAINT "workflow_instances_submitter_id_fkey" FOREIGN KEY ("submitter_id") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "workflow_instances" ADD CONSTRAINT "workflow_instances_context_department_id_fkey" FOREIGN KEY ("context_department_id") REFERENCES "departments"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "workflow_tasks" ADD CONSTRAINT "workflow_tasks_instance_id_fkey" FOREIGN KEY ("instance_id") REFERENCES "workflow_instances"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "workflow_tasks" ADD CONSTRAINT "workflow_tasks_assignee_id_fkey" FOREIGN KEY ("assignee_id") REFERENCES "employees"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "workflow_tasks" ADD CONSTRAINT "workflow_tasks_completed_by_fkey" FOREIGN KEY ("completed_by") REFERENCES "employees"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "workflow_history" ADD CONSTRAINT "workflow_history_instance_id_fkey" FOREIGN KEY ("instance_id") REFERENCES "workflow_instances"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "workflow_history" ADD CONSTRAINT "workflow_history_task_id_fkey" FOREIGN KEY ("task_id") REFERENCES "workflow_tasks"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "workflow_history" ADD CONSTRAINT "workflow_history_actor_id_fkey" FOREIGN KEY ("actor_id") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "approval_transfers" ADD CONSTRAINT "approval_transfers_reimbursement_id_fkey" FOREIGN KEY ("reimbursement_id") REFERENCES "reimbursement_forms"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "approval_transfers" ADD CONSTRAINT "approval_transfers_from_approver_id_fkey" FOREIGN KEY ("from_approver_id") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- AddForeignKey
|
||
ALTER TABLE "approval_transfers" ADD CONSTRAINT "approval_transfers_to_approver_id_fkey" FOREIGN KEY ("to_approver_id") REFERENCES "employees"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- Custom constraints & partial unique indexes (P0 requirements)
|
||
-- ------------------------------------------------------------
|
||
|
||
-- approval_transfers: 转审 reason≥10（P0）
|
||
ALTER TABLE "approval_transfers"
|
||
ADD CONSTRAINT "approval_transfers_reason_len_chk"
|
||
CHECK (char_length("reason") >= 10);
|
||
|
||
-- invoice_pool: 判重例外字段完整性（P0）
|
||
ALTER TABLE "invoice_pool"
|
||
ADD CONSTRAINT "invoice_pool_duplicate_override_chk"
|
||
CHECK (
|
||
"is_duplicate_override" = false
|
||
OR (
|
||
"override_ref_form_number" IS NOT NULL
|
||
AND "override_reason" IS NOT NULL
|
||
AND char_length("override_reason") >= 10
|
||
AND "overridden_by" IS NOT NULL
|
||
)
|
||
);
|
||
|
||
-- invoice_pool: linked_item_id 只能在存在 linked_reimbursement_id 时出现（P0）
|
||
ALTER TABLE "invoice_pool"
|
||
ADD CONSTRAINT "invoice_pool_linked_item_requires_reimbursement_chk"
|
||
CHECK ("linked_item_id" IS NULL OR "linked_reimbursement_id" IS NOT NULL);
|
||
|
||
-- invoice_pool: 状态机不变量（P0，见规格书 4.4.2/4.4.3）
|
||
ALTER TABLE "invoice_pool"
|
||
ADD CONSTRAINT "invoice_pool_status_invariants_chk"
|
||
CHECK (
|
||
(
|
||
"status" IN ('pending_ocr', 'ocr_failed', 'available')
|
||
AND "linked_reimbursement_id" IS NULL
|
||
AND "linked_item_id" IS NULL
|
||
AND "archived_reason" IS NULL
|
||
AND "archived_at" IS NULL
|
||
)
|
||
OR (
|
||
"status" = 'bound'
|
||
AND "linked_reimbursement_id" IS NOT NULL
|
||
AND "archived_reason" IS NULL
|
||
AND "archived_at" IS NULL
|
||
)
|
||
OR (
|
||
"status" = 'archived'
|
||
AND "archived_reason" IS NOT NULL
|
||
AND "archived_at" IS NOT NULL
|
||
AND (
|
||
(
|
||
"archived_reason" = 'used_complete'
|
||
AND "linked_reimbursement_id" IS NOT NULL
|
||
)
|
||
OR (
|
||
"archived_reason" IN ('unused_timeout', 'manual')
|
||
AND "linked_reimbursement_id" IS NULL
|
||
)
|
||
)
|
||
)
|
||
);
|
||
|
||
-- invoice_pool: 全局双策略判重（只对已使用历史生效；P0 定案）
|
||
CREATE UNIQUE INDEX "invoice_pool_file_sha256_dedup_ux"
|
||
ON "invoice_pool" ("file_sha256")
|
||
WHERE (
|
||
(
|
||
"status" = 'bound'
|
||
OR ("status" = 'archived' AND "archived_reason" = 'used_complete')
|
||
)
|
||
AND "is_duplicate_override" = false
|
||
);
|
||
|
||
CREATE UNIQUE INDEX "invoice_pool_invoice_code_number_dedup_ux"
|
||
ON "invoice_pool" ("invoice_code", "invoice_number")
|
||
WHERE (
|
||
"invoice_code" IS NOT NULL
|
||
AND "invoice_number" IS NOT NULL
|
||
AND (
|
||
"status" = 'bound'
|
||
OR ("status" = 'archived' AND "archived_reason" = 'used_complete')
|
||
)
|
||
AND "is_duplicate_override" = false
|
||
);
|