Data model: Tenant, Principal, Email triage

Status: Draft · supports ADR-0017 (employees as principals) · Date: 2026-07-31 Diagram: docs/architecture/diagrams/abos-er.svg (source: abos-er.dot)

This document predates ADR-0032 and needs reconciling

It was written before the work item existed. Its Message entity does the job now split between work_item (one conversation, our unit of work) and message (one email). Classification and Draft hang off Message here, where they should hang off work_item.

The Identifiers and threading section below is current. The entity list and the ER diagram are not. Tracked as OI-071.

This document is the narrative companion to the ER diagram. It describes how an employee (Principal) and a triaged email are represented across the two planes, and, critically, which relationships are enforced foreign keys and which are logical references across a database boundary.

Identifiers and threading

Three identifiers, and confusing them is the usual mistake.

ID	Identifies	Created by
work_item.id	One conversation, our unit of work	Us
message.message_id	One email	The sending mail server, as an RFC 5322 header
message.references[]	The chain of earlier Message-IDs	The sending mail client

The first email in a conversation creates a work item. A reply joins that same work item rather than creating another. So one work item holds several messages.

flowchart LR
    WI[("work_item")] --> MSG[("message<br/>one row per email")]
    MSG --> LAND[("landing<br/>raw, untouched")]
    MSG -.->|"indexed"| IDX[("message_id lookup")]

Three layers, three jobs. Landing holds the raw message untouched, including every header. message holds what you query on. The index makes arrival-time matching fast.

What to extract per message

Field	Why
message_id	The matching key. Indexed, unique per tenant
in_reply_to	Direct parent
references[]	The full ancestor chain, and what arrival matches against
direction	inbound or outbound
sent_at	From the Date header. Relative dates resolve against this, never against processing time
from, to, cc	Sender resolution, and who else is on the conversation
subject	Not used for threading. Useful for display, and as a fact
provider_thread_id	The provider's own grouping ID: conversationId on Microsoft Graph, threadId on Gmail. Secondary signal only (ADR-0032, OI-070)

Matching on arrival: take the incoming references[] plus in_reply_to, look each up in the index, and the first hit gives the work item.

Store the Message-ID of what we send

If a reply is sent without recording its Message-ID, the customer's reply to our reply references an ID we have never seen, and it starts a new work item.

Half of all conversations would break, and only on the second exchange, which is a miserable thing to debug. The mail connector's send_draft returns it for exactly this reason.

Providers name it differently, and it is the same RFC 5322 field: Microsoft Graph calls it internetMessageId, Gmail returns it in the message headers. We store message_id, which is the standard name.

Scope the index per tenant

Message-IDs are globally unique in theory and not in practice. A collision across tenants would be a cross-tenant leak, so the uniqueness constraint and every lookup are scoped to the tenant.

Do not copy the whole header block into the modelled layer. Landing already holds it, and duplicating means two places to keep in step.

Proposed schema

Proposed, not built

This is the shape implied by ADR-0032 and the sections above. It supersedes the entity list further down, which predates the work item (OI-071). Column types are indicative.

erDiagram
    work_item ||--o{ message : "holds"
    work_item ||--o{ decision : "one per turn"
    work_item ||--o{ approval : "one per gated action"
    work_item }o--o| job : "may reference"
    job }o--|| customer : "belongs to"
    message ||--|| landing_email : "raw"

work_item

One conversation. The unit the Progress engine moves through states.

Column	Notes
id	Ours
principal_id	RLS boundary (ADR-0017)
source	email, later form, webhook
status	new → assessing → awaiting_approval → sent_by_us | closed_by_human | closed_manual | closed_no_action
attention_reason	Nullable. Null means routine. Why this item needs care, kept separate from status because a flagged item can still be sent by us
attention_detail	Free text for the human
brain_calls	Times the brain was invoked. A ceiling halts a looping item (ADR-0032 §3)
model_calls	Actual model invocations. The cost figure, since a brain call may make zero, one or two
tokens_in, tokens_out	Cumulative, so cost per item is a fact rather than an estimate
config_version	Pinned at first brain call. Null means not ready, and nothing may act on it
catalogue_version	Pinned likewise
category	Latest assessment. A label, and it decides nothing on its own
current_stage	Denormalised from the latest stage_trace row. Exists so the live funnel view is a simple group-by rather than a per-item max over the whole trace table
priority	A property of the item, never an action parameter
job_id	Nullable. Many items have no job
job_link_basis	How the link was derived: quoted_reference, sole_open_job, human_confirmed
job_link_certain	false means the draft hedges, and the approver sees it
created_at, closed_at	closed_at is cleared on reopen
first_closed_at	Set once, never cleared. Without it, a reopened item cannot be distinguished from one that never closed, and reporting double-counts
reopen_count	How many replies arrived after a close

status distinguishes closed_manual (the human replied first) from closed_by_human (the human handled it instead of using our draft). Different outcomes with different meanings in reporting, and collapsing them hides a real signal.

message

One email. Several per work item.

Column	Notes
id	Ours
work_item_id
message_id	RFC 5322. Unique per tenant, indexed
in_reply_to
references	Array. Matched against on arrival
direction	inbound | outbound. Outbound rows are what stop the second exchange breaking
sent_at	From the Date header. Relative dates resolve against this
from_address, to_addresses, cc_addresses
subject	Display and facts only, never threading
conversation_id	Provider ID. Secondary signal
landing_ref	The raw message

decision

One per brain call. Append-only.

Column	Notes
id, work_item_id, brain_call
idempotency_key	work_item_id + brain_call. Unique. A retry returns the stored row rather than deciding again (ADR-0032 §6c)
facts	Deterministic, from code
observations	From the model
prompt_user_block	The per-item half of the prompt: facts, state, permitted actions, the masked message. Stored deliberately, so a decision can be explained and the calibration loop has something to work from
model_response_raw	What came back, before parsing. Enough to see why it decided as it did
rules_fired	Which rules matched, by ID and tier. Required, or rule health cannot be measured and a bad outcome cannot be traced back to the rule that caused it
assessment	Category, priority, confidence, summary
action_name, action_params	Nullable, since not every brain call produces an action
model, prompt_version, tokens_in, tokens_out, model_calls	Cost and eval. model_calls on the decision is what tells you whether a crashed call had already paid
created_at

Splitting facts from observations matters for eval: facts are objectively right or wrong and can be scored without a human, while observations need judgement.

The system block is not stored, and does not need to be

Only the user block is kept per decision. The system block is reconstructible from prompt_version, and storing it per row would duplicate the same six sections thousands of times (prompt anatomy).

That is the cache boundary earning its keep twice: once on cost, once on storage.

Where prompt content may and may not go

A rule that needs sharpening rather than reversing.

Destination	Prompt content?
Sentry, application logs, the Console	Never
Tenant Postgres decision	Yes, deliberately. Same residency, backup, access and exit rules as everything else in that database
Langfuse	Short retention, debugging only. Never the durable record

"Never log prompt content" was always about incidental surfaces, where data ends up somewhere with different rules by accident. Deliberate storage in the tenant's own database, under ADR-0008, ADR-0012 and ADR-0026, is a different thing.

This is what makes calibration possible. Without the input and the raw response, a wrong classification cannot be diagnosed, and there is nothing to improve a prompt against.

stage_trace

One row per stage, written as it is reached. Around ten per exchange.

Column	Notes
id, work_item_id
brain_call	Null for stages n8n records
stage	captured, classified, resolved, decided, executed, awaiting_human, reconciled, completed
succeeded
detail	Optional. Counts, error class. Never content
at	Timestamp

Written as each stage is reached, never batched at the end

A trace that only lands on success tells you nothing about failures, which is the one case it exists for.

Paired with work_item.current_stage, which is the same value denormalised. The trace answers historical questions, and the denormalised column answers the live one.

Partition by month. Old partitions detach cleanly to Parquet in AU object storage, and stay queryable with DuckDB on the rare occasions anyone asks (OI-033 is the same pattern, already noted for dashboards). If nobody ever queries an archive, drop the partitions instead and save the effort.

Query	From
How many items are at each stage right now	work_item.current_stage, grouped
How many passed through classified in the last hour	stage_trace, counted
Where did this item fail	stage_trace, last row
Which stage is slow	stage_trace, gaps between rows

Without the denormalised column, the live funnel needs a per-item max across every row ever written, which degrades as the table grows. Index on (work_item_id, at) for the historical queries.

Stage-level, not step-level. Sub-stage detail (masking, the model call, parsing) belongs in the tracing tool rather than Postgres. The one question that needed step granularity, "did we pay before it crashed", is answered by model_calls instead.

	This table	LangSmith or Langfuse
Granularity	Stage	Every step, with payloads
Retention	Long	Short
Queryable alongside work items	Yes	No
Answers	What happened to this item	Why was this call slow

At 40 emails a day this is a few hundred rows daily, which is nothing for Postgres. Worth a retention policy eventually, since stage traces age out long before approval records do.

approval

One per gated action. Append-only, never updated in place (OI-018).

Column	Notes
id, work_item_id, decision_id
action_name, action_params	Exactly what is being approved
payload_hash	Of the exact content. If the payload changes, the approval is void
status	pending → approved | regenerated | handled_by_human | superseded
human_response_reason	Mandatory on regenerated, since it goes into the Draft prompt and is what makes the second attempt different. Free text on the others
corrected_category	Orthogonal to status. Set when a human said the classification was wrong and supplied the right one, whatever outcome they chose. Optional, and a labelled example for the eval set when present
decided_by, decided_at
final_payload_hash	In review_app mode, the human's edited text re-hashed. This is what was sent

This table is the evidence behind "no review, no go", and it carries a retention floor no client can request below (ADR-0026 §6).

There is no expires_at. Approvals do not expire: ageing handles "nobody has looked at it" and draft_staleness handles "the draft no longer fits". Nothing auto-approves.

Superseded records are kept, never deleted. When a draft is regenerated, whether by a human asking or by staleness, the old record moves to superseded and a new pending one is written. Only one is ever pending, so "which is live" is unambiguous.

Keeping the old one matters because the sequence is the evidence: what we proposed, why it was rejected, and what we proposed instead. Deleting it would leave a chain showing only the version that happened to be accepted.

mask_map

One row per masked value per work item. Shared by every model call on that item.

Column	Notes
id, work_item_id
token	[PERSON_1], [ORG_1], and so on. Unique within the work item
value	The real string
kind	person, org, email, phone, address, account
first_seen_turn	Which turn introduced it

Numbering is stable across the whole work item, not per call

Classify and Draft are separate model calls, and they share one mapping.

If Sarah is [PERSON_1] at turn one and [PERSON_2] at turn three, the model sees two different people in what it believes is one conversation, and the draft addresses the wrong person or hedges oddly.

A token is allocated once, on first sight, and reused for the life of the item.

Tokens, never plausible pseudonyms. If replacement fails on the way out, Dear [PERSON_1] is obviously broken and the approver catches it, whereas Dear Robert reads as a normal email and gets sent to Sarah. Tokens fail loudly, pseudonyms fail silently, and with an approval gate in front, loud failure is what you want.

The map lives in the tenant's database, never in the brain, because the brain is stateless (ADR-0030). It is client data, so it falls under the same residency and backup rules as everything else in that database.

customer and job

Canonical, and populated by backfill plus ongoing sync (OI-068).

No source fields. job maps to a portal project and to an AroFlo work order without either vocabulary leaking in, for the same reason the canonical model exists at all.

job	Notes
id	Ours
source_system, source_id	Provenance, and the sync key
reference	What the customer would quote, such as J-4471
customer_id, status, site, opened_at, closed_at

source_id is where the export-versus-API question bites: if a file export uses different keys from the API, backfilled rows cannot be matched to later updates.

The two planes

Control plane, the shared registry. Two writers only, being Ansible at bootstrap and BOS Console edits, with the brain reading it read-only (ADR-0006). Holds the map, not business content: Tenant, Role, Principal, MailboxBinding, Rule.

Tenant data plane, per-tenant Postgres inside the tenant's own container (ADR-0023). Holds business content, every row stamped principal_id, with RLS enforcing cross-employee isolation (ADR-0017). This plane goes with the client on exit (ADR-0012). Holds: Message, Classification, Draft, TriageFeedback, GenerationFeedback, VoiceExample, LearnedRuleCandidate.

Enforced FKs vs logical cross-plane references

The distinction the diagram encodes with solid vs dashed edges is load-bearing:

• Solid edges = real foreign keys within one database.
• Dashed edges = logical references that cross the plane boundary. Message.principal_id and Draft.principal_id → Principal, and Classification.matched_rule_id → Rule, are not enforced DB foreign keys, because the registry and the tenant Postgres are separate databases in separate containers. The brain resolves them at runtime. Modelling them as enforced FKs would misrepresent the isolation architecture.

Consequently the registry's tenant → connection-string resolution is security-critical: a wrong mapping is a cross-tenant leak.

Entities of note

• Principal — an employee, or a shared mailbox (kind = person | shared). Carries role_id, autonomy_level (L1 default), and its mailbox binding. Onboarding = create + assign role (inherits ruleset) + delegated OAuth consent.
• Rule — polymorphic scope_type (system | vertical | tenant | role | personal | learned) with nullable scope_ref. Lives in the control plane because rules are config. Precedence resolves in that order.
• Draft — the approval queue is the set of status = pending rows; there is no separate queue table (DB-first, clean workflow exit). approver_principal_id is a pointer: self-review by default, re-routable to a manager/rota without a schema change.
• Classification → TriageFeedback trains the shared role layer. Draft → GenerationFeedback trains only that principal's voice. The two channels are never conflated.
• VoiceExample — pgvector, per-principal, never pooled across employees.
• LearnedRuleCandidate — evidence aggregated in the tenant plane; promoted to an active Rule in the registry only on human approval (the "No review, no go" gate; also the insertion point for the curator / SkillOpt per ADR-0019).

Runtime path for one email

1. A Graph change-notification (one subscription per employee mailbox) fires; the subscription id resolves to (tenant, principal) via the registry and dispatches into that tenant's pipeline.
2. Capture pulls the message with the principal's token, composes the effective ruleset (system → vertical → tenant → role → personal → learned), classifies, writes a principal-scoped row, decides promote / notify / ignore.
3. On promote, Generate retrieves only that principal's VoiceExamples, drafts via llm-client, writes the Draft immediately (DB-first), status pending.
4. Gate: the principal's autonomy_level decides — L1 → queue; higher → auto-send only if the client lifted that gate, with the authorisation recorded. Workflow terminates cleanly; the pending row is the queue.
5. Feedback on approve/edit splits: triage signal → role layer; edit-diff → that principal's voice corpus. Retrieval, not fine-tuning.

Open items

Tracked in the register, not here:

• OI-071: this entity model predates the work item and needs reconciling with ADR-0032.
• OI-070: threading must use RFC headers, not conversationId.
• RLS session-principal mechanics: how the brain sets the Postgres session context per invocation so RLS binds. Now interacts with the per-tenant database role in ADR-0032 §5.
• Graph subscription lifecycle: renewal, and revocation on offboarding.
• ~~Confirm tenant compose layout~~ Resolved by ADR-0023: the tenant's n8n runs in the same Compose stack on the tenant's private network, with its own Postgres separate from the business Postgres.