Jay Brown
27 lines
1.3 KiB
SQL
27 lines
1.3 KiB
SQL
-- Mutable metadata feature, milestone 1.1: schema_status_type enum and
|
|
-- client_metadata_schemas table. See plan Section 4.1 and 4.2 of
|
|
-- plans/mutable.metadata.plan.combo.v4.md for the canonical definition.
|
|
|
|
CREATE TYPE schema_status_type AS ENUM ('active', 'superseded', 'retired');
|
|
|
|
CREATE TABLE client_metadata_schemas (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v7(),
|
|
client_id varchar(255) NOT NULL REFERENCES clients(clientId) ON DELETE CASCADE,
|
|
name varchar(255) NOT NULL,
|
|
description text,
|
|
schema_def jsonb NOT NULL,
|
|
version int NOT NULL DEFAULT 1,
|
|
status schema_status_type NOT NULL DEFAULT 'active',
|
|
created_at timestamptz NOT NULL DEFAULT NOW(),
|
|
created_by varchar(255) NOT NULL,
|
|
|
|
CONSTRAINT pk_client_metadata_schemas PRIMARY KEY (id),
|
|
CONSTRAINT uq_client_schema_name_version UNIQUE (client_id, name, version),
|
|
CONSTRAINT uq_cms_id_client UNIQUE (id, client_id),
|
|
CONSTRAINT chk_schema_def_size CHECK (pg_column_size(schema_def) <= 70000)
|
|
);
|
|
|
|
CREATE INDEX idx_cms_client_id ON client_metadata_schemas(client_id);
|
|
CREATE INDEX idx_cms_client_name ON client_metadata_schemas(client_id, name);
|
|
CREATE INDEX idx_cms_client_status ON client_metadata_schemas(client_id, status);
|