Roll up: database design
17 Nov 2026. Design only, nothing built. Source: Roll up design doc section 3 (verdicts/ROLLUP-DESIGN-2026-11-17.md), checked against our table design skill, db-architecture-planning, and the PostgreSQL schema conventions it loads.
In short
A Roll up is a named group of Contexts that you can share. It holds Contexts, not copies. Its issues, actions and decisions are the live items of its Contexts. Ticking one in a Roll up ticks the same row in its Context.
- New tables: 9. Four of them have their own history log, and there is one global seeded config table for the Patterns prompt.
- Changes to existing tables: none. The only addition is the job-kind value
rollup_pattern. Nothing is added tocontextorcontext_item. - The big decision: a Roll up gets its own table. It is not a
kindcolumn oncontext. Reasons are in the next section.
Why its own table and not context.kind
| If Roll up were a kind of Context | Why that breaks our rules |
|---|---|
At least nine tables point at context and assume it has sources or people: context_assignment, context_item, context_pipeline_job, context_pipeline_reservation, context_source_rule, context_link, context_email_receiving, context_tag and context_member. | A foreign key can't say "only Contexts of kind = context". Each of those tables would then accept a Roll up id. Only a trigger on every table could stop that, and one missed trigger is a data leak. The skill's rule is that the foreign key carries the meaning: no kind-plus-id pointers, and the specific table points at the shared one. |
| A Roll up has no sources, no pipeline, no rules and no email in. | Most of context's columns would be empty for every Roll up row. Two different things would share one table. |
| With a separate table… | …every Roll up foreign key is exact. A Roll up can never receive an item, an assignment or a source rule, because the database itself refuses it. A Roll up of Roll ups is impossible too, because rollup_context.context_id points only at context. |
The skill's rules, and where each one shows up
| # | Rule (db-architecture-planning) | Where it shapes this design |
|---|---|---|
| R1 | Every reference is a foreign key. No ids inside JSONB, arrays or text. A relationship is stored once. | Every _id column below has a real FK. Pattern evidence is a row per item in rollup_pattern_item, not an id array on the pattern. A pattern's Context chips are worked out from its items when read, not stored a second time. |
| R2 | No kind-plus-id pointers. The specific side points at the shared side. | A separate rollup table, not context.kind. rollup_pattern_job is the Roll-up-specific side of the shared job table, the same shape as context_pipeline_job. |
| R3 | Write a row once. Update it only on a real change. History goes in an append-only log. Data that changes on its own schedule goes in a narrow side table. | A pattern's title and explanation are written once. A re-run that confirms a pattern changes only last_confirmed_job_id. Feedback is insert-only, and the latest row per person wins. |
| R4 | Anything a person edits, or that grants access, keeps whole-row history (L.7): a trigger-captured <table>_log. Runtime may only INSERT and SELECT the log. | Four tables get one: rollup_log, rollup_context_log, rollup_member_log and rollup_tag_log. Who is in a Roll up decides who can read what, so every add and remove is kept. |
| R5 | Tenant in every key; UUIDv7 ids from the writer; hash-partitioned by tenant like context. | Every primary key is (tenant_id, <table>_id) and every unique index starts with tenant_id. The tables partition the same way context does. |
| R6 | Exclusivity is a unique index plus a plain INSERT. Never a lock or a check-then-write. A violation (code 23505) becomes a typed error. | A Context can be in a Roll up once: UNIQUE (tenant_id, rollup_id, context_id). A person has one role: UNIQUE (tenant_id, rollup_id, user_id). A Patterns run happens once per input version: UNIQUE (tenant_id, rollup_id, input_version). |
| R7 | RLS forced on every tenant table. The runtime role has no BYPASSRLS and never switches role. | Each table gets the same four tenant policies as context_member, plus the purge-role policies so a workspace delete still cleans up. Each table also gets a tests/rls/ test proving a second tenant sees and writes nothing. |
| R8 | One owner per table and one set-based write routine. Every table has a writer and a reader. | Each table has exactly one owning crate (listed in the next section), with a fail-closed boundary test from its first commit. Adding 5 Contexts is one INSERT … SELECT unnest($ids), not 5 inserts. |
| R9 | Lock the narrowest entity, never the tenant. | Roll up writes lock the one rollup row only. |
| R10 | Text, never varchar(n). Access columns are enums, not booleans. | Names and descriptions are text, with the same length checks as Contexts so one form limit serves both. The share switch is an enum (none or viewer) so a later level doesn't need a migration of a boolean. |
| R11 | Classify every table (K-0 class) and register it: sor_table_classification.txt and db_write_path_register.txt. | The class is in each table's card below. The register rows are listed in the design doc section 3.3. |
Relationship diagram
newnew table existingexisting table loghistory log (no FKs, so it survives a delete). Arrows point from the referencing table to the one it references.
Tables, one by one
rollup newmutable_configowner: rollup-db
- FKs:
owner_user_id→tenant_member, the same as a Context's owner.created_by_user_id→auth_user, set to null if the user is deleted. R1 - Keys: PK
(tenant_id, rollup_id), plus an index on(tenant_id, owner_user_id)for "my Roll ups". R5 - History:
rollup_log. A switch change is recorded with who made it. R4
Why it's built this way: the Owner is a column, not a member row, the same as Contexts, so there is exactly one Owner. The share switch lives on the Roll up because only the Owner changes it. It is an enum so a third level later is one new value. R10 The name and description checks are copied from context so the same form limits apply.
rollup_context newmutable_configowner: rollup-db
- FKs:
rollup_id→rollup, cascade on delete.context_id→context, restrict.added_by_user_id→tenant_member. - Keys: UNIQUE
(tenant_id, rollup_id, context_id), plus an index on(tenant_id, context_id). R6 - History:
rollup_context_log, because membership decides access. R4
Why: this is the link table. It is the only place a Roll up names a Context. added_by_user_id matters: if the share switch is on, it is how we check, live, that the person who added the Context still owns or edits it (see the share-switch section). The Context side is restrict on purpose. A Context delete must remove these rows itself, in its purge order, so a Roll up never silently loses a Context through a cascade. The index on context_id answers "which Roll ups include this Context?" and serves the access check.
rollup_member newmutable_configowner: context-access
- FKs:
rollup_idcascade.user_idandgranted_by_user_id→tenant_member. - Keys: UNIQUE
(tenant_id, rollup_id, user_id): one role per person. Index on(tenant_id, user_id)for "Roll ups shared with me". R6 - History:
rollup_member_log. R4
Why: this is the same shape as context_member, so the one access owner (context-access) decides both. It is a separate table, not context_member with a kind column, because a foreign key has one target. R2 The Owner is never a row here.
rollup_hidden newmutable_configowner: context-access
Why: "Hide for me" is one person's preference, not a change to the Roll up. It is a row per person, so hiding never touches the shared row. It has no log because it grants nothing. Its shape must match whatever Contexts' own "Hide for me" (U5) builds. If U5 picks a different shape, this follows it.
rollup_tag newmutable_configowner: rollup-db
Why: this copies context_tag. Tags are case-preserving but unique case-insensitively, via UNIQUE (tenant_id, rollup_id, tag_key). A row per tag rather than a text array keeps tags filterable through the filter engine. R1 It has a log because a person edits tags. R4
rollup_pattern newmutable_configowner: rollup-pattern-db
Why: a pattern is an LLM finding across the Roll up's Contexts. Its title and explanation are written once, so they are never rewritten in place. R3 A later run that sees the same pattern changes only last_confirmed_job_id, and a gone pattern changes only its status. Both job ids are FKs to job, so every pattern traces to the run that made it. R1 It lives in its own crate because the pattern job and the Roll up editor change for different reasons.
rollup_pattern_item newmutable_configowner: rollup-pattern-db
Why: the evidence for a pattern is a row per item, not an array of ids on the pattern. R1 The Context chips on a pattern card come from these items' own Contexts when read, so there is no second pattern-to-Context table that could drift. If an item is deleted, its evidence row goes with it (cascade).
rollup_pattern_feedback newledgerowner: rollup-pattern-db
Why: the 👍 / dismiss buttons. Feedback is insert-only, and the latest row per person wins. UPDATE and DELETE are revoked. That keeps every vote as a fact for tuning the prompt later, and nobody's click ever rewrites a row. R3
rollup_pattern_job newmutable_configowner: pipeline-job-db
Why: this is the Roll-up side of the shared job table, so the specific side points at the shared side and job gets no Roll up column. R2 UNIQUE (tenant_id, rollup_id, input_version) is what makes "run Patterns" safe to trigger many times. A second trigger with the same inputs hits the unique index and reuses the run, with no lock and no pre-check. R6 If the Roll up is deleted mid-run, the job row stays for its history, and the run ends with outcome deleted.
context_pattern_kind new · globalseeded, read-only at runtime
Why: our rule is that prompts live in database config, never in code. Context Patterns (U8) creates this table, and Roll up adds its row. It's global because the prompt is the same for every workspace. Its one change is that a migration seeds it.
History logs ledger
Why: a trigger writes them, never app code, so no write path can forget. Each stores the whole row, not a diff, so any past version can be read back on its own. R4 They have no FKs on purpose, so history survives a Roll up delete. Runtime can only insert and read.
Views (read-only, run as the caller so RLS applies)
| View | What it's for |
|---|---|
rollup_list_v | Roll up list rows with counts (Contexts, sources), last source added, owner name and tags. |
rollup_context_v | The Contexts tab inside a Roll up. Archived Contexts are left out. |
directory_entry_v | Contexts and Roll ups in one list, for the Contexts page's Type filter. This depends on open question Q1. |
rollup_context_passthrough_v | The only thing the access crate reads from the Roll up tables: which Roll ups have the switch on. A gate test pins that. |
The share switch: "Viewers can open the linked Contexts"
Stored: one enum column, rollup.linked_context_access. It defaults to none and only the Owner changes it. Every change is logged with who made it.
Enforced: the one access owner (context-access) gets one extra branch in each Context read rule (Neuma reads, source reads and the two AI-tool reads). The branch lets a Roll up member read a Context only when all three of these hold:
- the Roll up has the switch on;
- the person is a member of that Roll up;
- the person who added the Context still owns or edits it, checked live.
Why no extra context_member rows are written: copying access into rows would need a sync on every share, switch flip and role change, and one missed sync means someone keeps access they shouldn't have. Checking live has nothing to sync. A derived reader is always a Viewer, even if they are an Editor of the Roll up. The Context page can show how they got in.
Effect: a Roll up can never share more than the people who built it could share themselves.
Write path: the skill's six questions
The skill requires these answers before any table is created. An unknown answer blocks the design.
| Question | Answer |
|---|---|
| Writes per unit of work | Add or remove N Contexts: one set-based insert or delete, plus one log row each by trigger. Share: one member row and one log row. Patterns run: one job row, then K pattern rows and their item rows in one publish transaction. |
| Can updates be HOT? | to confirm Yes, if rollup.modified_at has no index. The design doesn't index it. Confirm the list sort uses last_source_added_at from the view, not modified_at. |
| Is each row written once? | Patterns: yes, and only status and the last-confirmed job change. Feedback and logs: insert-only. The Roll up row changes only on a real edit. |
| What makes writers wait, and for how long? | Only a lock on the one rollup row, held for the length of a single request transaction. Nothing tenant-wide. |
| Derived or search data kept inside the write? | None. Counts and chips are computed in views at read time. |
| Largest burst | to confirm A Patterns publish for a Roll up with many Contexts. That's bounded by its item window, but the design doesn't size it yet. Unit R6 must state it. |
Open items that change the tables
| Q | Question | Effect on the tables |
|---|---|---|
| Q1 | One list for Contexts and Roll ups: new /directory, or a kind on /context? | Recommended: /directory, which keeps directory_entry_v. The other option puts Roll ups into the /context response but still needs no table change. |
| Q4 | Can a Roll up Editor share and add Contexts? | No table change either way. It decides which roles the write routines accept. |
| Q5 | One item raised in two Contexts: one row with "+N", or two rows? | Recommended: two rows, which needs no new table. "+N" needs a cross-Context match table and a job, as a later unit. |
| Naming | The review called the Roll up-to-Context link rollup_member. This design uses rollup_context for that link, and rollup_member for people, matching context_member. | Name only. |
| Depends | "Hide for me" and Context Patterns are not built yet (U5, U8). | rollup_hidden and context_pattern_kind follow whatever shape those units choose. |