从 20 秒勇气到可审计 AI：PostgresEDI 七月聚会的两条工程主线

标题：一次故障之后，我们如何让 PostgreSQL 迁移可回滚

问题：
数据库迁移经常在应用发布过程中执行，但失败后的恢复路径没有经过验证。

听众收获：
1. 识别不可逆 DDL；
2. 为迁移设计演练和回滚步骤；
3. 在 CI 中验证旧版本应用与新模式的兼容性。

证据：
一个最小演示、一次真实故障的时间线，以及迁移前后的检查命令。

docker run --name auditable-rag \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  -d pgvector/pgvector:pg16

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE document_versions (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id text NOT NULL,
    document_key text NOT NULL,
    version integer NOT NULL,
    content_hash text NOT NULL,
    UNIQUE (tenant_id, document_key, version)
);

CREATE TABLE chunks (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    document_version_id bigint NOT NULL REFERENCES document_versions(id),
    chunk_no integer NOT NULL,
    content text NOT NULL,
    embedding vector(3) NOT NULL,
    embedding_model text NOT NULL,
    UNIQUE (document_version_id, chunk_no)
);

CREATE TABLE retrieval_runs (
    id uuid PRIMARY KEY,
    tenant_id text NOT NULL,
    actor_id text NOT NULL,
    query_text text NOT NULL,
    query_embedding vector(3) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE TABLE retrieval_hits (
    run_id uuid NOT NULL REFERENCES retrieval_runs(id),
    rank bigint NOT NULL,
    chunk_id bigint NOT NULL REFERENCES chunks(id),
    distance double precision NOT NULL,
    document_hash text NOT NULL,
    embedding_model text NOT NULL,
    PRIMARY KEY (run_id, rank)
);

ALTER TABLE document_versions ENABLE ROW LEVEL SECURITY;
ALTER TABLE chunks ENABLE ROW LEVEL SECURITY;

CREATE POLICY document_tenant_policy ON document_versions
USING (tenant_id = current_setting('app.tenant_id', true));

CREATE POLICY chunk_tenant_policy ON chunks
USING (
    EXISTS (
        SELECT 1
        FROM document_versions d
        WHERE d.id = chunks.document_version_id
          AND d.tenant_id = current_setting('app.tenant_id', true)
    )
);

CREATE OR REPLACE FUNCTION retrieve_and_audit(
    p_query text,
    p_embedding vector(3),
    p_limit integer DEFAULT 3
)
RETURNS TABLE (
    run_id uuid,
    rank bigint,
    chunk_id bigint,
    content text,
    distance double precision
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_run_id uuid := gen_random_uuid();
    v_tenant_id text := current_setting('app.tenant_id', true);
    v_actor_id text := current_setting('app.actor_id', true);
BEGIN
    IF v_tenant_id IS NULL OR v_actor_id IS NULL THEN
        RAISE EXCEPTION 'app.tenant_id and app.actor_id must be set';
    END IF;

    INSERT INTO retrieval_runs (
        id, tenant_id, actor_id, query_text, query_embedding
    ) VALUES (
        v_run_id, v_tenant_id, v_actor_id, p_query, p_embedding
    );

    RETURN QUERY
    WITH selected AS MATERIALIZED (
        SELECT
            row_number() OVER (ORDER BY c.embedding <=> p_embedding) AS hit_rank,
            c.id AS hit_chunk_id,
            c.content AS hit_content,
            c.embedding <=> p_embedding AS hit_distance,
            d.content_hash,
            c.embedding_model
        FROM chunks c
        JOIN document_versions d ON d.id = c.document_version_id
        ORDER BY c.embedding <=> p_embedding
        LIMIT p_limit
    ),
    logged AS (
        INSERT INTO retrieval_hits (
            run_id, rank, chunk_id, distance,
            document_hash, embedding_model
        )
        SELECT
            v_run_id, hit_rank, hit_chunk_id, hit_distance,
            content_hash, embedding_model
        FROM selected
        RETURNING chunk_id
    )
    SELECT
        v_run_id,
        s.hit_rank,
        s.hit_chunk_id,
        s.hit_content,
        s.hit_distance
    FROM selected s
    JOIN logged l ON l.chunk_id = s.hit_chunk_id
    ORDER BY s.hit_rank;
END;
$$;

INSERT INTO document_versions (
    tenant_id, document_key, version, content_hash
) VALUES
    ('bank-a', 'loan-policy', 1, 'sha256:policy-v1');

INSERT INTO chunks (
    document_version_id, chunk_no, content, embedding, embedding_model
) VALUES
    (1, 1, 'Loans above two million require committee review.', '[1,0,0]', 'demo-embedding-v1'),
    (1, 2, 'Applicants must provide verified income records.', '[0,1,0]', 'demo-embedding-v1'),
    (1, 3, 'Decisions must retain supporting evidence.', '[0,0,1]', 'demo-embedding-v1');

BEGIN;
SET LOCAL app.tenant_id = 'bank-a';
SET LOCAL app.actor_id = 'analyst-42';
SELECT * FROM retrieve_and_audit(
    'What review is required for a large loan?',
    '[0.9,0.1,0]',
    2
);
COMMIT;

TABLE retrieval_runs;
TABLE retrieval_hits;