Empire Teas CRM — Conceptual Entity Map
Status: accepted — proposed 2026-08-31, accepted 2026-08-31 (lead sign-off: Tharinda) Scope: every persistent entity V1 needs, and every relationship between them, in the one
publicschema (FS-14 collapsed the former two) Related:.forge/project-prd.md§Domain Model,.forge/design/architecture.md§Core / Domain Separation,forge-et-be/docs/data-model.mdAD #31 (2026-09-09, Rev 4 below): FS-14 collapsed the split — onepublicschema, feature-first packages. TheHomecolumn below names the unqualified table; the former per-schema prefixes are dropped and the cluster headings name the owning capability (its feature-first package once built). Relationships, cardinalities and statuses are unchanged; where a row reasons from the old boundary or a retired guard, read it as the historical rationale for a placement that now stands as plain data-model design.
Why this document exists
The PRD’s §Domain Model states each entity in one row; data-model.md §2 carries the verified
schema for one subsystem (bulk pricing) and stubs for everything else; the architecture’s module
layout names which module owns which entity family. No single document draws the whole graph,
and several load-bearing edges cross feature boundaries — a quote’s relationship to a deal is
neither F7’s decision alone nor F2’s; a lead’s conversion touches F11, F1 and F2 at once. Left
unmapped, each feature spec would settle its corner independently, and the edges between corners
would be settled twice, differently.
This map fixes the conceptual graph — entities, homes, edges, cardinalities — so every feature
spec designs against the same picture. It deliberately contains no DDL and no field lists
beyond what an edge needs: fields are the owning spec’s job, and the realized schema lives in
data-model.md, which this map never overrides.
Authority (per .claude/rules/source-trust.md): the PRD outranks this map on what;
data-model.md outranks it on any realized schema; architecture.md outranks it on layering.
This map’s added value is the edges none of them state — each marked with a status and the spec
that confirms it. On any conflict, raise it; two conflicts found during authoring are recorded in
§Contradictions rather than silently resolved.
How to read it
Cardinality is written from the left entity: Deal N:1 Account = many deals per account.
M:N via x names the join entity. Home is the owning table in the one public schema (FS-14) plus, via the cluster heading, the feature-first package that owns it. Every entity-bearing table additionally carries the §1
base columns from data-model.md (identity, version, audit stamps; effective-dated tables per
the temporal conventions) — not restated per row.
| Status | Meaning |
|---|---|
| shipped | Migration on main today |
| verified | DDL written and proof-verified in data-model.md §2, not yet shipped (F26 / feature specs ship it) |
| designed | Stated in an approved document (PRD §Domain Model, architecture module layout) |
| proposed | This map’s recommendation — the named spec confirms or revises it |
| open | Blocked on a decision or OQ — the note says which |
The entangled entities (typed 1:1 tea extension tables, extension point 1): Account (struck at
Rev 12 — F1-a ships it as flat columns), Deal,
Product (struck at Rev 7 — the buying standard is the sellable record), Quote, LineItem (architecture §Entity extension, LineItem added by SP-ARCH-003). This map
proposed Lead as a sixth; D-4 is closed against it at Rev 15 — F11-a ships lead as flat columns.
The spine
The hub entities and the edges everything else hangs off. [t] = has a tea 1:1 extension table.
app_user (owns most records; one role each — roles are Empire's, FS-6 + F5-k)
│
converts to (D-5) │
lead ──────────▶ account ◀──N:1── contact
│ │ 1:N
│ N:1 source ▼
│ deal[t] ──N:1─▶ stage ──N:1─▶ pipeline
│ │ │ │ 1:1
│ │ │ └─▶ product_brief (private label, F8)
│ │ └────▶ sample 1:N (rounds, F6)
│ │
│ ┌──────┴──────────┐
│ ▼ 1:N ▼ 1:N (D-2)
│ quote[t] deal_line_item ─N:1▶ buying_standard
│ │ 1:N │ 1:N
│ ▼ ▼
│ line_item ──1:1─▶ line_item_bulk_price ──▶ buying_standard_price
│ │ N:1 buying_standard │ N:1 │ anchored to
│ ▼ ▼ ▼
│ quote_cost_line (N:1 quote) buying_standard auction_sale
│ (N:1 account — the sellable record, Rev 7)
│
one `public` schema (FS-14); `[t]` = has a typed 1:1 extension table (formerly a tea-schema table).
cross-cutting, attach to the entities above:
activity · note · tag (per entity type) · email_message · attachment (D-6) · custom fields (JSONB, F34)
timeline = stored timeline_event rows, one per event, written by each producer (D-7, confirmed by F1-a)
search = each list's own q filter; no global search index (F31 dropped, Rev 25)
Clusters
1. Identity, access, audit — platform
| Entity | Home | Status | Realized by | Notes |
|---|---|---|---|---|
| User | app_user |
verified (§2.4) | FS-6 + F5 | Owns records across the system; region tag for P-34 scoping. Gains manager_id — a nullable self-reference (AD #30, 2026-09-07): the reporting hierarchy behind team scope — since AD #37 (2026-09-27) the hierarchy every key reaches down, so setting a manager is an access decision. Hierarchy and lifecycle policy — decided by AD #34 / AD #35 (2026-09-24), answering OQ-56: team is recursive through all levels; a user is pre-created by the settings.manage holder (access.manage until 2026-10-26; no auto-provisioning), deactivated and reactivated, never deleted; deactivation is refused while the user has direct reports or owns records, and deactivated users stay in the tree (F5-c) |
| User identity | user_identity |
shipped (FS-6, 2026-09-15) | FS-6 | Microsoft-only login onto one user (AD #5 as refined by AD #28): provider (microsoft) + subject; table kept provider-generic so a later provider is data, not schema. No password hash anywhere — the local path was dropped (FS-6 spec Rev 1 removed user_credential). Changing a user’s email clears this link (AD #34), so the next sign-in re-links by the new address |
| Role | role |
shipped (FS-6, 2026-09-15) — AD #15 as refined by AD #30, #26, #32, #34 | FS-6 + F5 + F5-k | Admin-authored roles (AD #30, 2026-09-07); no templates (AD #32, 2026-09-11). Realigned by AD #34 (2026-09-24), built by F5-k: no superadmin and no non-deletable system role — the is_system flag and the superadmin row FS-6 shipped are removed; each role has an immutable key generated at creation and an editable display name; the only seeded row is an ordinary, editable IT Admin role, seeded by one Flyway migration in every environment together with one active user, holding settings.manage (it was seeded with access.manage, which V54 folded into settings.manage on AD #34’s refinement of 2026-10-26 — Rev 36); Empire’s administrator creates every other role from the catalog (the six confirmed roles + the Managing Director role are the recommended day-one set, not data). The template_key column and the role_template* tables FS-6 spec Rev 3 shaped are removed by spec Rev 5 (2026-09-11). User↔role is N:1 — one role per user (app_user.role_id; the M:N join and the most-permissive union are dropped); a role held by any user, active or deactivated, cannot be deleted, and at least one active user always holds settings.manage |
| Permission / grant | catalog in code (per-package constants); grants role_grant |
shipped (FS-6, 2026-09-15) — AD #15 as refined by AD #30, #26, #34, #37 | FS-6 + F5-k + F5-l | (role, permission_key) grants — no scope column (AD #37, 2026-09-27; F5-l drops role_grant.scope) — over a code-declared catalog whose metadata — label, description, UI group — lives in code, not in a table. Each entity that has an owner (lead, account; deal, quote, sample in Phase 2) has a third key, <entity>.all, which widens the holder’s view or manage on it to every record and is refused without one of them; without it a key reaches the records owned by, or shared with, the user or anyone below them in the reporting tree. Was, until AD #37: (role, permission_key, scope) with a scope-aware flag per key. AD #34 (2026-09-24) makes the catalog minimal: <entity>.view / <entity>.manage per business entity (manage implies view), plus settings.manage (all configuration and all user, role and hierarchy administration — it absorbed access.manage on 2026-10-26, Rev 36), email.manage (own mailbox only), price.publish and quote.approve_sub_floor — listed in FS-6 Appendix A. No category axis, no * wildcard grant. Until AD #37: scope own / team / all per grant, with shares counted in own and team (AD #35 — see the Record share row); shares now count for the user and everyone above them. quote.approve_sub_floor is held by the Managing Director role in the recommended configuration (AD #26 unchanged). Was: (role, resource, level) grants with the level as a column — superseded 2026-09-07; then ~100–150 <resource>.<action> keys each carrying a read / edit / execute category (AD #32) — superseded 2026-09-24. Confirmed by FS-6 spec Rev 3 (2026-09-07): the grant table is role_grant keyed UNIQUE (role_id, permission_key), and the separate named-permission store (role_permission) is removed, so one table answers every check. The Tea Room / Executive role contents are Empire’s configuration (OQ-53 closed as not required, PRD Rev 37) |
| Audit event | audit_event |
shipped — confirmed by F39 (spec FR-1; V24__audit_event.sql) |
F39 | Append-only log of every write and every security event (sign-in success and refusal, sign-out, deactivation and reactivation, role and permission changes, sharing and reassignment, exports), written in the same transaction as the change — only the sign-in, refusal and sign-out events commit in a transaction of their own (FR-8); not reads; kept indefinitely in V1. The actor is a kind, not a reserved user: actor_kind is USER, SYSTEM (background work, the job’s name in actor_label) or ANONYMOUS (a refused sign-in that matched no user), and actor_user_id is set exactly when the actor is a USER — a plain id with no foreign key (spec Rev 2: users are never deleted in the application). The record is a loose (entity_type, entity_id) pair — the record’s table name and id, both set or both null (D-8). changes (only the changed fields, old/new; a hard delete’s before-image; a sensitive field as {"changed": true}) and context are JSON objects. The database refuses every UPDATE, DELETE and TRUNCATE. Indexed for one record’s history, one user’s actions and a time window. Event codes: forge-et-be/docs/audit/README.md |
| Session | Spring Session JDBC tables | shipped (FS-6) (V7__spring_session.sql) |
FS-6 | Infrastructure, not domain — listed to keep it out of the domain graph. Deactivating a user deletes that user’s session rows by the principal-name index (AD #34, F5-c) |
Key edges: app_user 1:N owns {lead, account, deal, quote, saved_view, mailbox_connection…}
(designed — PRD); app_user 1:N is assigned activity (F3-a — the assignee gives no reach; a follow-up’s
reach is its linked record’s, Rev 29). Every audit stamp (created_by/modified_by) is an FK to app_user (shipped
convention, §1; product’s pair was owed by the migration that creates app_user — moot once the table retires, Rev 7).
Every table carries created_at / modified_at, and created_by / modified_by where a person
makes the change (AD #36) — F39 checks every table on main and adds the columns where missing.
How the SYSTEM actor is represented against the created_by FK (a reserved app_user row kept out
of sign-in, the last-admin count, the directory and pickers — or a nullable stamp plus an actor
column) is F39’s decision.
2. CRM spine — lead, account, contact, deal, pipeline (+ typed extensions)
| Entity | Home | Status | Realized by | Notes |
|---|---|---|---|---|
| Account | account (flat columns — no account_profile) |
shipped — confirmed by F1-a (spec FR-S1; V15__account.sql) |
F1-a | One table: name, country, region, customer type, website, owner (owner_id NOT NULL — AD #35; shareable through record_share), archive flag, preferred packaging and Incoterms (TEXT[] with vocabulary CHECKs), migration identity (D-11) and source_owner_name (F4-d FR-S1 — the Pipedrive owner’s name, never shown). The tea attributes are flat columns, not a 1:1 account_profile extension — AD #31 removed the vocabulary rule the split existed for. Required certifications are the account_certification join (D-10). LTV is an entered estimate (estimated_ltv + ltv_currency, FR-S1) — nothing in V1 computes lifetime value, because there is no order entity to compute it from (OQ-63); dormancy stays derived, from the latest timeline_event.occurred_at (FR-S6; the account list’s dormant flag, AC-28) |
| Contact | contact |
shipped — F1-a (V19__contact.sql) |
F1 | N:1 account (nullable). A contact added from a user’s Outlook contacts (F22) gets an account unless the user holds account.all, who may choose none (F22 spec FR-7); the per-user mapping to Outlook is the Contact sync link row. Several email addresses (confirmed by F20-l, Rev 37): contact.email stays the main address; the others are contact_email (contact_id, email, position, base columns; unique per contact by lower(email); N:1 contact; at most 20 per contact), and both are matched on F11-d’s trimmed, lower-cased address key (F20-l FR-1, FR-8) |
| Lead | lead (flat columns — no lead_profile, D-4 closed) |
shipped — confirmed by F11-a (spec FR-S1; the lead migration) | F11-a (table, intake, single-lead API) · F11-c, F11-d, F11-g, F11-h, F11-e, F12 (their own columns) | Triage inbox record, not on a pipeline. One table, every field a flat column: company name and contact name (at least one, lead_has_party), contact email / phone / job title, website, country, buyer type (the account.customer_type vocabulary), estimated value + value_currency (USD), lead_source + lead_source_detail, inquiry text, received_at, status (new / approved / converted / archived — Archive / nurture is the archived status, with no separate nurture status, and restore returns archived to new, F11-f decisions 2 and 3), owner (owner_id nullable only for a Pipedrive import — AD #35, the named CHECK lead_unowned_only_imported; an unowned lead is visible only at all scope, through F11-b’s Unassigned filter; shareable through record_share), the optional matched account and contact (key edges below), and the migration identity (D-11), which on lead is also the intake’s replay key. Added later by the features that write them, not by F11-a: the tea details F12 extracts — built by F12 (V38__ai_invocation_and_lead_tea_fields.sql, F12 spec FR-T1): product_line, grade, volume, packaging (TEXT[]), incoterm, destination_port and timeline as nullable columns on lead, with named vocabulary and length CHECKs, and the certifications as the join table lead_certification (D-10; buying_standard_certification’s shape — the API that reads and writes them is F12’s wave 2); the AI outputs — score, priority, confidence, “why” and recommended action (F11-g), drafted follow-up (F11-h); and the conversion links (F11-e, D-5); and source_owner_name — built by F4-b (FR-9): the Pipedrive owner’s name, never shown, written only by a Pipedrive migration run. The score storage is F11-g’s. F11-c’s research snapshot is not on lead either: it lives in lead_research, one row per lead, written only by F11-c’s background job and its person-removal action, beside search_call (one row per search, no query text) and search_provider_pause (F11-c spec FR-S1, FR-L2, FR-L4; Rev 33). F11-d’s summary, spam / duplicate tag and proposed customer match are not lead columns: they live in lead_triage, one row per lead, written only by F11-d’s background job and its dismiss action, so the job never moves the lead’s version (F11-d spec FR-S1, decision 5; Rev 32). A migrated lead’s summary from its linked email lives in lead_mail_summary (designed — F11-j), one row per lead, written only by F11-j’s background job, for the same reason (F11-j spec FR-S1, D-4; Rev 53). F11-g’s priority, confidence, reason and next action likewise live in lead_ranking, one row per lead, written by F11-g’s background job and, at the conversion of a captured lead, by its copy of the capture’s ranking (origin capture, against crm for the job’s rows), and keeping the campaign focus a ranking read in focus and focus_campaign_name through every later write (F11-g spec FR-S1, FR-S4, FR-A9 — Rev 6; F36-c FR-CV1); hot / cold is computed when read and stored nowhere (F11-g spec FR-S1, FR-E1, decisions 6 and 8; Rev 38) |
| Lead source | lead_source + lead_source_detail columns on lead |
shipped — confirmed by F11-a (spec FR-S1, FR-R1) | F11-a | Fixed set (Website / Trade Fair / Referral / Email / Other) as a vocabulary CHECK mirrored by LeadVocabularies, + free-text detail (which fair — F36 attribution). F17 charts group by the vocabulary. Named lead_source, not source, because source / source_ref is the migration-identity pair on every migratable table (D-11) — a rep’s “Referral” and F4-b’s 'pipedrive' must not share a column |
| Deal | deal + deal_profile |
designed (PRD) | F2 | Core: title, value, currency, owner, expected close, label, status. Tea profile: volume + units, brand (OQ-20; banned word in core), destination interest. Rotting/age-in-stage computed from last-touch, not stored (F18/F24) |
| Pipeline | pipeline |
designed (PRD/AD #11) | F24 | Admin-configurable; seeded Private Label + Bulk |
| Stage | stage |
designed (AD #11) | F24 | N:1 pipeline, ordered; editable name + stable stage_type (core: NEW/WON/LOST; tea registers BRIEF/SAMPLE/QUOTE… via StageTypeProvider); win probability, rotting days |
Key edges:
| Relationship | Cardinality | Status | Settled by | Notes |
|---|---|---|---|---|
| Contact → Account | N:1 (nullable) | shipped — F1-a (V19__contact.sql) |
F1 | |
| Deal → Account | N:1 | designed | F2 | Required — B2B; every deal has a buyer org |
| Deal → Contact (primary) | N:1 (nullable) | designed | F2 | PRD deal creation names a person + org |
| Deal → Stage | N:1 | designed | F2/F24 | Pipeline reached via stage; a cross-pipeline move is a stage change. If pipeline_id is ever denormalized onto deal, use the FR-I1 composite-FK idiom |
| Deal status vs stage | — | proposed | F2 | Deal carries its own status (open/won/lost) alongside stage; a stage_type = WON stage and status = won must agree — F2 owns the mechanism |
| Lead → converted Account / Contact / Deal | N:1 each (nullable) | proposed (D-5) | F11 | Set at conversion; the lead row is kept (status: converted) — OQ-21 forbids deleting history |
| Lead → matched Account / Contact | N:1 each (nullable) | shipped — confirmed by F11-a (spec FR-O6) | F11-a | The existing customer an inquiry comes from, before any conversion: lead.account_id and lead.contact_id. Set by the intake, by PATCH, by F4-b when it migrates a Pipedrive lead whose organisation F4-d wrote, and proposed by F11-d’s duplicate detection. Not the conversion link (the row above, D-5). Changes nobody’s reach (AD #35): a lead’s reach is its own owner and shares, never its account’s, and the account’s reach never extends to the lead. The contact and account must agree when the link is set — checked by the API at that moment, not stored, so a contact that later moves to another account leaves the match as recorded |
| Lead / Deal → Sequence | M:N via enrollment | designed | F25 | See cluster 7 |
3. Commerce — the buying standard as the sellable record, deal line items, quote, line items — quoting (the catalog package is retired, Rev 7)
| Entity | Home | Status | Realized by | Notes |
|---|---|---|---|---|
— (dropped: V11__drop_product.sql) |
removed — FS-15, 2026-09-16 | FS-15 | There is no products catalog: the Buying Standard (cluster 4) is the sellable record for bulk and private label and takes over code / name, grade, origin, brand, held certifications (cluster 6), cost and price history. The shipped table, the catalog package (Product, ProductController — FS-10’s worked API example), ListPriceModel and the product.* keys are retired by the backend hand-off in PRD Rev 27; until then the product_id FKs below are drift to remove, not design |
|
| Deal line item | deal_line_item (was deal_product) |
proposed (D-2, retargeted Rev 7) | F26 | Buying standard attached to a deal with qty + discount; rolls up into deal value (PRD F26). Mutable working set |
| Quote | quote + tea quote extension (D-3) |
verified (§2.4) / proposed | F7 | Core: account, deal (D-1), currency, status, payment terms. Tea extension: Incoterm, destination market, readiness config — see C-1 |
| Quote line item | line_item |
verified (§2.4) | F7 | N:1 quote (CASCADE), N:1 buying_standard (was N:1 product — Rev 7, retarget owed in §2.4); unit_price is an immutable snapshot (FR-T7 trigger) — so “refresh price” replaces lines, never updates them |
| Quote cost line | quote_cost_line |
verified (§2.4) | F7 | Named human-entered amounts (freight, certification, export docs); never computed |
| Quote revision | quote_revision |
proposed | F7 | Append-only log (v1/v2/v3 + note + actor); PRD requires revision history and price-refresh logging |
| Readiness checklist (8-item) | ChecklistEngine config, computed |
designed (built by F7-b — FS-13 cancelled, AD #29) | F7 | Not an entity — computed from quote state per checklist definition; the GP-floor gate is a GateRule. Sub-floor approval record: the named-permission approval is audited (cluster 1) |
Key edges:
| Relationship | Cardinality | Status | Settled by | Notes |
|---|---|---|---|---|
| Quote → Deal | N:1 | proposed (D-1) | F7 | §2.4 has account_id only — F7 adds deal_id; keep both consistent via the FR-I1 composite FK. Recommended NOT NULL (see D-1) |
| Quote → Account | N:1 | verified (§2.4) | F7 | Retained; pinned to the deal’s account by D-1’s composite FK |
| Deal line item → Deal / Buying standard | N:1 / N:1 | proposed (D-2, retargeted Rev 7) | F26 | |
| Line item → Quote / Buying standard | N:1 / N:1 | verified — retargeted by FS-15 (line_item.buying_standard_id; data-model.md §2.4) |
F7 | Quote lines seeded from the deal’s line items at quote creation (D-2) |
| Line item → bulk price snapshot | 1:1 | verified (§2.5) | F26 | line_item_bulk_price — the fifth entangled entity; composite FKs pin it to a published price and its cost (state-narrowing idiom) |
4. Bulk pricing — pricing — settled, do not re-derive
The whole cluster is specified and DDL-verified in data-model.md §2 (SP-ARCH-003). Edges only,
for completeness — §2 is the authority:
| Relationship | Cardinality | Status |
|---|---|---|
| Buying standard → Account | N:1 | verified |
| — | removed — Rev 7 (PRD Rev 27: the standard is the sellable record and carries grade / origin / brand itself; buying_standard.product_id is dropped by the data-model revision — Tharinda) |
|
| Price → Buying standard × validity | effective-dated, one published per key | shipped — F26-a (V29__price_and_cost.sql, Rev 17): the Rev 23 (no market axis) and Rev 31 (four-figure build-up) shape of data-model.md §2.2/§2.5; a published row can be neither deleted nor reverted to draft (FR-S8) |
| Cost → Buying standard × validity | effective-dated (no market axis) | shipped — F26-a (V29__price_and_cost.sql, Rev 17) |
| Price / Cost → Auction sale (anchor) | N:1 | shipped — F26-a (V29__price_and_cost.sql, Rev 17): required on a price, optional on a cost |
| Line-item snapshot → Price / Cost | composite FKs (published only) | verified |
market (destination markets — a customer attribute and quote field since PRD Rev 23, no longer a price key — distinct from account.region, which is an office/scope
tag) and auction_sale are reference-grade entities in this cluster. PriceModel (extension
point 8) is the seam: core resolves a line price as-of a date; tea’s implementation answers from
this cluster; the shipped fallback was product.list_price (retired with the catalog — Rev 7).
5. Samples & private label — samples, privatelabel
| Entity | Home | Status | Realized by | Notes |
|---|---|---|---|---|
| Sample (round) | sample |
designed (PRD) | F6 | One row per round: N:1 deal, round number, 7-state lifecycle (on core RoundLifecycle), owner (taster) N:1 app_user, courier + tracking (manual V1), due, outcome, feedback. Rejected outcome auto-creates the next round |
| Product brief | product_brief |
designed (PRD) | F8 | 1:1 deal (private label only — UNIQUE(deal_id)); blocking open questions as GateRule config or child rows — F8’s call |
| Courier (lookup) | courier reference |
proposed | F6 | Samples-only in V1; tea reference data |
Key edge note — Sample → Account is deliberately absent (D-9): the PRD lists both N:1 Deal
and N:1 Account, but account is derivable through the deal; storing both invites drift. Display
joins through the deal. The sample→deal-stage automation (the prototype’s most detailed logic)
keys off stage_type, not entities — F6/F2 seam, no extra edge.
6. Compliance — certifications & export docs — compliance
| Entity | Home | Status | Realized by | Notes |
|---|---|---|---|---|
| Certification (taxonomy) | certification |
shipped — F1-a (spec FR-S3; V14__certification.sql) |
F9 | Reference entity (EU Organic, Halal, JAS, …) |
| Account required certs | account_certification (join) |
shipped — confirmed by F1-a (spec FR-S3; V15__account.sql) |
F1-a / F9 | M:N account↔certification, composite primary key and both foreign keys — a join table for FK integrity (D-10), not the text[] the architecture example once sketched. F9 reads it for the set difference |
| Standard held certs | buying_standard_certification (join) |
shipped — F26-b (spec FR-S1; V32__buying_standard_certification.sql; Rev 20) |
F26-b | Since PRD Rev 27 the buying standard carries its held certifications (no product catalog). Held per standard (OQ-9’s 2026-09-17 narrowing): a join with a composite primary key, the same shape as account_certification, so F9 compares the two sets in SQL (F26-b spec FR-S1). Where Empire’s existing data lives is OQ-9’s open client half, a migration source only. F9 reads it; proposal fallback for F9 is a manual checklist |
| Export document rule | export_document_rule |
designed (PRD/arch) | F10 | Base + per-country + per-certification document rules (admin reference data) |
| Per-deal doc checklist state | ChecklistEngine instance |
designed (built by F7-b — FS-13 cancelled, AD #29) | F10 | Manually-toggled items — so the engine must support stored (toggled) items as well as computed ones (F7’s) — worth stating in the F7-b spec |
F9 itself stores nothing: it is TagSetCompatibility (extension point 4) — a set-difference of
required vs held certification sets, computed at read time. Deal-level “certifications held” chips
are derived from the deal’s buying standards.
7. Communications — mailbox, email, templates, sequences, calendar — comms
| Entity | Home | Status | Realized by | Notes |
|---|---|---|---|---|
| Mailbox connection | mailbox_connection |
confirmed — F20-a spec (approved 2026-09-21), row adopted unchanged | F20-a | N:1 app_user. Holds envelope-encrypted refresh token, Graph subscription state. F20-a ships the table with the owner nullable from the start, for the shared info@ inbox as an unowned connection (OQ-14), so F20-e would need no schema change; it also adds partial unique indexes enforcing at most one non-DISCONNECTED connection per owner and per provider_account_id. Since 2026-09-24 there is no unowned connection in V1: F20-e has left V1 and info@ reaches the CRM by forwarding to a rep, so no V1 row uses the nullable owner; whether to make it NOT NULL is an open schema question (ASK FIRST). A connection is only ever its owner’s — no key reads another person’s mailbox (AD #34). F20-b adds four sync-state columns by ALTER (spec FR-2b): delta_token, last_synced_at, backfill_cursor, backfill_completed_at — no edge changes |
| Email message | email_message |
confirmed — F20-b spec (approved 2026-09-23), row revised on both halves (spec D-4, FR-2) | F20-b (table, N:1 mailbox_connection, both directions) · F20-f (the auto-link edges, through email_link) · F20-g (tracking state) |
N:1 mailbox_connection — the only edge F20-b ships: a message is stored against its connection and nothing else. F20-b stores direction (derived at ingest from the sender, never from Graph), the Graph message id, internet message id and conversation id (threading — stored from the first migration so F20-f’s grouping is a rendering change), subject, participants (parallel TEXT[] pairs), sent/received times, the body in Postgres, not S3 (spec D-1), snippet, has_attachments, and the ai_drafted + “edited by you” flags; unique on (mailbox_connection_id, graph_message_id). email_link (below), so one message can be linked to several records, one row per record (F20-f D-1); email_message itself is unchanged. Since F20-f, a message is stored only while it has at least one link, once F20-f’s sweep has run for its connection (F20-f FR-5, FR-8, FR-21). Tracking state is F20-g’s and is not a column here. Explicitly not F20-a’s (its decision D-4) |
| Email link | email_link |
confirmed — F20-f spec (approved 2026-11-19; D-1) | F20-f · F11-e (a converted lead’s links) · F20-j (past mail) | One row per link of a stored message to one record: N:1 email_message (not null) and exactly one of N:1 lead, N:1 account, N:1 contact (a CHECK, as record_share does — AD #35); the method (ADDRESS, THREAD, SEND, MANUAL), linked_by (N:1 app_user, NULL for a link the system made) and the BaseEntity columns; unique per message and record column. Linked mail follows its record’s reach (AD #35, AD #37; F20-f D-9). Deals and quotes join in Phase 2 with a column each, as record_share will (F2-c). Removing a message’s last link deletes the message (F20-f D-7) |
| Email link refusal | email_link_refusal |
confirmed — F20-f spec (approved 2026-11-19; D-7) | F20-f · F20-j (checks it) | One row per unlink: N:1 mailbox_connection, the message’s Graph id and the record (one of lead, account, contact), holding no message content. Automatic linking — F20-f’s and F20-j’s — makes no link of that message to that record while the row exists; a manual link of that message to that record removes it |
| Email attachment | email_attachment |
confirmed — F20-f spec (approved 2026-11-19; D-6) | F20-f | One row per file of a stored message (the files the email carried, as received from Microsoft), inline images included: N:1 email_message, the Graph attachment id, the name, the content type Graph reports, the size, the inline content id, the S3 key (prefix email-attachments/<message id>/<attachment row id>) and a state (PENDING, STORED, SKIPPED_TOO_LARGE, FAILED), or one marker row in state LIST_UNAVAILABLE with no name, size or Graph id when the message’s attachment list could not be read. Not an FS-11 attachment row and not through its upload pipeline (F20-f D-6): any content type is kept, nothing is scanned, and the row and every S3 object version are deleted with the message (F20-f D-7) |
| Email block | email_block |
confirmed — F20-k spec (approved 2026-11-20; D-2) | F20-k · F20-f and F20-j (their automatic linking checks it) | One row per blocked address or domain: N:1 app_user as owner_user_id (the user whose personal list it is, or NULL for the organisation list), kind (ADDRESS, DOMAIN), value (trimmed, lower case) and the BaseEntity columns; unique per list, kind and value; at most 1,000 entries per list. A blocked address never causes an automatic link (F20-k D-1); no other table references it |
| Email send | email_send |
confirmed — F20-c spec (approved 2026-10-12; FR-R1) | F20-c | One row per send from the CRM: idempotency key unique per sender, a hash of the request, N:1 app_user (sender), N:1 mailbox_connection, kind (new / reply / reply-all / forward), N:1 email_message as the source (nullable), the origin as one nullable foreign key per type — lead, account, contact — at most one set (record_share’s shape), recipient count, the AI flags, status (SENDING / SENT / FAILED / UNCERTAIN), the Graph draft id, the internet message id, and N:1 email_message as the match once the Sent Items copy is ingested (nullable). No address, subject or body — the content is stored once, in email_message. F20-f reads the origin from it to link mail to records; F20-h adds the files sent (email_send_file, below), the send’s stored deadline and the count of forwarded originals removed (F20-h FR-R1, FR-S4); F20-g adds tracked (F20-g FR-T1) |
| Email send file | email_send_file |
confirmed — F20-h spec (approved 2026-10-13; FR-R1) | F20-h | One row per new file a send carried: N:1 email_send, its position, N:1 attachment (the email_upload file; kept after the file is deleted, since the row is kept), the file name, content type and size. No file content — the sent file is in the user’s Sent Items |
| Email tracking | email_tracking |
confirmed — F20-g spec (approved 2026-11-09; FR-T2, FR-T5) | F20-g | 1:1 email_send for a tracked send only: the token (unique, at least 128 random bits), the open and click counts, the first and last open and click times. No IP address, user agent or location (F20-g decision 3). Read by F37-b’s per-send summary (F20-g FR-T6) |
| Email tracked link | email_tracked_link |
confirmed — F20-g spec (approved 2026-11-09; FR-T3) | F20-g | One row per rewritten link of a tracked send: N:1 email_send, its position, the original address (the only place the redirect reads from), its click count and last click time. Reached by the data-protection erasure path with the rest of a person’s email |
| Scheduled email | scheduled_email |
confirmed — F20-g spec (approved 2026-11-09; FR-L2) | F20-g | One row per email scheduled for later: idempotency key unique per sender, a request hash, N:1 app_user (sender), N:1 mailbox_connection, kind, the source as N:1 email_message or a live Graph id (at most one), the origin as one nullable foreign key per type — lead, account, contact — at most one set, the recipients, subject and cleaned body (cleared once sent, cancelled or dismissed), the AI flags, track, the files and removed originals, send_at, status (SCHEDULED / SENDING / SENT / FAILED / UNCERTAIN / CANCELLED), the failure reason, attempts, and N:1 email_send once sent. Its uploads are kept from F20-h’s clean-up while it can still be sent (F20-g FR-L10) |
| Email template | email_template |
confirmed — F20-d spec (approved 2026-10-15; FR-T1) | F20-d | A name, a subject and a body (HTML, cleaned to F20-c’s allow-list) holding merge-field tokens {{group.field}}; visibility shared (every email.manage holder uses it; settings.manage edits it) or personal (N:1 app_user as owner, owner only — a check constraint ties the owner to the kind); an optional purpose, unique where set, whose one value weekly_prices marks the template F26-d fills; deleted by marking (deleted_at, deleted_by), never removed; no attachments. Used by composes (F20-d), the weekly price send (F26-d), sequence steps (F25-a) and list sends (F37-b) |
| Price email | price_email, price_email_price |
shipped — F26-d (spec FR-R1; V48__price_email.sql) |
F26-d | One row per weekly price email sent and recorded: N:1 account, N:1 contact (the addressee), N:1 auction_sale (the week), 1:1 email_send (unique — the F20-c send that carried it; its sender, status, kind and origin are checked when the row is written), the sender, the send time and the number of standards; and price_email_price, N:M buying_standard_price — the prices listed. Insert-only (a trigger refuses update and delete). No address, subject or body. Writes one timeline_event row keyed on the send (the price-email rule); read by the account header’s “sent this week” marker and by F37-a, which records each email it sends the same way |
| Price broadcast | price_broadcast, price_broadcast_item, price_broadcast_item_price |
confirmed — F37-a spec (approved 2026-11-15; FR-B1) | F37-a | One row per confirmed batch of weekly price emails: N:1 app_user (the sender), N:1 auction_sale, the copies and tracking choices, a status (SENDING, FINISHED, CANCELLED) and an idempotency key unique per sender. price_broadcast_item, one per customer in the batch: N:1 price_broadcast, N:1 account (unique within the batch), 0..1 contact (the addressee, stored before the send), 0..1 email_send, 0..1 price_email, a status, a reason and the attempt, re-claim and record counts the sweep uses; and price_broadcast_item_price, N:M buying_standard_price — the prices the email listed, stored before the send so the price email can be recorded later. No address, subject, body or price amount. Each item sent writes one price_email row the same way F26-d’s account-page send does |
| Bulk email | bulk_email, bulk_email_item |
confirmed — F37-b spec (approved 2026-11-19; FR-B1) | F37-b | One row per confirmed bulk send: N:1 app_user (the sender), 0..1 saved_view (the contact list it was made from, set to null when the view is deleted; the list’s name is kept at confirmation), 0..1 email_template (when one was used), the subject and the body with merge fields unfilled, track, the status, an idempotency key unique per sender. One item per contact: N:1 bulk_email, N:1 contact, the address it went to, the status and reason, 0..1 email_send once sent, a unique unsubscribe token (at least 128 random bits), and when it bounced and when its token was used |
| Bulk email suppression | bulk_email_suppression |
confirmed — F37-b spec (approved 2026-11-19; FR-U1) | F37-b | One row per address left out of every later bulk send: the address in lower case (unique), the reason (UNSUBSCRIBED, BOUNCED), when, 0..1 bulk_email_item (the email that caused it). Keyed by address, not by contact, so it holds whichever contact carries the address; read by F37-b only, never by F20-c, F20-g or F37-a. There is no per-contact consent state (PRD Rev 103) |
| Tracking event | email_tracking_event |
proposed | F20 | Open/click events N:1 message (or counters on message — F20’s call) |
| Sequence | sequence |
designed (PRD) | F25 | target_type locks to deals-only or leads-only on first use (PRD lifecycle) |
| Sequence step | sequence_step |
designed (PRD) | F25 | Ordered; kind email (N:1 template, AI-drafted, HITL) or task |
| Sequence enrollment | sequence_enrollment |
designed (PRD) | F25 | N:1 sequence + exactly one of deal / lead; status, current step, next-fire time; sends become email_message rows after human approval |
| Calendar sync link | calendar_sync_link (or column on activity) |
proposed | F21 | Maps activity ↔ Graph event id for two-way sync. External non-CRM events: read-through or lightweight cache — F21’s call, flagged not settled |
| Contact sync link | the contact suggestion table (name set by F22’s plan) | confirmed — F22 spec (approved 2026-11-20; FR-5) | F22 | One row per (user, Graph contact id) for each Outlook contact that is suggested, dismissed or added: N:1 app_user, N:1 mailbox_connection, the copied name, job title, company, addresses and phones, N:1 account (the suggested account, nullable), a state (suggested / dismissed / added) and, when added, N:1 contact. Two users who hold the same person each have their own row; matching every address (F20-l) keeps the person to one CRM contact when the copies share an address (one held under different addresses can still become two). A row other than added is deleted when its Outlook contact is deleted, when it now matches a CRM contact, or with the connection or the switch; an added row then keeps only its state and contact id (spec FR-4, FR-5, FR-11, FR-13) |
8. Activities, notes, timeline
| Entity | Home | Status | Realized by | Notes |
|---|---|---|---|---|
| Activity | activity |
confirmed — F3-a spec (FR-S2, FR-S3, approved 2026-10-13; Rev 29) | F3-a (account and lead follow-ups) · F3-b (the deal link) | N:1 activity_type; title; notes (one free-text field — no separate agenda or meeting notes; F16, which would have read them, was dropped — PRD Rev 61); links → account / lead (each N:1 nullable, exactly one by the named CHECK activity_has_one_record) — no deal column yet: F3-b adds deal_id and widens the check in its own migration, as F2-a does for note; assignee (assignee_id, N:1 app_user, required): an active user who holds the linked record’s manage key and reaches it, chosen when the follow-up is created or edited — the assignee gives no reach of its own, so a follow-up is not an owned record in the AD #35 sense and does not join F5-c’s owned-records refusal (F3-a decision 8); due_date (a Colombo business date) + optional due_time; done_at / done_by (both null or both set); soft delete — deleted_at / deleted_by, as F1-e’s notes. No stored status: overdue / today / upcoming / done is derived against the Colombo date (F3-a FR-A3). Reach follows the linked record — the record’s view key to read, its manage key for every write, no activity.* key (AD #34, AD #35; F3-a FR-P1) |
| Activity type | activity_type |
confirmed — F3-a spec (FR-S1, approved 2026-10-13; Rev 29) | F3-a (the built-in rows) · F3-b (custom types) | key (unique, never changed), label, sort_order, active, built_in. F3-a’s migration seeds six built-in rows — call, meeting, task, deadline, email, lunch — and has no route that writes the table; F3-b adds the admin-defined custom types as rows with built_in = false |
| Email follow-up | email_follow_up, with email_follow_up_email and the one-row email_follow_up_setting |
confirmed — F44 spec (FR-S1, FR-S2, approved 2026-11-19; Rev 51) | F44 | One row per follow-up F44 raised for an unanswered linked email: 1:1 activity (required, unique — the follow-up a person sees is an ordinary F3-a activity, type email, title Reply to email); N:1 email_message (nullable, ON DELETE SET NULL — the copy it points at); N:1 mailbox_connection (required — the mailbox it was raised from) and that copy’s Graph conversation id; the D-7 anchor (LEAD, ACCOUNT, CONTACT_ACCOUNT); open (mirrors the activity), outcome, reopened_at. email_follow_up_email (N:1 email_follow_up) lists every email key (Internet message id) the follow-up covers, unique, so an email raises at most one follow-up. No row is ever deleted. Reach follows the activity’s record (F3-a FR-P1); the follow-up holds no email content, and its sender and subject are read from the linked email while it is still linked to that record (F44 FR-A2). Closing one writes no timeline row (F44 D-10) |
| Note | note |
shipped — confirmed by F1-a (spec FR-S4; V21__note.sql; the account and contact notes API is F1-e’s) · lead notes API built — F11-f (spec FR-N1, FR-N2; Rev 23): GET and POST /api/v1/leads/{id}/notes, list and add, in the lead package; lead note edit and delete built — F11-f (spec FR-N4, Rev 13; Rev 43): PATCH and DELETE /api/v1/leads/{id}/notes/{noteId}, on F1-e’s rule for every record type — any holder of lead.manage who reaches the lead may edit or delete any note on it, a deleted note is marked and left out of the list, and each note carries editedAt, editedBy and version |
F1-a (table) · F1-e (account/contact notes API, note editing) · F11-f (lead notes API) | Author (created_by) + body; links → account / contact / lead (each N:1 nullable, at least one by the named CHECK note_has_target); lead_id shipped — confirmed by F11-a (spec FR-S3), which re-added note_has_target with lead_id and added note_lead_exclusive: a lead note has the lead as its only target, so its reach is the lead’s and no precedence is needed. deal_id is added by F2-a, which widens the CHECK again in its own migration. Migration identity per D-11. Reach (AD #35, F1-a WI-3.1 plan D16 — confirmed by F1-a): a note holds no permission key of its own and follows its parent’s reach, gated on the parent’s view / manage. A note with both account_id and contact_id follows its account — the account governs whenever it is set, and the contact’s reach applies only when account_id is null — because the account is the owned record, a contact can move between accounts, and the timeline already follows its account. A note whose only target is an account-less contact is therefore unowned, reachable only at all scope. When F11-a and F2-a add their columns they state the precedence on the same principle: the owned record the note was filed on governs. Account and contact notes API — specified by F1-e (spec FR-N1–N7, 2026-10-10; PRD Rev 59): list, add, edit and delete under /api/v1/accounts/{id}/notes and /api/v1/contacts/{id}/notes, in the account package; an account note has account_id only and a contact note contact_id only; any holder of the parent’s manage who reaches it may edit or delete any note, with optimistic locking on version; a deleted note is marked, never removed — F1-e adds deleted_at and deleted_by (null until deleted), and every route leaves a deleted note out; editedAt / editedBy are modified_at / modified_by once version is above 0. Lead notes adopted the editing and deletion rule in F11-f Rev 13 (Rev 43) |
| Timeline | timeline_event (stored) |
shipped — confirmed by F1-a (spec FR-S6, revising D-7; V23__timeline_event.sql) |
F1-a (table) · F1-c (rendering) · each producer its own rows (F1-e, F3-a, F11-f, F20-f, …) | One insert-once row per event: N:1 account (required), N:1 contact (nullable), kind (lead / order / sample / call / quote / note / email / activity), occurred_at, summary, actor_id → app_user (nullable — system-written rows have none), and the producing record as (source_type, source_id), loosely referenced per D-8. source_type and source_id are both NOT NULL, so a producer with no natural source record cannot write a timeline row — every future producer designs against this. UNIQUE (source_type, source_id, kind) makes a producer’s retry a no-op (AD #13). Immutable except summary and version — and, since F39, the modified_at / modified_by pair that stamps a summary refresh: F39’s FR-10 migration adds both columns and re-creates the forbid_value_update trigger with the writable set summary, version, modified_at, modified_by (spec FR-10, W-1); every other column stays immutable. When a source record changes, its producer refreshes summary through the same key — and must bump version in the same write (version = timeline_event.version + 1), because the trigger permits the bump but never performs it and version is the row’s only change signal. Indexed (account_id, occurred_at DESC) for F1-c and the account list’s lastContactAt / dormant (AC-28). A row follows its account’s reach (AD #35) and holds no key of its own. The lead rule (F11-f FR-T1–T5, T4a): while a lead is matched to an account, that account has exactly one row for it — source_type = 'lead', kind = 'lead', source_id the lead, the matched contact, occurred_at the lead’s received_at — whose summary is Inquiry received (leadSource changes; the lead service inserts, moves or deletes the row in the same transaction as the match change. The summary carries nothing else from the lead, the row is shown to every reader of the account (opening the lead still needs reach to the lead), and it counts as contact for lastContactAt and dormancy (F1-c owns the final rule). The note rule (F1-e FR-N6, FR-N7 — specified 2026-10-10): each account note has exactly one row — source_type = 'note', kind = 'note', source_id the note, contact_id null, occurred_at the note’s created_at, actor_id the author — whose summary is the body with whitespace collapsed, cut to 200 characters with …; editing the note rewrites the summary (bumping version and stamping modified_*), deleting it deletes the row. A contact note writes no row, because a contact’s account can change and a row’s cannot. A note row counts as contact, as Phase 1 criterion C1 states. The activity rule (F3-a FR-T1–T4 — specified 2026-10-13): each account follow-up that is done has exactly one row — source_type = 'activity', kind = 'activity', source_id the follow-up, contact_id null, occurred_at its done_at, actor_id the user who marked it done — whose summary is …. Reopening or deleting the follow-up deletes the row, and marking it done again writes a new one; editing the type or title of a done follow-up rewrites the summary (bumping version and stamping modified_*). An open follow-up writes no row, a lead follow-up writes none, open or done, and a follow-up F44 raised writes none (F44 D-10, Rev 51). An activity row counts as contact, so completing an account follow-up moves the account’s lastContactAt (F1-c owns the final rule). The price-email rule (F26-d FR-R3 — specified 2026-10-16): each recorded price email has exactly one row, keyed on its F20-c send — source_type = 'email_send', kind = 'email', source_id the email_send id, contact_id the addressee, occurred_at the send time, actor_id the sender — whose summary is Weekly prices sent for …. When a row under that key already exists (F20-f linked the Sent Items copy first), F26-d rewrites its summary instead of inserting. F20-f keys the row of any CRM-sent message on the same key and writes none when one exists, so a send has one row and F20-f never reads F26-d’s tables (AD #31). It counts as contact |
| Tag | tag + joins account_tag, contact_tag (F1-a) · lead_tag (F11-a) · deal_tag (F2-a) |
shipped — confirmed by F1-a (spec FR-S5; V22__tag.sql; the read-only tag list GET /api/v1/tags?entityType=… is F11-b’s — F11-b spec FR-A5, Rev 21 — and the tag writes and account/contact assignment are F1-e’s) · lead_tag shipped — confirmed by F11-a (spec FR-S2) · lead tag routes built — F11-f (spec FR-G1–FR-G3; Rev 23): attach and detach, PUT / DELETE /api/v1/leads/{id}/tags/{tagId}, in the lead package; lead tag create, POST /api/v1/tags with entityType: lead, in tag, taking lead.manage through the tag.TagWriteAccess contribution point that lead fills, so tag imports nothing from lead — F1-e makes account and contact tags creatable by adding its own contributions |
F1-a (tag, account_tag, contact_tag) · F11-a (lead_tag) · F2-a (deal_tag) · F11-b (read-only tag list) · F1-e (tag rename/retire, account/contact assignment and create) · F11-f (lead tag attach, detach and create) |
Labels per entity type, as Pipedrive’s are: tag.entity_type ∈ account / contact / lead / deal (a vocabulary CHECK, so the last two need no migration when their joins arrive); name, colour, archive flag; unique on (entity_type, lower(name)) by a functional index. Each join is M:N <entity> ↔ tag with a composite primary key and both foreign keys (plus the base id, version, created_* its entity maps). Consumed by F32’s saved filters. Gating (F1-a WI-3.1 plan D16 — confirmed by F1-a): the tag name list is reference data, readable by any signed-in user (like the certification list); attaching or detaching a tag — and creating a new name while attaching — takes the tagged entity’s manage key (account.manage for an account or contact tag); renaming or retiring a name changes it on every record, so it takes settings.manage (AD #34). A join row follows its parent’s reach (AD #35). Account and contact tag routes and tag configuration — specified by F1-e (spec FR-G1–G7, 2026-10-10; PRD Rev 59): GET / PUT / DELETE under /api/v1/accounts/{id}/tags and /api/v1/contacts/{id}/tags; POST /api/v1/tags for account and contact through two TagWriteAccess contributions in account; PATCH /api/v1/tags/{id} under settings.manage for name, colour (one of the four Badge tones, or none) and active; the tag list gains an active filter (its items stay { id, name, colour }), and GET /api/v1/tags/{id} returns one tag with its entityType, active and version. A tag is retired (is_active = false), never deleted: it stays on its records, cannot be newly attached through the routes, and can always be detached; the migration’s service path may attach a retired tag (FR-G7) |
| Order (ERP reference) | order |
not in V1 (F1-a builds no order table — OQ-63) |
F1 | Read-only reference rows: N:1 account, ref, value, date, source = erp. Feeds LTV, dormancy, reorder triggers, F13 briefings |
9. AI layer — ai
| Entity | Home | Status | Realized by | Notes |
|---|---|---|---|---|
| AI invocation log | ai_invocation |
built by F12 (spec FR-B6; V38__ai_invocation_and_lead_tea_fields.sql) |
F12 (the AI capability’s first consumer); every later AI feature writes through it | One insert-only row per call to the AIProvider seam, whatever the outcome: feature key, provider, model, prompt id and version, user (null for a SYSTEM caller, no foreign key), outcome, tokens, cost, latency, attempts — what makes spend attributable — with an ai.invoked audit event in the same transaction. Never the prompt, the user text or the reply (AD-O-6). References no record: the call’s result goes back to the caller, and the extraction’s id is this row’s (forge-et-be/docs/data-model.md §6) |
| AI suggestion (HITL queue entry) | ai_suggestion |
proposed — not built by F12 (a synchronous, user-invoked extraction needs no queue; F12 spec § Out of Scope) | the first background AI feature / F11 — not built by F11-d either (the first background AI feature: its tag and proposed match each have a typed home and action — F11-d decision 11, Rev 32) | Generic proposed-action record (feature, target, payload, status proposed→approved/rejected, edited flag, actor) — the module layout names a “HITL queue”; features project it into their surfaces. Approved content lands in typed homes (lead fields, email drafts) |
| AI feature config | ai_feature_config |
designed (AD #19) — not built by F12: per-feature {model, enabled} is the et.ai configuration, read through one resolver so this table can replace it; the administrator switch and the monthly ceiling are F12 spec Q-1 |
owner open (F12 spec Q-1) | Per-feature {model, enabled} runtime config + monthly ceiling — admin-editable without deployment |
| Prompts / extraction schemas | code, in the ai package |
designed (arch guardrail) | F11–F15, F36 | Not data — prompts stay rich and live in one place (P-21; the former PromptCatalog extension point) |
Lead triage outputs (priority, confidence, summary, why, recommended action, draft reply) land as
typed columns on lead — generic triage vocabulary, not tea. Account briefings / morning
briefings / quote-assistant reads are generated, not persisted (regenerate on demand;
consistent with AD-O-6) — if caching is ever wanted it is a feature-spec decision.
10. Data management & integration — datamgmt, integration
| Entity | Home | Status | Realized by | Notes |
|---|---|---|---|---|
| File | attachment |
shipped — FS-11 (V9__attachment.sql; V33, V44) · D-6 revised by F33-a (spec decision 1, Rev 41) |
FS-11 (table) · F33-a (accounts, contacts, leads) and F33-b (deals, quotes, samples, briefs) — Rev 39 · F20-h (email_upload) |
One table: the opaque S3 key, the original name, the sniffed content type, the size, the uploader (N:1 app_user) and the scan status (PENDING, CLEAN, INFECTED, NOT_SCANNED). A file names its one record by owner_type and owner_id, with no foreign key and no link tables (D-6, revised): account and lead, contact (F33-a — it follows its account’s reach and keys) and email_upload; each owner type has its own visibility and upload policy, and a record’s file follows the record’s reach (AD #35). F20-h (spec approved 2026-10-13): owner type email_upload (owner id the uploader’s app_user id), for files attached to an email before it is sent; and deleted_at — a deleted file keeps its row and its S3 version behind a delete marker, recoverable on request (proposal §A.5, §A.6.3), except FS-11 FR-S3’s purge on infection. F33-a deletes a record’s file the same way |
| Custom field definition | custom_field_definition |
designed (AD #8, F34); specified for account, contact and lead by .forge/specs/F34-c-custom-fields-accounts-contacts-leads-spec.md (approved 2026-11-08) |
F34-c (account, contact, lead); F34-a (deal, quote, buying standard, sample) | Per entity (account/contact/deal/lead/quote/buying standard/sample); values in a custom_fields JSONB column on each of those tables (a JSONB column on sample is fine — the ban is on core→tea FKs and tea words in core, not on tea tables having columns) |
| Record share | record_share |
confirmed — F5-f spec (FR-S1, approved 2026-10-02; AD #35; PRD Rev 29 / OQ-58) | F5-f | The third visibility path beside ownership and the reporting line. shared_with_user_id (N:1 app_user), shared_by_user_id (N:1 app_user) and shared_at, plus links → account_id / lead_id now, deal_id / quote_id when those tables land (each N:1 nullable, exactly one by a CHECK — the same shape as activity above, and chosen for that reason); unique per (record, user). No access level column, deliberately: a share widens reach, the recipient’s role grants authority (design/data/record-sharing-model.md §2.2). Owner stays singular on the record itself (owner_id). A share counts for its recipient and for everyone above them in the reporting tree while the recipient is active — shares roll up (AD #35, restated by AD #37 without scopes), and a share to a deactivated user is inert until they are reactivated (PRD Rev 49); a record with no owner cannot be shared; sharing is transitive; sharing, removing a share and reassigning the owner need the entity’s manage. Children — contacts, notes, tasks, attachments, timeline, record-linked email — follow their parent’s reach. Cascade-deletes with its record. Confirming spec: F5-f, which builds the owner pattern, this table and the one visibility rule (lists and per-record checks) before any list read-path. |
| Saved view | saved_view |
settled — F32-d spec (FR-V1–FR-V5; PRD Rev 87) | F32-d (accounts, leads, contacts) · F32-a (deals, quotes, samples, standards) | N:1 app_user owner; the list it belongs to; name unique per owner and list regardless of case; personal or shared with everyone who holds the list’s view key; filters as JSONB (the list endpoint’s own parameters), sort, columns (the list row’s member names); only the owner changes or deletes it, except that settings.users.manage may delete a deactivated owner’s shared view |
| List preference | list_preference |
settled — F32-d spec (FR-C3) | F32-d | A user’s column choice for one list: N:1 app_user, the list, columns (the list row’s member names); one row per user per list |
| Import job | import_job (+ row-error child) |
proposed | F4/F23 | The shared engine’s run record: source, mapping, counts, re-runnable (AD-O-7 replay + delta pass) |
| External ref (migration identity) | columns on migratable entities | proposed (D-11) · on account: settled by F4-d (spec FR-S1; V50__account_source_owner_name.sql) · on lead: settled by F4-b (spec FR-9) |
F4 | source + source_ref (Pipedrive id) on lead/contact/account/deal/activity/note — written with OVERRIDING SYSTEM VALUE where ids are preserved (FR-P1); serves AD-O-7’s skip-already-migrated delta pass. account and lead also carry source_owner_name, the Pipedrive owner’s name, never shown, read only by F32-c’s hand-out filter (F4-d FR-S1; F4-b FR-9): a nullable TEXT that only a Pipedrive migration run writes — on an account on create, merge and update, on a lead on create and update (F4-b FR-5, FR-13); contacts do not carry it, because a contact follows its account |
| ERP handoff record | handoff |
proposed | F19 | On a Won deal: N:1 deal, readiness snapshot, status pending→generated, generated document (→ file). Payload/generator lives with the ERP feature (ErpHandoffService; the former HandoffTarget extension point) |
| Search | — | withdrawn (Rev 25) | — | Global search (F31) is dropped and AD-O-2 closed (PRD Rev 60); no tsvector column or index is built. Each list endpoint keeps its own q filter |
Reference lists (PRD §Domain Model last row): activity types, customer/buyer types, currencies, countries, grades, packaging formats, markets, certifications, export-doc rules, couriers. Each is admin-managed reference data; the owning feature spec decides lookup-table vs validated-enum per list.
Capture app (F36): the PWA keeps its own repo and, on the phone, any capture not yet sent (F36-a’s keep-and-resend; F36-e’s offline queue was dropped). A capture is not a lead when it arrives. Since PRD Rev 95 the backend keeps it as a captured lead in the capture app until a person converts or rejects it, and since PRD Rev 96 the reps plan each show in the app with a capture campaign and its exhibitors; PRD Rev 97 adds the three to §Domain Model › Key Entities and the captured lead’s states to §Lifecycle States. The three tables are F36-a’s, in the capture package (F36-a spec FR-L1, FR-P1, FR-P3, decision D-3):
| Entity | Home | Status | Realized by | Notes |
|---|---|---|---|---|
| Captured lead | captured_lead |
shipped — F36-a (spec FR-L1; V71__captured_lead.sql, campaign_id in V73__capture_plan.sql, later columns in V75__captured_lead_card_read.sql and V77__captured_lead_review.sql) |
F36-a · F36-b (the card’s contact fields) · F36-c (its research and ranking, in capture_research) |
One card photo or badge captured at a show: the capturing user, a client id unique per capturing user (the upload’s replay key, FR-C6), kind card or badge, the captured time, the booth note, the photo’s object key (a private capture/ key in the attachments bucket, read only through the capture API — not an attachment row, FR-C7) or the badge’s raw text, the contact fields, status to_review → converted or rejected, and rejected → to_review (PRD Rev 97), with who decided and when, the CRM lead’s id on conversion, and the reject reason and note. No owner and no reach (FR-L2): every holder of capture.use sees every captured lead; AD #35’s owner applies only to the CRM lead a conversion creates |
| Capture campaign | capture_campaign |
shipped — F36-a (spec FR-P1; V73__capture_plan.sql) |
F36-a · F36-c (its focus) | One trade show: a name, the show’s first and last day (both optional), archived and restored. An optional focus (F36-c spec FR-FC1, its wave 3): one line of at most 300 characters that F36-c’s ranking reads for the campaign’s exhibitors and captured leads, as data; editing it ranks them again (FR-TR7). Upcoming, Running and Ended are computed from the days and the business clock, not stored. The rep’s active campaign is kept on the phone per user, not in the database (FR-P2). Not the marketing Campaign F28 would have built (dropped, PRD Rev 66) |
| Exhibitor | exhibitor |
shipped — F36-a (spec FR-P3; V73__capture_plan.sql) |
F36-a · F36-c (its research and ranking, in capture_research) |
A company exhibiting at a show: company, website, booth code, hall, country, notes, how it was added (upload or manual), archived with an optional reason and note, and one visit — a note and a visited mark, each with who and when — shared by every holder of capture.use (FR-P6). An uploaded list is read and imported request by request; nothing is stored between its preview and its import (FR-P5) |
| Capture research | capture_research |
shipped — F36-c (spec FR-ST1; V76__capture_research.sql, wave 1; its captured-lead columns used from wave 2) |
F36-c | One row for each researched captured lead or exhibitor — exactly one of the two, each at most once. It holds the research (its state, the company findings and, for a captured lead, the person findings, the confidence, the research call) and the ranking (the priority P1–P3, shown in the app as the tier Hot, Warm or Cold, a fit score from 0 to 100, the reason, the next action, the basis, the ranking call and, from F36-c’s wave 3, the campaign focus the ranking read, ranking_focus (FR-ST1, FR-FC4)), with the fingerprint of the inputs it was computed for, the counted failures, and for a captured lead the removal of the person’s findings and when its Ready to review notification was written. Written only by F36-c’s research jobs, Research Again and the person removal, each audited; nothing is added to captured_lead or exhibitor, so a background write never refuses a rep’s open edit (FR-ST1, FR-ST3). Raw search results are never stored |
| Capture notification | capture_notification |
shipped — F36-c (spec FR-ST2; V79__capture_notification.sql, wave 2) |
F36-c | One in-app notification: its kind (ready, converted or rejected), the captured lead, and the user who converted or rejected it (none for ready). It holds ids only; its text is rendered from the captured lead when read. Kept; nothing deletes notifications in V1 (decision D-10) |
| Capture notification recipient | capture_notification_recipient |
shipped — F36-c (spec FR-ST2; V79__capture_notification.sql, wave 2) |
F36-c | One row for each notification and each user told of it — the show team (the capturing user and everyone who captured into the same campaign), less the user who acted — with when that user marked it read (FR-NT4, FR-NT5). Key (notification_id, user_id) |
Key edges (F36-a spec; PRD Rev 97): captured_lead N:1 capture_campaign (nullable — a capture taken with no active campaign, or naming an archived or unknown one, has none, FR-C6); exhibitor N:1 capture_campaign; captured_lead N:1 app_user as its capturing user, and as the user who converted, rejected or reopened it; captured lead → lead on conversion, 1:1, set only by converting (FR-L4): the conversion creates the lead through the F11 intake with source = 'capture' and source_ref = the captured lead’s id as the replay identity, lead_source = 'Trade Fair', lead_source_detail = the campaign’s name, the converting user as the lead’s owner, and a card’s photo as an attachment on the new lead (FS-11), and the captured lead keeps the lead’s id. A rejected captured lead never reaches the CRM.
Key edges (F36-c spec FR-ST1, FR-ST2, FR-CV1): capture_research 1:1 captured_lead or capture_research 1:1 exhibitor — exactly one is set on each row, and a subject has no row until its research job first writes for it; capture_research N:1 ai_invocation as its research call and as its ranking call; capture_research N:1 app_user as the user who removed the person’s findings (captured leads only); capture_notification N:1 captured_lead; capture_notification N:1 app_user as the user who converted or rejected the lead; capture_notification 1:N capture_notification_recipient; capture_notification_recipient N:1 app_user. On conversion a captured lead’s completed research is copied into the new lead’s lead_research row when the research’s fingerprint matches the new lead’s (FR-CV1) — a copy, not an edge. (F36-c Rev 6, from its wave 3) When the captured lead’s ranking is done for its current fingerprint, the conversion also copies it into the new lead’s lead_ranking row (FR-CV1; F11-g FR-S4): the tier as P1–P3, the confidence, the reason without its tier prefix, the next action and the ranking call, with origin = 'capture', the focus the ranking read in focus and the campaign’s name at conversion in focus_campaign_name — again a copy, not an edge, since focus_campaign_name is text and not a reference to capture_campaign; only the 0–100 fit score is not carried, and stays on the captured lead.
Cross-feature decisions this map proposes
Each is a recommendation with its confirming owner — none is settled until the named spec adopts it (or revises it with reasons).
D-1 — Quote hangs off the Deal; the account edge is pinned by a composite FK. data-model.md
§2.4’s quote carries account_id and no deal_id (it is the deliberately minimal
pricing-facing shape). The PRD says “N:1 Account/Deal” and every journey creates quotes from a
deal; both tea pipelines contain a quote stage. Recommendation: F7 adds deal_id NOT NULL, and
account_id stays but is made unable to disagree with the deal’s account via the FR-I1
snapshot-consistency idiom (deal carries UNIQUE (id, account_id); quote’s FK is composite
(deal_id, account_id)). If a genuinely deal-less quote emerges in F7 discovery, relax to
nullable — but decide it there, once. Confirms: F7 spec (with a §2.4 companion revision + proof
re-run, same procedure as FS-2’s FR-P1 rename).
D-2 — Deal line items and quote line items are two entities, not one. F26 requires buying standards
attached to the deal (qty + discount, rolling into deal value, from the first stage); F7 requires
priced quote lines with an immutable unit_price snapshot (§2.4 line_item, quote-scoped).
One entity cannot be both a mutable working set and an immutable snapshot. Recommendation:
deal_line_item (mutable; was deal_product — Rev 7) + line_item (quote-scoped, verified shape); creating a quote
seeds its lines from the deal’s standards, then the quote’s lines evolve with revisions. The PRD’s
own entity table already lists line items on both Deal and Quote. Confirms: F26 + F7 specs.
D-3 — The quote’s trade fields (Incoterm, destination market). These once had to live on a
quote_profile extension table, because the banned-vocabulary rule forbade incoterm as a core
identifier and the module layout assigned Incoterm to the tea-quoting module. FS-14 removed both,
so Incoterm + destination can sit on quote directly or on a quote_profile extension — a plain
data-model choice for the F7 spec. §2.4 already ships an incoterm column on quote (see C-1, now
resolved); the recommendation is to keep it there unless a profile split earns its keep.
Confirms: F7 spec.
D-4 — Lead carries a typed 1:1 extension table (sixth entangled entity). F12 extracts tea
vocabulary onto the lead (grade, volume + units, packaging, certifications, Incoterm, destination
port) and P-09 requires typed fields. The former banned-vocabulary rule forced these onto a
separate lead_profile (1:1 on lead); FS-14 removed that rule, so keeping a separate profile vs
folding the fields onto lead is now a plain data-model choice — record Lead’s shape here and in
data-model.md when F11/F12 confirm it. Confirms: F11/F12 specs.
Closed at Rev 15 — against the extension table. F11-a ships lead with every field as a flat
column, and F12 adds its extracted fields to lead itself, next to the prompt that fills them (F11-a
spec FR-S1, FR-D2 (a)). There is no lead_profile.
D-5 — Conversion links, not conversion moves. Converting a lead creates/matches an Account +
Contact and creates a Deal; the lead row is retained with nullable converted_* FKs to all three
and status: converted. Nothing is deleted (OQ-21’s import-everything posture extends naturally:
history is kept, always). Confirms: F11 spec.
F11-a (Rev 15) adds a separate, earlier link — the matched account and contact (key edges,
cluster 2) — which is not a conversion link. Whether a conversion starts from the match (pre-fills
the account and contact it creates or matches) is F11-e’s decision.
The lead’s email on conversion (Rev 34; the lead, 2026-10-26): every email linked to the lead is
also linked to the account, contact and deal the conversion sets — links to the one stored copy, not
copies — and a converted lead is no longer a target of automatic linking. Confirms: F11-e, F20-f.
D-6 — One file table; each file names its one record. Revised — confirmed by F33-a (spec decision 1), which adopts a different shape from the one this map recommended. The adopted shape: one attachment table (FS-11, V9__attachment.sql) holds the blob identity — the opaque S3 key, the original name, the sniffed type, the size, the uploader, the scan status and, since F20-h, deleted_at — and names its record in two columns, owner_type and owner_id, with no foreign key and no link tables. A file therefore belongs to exactly one record. Each owner type registers its own visibility (F5-f’s RoutingRecordVisibilityCheck routes the check to the type’s OwnerTypeVisibility) and its own upload policy (the type allow-list), and a file follows its record’s reach (AD #35). The owner types are account and lead (F5-f), contact (F33-a, following its account) and email_upload (F20-h); F33-b adds deals, quotes, samples and product briefs as owner types on the same table. Why no link tables (F33-a decision 1): the owner columns were already on main, the visibility routing already read them, and accounts and leads already used them, so a link table per record type would state the owner twice. Whether a lead’s files move or are copied when the lead converts is F11-e’s decision.
Original text, kept as the rationale that was revised: F33 attaches files to deals, accounts, contacts, quotes, samples and product briefs, and since Rev 39 leads — F33-a on accounts, contacts and leads, F33-b on the rest. Recommendation: file holds the blob identity (S3, uploader, scan state) and a small link table per target type attaches it. The former core→tea FK ban that shaped this is gone (FS-14 — one schema), but one blob table plus per-target link tables stays the clean design.
Confirmed by: FS-11 (the table) and F33-a (spec decision 1, approved 2026-11-06); F33-b adds its owner types.
D-7 — The account timeline is a projection, never a table. Revised — confirmed by F1-a (spec
FR-S6), which adopts the opposite shape: a stored timeline_event table. The adopted shape: one
insert-once row per event, written by the feature that owns the event, keyed
UNIQUE (source_type, source_id, kind) so a producer’s retry is a no-op (AD #13), and immutable
except summary and version by the forbid_value_update() trigger. The drift this paragraph
feared is closed by an edit rule rather than by not storing: when a source record changes, its
producer rewrites its own row’s summary through the same key (bumping version in the same
write), and kind, occurred_at, the source and the account never change — so the timeline never
shows text the source no longer carries. Why stored (spec FR-S6’s rationale): (1) several kinds have
no source table of their own in Phase 1 — a lead being approved or converted, an email being linked
to the account — so a union would have nothing to read; (2) the union would grow by one branch per
producing feature, coupling every producer to F1-c’s read path, where a write-a-row contract
decouples them; (3) the drift is contained, because the row carries a pointer and the few fields the
timeline renders, and the producing record stays the truth. The row follows its account’s reach
(AD #35).
Original text, kept as the rationale that was revised: every timeline kind
(lead/order/sample/call/quote/note/email) already has a source entity; a stored timeline_event
would be a second authoritative copy that drifts — the same reasoning that banned stored
current/expiring/expired (FR-T4). Cost: a union query per account page, immaterial at PRD volumes.
Confirmed by: F1-a (Q-1 acknowledged by Tharinda, 2026-09-24).
D-8 — Log-class tables reference records loosely; domain tables never do. audit_event
and ai_invocation reference “any record” — ten nullable FKs per log table is worse than a
(entity_type, entity_id) pair on an append-only log that is never joined for integrity. This is
a deliberate, named exception to the FK-integrity house style, scoped to log-class tables only —
with one domain table also named (Rev 41): attachment names its record by owner_type and
owner_id with no foreign key (D-6, revised by F33-a), because each owner type’s visibility routing
checks the owner on every read and write, and a link table per record type would state the owner twice.
timeline_event is the third log-class table (F1-a): its (source_type, source_id) pair names
the producing record loosely, and both columns are NOT NULL, so a producer with no natural source
record cannot write a timeline row, and a producer that refreshes a row’s summary by raw SQL must
bump version in the same write. F1-e, F1-c, F3-a, F11-f and F20-f design against both rules.
Confirms: F39 (audit — AD #36; was FS-6) — the audit half confirmed by F39 (spec FR-1,
V24__audit_event.sql: entity_type is the record’s table name, both columns set or both null, and
the actor’s actor_user_id is also a loose id, with no foreign key — spec Rev 2) / the AI capability
(first consumer) spec; the timeline half confirmed by F1-a.
D-9 — Sample carries no direct account FK. Derivable via the deal; two paths drift. The PRD row listing both edges is read as navigational, not two stored columns. Confirms: F6 spec.
D-10 — Certification sets are join tables, not arrays. The architecture’s
required_certifications text[] sketch is illustrative; joins to certification give FK
integrity, admin-editable taxonomy, and clean set-difference queries for F9. OQ-9 may force the
standard side to a different grain (batch/lot) — the account side does not wait for it.
The adopted shape (confirmed by F1-a, spec FR-S3): account_certification (account_id, certification_id), a composite primary key and both foreign keys, into the admin-editable
certification reference table whose codes are stable and whose names are editable — shipped in
V14__certification.sql and V15__account.sql. The same join-not-array rule gives F1-a’s tags
their account_tag and contact_tag joins. Confirms: F1/F9 specs — the account side confirmed by
F1-a; the standard side is still F9’s (OQ-9).
D-11 — Migration identity is per-entity columns, not a mapping table. source +
source_ref (unique-per-source) on each migratable entity: typed, indexable, and exactly what
AD-O-7’s delta-reconciliation pass needs to skip already-migrated records. Confirms: F4 spec.
Contradictions and gaps surfaced (raise, don’t silently fix)
C-1 — quote.incoterm (§2.4) — RESOLVED by FS-14. data-model.md §2.4 — authoritative for
the schema, drift-checked against the proofs — declares incoterm TEXT on quote. This once
conflicted with the banned-vocabulary ArchUnit rule (which forbade incoterm as a core identifier)
and the module layout assigning Incoterm to the tea-quoting module — a JPA entity mapping that
column would not have compiled past the suite. FS-14 removed both the rule and the module split,
so the column compiles and the contradiction is gone. Whether Incoterm + destination live on
quote or on a quote_profile extension (D-3) is now a plain data-model choice for the F7 spec,
not a boundary question. Owner: F7 spec author.
C-2 — Nothing owns getting ERP order data into V1. order is load-bearing (LTV,
dormancy detection, reorder triggers, F13 briefings, the account 360°'s past-orders panel) and the
PRD marks it “ERP-sourced, read-only” — but V1 has no ERP integration (AD #10; automated IFS
sync is a priced follow-on) and no feature paragraph claims the import path. Options: an F23
spreadsheet import of an ERP order extract, a manual admin surface, or shipping F1 with the panel
empty-stated. Needs an owner and a client conversation (does an order extract exist?). Answered at F1 spec
time: V1 has no order entity and no past-orders panel (F1-a, F1-d); the question to Empire is OQ-63
in project-prd-signals.md.
C-3 — MOOT since FS-14. The old architecture module summary listed “Account, Contact, Deal, Pipeline, Stage, Activity, Note, Timeline” and omitted Lead. FS-14 removed that module layout, and this map’s spine diagram carries Lead as a first-class hub entity — so the omission has nothing left to mislead. Lead is a plain spine entity (P-05 replicates Pipedrive’s leads inbox; F11 redesigns it).
C-4 — The ChecklistEngine must support stored and computed items. F7’s readiness items
are computed from quote state; F10’s export-doc items are manually toggled and stored; F19 mixes
both. If the engine models only one kind, two of its four consumers don’t fit. Not a data-model
contradiction — a substrate requirement. FS-13 was cancelled (AD #29), and the engine is built by
its first consumer, F7-b (features.md). Owner: F7-b spec author.
Maintenance
- A feature spec that touches a listed edge cites this map in
## Input Sourcesand either adopts the row or revises it — a revised row gets the new answer, the spec pointer, and a status flip to settled. Silent divergence between a spec and this map is a review finding (/forge-spec-reviewdimension: consistency with dependency artifacts). - When
data-model.mdgains a specified section for a cluster, that section becomes the authority; the cluster here shrinks to edges + a pointer (as §4 already does for pricing). - Status flips (
proposed → settled,open → settled) happen when the owning spec is approved, in the same change that approves the spec. This document never outranks a realized schema. - The
## Revisionsconvention from the spec rules applies once the team accepts this draft.
Sources
Derived 2026-08-31 from: .forge/project-prd.md (Rev 12 body — §Domain Model, §Functional
Surface, §Lifecycle States, capability matrix), .forge/design/prototypes/feature-detail.md
(2026-08-21 verification), .forge/design/architecture.md (Rev 25 — module layout, extension
points, enforcement, pricing subsystem), forge-et-be/docs/data-model.md (§1 conventions, §2
verified pricing schema, §3–§7 stubs), FS-2 spec + shipped migrations (V1, V2, V200, V1000), and
the AD table in .claude/CLAUDE.md (#5, #8, #11, #14–#17, #26). Where a statement here goes
beyond those sources it is marked proposed and carries its confirming owner.
Revisions
Rev 1 — 2026-09-07
- Changed: header note, the
Homedefinition, D-4 and C-1 annotated for AD #29 — the core/domain split is no longer extended; the two-schema homes stand because the schemas and build guards FS-1/FS-2 shipped stay in place, not because a reusable core is still a goal. No edge, cardinality or status changed. - Why: AD #29 (2026-09-07) supersedes AD #17; this map’s placement arguments cited AD #17’s reuse goal and would otherwise read as if it still held.
- Impact: none on any spec; F7 (C-1) and F11/F12 (D-4) inherit a placement decision instead of a reuse argument.
- Approved by: Tharinda, 2026-09-07 — the approval on harness PR #52, alongside AD #29.
Rev 2 — 2026-09-07
- Changed:
Usergainsmanager_id(nullable self-reference — the reporting hierarchy behindteamscope);Rolebecomes admin-authored with the six confirmed roles as copy-on-create templates and an M:N user↔role edge unioning to the most permissive grant;Permission / grantbecomes(role, permission_key, scope)over a code-declared catalog (the permission metadata lives in code, not in apermissiontable) with scopeown/team/allper permission. Statuses staydesigned; realized-by widens to FS-6 + F5 forRole. - Why: AD #30 (2026-09-07, refining AD #15; PRD Rev 21) — Empire asked for fully configurable role management. The three rows cited the
(role, resource, level)model and would otherwise read as the shape FS-6 should build. - Impact: FS-6 spec Rev 3 (owed) confirms the table names —
role_grantis the FS-6 spec’s name and is used here provisionally; a role-template entity or flag is that revision’s to shape. Hierarchy policy (recursion, deactivating a manager with reports, unowned records, edit-vs-view) is OQ-56. No other edge changed. - Approved by: Tharinda, 2026-09-07 — with AD #30.
Rev 3 — 2026-09-07
- Changed: the two cluster-1 rows Rev 2 left provisional are confirmed against FS-6 spec Rev 3, authored the same day.
Rolegains the concrete shape —is_system(the non-deletable superadmin) andtemplate_key, provenance for display and “reset to template” and deliberately not a foreign key, so copy-on-create is a snapshot.Permission / grantrecords thatrole_grantis keyedUNIQUE (role_id, permission_key)and that the separate named-permission store is removed, so one table answers every check;APPROVE_SUB_FLOOR_QUOTEis written in its catalog form,quote.approve_sub_floor. No edge, cardinality or status changed. - Why: Rev 2 deferred exactly these two questions to FS-6 spec Rev 3 (“confirms the table names… a role-template entity or flag is that revision’s to shape”). That revision exists now, so the deferral is discharged rather than left pointing at a document that has already answered.
- Impact: none on any other spec. Template contents for Tea Room / Executive remain OQ-53(b); the hierarchy policy remains OQ-56; where template content physically lives is FS-6’s Q-12, still open as a plan decision, so this map names the columns and not the seed vehicle.
- Approved by: pending — rides FS-6 spec Rev 3’s lead ack (Tharinda), since it records that revision’s outcome and nothing else.
Rev 4 — 2026-09-10 — schema qualifiers dropped for the single-schema collapse (AD #31, FS-14)
- Changed: the header note, the
Homedefinition, the spine diagram, everyHomecell and the D-4 / D-6 / C-1 blocks are de-qualified — the formercore./tea.schema prefixes are removed (onepublicschema since FS-14), cluster headings name the feature-first capability instead of the removedcore-*/tea-*module, and the placement arguments that once cited the banned-vocabulary rule or thecore → teaFK ban are reframed as plain data-model choices (those guards are gone). C-1 is marked RESOLVED —quote.incotermno longer conflicts with anything once the vocabulary rule is removed. - Why: AD #31 (2026-09-09, FS-14) collapsed the two schemas into one and removed the split’s guards. The map’s homes and its boundary-driven reasoning would otherwise read as a schema layout and a set of constraints that no longer exist.
- Impact: no edge, cardinality, status or entity changed — this is a naming/qualifier pass plus the discharge of C-1. The typed 1:1 extension tables survive as data-model design (now unqualified); a feature spec may fold one into its base table (D-4 notes this for Lead).
- Approved by: Tharinda, 2026-09-09 — the AD #31 lead sign-off (the FS-14 cascade carries it).
Rev 5 — 2026-09-11 — the price loses its market axis (PRD Rev 23)
- Changed: the Price edge becomes
Buying standard × validitywith status revision owed; themarketnote states it is a customer attribute and quote field, not a price key. Cost was already market-free. - Why: Empire confirmed one price per standard on 2026-09-09 and their weekly price workbook matches; PRD Rev 23 records the decision (Dinil, 2026-09-11) and revises OQ-38’s Rev 6 answer.
- Impact:
forge-et-be/docs/data-model.md§2.2 and the price/snapshot DDL it describes need revising before the F26 spec — owner Tharinda (AD #31). No shipped migration carries the axis. The quote’s destination-market trade field (D-3) is unaffected.
Rev 6 — 2026-09-11 — the role model is simplified (AD #32, PRD Rev 24)
- Changed: the Role row — no templates (only the system superadmin is seeded;
template_keyand therole_template*tables go with FS-6 spec Rev 5), User↔Role becomes N:1 (one role per user; the M:N join and the most-permissive union are dropped), and a role still held cannot be deleted. The Permission / grant row’s category list becomes read / edit / execute — AI is not a grantable level — and the Managing Director’s held key is described as a role, not a template. - Why: team decision (Tharinda, 2026-09-11; confirmed by Dinil) to remove complexity nobody asked for; recorded as AD #32 / PRD Rev 24.
- Impact: FS-6 spec Rev 5 + plan revision (2026-09-11) drop
role_template/role_template_grant, replaceuser_rolewithapp_user.role_id, remove the union and the ai-use category); no other edge or entity changes; the FS-3PermissionDecisionseam is unaffected.
Rev 7 — 2026-09-15 — the products catalog is removed; the Buying Standard is the sellable record (PRD Rev 27)
- Changed: cluster 3’s
Productrow is struck (retired — removal owed) and the cluster re-headed;deal_productbecomesdeal_line_itemand, withline_item, points at the buying standard; cluster 4’sBuying standard → Productedge is removed; cluster 6’s held-certs join becomes the standard’s; thePriceModelfallback note reads as history. - Why: PRD Rev 27 (2026-09-15): one list instead of two — every sellable item, bulk or private label, is a client’s standard, so the catalog and its
N:1 Productedges have nothing left to carry. Applied to the prototype in harness PR #88. - Impact:
forge-et-be/docs/data-model.md§2 (buying_standard.product_id,line_item.product_id,product,ListPriceModel) is revised by its owner — Tharinda (AD #31); the shippedcatalogpackage,V3__product.sqlandProductPermissionsare retired with it (with F26’s spec or earlier). Whether private-label standards carry weekly prices is OQ-61; OQ-9’s grain question now attaches to the standard. No other edge, cardinality or entity changed. - Approved by: Tharinda, 2026-09-16 — the FS-15 spec approval (harness PR #99) carries PRD Rev 27’s lead ack.
Rev 8 — 2026-09-16 — FS-15 ships the removal Rev 7 recorded as owed
- Changed: the struck
Productrow’s status goes from removal owed to removed (V11__drop_product.sql), and the line-item edge from retarget owed to verified —line_itemreferencesbuying_standardindata-model.md§2.4. The buying standard is the substrate’s reference entity, in the minimal shape FS-15’sV10ships; F26 extends it (the allocatorDEFAULT) and F1 addsaccount_id. - Why: FS-15 (PRD Rev 27) implemented what Rev 7 had only recorded. Nothing else moves: no new entity, no cardinality change, and the held-certifications join is still OQ-9’s.
- Impact:
forge-et-becarries noproducttable,catalogpackage orproduct.*permission key. - Approved by: (owed — the approval of the FS-15 harness PR carrying this revision)
Rev 9 — 2026-09-22 — the mailbox_connection row is confirmed by F20-a; email_message is re-attributed to F20-b
- Changed: §7’s
Mailbox connectionrow moves from designed (PRD/arch) to confirmed, realized by F20-a rather than the umbrellaF20, and records the two facts F20-a’s approved spec adds to the edge — the owner stays nullable from the start so F20-e needs no schema change (FR-4), and partial unique indexes enforce at most one non-DISCONNECTEDconnection per owner and perprovider_account_id. TheEmail messagerow’sRealized bymoves fromF20to F20-b, with F20-a’s decision D-4 named. - Why: the F20-a spec (approved 2026-09-21) cites this file in
## Input Sourcesand adopts themailbox_connectionrow unchanged, which is the adopt-or-revise obligation the Reference Map states for any spec touching a listed edge — but no work item owned the write-back, so the row still read as un-confirmed after the spec that confirmed it. D-4 is recorded on theemail_messagerow for the same reason: F20-a ships no table without a writer, and leaving the row attributed toF20invites the next reader to expect it in this feature. - Impact: none on code. No entity is added, no cardinality changes, and the held-certifications join
is still OQ-9’s. F20-b confirms the
email_messagerow when its spec is written. - Approved by: (owed — the approval of the F20-a harness PR carrying this revision)
Rev 10 — 2026-09-24 — access, ownership and sharing, and the audit trail (AD #34, AD #35, AD #36)
- Changed — cluster 1:
Userrecords the hierarchy and lifecycle policy that answers OQ-56 (recursiveteam; pre-created users, deactivated and reactivated, never deleted; deactivation refused while the user has direct reports or owns records; deactivated users stay in the tree).User identitynotes that an email change clears the link.Roledrops the superadmin andis_systemand records the Flyway-seeded, editable IT Admin role, the immutable role key, delete blocked while any user (active or deactivated) holds the role, and the last-admin invariant; realized-by adds F5-k.Permission / grantrecords AD #34’s minimal catalog (view / manage per entity,manageimpliesview, the special keys, oneaccess.manage), with no category axis and no*grant; the ~100–150-key, category-carrying catalog becomes a Was line.Audit eventmoves to AD #36’s design, realized by F39 (was “FS-6 / first consumer”).Sessionnotes that deactivation deletes the user’s sessions. The key-edges note gainscreated_at/modified_aton every table and leaves theSYSTEMactor’s representation to F39. The spine diagram’s “six roles” becomes “one role each”. - Changed — clusters 2, 7 and 10:
Accountrecordsowner_id NOT NULL;Leadrecords the owner nullable for import edge cases only, with unowned leads visible atallscope only.Mailbox connectionrecords that no unowned connection exists in V1 (F20-e left V1 on 2026-09-24;info@arrives by forwarding), leaving the shipped nullable owner unused.Record sharemoves from proposed to designed, with AD #35’s columns (shared_with_user_id,shared_by_user_id,shared_at;account_idandlead_idnow,deal_idandquote_idlater; exactly one set; unique per record and user) and F5-f as the confirming spec in place of F1/F2.Searchnames the one visibility rule. D-8’s confirming spec for the audit half becomes F39. - Why: the lead’s access-model decisions, signed off 2026-09-24 as AD #34 (access model), AD #35
(ownership and sharing) and AD #36 (audit trail). Full rationale:
.forge/engagement-log.md§ AD #34, AD #35, AD #36; the sharing design record isdesign/data/record-sharing-model.md(revised the same day). - Impact: F5-k removes
is_system, the superadmin row and the*grant and seeds IT Admin; F5-f buildsrecord_shareand the visibility rule; F39 buildsaudit_eventand adds missing timestamp columns.forge-et-be/docs/data-model.mdgains therecord_shareDDL with F5-f. No other entity or cardinality changes; the held-certifications join is still OQ-9’s. - Approved by: (owed — the approval of the harness PR carrying the AD #34–#36 changes)
Rev 11 — 2026-09-23 — F20-b confirms email_message, revising the row on both halves
Written 2026-09-23 on the F20-b wave-1 branch as “Rev 10”; renumbered Rev 11 when that branch merged after the AD #34–#36 Rev 10 reached main first (an unmerged claim yields — .claude/rules/tracker.md, L-057).
- Changed: §7’s
Email messagerow moves from designed (PRD) to confirmed against the F20-b spec (approved 2026-09-23), and is revised rather than adopted, on both halves. (1) The auto-link edges → account / contact / deal / lead / quote are re-attributed to F20-f: F20-b ships onlyN:1 mailbox_connection. (2) Tracking state is re-attributed to F20-g and is not a column of the table F20-b ships. The row also records what F20-b decided that the old row left open — the body is stored in Postgres (spec D-1),directionis derived at ingest,conversation_idis stored from the first migration — andRealized bynames all three owners. TheMailbox connectionrow notes F20-b’s four sync-state columns (spec FR-2b), with no edge change. - Why: spec D-4 — none of the five linked tables existed when F20-b was written (the schema then ran
V1…V13with no account/contact/deal/lead/quote table), so F20-b could not declare those foreign keys at all, and F20-f is the feature that populates them. Tracking is F20-g’s by the decomposition. Leaving the row as it was would have told the next reader to expect the links and the tracking columns in F20-b. The adopt-or-revise obligation the Reference Map states for a spec touching a listed edge is discharged here, in the WI that ships the table (F20-b-WI-1.1, AC-3). - Impact: F20-f owns an
ALTER TABLE email_messageadding the five nullable link columns; F20-g owns the tracking columns or table.forge-et-be/docs/data-model.md§9.1 carries the shippedCREATE TABLE. - Approved by: (owed — the approval of the F20-b wave-1 harness PR carrying this revision)
Rev 12 — 2026-09-25 — F1-a confirms the account and contact substrate: Account, Note, Timeline, Tag, D-7, D-8, D-10
- Changed — cluster 2: the
Accountrow moves to shipped — confirmed by F1-a, revised on two halves (spec FR-D3 (a), (b)): the home isaccountalone, with the tea attributes as flat columns rather than anaccount + account_profile1:1 split; and “LTV + dormancy are derived, never stored” becomes LTV is an entered estimate (estimated_ltv+ltv_currency) with dormancy still derived, from the latesttimeline_event.occurred_at. The spine diagram’saccount[t]loses its extension marker, and the entangled-entities list strikes Account. - Changed — cluster 6:
Account required certsmoves to shipped — confirmed by F1-a. - Changed — cluster 8:
Notemoves to shipped — confirmed by F1-a, with the namednote_has_targetCHECKthat F11-a and F2-a widen, and the reach rule the F1-a WI-3.1 plan decided (D16, Q-10): a note holds no key of its own and follows its parent; a note with both targets follows its account; a note on an account-less contact is reachable only atall.Timelineis revised from no table to the storedtimeline_event(spec FR-D3 ©), with its unique replay key, itssummary/versionwritable set, theNOT NULLsource pair and the caller’sversionbump. ATagrow is added (spec FR-D3 (d)) —tagperentity_typewith unique(entity_type, lower(name)); joinsaccount_tagandcontact_tagowned by F1-a,lead_taganddeal_tagowed by F11-a and F2-a — with D16’s vocabulary gate (names readable by any signed-in user, tagging under the tagged entity’smanage, rename and delete undersettings.manage). The spine diagram’s cross-cutting line no longer calls the timeline a read-time projection, and liststag. - Changed — decisions: D-7 is revised to the stored shape and keeps its original text as the
rationale that was revised; D-8 names
timeline_eventas its third log-class table and records the two rules every producer designs against; D-10 records the shipped account-side join. - Why: F1-a (spec approved at Rev 5) ships these tables, and the Reference Map’s adopt-or-revise obligation for a spec touching a listed edge is discharged in the work item that ships them (F1-a-WI-3.1, subtask 8, AC-26 (d)). The stored timeline was Q-1 of the spec, acknowledged by Tharinda on 2026-09-24.
- Impact: F1-e builds the notes and tags APIs to the D16 rules; F1-c renders
timeline_eventand owns the final dormancy rule; F3-a, F11-f and F20-f write timeline rows that always name a source and bumpversionwhen they refresh a summary; F11-a and F2-a addlead_tag/deal_tagand widennote_has_target.forge-et-be/docs/data-model.md§3 carries the shippedCREATEs. - Approved by: (owed — the approval of the F1-a WI-3.1 harness PR carrying this revision)
Rev 13 — 2026-09-25 — F39 confirms the audit trail and revises the Timeline row
- Changed — cluster 1: the
Audit eventrow moves from designed to shipped — confirmed by F39 (spec FR-1,V24__audit_event.sql). It now names the actor-kind column (USER,SYSTEM,ANONYMOUS) instead of a reserved user, the plainactor_user_idwith no foreign key (spec Rev 2), and the loose(entity_type, entity_id)pair, and records that the database refuses every update, delete and truncate. - Changed — cluster 8: the
Timelinerow records themodified_at/modified_bycolumns F39’s FR-10 migration adds and the widened trigger writable set (summary,version,modified_at,modified_by), replacing “nomodified_*columns” (spec FR-10, W-1). - Changed — decisions: D-8’s audit half is confirmed by F39:
entity_typeis the record’s table name, and the actor’s user id is a loose reference too. - Why: F39 (spec approved, Rev 1 to Rev 4) ships the table, and the Reference Map’s adopt-or-revise
obligation for a spec touching a listed edge is discharged in the work item that ships it
(F39-WI-1.1, subtask 8). AD #36’s every-table timestamp rule is what reaches
timeline_event. - Impact: every writing feature records its events through the audit service and adds any new
code to
forge-et-be/docs/audit/README.md; F5-c, F5-f and the first export feature write the codes reserved for them there.timeline_eventproducers (F1-e, F3-a, F11-f, F20-f) may stampmodified_at/modified_bywhen they refresh a summary. Biseni, the F1-a owner, was told of the Timeline revision in forge-et-be#68 and forge-et-harness#210.forge-et-be/docs/data-model.md§3 and §7.1 carry the shapes. - Approved by: Biseni Perera (approval of the F39 Wave 1 harness PR, forge-et-harness#210, 2026-09-25)
Rev 14 — 2026-09-25 — F26-a adopts cluster 4; the Price row’s “revision owed” is cleared
- Changed — cluster 4: the “Price → Buying standard × validity” row’s status moves from revision
owed to verified:
data-model.md§2.2/§2.5 now carry the Rev 23 (no market axis) and Rev 31 (four-figure build-up) revisions. The F26-a spec adopts the cluster-4 rows as written (F26-a spec Input Sources, FR-D2); the Price, Cost and “Price / Cost → Auction sale” rows move to shipped in the harness PR that accompanies F26-a’s backend PR. The “Line-item snapshot → Price / Cost” row, and cluster 3’s “Line item → bulk price snapshot” row, stay verified — Phase 2’s (F26-a spec Q-2). - Numbering: drafted as Rev 13 on the F26-a spec branch; renumbered Rev 14 when
mainmerged in, because F39 landed its Rev 13 first. - Approved by: (owed — the approval of the F26-a spec PR carrying this revision)
Rev 15 — 2026-09-26 — F11-a confirms the lead substrate: Lead, Lead source, the matched account and contact, Note, Tag, D-4, D-5
- Changed — cluster 2: the
Leadrow moves to shipped — confirmed by F11-a (spec FR-D2 (a), ©): the home isleadalone with every field as a flat column, notlead + lead_profile; the row lists the columns F11-a ships and names the columns later features add — F12’s extracted tea details (onleaditself), the AI outputs of F11-d, F11-g and F11-h, and F11-e’s conversion links — and the research-snapshot table and score storage as F11-c’s and F11-g’s. TheLead sourcerow moves to shipped aslead_source+lead_source_detail, with the reason it is notsource(FR-D2 (b)). A key-edges row is added, Lead → matched Account / Contact (N:1 each, nullable, F11-a), set by the intake,PATCH, F4-b and F11-d, not the conversion link, changing nobody’s reach (FR-D2 (e)). The spine diagram’slead[t]loses its extension marker, and the entangled-entities sentence records Lead as closed. - Changed — cluster 8: the
Noterow markslead_idshipped, withnote_lead_exclusive(a lead note’s only target is the lead); theTagrow markslead_tagshipped and names F11-f as the builder of the lead tags API (FR-D2 (d)). - Changed — cluster 10: the capture sentence reads
lead_source = 'Trade Fair'+lead_source_detail, withsource = 'capture'as the replay identity (FR-D2 (f)). - Changed — decisions: D-4 is closed against the
lead_profileextension table; D-5 gains a note that the match is a separate, earlier link and that F11-e decides whether a conversion starts from it (FR-D2 (e)). - Why: F11-a (spec approved 2026-09-25) ships
lead,lead_tagandnote.lead_id, and the Reference Map’s adopt-or-revise obligation for a spec touching a listed edge is discharged in the work item that ships them (F11-a-WI-1.1, subtask 5, AC-29 (d)). The flat-column decision and the move of the snapshot and score tables to F11-c and F11-g are the developer decisions of 2026-09-25 recorded in the spec’s## Context. - Impact: F12 adds its extracted fields to
leadby migration; F11-c, F11-d, F11-g and F11-h add their own columns or tables; F11-e reads the match when it designs conversion; F4-b writeslead_sourcefor the channel andsource = 'pipedrive'for the identity, and sets the match it knows; F36-a writessource = 'capture'; F2-a still widensnote_has_targetfordeal_id.forge-et-be/docs/data-model.md§3 carries the shippedCREATEs and thenoteALTERblock. - Numbering: drafted as Rev 14 on the F11-a wave branch; renumbered Rev 15 when
mainmerged in, because F26-a landed its Rev 14 first. - Approved by: (owed — the approval of the F11-a harness PR carrying this revision)
Rev 16 — 2026-09-27 — AD #37: a grant has no scope
- Changed: the
Permission / grantrow — grants are(role, permission_key)with no scope (F5-l dropsrole_grant.scope); each owned entity gains an<entity>.allkey; the scope-aware flag leaves the catalog metadata; F5-l joins the owner column. TheUserrow notes that the reporting hierarchy is what every key reaches down, so setting a manager is an access decision. TheRecord sharerow states the roll-up without scopes. - Why: AD #37 (lead decision, 2026-09-27; PRD Rev 43). No other entity, column or relationship changes:
app_user.manager_id, everyowner_idandrecord_sharestay as they are. - Approved by: Tharinda (lead), 2026-09-27 — the AD #37 sign-off.
Rev 17 — 2026-09-28 — F26-a ships the price, cost and auction-sale tables
- Changed — cluster 4: the rows “Price → Buying standard × validity”, “Cost → Buying standard ×
validity” and “Price / Cost → Auction sale (anchor)” move from verified to shipped — F26-a,
naming
V29__price_and_cost.sql. The “Line-item snapshot → Price / Cost” row, and cluster 3’s “Line item → bulk price snapshot” row, stay verified — Phase 2’s (F26-a spec Q-2). - Why: F26-a-WI-1.1 shipped
auction_sale,buying_standard_priceandbuying_standard_costwith their base columns, the value-immutability opt-ins, the FR-S8 triggers (a published price can be neither deleted nor returned to draft) and the FR-S9 indexes, as Rev 14 said the rows would move in the harness PR beside the backend PR (F26-a spec FR-D2, AC-22).forge-et-be/docs/data-model.md§2.5 carries the shippedCREATEs. - Impact: none on other edges. F26-b, F26-c, F26-f and F26-g add their write paths against the
shipped tables; Phase 2’s quote snapshot references
price_identityandcost_identityas §2.5 shows. - Numbering: drafted as Rev 16 on the F26-a-WI-1.1 harness branch; renumbered Rev 17 when
mainmerged in, because AD #37 landed its Rev 16 first. - Approved by: (owed — the approval of the F26-a Wave 1 harness PR carrying this revision)
Rev 18 — 2026-09-29 — F26-b takes the standard’s held certifications
- Changed — cluster 6: the row “Standard held certs” moves from open — OQ-9, owner F9, to
proposed — F26-b, owner F26-b, with the grain stated as per standard and the shape as a join
with a composite primary key, like
account_certification. - Why: OQ-9 was narrowed on 2026-09-17 to “held per standard”, with F26-b building the join and F9 comparing (F26-a spec Q-1). The F26-b spec (FR-S1, FR-D2) adopts the row. Only OQ-9’s client half stays open — where Empire’s existing data lives — and it is a migration source, not a shape.
- Impact: F9 reads the join and builds no table of its own. The row moves to shipped in the harness PR that accompanies F26-b’s backend PR.
- Approved by: Biseni Perera (2026-09-29, with the F26-b spec approval)
Rev 19 — 2026-10-02 — F5-f confirms record_share
- Changed: the
Record sharerow moves from designed to confirmed — F5-f spec (FR-S1), and states two rules: a share to a deactivated user is inert until they are reactivated, and a record with no owner cannot be shared. - Why: the F5-f spec (approved 2026-10-02) confirms the table’s shape and records both rules — the
first a lead decision at spec authoring (PRD Rev 49), the second following the PRD’s “unowned
records are visible only to
lead.allholders”. - Impact: none to the shape. The row moves to shipped in the harness PR that accompanies F5-f’s backend PR.
- Approved by: Tharinda Rajapaksha (2026-10-02, with the F5-f spec approval)
Rev 20 — 2026-10-02 — F26-b ships the standard’s held certifications
- Changed — cluster 6: the row “Standard held certs” moves from proposed — F26-b to
shipped — F26-b, naming
V32__buying_standard_certification.sql. - Why: F26-b-WI-1.1 shipped
buying_standard_certificationin the shape Rev 18 records — held per standard, a join with a composite primary key and both foreign keys, withaccount_certification’s post-F39 base columns — and the index oncertification_idfor F9’s reverse lookup (F26-b spec FR-S1, FR-S2, AC-34).forge-et-be/docs/data-model.md§2.5 carries the shippedCREATE; the join is written only throughBuyingStandard.heldCertifications. - Impact: none on other edges. F9 compares the join with
account_certificationin SQL and builds no table of its own; loading Empire’s standard-held certification data (OQ-9’s client half) stays a migration source for F4-d / F23-b. - Approved by: (owed — the approval of the F26-b Wave 1 harness PR carrying this revision)
- Renumbered from Rev 19 to Rev 20 when
mainwas merged into the wave branch: F5-f’s Rev 19 merged first.
Rev 21 — 2026-10-05 — F11-b takes the read-only tag list
- Changed — the Tag row: the tags API is split by who builds each part. The read-only list of a type’s
active tags,
GET /api/v1/tags?entityType=…, is F11-b’s (its lead filter needs the options); tag writes and assignment to accounts and contacts stay F1-e’s, which extends that route rather than building a second list; attaching, detaching and creating lead tags is F11-f’s. - Why: the F11-b spec (2026-10-05, developer decision 3) filters the lead list by tag, and no tag read
route exists on
forge-et-bemain. The F1-e stub had said “Lead tags — F11-b” and the F11-a spec (Q-1) had proposed F11-f; the F11-b spec settles it and corrects the F1-e and F11-f stubs. - Impact: no table, column or relationship changes; only the API owner column of the Tag row.
- Approved by: (owed — the approval of the F11-b spec PR carrying this revision)
Rev 22 — 2026-10-06 — F11-f adds the lead rule to the Timeline row and the nurture meaning to the Lead row
- Changed — cluster 8, the Timeline row: records the lead rule — one row per matched lead
(
source_type = 'lead',kind = 'lead', dated the lead’sreceived_at), its summary Inquiry received () — rewritten when the lead’s status orleadSourcechanges, the row moved or removed with the match, shown to every reader of the account, and counted as contact forlastContactAtand dormancy (F11-f spec FR-T1–T5, FR-T4a). - Changed — cluster 2, the Lead row: Archive / nurture is the
archivedstatus, with no separate nurture status, and restore returnsarchivedtonew(F11-f spec decisions 2 and 3). - Why: the F11-f spec (approved 2026-10-06) is the first writer of
timeline_eventand the first feature to change a lead’s status; its FR-H1 records both rules here. - Impact: no table, column or relationship changes. F1-c renders the lead rows and owns the final dormancy rule (F11-f Q-3). The Note and Tag rows mark F11-f’s lead routes as built when its backend PR merges.
- Approved by: (owed — the approval of the F11-f spec PR carrying this revision)
Rev 23 — 2026-10-06 — F11-f builds the lead notes and lead tag routes
- Changed — the Note row: the lead notes API is built by F11-f —
GETandPOST /api/v1/leads/{id}/notes, list and add (F11-f spec FR-N1, FR-N2), in theleadpackage over its ownLeadNoteRepository. Note editing and deletion, and the account and contact notes API, stay F1-e’s. - Changed — the Tag row: F11-f builds lead tag attach and detach (
PUT/DELETE /api/v1/leads/{id}/tags/{tagId}) and lead tag create (POST /api/v1/tagswithentityType: lead, a409naming the existing tag withexistingTagIdandexistingTagActive). The create learns its key fromtag.TagWriteAccess, a contribution point the owning feature fills (lead.managefromlead), sotagimports nothing fromlead(F11-f spec FR-G3, NFR-4); F1-e adds its own contributions for account and contact tags. - Why: F11-f spec FR-H1 — the Note and Tag rows mark F11-f’s lead routes as built with its backend PR (F11-f WI-1.2).
- Impact: no table, column or relationship changes; only the build status and owner columns of the two rows.
- Approved by: (owed — the approval of the F11-f WI-1.2 harness PR carrying this revision)
Rev 24 — 2026-10-10 — F1-e specifies the account and contact notes and tags
- Changed — the Note row: F1-e specifies the account and contact notes API (list, add, edit, delete),
the editing rule for every record type (any holder of the parent’s
managewho reaches it) and deletion by marking:notegainsdeleted_atanddeleted_by(F1-e spec FR-N1–N5; PRD Rev 59). - Changed — the Timeline row: the note rule — one row per account note, its summary the body cut to 200 characters, rewritten on edit and deleted with the note; contact notes write none (F1-e spec FR-N6, FR-N7).
- Changed — the Tag row: “renaming or deleting a name” becomes “renaming or retiring”: a tag is retired,
never deleted. F1-e specifies the account and contact tag routes, the two
TagWriteAccesscontributions,PATCH /api/v1/tags/{id}undersettings.manage,GET /api/v1/tags/{id}and the list’sactivefilter (F1-e spec FR-G1–G7). - Why: F1-e spec FR-H1, in the spec PR, so the map and the spec agree before the plan is written.
- Impact: one schema change, two nullable columns on
note(built by F1-e’s backend work item, an ASK FIRST item in its plan); the build status of the three rows moves when that work merges. - Approved by: Biseni Perera, at the F1-e spec approval (2026-10-11)
Rev 25 — 2026-10-11 — the Search row is withdrawn
- Changed — the Search row: the per-entity
tsvectordesign is withdrawn; nothing was built. - Why: the lead dropped global search (F31) on 2026-10-11 (PRD Rev 60), which closes AD-O-2.
- Impact: none on the schema; each list endpoint keeps its own
qfilter. - Approved by: Tharinda (lead, 2026-10-11)
Rev 26 — 2026-10-11 — capture-app storage, and pointers to dropped features
- Changed — the capture-app line: the PWA keeps on the phone any capture not yet sent (F36-a’s keep-and-retry), not an offline queue (F36-e dropped, PRD Rev 64).
- Changed — the Activity and Prompts rows: they no longer name F16 (dropped, PRD Rev 61) as a consumer.
- Impact: none on the schema.
- Approved by: Tharinda (lead, 2026-10-11)
Rev 27 — 2026-10-12 — the email_send row
- Added — §7
Email send(email_send), proposed by the F20-c spec (FR-R1): one row per email sent from the CRM, holding its idempotency key, outcome and origin record but none of its content; the Sent Items copy stays the oneemail_messagerow, matched to it by internet message id (F20-c FR-R2). - Impact: one new table, by F20-c’s migration;
email_messageis unchanged (F20-c copies the AI flags into its existing columns). - Approved by: with the F20-c spec’s approval (the row moves to confirmed then).
Rev 28 — 2026-10-13 — the files an email carries
- Added — §7
Email send file(email_send_file), by the F20-h spec (FR-R1): one row per new file a send carried, with its name, type and size and a link to the uploadedattachment; no file content. - Changed — §7
Email send: F20-h adds the send’s stored deadline and the count of forwarded originals removed (F20-h FR-R1, FR-S4). - Changed — §10
File: a new owner type,email_upload, owned by the uploading user, anddeleted_at; a deleted file keeps its row and its S3 version behind a delete marker (F20-h decision 3). - Impact: one new table and two
email_sendcolumns, and one nullableattachmentcolumn, by F20-h’s migration. - Approved by: Dinil Gamage, with the F20-h spec’s approval (2026-10-13).
Rev 29 — 2026-10-13 — F3-a confirms Activity and Activity type
Rev 28 is claimed by the open branch feature/F20-h-spec. If another branch merges a Rev 29 first, this entry takes the next free number (an unmerged claim yields).
- Changed — cluster 8, the Activity row: records the F3-a shape — exactly one linked record, an account or a lead, under the named
CHECKactivity_has_one_record(was “at least one” of account, deal or lead); no deal column until F3-b addsdeal_idand widens the check; the owner becomes an assignee, an active user who holds the record’smanagekey and reaches it and who gives no reach of its own; a Colombodue_datewith an optionaldue_time; onenotesfield;done_at/done_by; soft delete withdeleted_at/deleted_by; reach follows the linked record (F3-a spec FR-S2, FR-S3, FR-P1, decisions 3, 6 and 8). - Changed — cluster 8, the Activity type row: six seeded built-in rows, a
built_inflag, custom rows added by F3-b (F3-a spec FR-S1). - Changed — cluster 8, the Timeline row: the activity rule — one row per completed account follow-up, its summary
done: , removed on reopen or delete, rewritten on a type or title edit of a done follow-up; none for an open or a lead follow-up; it counts as contact (F3-a spec FR-T1–T4). - Changed — cluster 1, the key edges:
activityleaves theownsedge and gets its ownis assignededge, because a follow-up is not an owned record in the AD #35 sense (F3-a decision 8). - Why: F3-a spec FR-H1, in the spec PR, so the map and the spec agree before the plan is written.
- Impact: two new tables, by F3-a’s migration (an ASK FIRST item in its plan). The Record share row’s “exactly one … the same shape as
activity” now matches the Activity row. The PRD’s Activity and User rows still read “owner” until F3-a’s shipping harness PR records the PRD revision (F3-a FR-H1). - Approved by: Nanndish Satgunarajah, at the F3-a spec approval (2026-10-13)
Rev 30 — 2026-10-15 — F20-d confirms Email template
- Changed — §7, the Email template row: records the F20-d shape — shared or personal templates, a personal one owned by one user, an optional unique
purposefor the weekly-prices template, merge-field tokens in the subject and body, deletion by marking (F20-d spec FR-T1, decisions 2, 3 and 5). It moves from designed (PRD) to confirmed, and its owner from F20 to F20-d. - Why: F20-d spec FR-D1, in the spec PR, so the map and the spec agree before the plan is written.
- Impact: one new table and its seeded starter rows, by F20-d’s migrations. The sequence-step row’s
N:1 templatenow points at a row that is never removed, so a step keeps reading a template that was deleted after the step was written. - Approved by: Dinil Gamage, at the F20-d spec approval (2026-10-15)
Rev 31 — 2026-10-16 — F26-d proposes Price email and the price-email timeline rule
- Added — §7, the Price email row (
price_email,price_email_price): one insert-only record per weekly price email, tied one-to-one to the F20-c send that carried it, with the prices it listed (F26-d spec FR-R1, decision 5). - Changed — §7, the Timeline row: gains the price-email rule — one
emailrow per record, keyed on its F20-c send('email_send', <send id>, 'email')(F26-d spec FR-R3, decision 6). - Why: F26-d spec FR-D1, in the spec PR, so the map and the spec agree before the plan is written. F20-c keeps no subject or body on
email_send, so only a record of F26-d’s own says a send was a price email. Keying the row on the send lets F20-f find it throughtimelinewithout depending on F26-d’s package. - Impact: two new tables by one F26-d migration. F20-f, when specified, keys CRM-sent messages’ rows on the send and writes none where one exists (its stub carries the line).
- Approved by: Dinil Gamage, at the F26-d spec approval (2026-10-16)
Rev 32 — 2026-10-19 — F11-d keeps its AI outputs in lead_triage, not on lead
- Changed — cluster 2, the Lead row: F11-d’s summary, spam / duplicate tag and proposed customer match live in a table of their own,
lead_triage(one row per lead), not as columns onlead(F11-d spec FR-S1, decision 5). - Changed — the AI suggestion row: notes that F11-d, the first background AI feature, did not build
ai_suggestion(F11-d decision 11); the row stays proposed. - Why: a background job writing to
leadwould increment itsversionand turn a rep’s open edit into409 STALE_VERSION(F11-a FR-A3). A table of its own keeps the job’s writes off the lead. The tag and the proposed match each have a typed home and a typed action, so a generic queue adds nothing. - Impact: one new table by an F11-d migration;
forge-et-be/docs/data-model.md’s sentence that the F11 AI outputs areleadcolumns is revised in F11-d’s backend PR (FR-H1). F11-g, when specified, decides its own storage with the same reason in view. - Approved by: Dinil Gamage, at the F11-d spec approval (2026-10-19)
Rev 33 — 2026-10-21 — F11-c keeps its research snapshot in lead_research
- Changed — cluster 2, the Lead row: F11-c’s research snapshot lives in
lead_research(one row per lead: the state, the company and person findings as JSON, each finding with its source URL), withsearch_call(one row per search, never the query text) andsearch_provider_pause(one row) beside it (F11-c spec FR-S1, FR-L2, FR-L4, decision 11). - Why: the same reason as Rev 32 — a background job writing to
leadwould move itsversionand break a rep’s open edit; and the search record holds no personal data, because a query can carry a person’s name. - Impact: three new tables by an F11-c migration;
forge-et-be/docs/data-model.mdgains them in F11-c’s backend PR (FR-H1). - Approved by: Dinil Gamage, at the F11-c spec approval (2026-10-21)
Rev 34 — 2026-10-26 — a contact’s other email addresses; a lead’s email on conversion
- Changed — cluster 2, the Contact row: a contact can hold several email addresses —
contact.emailstays the main one, and the others go in a new child table, one row per address, whose shape F20-l’s spec settles; matching uses every address. - Changed — D-5: conversion also links the lead’s emails to the account, contact and deal it sets, and a converted lead stops being a target of automatic linking.
- Why: the lead’s decisions of 2026-10-26 (
.forge/design/architecture.md§ Mailbox integration, Rev 74): 246 people in the Pipedrive export have several work addresses; a converted lead’s history belongs on the records where the work continues. - Impact: one new table by an F20-l migration (F20-l was split from F20-f the same day,
project-prd-history.mdRev 77), and the migration mapping change (F4-a Rev 10, F4-d Rev 1, F1-a Rev 10); F11-e’s conversion writes the email links (Phase 2). - Approved by: Tharinda (lead), 2026-10-26 (in session)
Rev 35 — 2026-10-26 — status corrections for tables already on main
- Changed — the Status column of five rows: Session (
V7__spring_session.sql), Contact and Contact → Account (V19__contact.sql), Certification (V14__certification.sql) and Price email (V48__price_email.sql) readdesignedorproposed, but each table’s migration is onforge-et-bemain, which this map’s legend calls shipped. No edge, cardinality or note changes. - Not changed — the File row: the shipped table is
attachment(FS-11,V9__attachment.sql, one polymorphic owner and no link tables), not thefiletable with per-target link tables the row describes, so the row needs its design notes reconciled against the shipped table rather than a status change. - Why: the harness review of 2026-10-26 found the five rows behind
main. - Impact: none on any spec; the rows now match the migrations.
- Approved by: Tharinda (lead), 2026-10-26, harness review.
Rev 36 — 2026-10-26 — access.manage folded into settings.manage
- Changed — cluster 1, the User, Role and Permission / grant rows:
settings.manageis the one key for all configuration and all user, role and hierarchy administration, andaccess.manageleaves the catalog. The seeded IT Admin role holdssettings.manage, a user is pre-created by its holder, and the last-admin invariant holds on it. No table, edge or cardinality changes: the migrationforge-et-beV54rewritesrole_grantrows fromaccess.managetosettings.manage, each change audited. - Why: AD #34 refined by Nanndish on 2026-10-26 (
.claude/CLAUDE.md;engagement-log.md § Architecture Decisions — full rationale archive; PRD Rev 79; FS-6 spec Rev 19). - Impact: FS-6 Appendix A; the F5-c, F5-d, F5-e, F5-k specs’ revisions of the same date.
- Approved by: Nanndish, 2026-10-26; the AD #34 refinement signed off by Tharinda, 2026-10-27.
Rev 37 — 2026-10-28 — F20-l settles the contact’s other email addresses
- Changed — cluster 2, the Contact row: Rev 34’s proposed child table is confirmed as
contact_emailwith the shape F20-l FR-1 states, matched withcontact.emailon one address key (F20-l FR-8). - Why: F20-l spec approval, 2026-10-28.
- Impact: one Flyway migration in F20-l’s backend work.
- Approved by: Tharinda (lead), 2026-10-28, with the F20-l spec.
Rev 38 — 2026-11-05 — F11-g’s ranking table; hot / cold not stored
- Changed — cluster 9, the Lead row: the AI priority, confidence, reason and next action are in
lead_ranking, one row per lead, written only by F11-g’s background job, notleadcolumns; the hot / cold status is computed from the buyer’s last message when read and has no column. - Why: F11-g spec approval, 2026-11-05 (decisions 6 and 8): a background write to
leadwould move itsversionand refuse a rep’s open edit, as forlead_triage; hot / cold is a date check that a stored value would let go stale. - Impact: one Flyway migration in F11-g’s backend work.
- Approved by: Dinil Gamage, 2026-11-05, with the F11-g spec.
Rev 39 — 2026-11-06 — F33 split; leads take attachments
- Changed — the File row and D-6: the attaching features are F33-a (accounts, contacts, leads) and F33-b (deals, quotes, samples, product briefs); D-6’s target list gains leads, with one link table per target as before.
- Why: Gate 3 Run 4 (reslice, 2026-11-06, lead: Tharinda;
.forge/project-prd-history.mdRev 84) cut F33 by record type so the attachment store starts in Phase 1 on the records that exist; PRD §F33 now names leads. - Impact: F33-a builds the store, its first link tables and the panel; F33-b adds link tables. No change to the
filerow’s columns. - Approved by: Tharinda (lead), 2026-11-06.
Rev 40 — 2026-11-06 — F4-b adds source_owner_name to lead
- Changed — cluster 10, the External ref (migration identity) row, and cluster 2, the Lead row: the migration-identity row is settled on
leadby F4-b (spec FR-9), and its note saysleadcarriessource_owner_nameasaccountdoes, written on a lead’s create and update; the Lead row lists the column. - Why: F4-a FR-M5 — the feature that adds the column to a table revises this row in the same change; F4-b FR-22 names the row for the harness PR that ships the feature.
- Impact: one Flyway migration in F4-b’s backend work (F4-b-WI-1.1), with
forge-et-bedocs/data-model.mdchanged in the same wave. No edge or cardinality changes. - Approved by: Dinil Gamage, 2026-11-06, with the F4-b spec.
Rev 41 — 2026-11-06 — F33-a confirms the File row and revises D-6
- Changed — cluster 10, the File row: the table is
attachment, shipped by FS-11 (V9__attachment.sql), not afiletable; a file names its one record byowner_typeandowner_id, with no link tables; the owner types are listed,contactadded by F33-a. The status moves from designed to shipped, the reconciliation Rev 35 left open. - Changed — D-6: revised to the adopted shape, its original text kept as the rationale that was revised, as D-7 keeps its own. Rev 39’s “one link table per target” no longer holds.
- Changed — the spine:
file (D-6)readsattachment (D-6). - Changed — D-8:
attachmentis named as the one domain table that references its record loosely, with the reason (D-6, revised). - Why: F33-a spec decision 1 (Dinil, 2026-11-06): the owner columns, the visibility routing and two owner types were already on
main, so link tables would state each file’s owner twice. - Impact: no migration — F33-a adds no table or column (spec § Constraints and Dependencies). F33-b adds owner types and upload policies, not link tables; its stub is reworded in F33-a’s harness PR.
- Approved by: Dinil Gamage, 2026-11-06, with the F33-a spec.
Rev 42 — 2026-11-09 — F20-g adds email tracking and scheduled email
- Added — §7:
email_tracking,email_tracked_linkandscheduled_email, owner F20-g. - Changed — §7, the Email send row:
email_sendgainstracked. - Why: the F20-g spec (Dinil, 2026-11-09): tracking needs a token and counts per send and the original address of each rewritten link (FR-T2, FR-T3, FR-T5); a scheduled email must hold its content until it is sent, which
email_senddeliberately does not (F20-c decision 5; F20-g decision 5). - Impact: new tables and one column by Flyway migrations, named in the F20-g plan.
- Approved by: Dinil Gamage, 2026-11-09, with the F20-g spec.
Rev 44 — 2026-11-12 — F36-a adds the captured lead, the capture campaign and the exhibitor
- Changed — cluster 10, the capture-app paragraph: a capture is no longer a lead when it arrives. The paragraph names three entities of F36-a’s, with a row each — Captured lead (
captured_lead), Capture campaign (capture_campaign) and Exhibitor (exhibitor) — and their edges: captured lead → lead on conversion, campaign → captured leads, campaign → exhibitors, and the users who capture and decide. It replaces the paragraph that made a capture submission a lead with the capture app’s submission id as its replay identity and added no entity: a converted capture’s replay identity is now the captured lead’s id. - Why: PRD Revs 95, 96 and 97 (2026-11-12): captured leads wait in the capture app until a person converts or rejects them, and the reps plan each show in the app; the F36-a spec FR-L1, FR-P1, FR-P3 and FR-D1.
- Impact: three tables by Flyway migrations in F36-a’s waves 2 and 3, named in their plans; their rows stay
designeduntil those migrations are onmain. F11-a’s spec records the change to a converted capture’s owner andsourceRef(its Rev 6). - Approved by: Nanndish, 2026-11-12, with the F36-a spec (approved by Claude under his overnight delegation, for his review).
Rev 43 — 2026-11-11 — lead notes adopt F1-e’s editing and deletion rule
- Changed — cluster 8, the Note row: the lead notes API gains
PATCHandDELETE /api/v1/leads/{id}/notes/{noteId}(F11-f spec FR-N4, Rev 13): any holder oflead.managewho reaches the lead may edit or delete any note on it, with optimistic locking onversion; a deleted note is marked withdeleted_atanddeleted_by, never removed, and left out of the list; each note carrieseditedAt,editedByandversion. The row no longer says lead notes are list and add only, and its pointer to the follow-up in.forge/owed.mdis replaced by F11-f Rev 13. - Why: the CRM UI polish tracker, item i13 (Nanndish Satgunarajah, 2026-11-11): lead notes were the only notes in the CRM that could not be corrected or removed, and F1-e had settled the rule for every record type.
- Impact: no migration —
notealready hasversion,deleted_atanddeleted_by(F1-e). One new audit code,lead.note_edited, inforge-et-bedocs/audit/README.md. No edge or cardinality changes. - Approved by: Nanndish Satgunarajah, 2026-11-11, with F11-f Rev 13.
Rev 45 — 2026-11-15 — F37-a adds the price broadcast
- Added — §7: the Price broadcast row (
price_broadcast,price_broadcast_item,price_broadcast_item_price), owner F37-a. - Why: the F37-a spec (Tharinda, 2026-11-15, FR-B1, FR-S2, FR-S3): a batch of weekly price emails is confirmed once and sent by a sweep over the following minutes, so it needs a row per customer to claim, settle and report; and the addressee and listed prices are stored before each send so the price email can be recorded after it, without storing the email’s content.
- Impact: three tables by Flyway migrations, named in the F37-a plan; the row stays
confirmeduntil they are onmain. Each item sent writes F26-d’sprice_emailrows; the Price email row does not change. - Approved by: Tharinda Rajapaksha, 2026-11-15, with the F37-a spec.
Rev 46 — 2026-11-13 — F36-c adds the capture research table and the two notification tables
- Changed — cluster 10, the capture-app table: three rows — Capture research (
capture_research), Capture notification (capture_notification) and Capture notification recipient (capture_notification_recipient) — and a key-edges paragraph for them. The Captured lead and Exhibitor rows no longer say F36-c adds research and score columns to them: F36-c adds nothing tocaptured_leadorexhibitor, and keeps each one’s research and ranking in itscapture_researchrow. - Why: the F36-c spec FR-ST1, FR-ST2 and decisions D-2, D-10, D-11 and D-19 (approved 2026-11-13): a background write to the captured lead or the exhibitor would move its
versionand refuse a rep’s open edit, as forlead_research(Rev 33); notifications are read per user. - Impact: Flyway migrations in F36-c’s backend work —
capture_researchin its wave 1 (F36-c-WI-1.1), the two notification tables in its wave 2 (F36-c-WI-2.1); the rows staydesigneduntil those migrations are onmain(.forge/owed.md). The copy of a captured lead’s research into the new lead’slead_researchrow at conversion (FR-CV1) adds no edge; F11-c’s spec records it (its Rev 8). - Approved by: Nanndish, 2026-11-13, with the F36-c spec (approved by Claude under his overnight delegation of 2026-11-12, and confirmed by him on 2026-11-13, F36-c Rev 1).
Rev 47 — 2026-11-18 — four capture-app tables marked shipped
- Changed — cluster 10: Captured lead, Capture campaign, Exhibitor and Capture research move from
designedtoshipped, each citing its migration onforge-et-bemain:V71__captured_lead.sql,V73__capture_plan.sql(the campaign, the exhibitor andcaptured_lead.campaign_id) andV76__capture_research.sql. Capture notification and Capture notification recipient staydesigneduntil F36-c wave 2’s migration is onmain. - Why: the
owed.mdrows “Move F36-a’s entity-map rows to shipped” and “Close F36-c’s wave-2 marks…”, which ask for each row to move once its migration is onmain(Rev 44, Rev 46). - Impact: none; a record of what is built.
- Approved by: Tharinda (lead), 2026-11-18.
Rev 48 — 2026-11-19 — F37-b adds the bulk email and the suppression list
- Added — §7: the Bulk email row (
bulk_email,bulk_email_item) and the Bulk email suppression row (bulk_email_suppression), owner F37-b. - Why: the F37-b spec (Tharinda, 2026-11-19, FR-B1, FR-U1): a bulk send is confirmed once and sent by a sweep over the following minutes, as F37-a’s batch is, so it needs a row per contact to claim, record and summarise; OQ-62’s decision (PRD Rev 103) keeps unsubscribed and bounced addresses out of later sends, which needs a list keyed by address.
- Impact: three tables by a Flyway migration named in the F37-b plan; both rows are
confirmedwith the spec’s approval (2026-11-19) and move toshippedonce the migration is onmain. Each item sent writes one F20-cemail_send, whose row does not change. - Approved by: Tharinda Rajapaksha, 2026-11-19, with the F37-b spec.
Rev 49 — 2026-11-18 — F36-c keeps the campaign focus and carries the capture’s ranking into the CRM
- Changed — cluster 10: the Capture campaign row gains the campaign’s optional focus (
capture_campaign.focus, F36-c FR-FC1) and names F36-c beside F36-a; the Capture research row gainsranking_focus, the focus each ranking read (FR-ST1, FR-FC4); the F36-c key-edges paragraph’s conversion sentence, which said the capture’s tier, score and next action are not carried into the CRM, now says the ranking is copied into the new lead’slead_rankingrow withorigin = 'capture', the focus and the campaign’s name, and only the fit score stays on the captured lead (FR-CV1, FR-DC2). - Changed — cluster 9, the Lead row:
lead_rankingis written by F11-g’s job and by the conversion’s copy, and keeps the focus a ranking read infocusandfocus_campaign_name(F11-g FR-S1, FR-S4, FR-A9). - Why: the F36-c spec’s Rev 6 and the F11-g spec’s Rev 7 (approved 2026-11-18; PRD Rev 101, the lead’s decision of 2026-11-18): a converted captured lead arrives in the CRM with its ranking as its priority instead of being ranked a second time, and a campaign’s focus is kept with every ranking made for it (F36-c FR-DC2, F11-g FR-H4).
- Impact: one Flyway migration in F36-c’s wave 3 (F36-c-WI-3.1):
capture_campaign.focus,capture_research.ranking_focus, andlead_ranking’sorigin,focusandfocus_campaign_name, each with its namedCHECK. No new table and no new edge. The rows keep their status; the new columns are onmainwhen wave 3’s migration is. - Approved by: Dinil Gamage, 2026-11-18, with the F36-c spec’s Rev 6 and the F11-g spec’s Rev 7 (the lead’s decision of 2026-11-18, PRD Rev 101). Numbered Rev 49 at the merge of 2026-11-19: F37-b’s Rev 48 merged to
mainfirst (id-numbering rule 5).
Rev 50 — 2026-11-19 — F20-f confirms the email links, refusals and received files
- Changed — §7, the Email message row: the auto-link edges are no longer nullable columns on
email_message(Rev 11’s plannedALTER TABLE, struck in the row); each link is a row of a newemail_linktable, so one message can be linked to several records.email_messageis unchanged, and a message is stored only while it has a link. - Added — §7: the Email link (
email_link), Email link refusal (email_link_refusal) and Email attachment (email_attachment) rows, owner F20-f. A received file is anemail_attachmentrow under theemail-attachments/S3 prefix, not an FS-11attachmentrow, so the File row is unchanged. - Why: the F20-f spec (approved 2026-11-19): D-1 (one email often concerns several records — a buyer and a colleague on CC, a lead on the same thread), D-7 (an unlinked message is not linked back to the same record, and unlinking the last link deletes the copy without trace) and D-6 (a received file must be deletable without trace and needs none of FS-11’s upload rules).
- Impact: three new tables by an F20-f Flyway migration, named in the F20-f plan; the rows stay
confirmeduntil they are onmain. F20-f’s spec lists the new columns onlead,mailbox_connectionandemail_send(its § Constraints and Dependencies); they add no edge. - Approved by: Tharinda (lead), with the F20-f spec, 2026-11-19.
Rev 51 — 2026-11-19 — F44 adds the email follow-up
- Added — §7: the Email follow-up row (
email_follow_up,email_follow_up_email,email_follow_up_setting), owner F44. - Changed — the Timeline row’s activity rule: a follow-up F44 raised writes no timeline row.
- Why: the F44 spec (approved 2026-11-19): D-1 (the follow-up is an ordinary F3-a activity with a side row of its own), D-3 and D-5 (one follow-up per email, one open per conversation and mailbox), D-10 (closing one is not contact with the buyer).
- Impact: three new tables by an F44 Flyway migration, named in the F44 plan; the row stays
confirmeduntil it is onmain. - Approved by: Tharinda (lead), with the F44 spec, 2026-11-19.
Rev 52 — 2026-11-20 — the two capture notification tables marked shipped
- Changed — cluster 10: Capture notification and Capture notification recipient move from
designedtoshipped, citingV79__capture_notification.sqlonforge-et-bemain(F36-c wave 2, forge-et-be#475). No edge, cardinality or note changes. - Why: Rev 47 left the two rows
designeduntil F36-c wave 2’s migration was onmain; theowed.mdrow “Close F36-c’s wave-2 marks…” asked for the move, due at F36-c’s Reflect. - Impact: none; a record of what is built.
- Approved by: Dinil Gamage, 2026-11-20 — made by Claude at F36-c’s Reflect under his overnight delegation of 2026-11-19, then reviewed and approved by him; it carries out the condition Rev 47 (approved by Tharinda, the lead) set. Rev 50 and Rev 51 (F20-f, F44) were on
mainfirst; Rev 52 is the next free number (id-numbering rule 5).
Rev 53 — 2026-11-20 — F11-j adds the migrated lead’s email summary
- Changed — cluster 2, the Lead row: a migrated Pipedrive lead’s summary written from its linked received email lives in a table of its own,
lead_mail_summary(one row per lead,designed), not onleadand not inlead_triage. No edge changes: it referencesleadandai_invocation, and nothing references it. - Why: F11-j spec D-4 — a background write to
leadwould move itsversion, andlead_triagestays written only by F11-d (F11-d FR-S3). - Impact: F11-j’s backend migration creates it; the row moves to
shippedwhen that migration is onmain. - Approved by: Tharinda (lead), 2026-11-20, with the F11-j spec.
Rev 54 — 2026-11-20 — F22 confirms the contact sync link as a per-user suggestion table
- Changed — cluster 2: the Contact sync link row moves from
designed (PRD caveat)toconfirmed, as the F22 spec’s suggestion table (FR-5): one row per (user, Graph contact id), with the copied fields, a suggested account, a state and, when added, the contact. The Contact row’s F22 note no longer says a synced contact may precede its account: a contact added from Outlook gets an account unless the user holdsaccount.all. - Why: the lead’s decisions of 2026-11-20 in the F22 spec: contacts are read one way into a per-user review list, and nothing is created without the user’s action (PRD Rev 107).
- Impact: F22’s plan names the table and writes its migration; no shipped table changes.
- Approved by: Tharinda Rajapaksha (the lead), 2026-11-20, with the F22 spec. Rev 53 was taken by F11-j (forge-et-harness#1611); Rev 54 is the next free number (id-numbering rule 5).
Rev 55 — 2026-11-20 — F20-k adds the email block list
- Changed — the email rows: a new row,
email_block(confirmed): one row per blocked address or domain, on a user’s personal list or the organisation list (owner_user_idNULL). It referencesapp_user, and nothing references it. - Why: F20-k spec D-2 — both lists in one table, read together by F20-f’s automatic linking and F20-j’s scans.
- Impact: F20-k’s backend migration creates it; the row moves to
shippedwhen that migration is onmain. - Approved by: Tharinda (lead), 2026-11-20, with the F20-k spec. Rev 54 was taken by F22 (merged first); Rev 55 is the next free number (id-numbering rule 5).