Skip to content
Sign up

flagon-io/g1t

public
0
269 linesCodeBlameRaw
1-- Folios: what people call artifacts. Artifacts mode is one mode for docs,
2-- slides, designs and dashboards (docs/ARTIFACTS_MODE.md, section 2.2).
3-- A folio's live content is a Yjs document in its room (FolioRoom,
4-- src/folios/room.ts); these tables keep everything around it and the
5-- text rendition the room saves after each burst of edits.
6--
7-- This migration only adds tables. Docs' pages keep working on theirs
8-- until Phase 7 drops them. Keys are as in Docs: `user:<id>`,
9-- `agent:<id>`, `team:<slug>`.
10
11CREATE TABLE folios (
12 id TEXT PRIMARY KEY,
13 workspace_id TEXT NOT NULL,
14 kind TEXT NOT NULL CHECK (kind IN ('doc', 'slides', 'design', 'dashboard')),
15 title TEXT NOT NULL DEFAULT '',
16 icon TEXT,
17 cover TEXT,
18 -- Always a person: `user:<id>`. An agent's folio is owned by whoever it acted for.
19 owner TEXT NOT NULL,
20 -- NULL: the owner's Private section.
21 space_id TEXT REFERENCES spaces (id),
22 -- Only a doc is ever a parent. Children share their parent's space.
23 parent_id TEXT REFERENCES folios (id),
24 position REAL NOT NULL,
25 -- 1: follows its parent (or, at the top, its space). 0: "Only people invited".
26 inherit INTEGER NOT NULL DEFAULT 1,
27 -- The nearest of itself and its ancestors with inherit = 0 or no parent.
28 acl_root TEXT NOT NULL,
29 -- '/<top id>/…/<id>/', for subtree updates.
30 path TEXT NOT NULL,
31 general_access TEXT NOT NULL DEFAULT 'none' CHECK (general_access IN ('none', 'workspace', 'link')),
32 general_role TEXT CHECK (general_role IN ('view', 'comment', 'edit')),
33 -- NULL: the space's, or 'suggest' in Private.
34 agent_mode TEXT CHECK (agent_mode IN ('suggest', 'edit')),
35 -- The kind's text rendition: search, recall, the read view, export.
36 text TEXT NOT NULL DEFAULT '',
37 excerpt TEXT NOT NULL DEFAULT '',
38 -- Small JSON a card draws; never data values.
39 preview TEXT,
40 -- JSON { title, href }: where it was written up from.
41 source TEXT,
42 -- People already told they were mentioned in it.
43 mentioned TEXT NOT NULL DEFAULT '[]',
44 created_by TEXT NOT NULL,
45 created_at TEXT NOT NULL,
46 -- Any change: rename, move, share.
47 updated_by TEXT,
48 updated_at TEXT NOT NULL,
49 -- Content changes: "Edited 45m ago".
50 edited_by TEXT,
51 edited_at TEXT NOT NULL,
52 trashed_at TEXT,
53 trashed_by TEXT
54);
55CREATE INDEX folios_tree ON folios (workspace_id, space_id, parent_id, position);
56CREATE INDEX folios_owner ON folios (owner, edited_at) WHERE trashed_at IS NULL;
57CREATE INDEX folios_recent ON folios (workspace_id, edited_at) WHERE trashed_at IS NULL;
58CREATE INDEX folios_trashed ON folios (workspace_id, trashed_at) WHERE trashed_at IS NOT NULL;
59CREATE INDEX folios_root ON folios (acl_root);
60CREATE INDEX folios_path ON folios (path);
61CREATE INDEX folios_parent ON folios (parent_id);
62
63-- Explicit shares, as they were set.
64CREATE TABLE folio_grants (
65 folio_id TEXT NOT NULL REFERENCES folios (id) ON DELETE CASCADE,
66 principal TEXT NOT NULL,
67 role TEXT NOT NULL CHECK (role IN ('view', 'comment', 'edit', 'manage')),
68 granted_by TEXT NOT NULL,
69 granted_at TEXT NOT NULL,
70 PRIMARY KEY (folio_id, principal)
71);
72
73-- Effective explicit access, per folio, for list and search SQL: the
74-- owner, the owners of ancestors it inherits from (as `manage`), and every
75-- grant from the folio up to its acl_root, highest role each. Rebuilt for
76-- a subtree (by `path`) on a grant, move, restriction or ownership change
77-- (src/folios/access-store.ts).
78CREATE TABLE folio_access (
79 folio_id TEXT NOT NULL REFERENCES folios (id) ON DELETE CASCADE,
80 principal TEXT NOT NULL,
81 role TEXT NOT NULL,
82 -- The folio whose grant or owner this is (itself or an ancestor).
83 via TEXT NOT NULL,
84 -- For "Shared with you" ordering.
85 since TEXT NOT NULL,
86 PRIMARY KEY (folio_id, principal)
87);
88CREATE INDEX folio_access_principal ON folio_access (principal, since);
89
90-- Recent, and link access once opened.
91CREATE TABLE folio_visits (
92 folio_id TEXT NOT NULL REFERENCES folios (id) ON DELETE CASCADE,
93 user_id TEXT NOT NULL,
94 first_at TEXT NOT NULL,
95 last_at TEXT NOT NULL,
96 PRIMARY KEY (folio_id, user_id)
97);
98CREATE INDEX folio_visits_user ON folio_visits (user_id, last_at);
99
100CREATE TABLE folio_favorites (
101 user_id TEXT NOT NULL,
102 folio_id TEXT NOT NULL REFERENCES folios (id) ON DELETE CASCADE,
103 position REAL NOT NULL,
104 created_at TEXT NOT NULL,
105 PRIMARY KEY (user_id, folio_id)
106);
107
108-- Open spaces a member shows in their sidebar.
109CREATE TABLE space_joins (
110 space_id TEXT NOT NULL REFERENCES spaces (id) ON DELETE CASCADE,
111 user_id TEXT NOT NULL,
112 position REAL NOT NULL,
113 joined_at TEXT NOT NULL,
114 PRIMARY KEY (space_id, user_id)
115);
116CREATE INDEX space_joins_user ON space_joins (user_id);
117
118-- History: the Yjs state (an exact restore) and the text (reading, diffs).
119CREATE TABLE folio_versions (
120 id TEXT PRIMARY KEY,
121 folio_id TEXT NOT NULL REFERENCES folios (id) ON DELETE CASCADE,
122 created_at TEXT NOT NULL,
123 kind TEXT NOT NULL CHECK (kind IN ('created', 'edit', 'agent', 'suggestion', 'proposal', 'restore')),
124 authors TEXT NOT NULL DEFAULT '[]',
125 note TEXT,
126 text TEXT NOT NULL,
127 -- The Yjs state when it is at most 1.5 MB.
128 state BLOB,
129 -- Else its key in the file store.
130 state_key TEXT
131);
132CREATE INDEX folio_versions_folio ON folio_versions (folio_id, created_at);
133
134-- A doc's agents' tracked changes, as `suggestions` for pages.
135CREATE TABLE folio_suggestions (
136 id TEXT PRIMARY KEY,
137 folio_id TEXT NOT NULL REFERENCES folios (id) ON DELETE CASCADE,
138 author TEXT NOT NULL,
139 asked_by TEXT,
140 target TEXT NOT NULL,
141 before_markdown TEXT NOT NULL,
142 after_markdown TEXT NOT NULL,
143 note TEXT,
144 status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'accepted', 'rejected', 'stale')),
145 created_at TEXT NOT NULL,
146 decided_by TEXT,
147 decided_at TEXT,
148 marks_current INTEGER NOT NULL DEFAULT 0
149);
150CREATE INDEX folio_suggestions_folio ON folio_suggestions (folio_id, status);
151
152-- Other kinds: an agent's whole change as a Yjs update, previewed and applied or rejected.
153CREATE TABLE folio_proposals (
154 id TEXT PRIMARY KEY,
155 folio_id TEXT NOT NULL REFERENCES folios (id) ON DELETE CASCADE,
156 author TEXT NOT NULL,
157 asked_by TEXT,
158 note TEXT,
159 base_vector BLOB NOT NULL,
160 update_blob BLOB,
161 update_key TEXT,
162 summary TEXT NOT NULL,
163 status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'accepted', 'rejected', 'stale')),
164 created_at TEXT NOT NULL,
165 decided_by TEXT,
166 decided_at TEXT
167);
168CREATE INDEX folio_proposals_folio ON folio_proposals (folio_id, status);
169
170-- The workspace's own templates; the built-in ones are in code.
171CREATE TABLE folio_templates (
172 id TEXT PRIMARY KEY,
173 workspace_id TEXT NOT NULL,
174 kind TEXT NOT NULL CHECK (kind IN ('doc', 'slides', 'design', 'dashboard')),
175 name TEXT NOT NULL,
176 description TEXT NOT NULL DEFAULT '',
177 icon TEXT,
178 -- Markdown (doc, slides) or a JSON spec (design, dashboard).
179 body TEXT NOT NULL,
180 created_by TEXT NOT NULL,
181 created_at TEXT NOT NULL
182);
183CREATE INDEX folio_templates_ws ON folio_templates (workspace_id, kind);
184
185-- Files put in folios, kept in the file store under `docs/<key>` like pages' files.
186CREATE TABLE folio_files (
187 id TEXT PRIMARY KEY,
188 workspace_id TEXT NOT NULL,
189 folio_id TEXT REFERENCES folios (id) ON DELETE SET NULL,
190 key TEXT NOT NULL UNIQUE,
191 name TEXT NOT NULL,
192 content_type TEXT NOT NULL,
193 bytes INTEGER NOT NULL,
194 created_by TEXT NOT NULL,
195 created_at TEXT NOT NULL
196);
197CREATE INDEX folio_files_folio ON folio_files (folio_id);
198
199-- Links from one folio to another, rebuilt on each save: backlinks.
200CREATE TABLE folio_links (
201 from_folio TEXT NOT NULL REFERENCES folios (id) ON DELETE CASCADE,
202 to_folio TEXT NOT NULL,
203 PRIMARY KEY (from_folio, to_folio)
204);
205CREATE INDEX folio_links_to ON folio_links (to_folio);
206
207-- Projects (repositories, `owner/name` lowercased) a folio is about.
208CREATE TABLE folio_projects (
209 folio_id TEXT NOT NULL REFERENCES folios (id) ON DELETE CASCADE,
210 repo TEXT NOT NULL,
211 PRIMARY KEY (folio_id, repo)
212);
213CREATE INDEX folio_projects_repo ON folio_projects (repo);
214
215-- Code a folio cites, as `citations` for pages.
216CREATE TABLE folio_citations (
217 folio_id TEXT NOT NULL REFERENCES folios (id) ON DELETE CASCADE,
218 repo TEXT NOT NULL,
219 path TEXT NOT NULL,
220 kind TEXT NOT NULL CHECK (kind IN ('path', 'symbol', 'endpoint', 'env')),
221 label TEXT NOT NULL DEFAULT '',
222 ref TEXT,
223 source TEXT NOT NULL CHECK (source IN ('body', 'header')),
224 PRIMARY KEY (folio_id, source, repo, path, kind, label)
225);
226CREATE INDEX folio_citations_repo ON folio_citations (repo);
227
228-- Changes that touched code a folio cites, as `page_changes`.
229CREATE TABLE folio_changes (
230 folio_id TEXT NOT NULL REFERENCES folios (id) ON DELETE CASCADE,
231 repo TEXT NOT NULL,
232 repo_id TEXT NOT NULL,
233 commit_sha TEXT NOT NULL,
234 pull_number INTEGER,
235 pull_title TEXT,
236 paths TEXT NOT NULL DEFAULT '[]',
237 detected_at TEXT NOT NULL,
238 cleared_at TEXT,
239 cleared_by TEXT,
240 PRIMARY KEY (folio_id, repo, commit_sha)
241);
242CREATE INDEX folio_changes_open ON folio_changes (folio_id) WHERE cleared_at IS NULL;
243CREATE INDEX folio_changes_recent ON folio_changes (detected_at) WHERE cleared_at IS NULL;
244
245-- Full text over titles and text renditions; rebuilt for a folio on each save.
246CREATE VIRTUAL TABLE folios_fts USING fts5 (folio_id UNINDEXED, kind UNINDEXED, title, body, tokenize = 'unicode61 remove_diacritics 2');
247
248-- Folios' passages for the semantic index (Vectorize `g1t-folios`) and
249-- recall. `scope` is 'space:<id>' when the folio's access is exactly its
250-- space's, else 'folio:<acl_root>'; every hit is checked again against
251-- these tables before anyone sees it. Projects' docs keep `doc_chunks`.
252CREATE TABLE folio_chunks (
253 id TEXT PRIMARY KEY,
254 workspace_id TEXT NOT NULL,
255 folio_id TEXT NOT NULL,
256 kind TEXT NOT NULL,
257 scope TEXT NOT NULL,
258 seq INTEGER NOT NULL,
259 heading TEXT,
260 text TEXT NOT NULL,
261 hash TEXT NOT NULL,
262 vector_hash TEXT,
263 updated_at TEXT NOT NULL
264);
265CREATE INDEX folio_chunks_folio ON folio_chunks (folio_id, seq);
266CREATE INDEX folio_chunks_workspace ON folio_chunks (workspace_id);
267CREATE INDEX folio_chunks_unembedded ON folio_chunks (workspace_id) WHERE vector_hash IS NULL OR vector_hash <> hash;
268
269CREATE VIRTUAL TABLE folio_chunks_fts USING fts5 (chunk_id UNINDEXED, scope UNINDEXED, folio_id UNINDEXED, heading, text, tokenize = 'unicode61 remove_diacritics 2');