Jay Brown
40 lines
1.9 KiB
SQL
40 lines
1.9 KiB
SQL
-- Mutable metadata feature, milestone 2.1: document_custom_metadata table.
|
|
-- Stores every historical version of a document's custom metadata payload
|
|
-- against its bound schema (schema is looked up via documents.custom_schema_id
|
|
-- at read time, not stored here).
|
|
--
|
|
-- See plan Section 4.3 of plans/mutable.metadata.plan.combo.v4.md for the
|
|
-- canonical definition.
|
|
--
|
|
-- v4 simplifications compared to v3:
|
|
-- 1. No `schema_id` column. The binding lives on the document row
|
|
-- (documents.custom_schema_id); storing it again here would allow the
|
|
-- two copies to drift. Reads join through documents to
|
|
-- client_metadata_schemas to decorate the response.
|
|
-- 2. No `current_document_custom_metadata` view. The "current" row is
|
|
-- fetched with a simple ORDER BY version DESC LIMIT 1 query backed by
|
|
-- idx_dcm_doc_version_desc.
|
|
-- 3. ON DELETE CASCADE on the document_id FK replaces v3's explicit
|
|
-- delete-cascade workstream: dropping a document also removes its
|
|
-- metadata history automatically.
|
|
|
|
CREATE TABLE document_custom_metadata (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v7(),
|
|
document_id uuid NOT NULL,
|
|
metadata jsonb NOT NULL,
|
|
version int NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT NOW(),
|
|
created_by varchar(255) NOT NULL,
|
|
|
|
CONSTRAINT pk_document_custom_metadata PRIMARY KEY (id),
|
|
CONSTRAINT uq_doc_custom_metadata_version UNIQUE (document_id, version),
|
|
CONSTRAINT fk_dcm_document_id FOREIGN KEY (document_id)
|
|
REFERENCES documents(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- The single index v4 needs on this table: backs the "current metadata"
|
|
-- lookup (ORDER BY version DESC LIMIT 1). v3 also defined idx_dcm_schema_id;
|
|
-- v4 intentionally omits it because the schema_id column does not exist.
|
|
CREATE INDEX idx_dcm_doc_version_desc
|
|
ON document_custom_metadata(document_id, version DESC);
|