mirror of
https://github.com/multica-ai/multica.git
synced 2026-07-26 12:35:35 +02:00
f46d5d7ba5
Co-authored-by: Eve <eve@multica-ai.local> Co-authored-by: multica-agent <github@multica.ai>
44 lines
2.3 KiB
SQL
44 lines
2.3 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,
|
|
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()
|
|
);
|
|
|
|
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;
|
|
-- All indexes live in follow-up, single-statement migrations because CREATE
|
|
-- INDEX CONCURRENTLY cannot share a migration with other statements. The GIN
|
|
-- index is in 192; the issue_property indexes are in 194 and 195.
|