1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
-- Multi-document model under directives.
--
-- A directive is a folder that owns N documents (the user calls these
-- "directive contracts"). Each document has its own markdown body, status,
-- and may end up attached to a PR. Multiple documents can be active at the
-- same time. When a doc ships (PR raised) it transitions to 'shipped';
-- editing a shipped doc later flips it back to 'active' (handled in repo
-- update logic — wired into handlers in a later step).
--
-- Today, directives.goal IS the single contract document, so we backfill
-- one document per existing directive populated from directives.goal so the
-- existing UI keeps working.
CREATE TABLE directive_documents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
directive_id UUID NOT NULL REFERENCES directives(id) ON DELETE CASCADE,
title VARCHAR(500) NOT NULL DEFAULT '',
body TEXT NOT NULL DEFAULT '',
status VARCHAR(32) NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft', 'active', 'shipped', 'archived')),
pr_url TEXT,
pr_branch TEXT,
shipped_at TIMESTAMPTZ,
archived_at TIMESTAMPTZ,
version INTEGER NOT NULL DEFAULT 1,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_directive_documents_directive_id
ON directive_documents(directive_id);
CREATE INDEX idx_directive_documents_status
ON directive_documents(directive_id, status);
-- Backfill: one document per existing directive, copying the current goal
-- so the existing UI keeps showing what it shows today. Directives that
-- already have a PR url are seeded as 'shipped' (with shipped_at set),
-- everything else as 'active'. COALESCE protects against any unexpected
-- NULL goal rows even though the column is currently NOT NULL.
INSERT INTO directive_documents (
directive_id, title, body, status, pr_url, shipped_at
)
SELECT
d.id,
d.title,
COALESCE(d.goal, ''),
CASE WHEN d.pr_url IS NOT NULL THEN 'shipped' ELSE 'active' END,
d.pr_url,
CASE WHEN d.pr_url IS NOT NULL THEN NOW() ELSE NULL END
FROM directives d;
|
