20 lines
961 B
SQL
20 lines
961 B
SQL
-- Per-issue custom metadata: a small JSONB KV map agents use to record
|
|
-- pipeline state (PR number, pipeline_status, waiting_on, …) that doesn't
|
|
-- fit cleanly into the structured issue fields.
|
|
--
|
|
-- Application-level validation (handler) enforces key regex, key count
|
|
-- cap, and primitive-only values. The DB CHECKs below are defense-in-depth
|
|
-- against migrations or direct SQL writes that bypass the API surface.
|
|
ALTER TABLE issue ADD COLUMN metadata JSONB NOT NULL DEFAULT '{}'::jsonb;
|
|
|
|
ALTER TABLE issue ADD CONSTRAINT issue_metadata_is_object
|
|
CHECK (jsonb_typeof(metadata) = 'object');
|
|
|
|
ALTER TABLE issue ADD CONSTRAINT issue_metadata_size_limit
|
|
CHECK (pg_column_size(metadata) <= 8192);
|
|
|
|
-- GIN with jsonb_path_ops: smaller index, faster `@>` containment.
|
|
-- We only need containment queries (no `?` existence), so the path-ops
|
|
-- variant is the right trade.
|
|
CREATE INDEX idx_issue_metadata_gin ON issue USING GIN (metadata jsonb_path_ops);
|