mirror of
https://github.com/multica-ai/multica.git
synced 2026-07-17 07:09:53 +02:00
48 lines
2.6 KiB
SQL
48 lines
2.6 KiB
SQL
-- Custom issue properties (MUL-4463).
|
|
--
|
|
-- Two-part model:
|
|
-- 1. issue_property — workspace-level property definitions (the catalog),
|
|
-- structurally a sibling of issue_label.
|
|
-- 2. issue.properties — JSONB value bag on each issue keyed by property
|
|
-- definition UUID, structurally a sibling of issue.metadata. Values are
|
|
-- typed per the definition and validated in the handler; keys are UUIDs
|
|
-- so definition renames never require value migrations.
|
|
--
|
|
-- Definitions are archived (archived_at), never deleted — historical values
|
|
-- stay resolvable. The active-definition cap (20/workspace) and per-type value
|
|
-- validation live in the handler.
|
|
|
|
CREATE TABLE issue_property (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL REFERENCES workspace(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
type TEXT NOT NULL CHECK (type IN ('text', 'number', 'select', 'multi_select', 'date', 'checkbox', 'url')),
|
|
description TEXT NOT NULL DEFAULT '',
|
|
-- select / multi_select: {"options": [{"id", "name", "color"}]}; {} for other types.
|
|
config JSONB NOT NULL DEFAULT '{}'::jsonb CHECK (jsonb_typeof(config) = 'object'),
|
|
position FLOAT NOT NULL DEFAULT 0,
|
|
archived_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_issue_property_ws_name ON issue_property (workspace_id, LOWER(name));
|
|
CREATE INDEX idx_issue_property_workspace ON issue_property (workspace_id);
|
|
|
|
ALTER TABLE issue ADD COLUMN properties JSONB NOT NULL DEFAULT '{}'::jsonb;
|
|
-- NOT VALID + VALIDATE keeps the ACCESS EXCLUSIVE lock instantaneous; the
|
|
-- validation pass then runs under SHARE UPDATE EXCLUSIVE without blocking
|
|
-- writes (zero-downtime deploys, review round 3).
|
|
ALTER TABLE issue ADD CONSTRAINT issue_properties_is_object
|
|
CHECK (jsonb_typeof(properties) = 'object') NOT VALID;
|
|
ALTER TABLE issue VALIDATE CONSTRAINT issue_properties_is_object;
|
|
-- Larger than metadata's 8KB: multi_select arrays and text values are bigger
|
|
-- than metadata primitives, but the bag is still bounded by the definition cap.
|
|
ALTER TABLE issue ADD CONSTRAINT issue_properties_size_limit
|
|
CHECK (pg_column_size(properties) <= 16384) NOT VALID;
|
|
ALTER TABLE issue VALIDATE CONSTRAINT issue_properties_size_limit;
|
|
-- The GIN index on issue.properties lives in the follow-up migration
|
|
-- (192_issue_properties_gin_index): CREATE INDEX CONCURRENTLY cannot share a
|
|
-- migration with other statements, and a plain CREATE INDEX would lock writes
|
|
-- on the hot issue table for the duration of the build.
|