inboxos-v1/docs/schemas.md

InboxOS — NocoDB Schema Design

Status: Phase 2 deliverable for INB-4 Applies to: NocoDB project at https://noco.cloudinit.dev Pipeline contract: pipelines/common.py::nocodb_upsert, pipelines/historical_pipeline.py (canonical), and the OpenCode prompts under .opencode/commands/inboxos:*.md

This document is the source of truth for the four module tables in NocoDB. The schemas are intentionally narrow: every column is either written by one of the pipelines (after extraction) or computed from the extraction JSON. No column is allowed to be filled by hand in NocoDB's spreadsheet UI in production — the pipelines are the only writer.

The live bootstrap script scripts/apply_schemas.py is idempotent and creates these tables + columns + unique constraint on Message_ID exactly as specified here. Re-running it on an already-bootstrapped project is a no-op.

Cross-cutting invariants

Invariant	Why	How it is enforced
Message_ID is the natural primary key for every module table.	The pipeline is retried; the same email may arrive multiple times. A stable natural key is the only way to make upserts idempotent.	UNIQUE constraint on Message_ID in every module table. nocodb_upsert in pipelines/common.py does a GET-then-PATCH/POST keyed on Message_ID.
Both pipelines write to the same tables.	The historical pipeline backfills the same data the live pipeline ingests, into the same tables. Joins across modules are performed at query time by Message_ID.	The schema includes the union of columns written by either pipeline; all optional.
The LLM extraction is strict JSON, gated by inboxos:classify.	Routing first lets us skip irrelevant modules (a receipt email doesn't waste inference on CRM extraction) and isolates per-module failures.	.opencode/commands/inboxos:classify.md returns {"modules": [...]}; .opencode/commands/inboxos:extract-<module>.md returns a flat record per module.
Module tables never cross-reference by foreign key.	The same email can simultaneously be a Finance record, a CRM record, and an Intel record. Cross-keys via Message_ID only.	All FK-style joins are done at query time by Message_ID.

Common columns (every module table)

Every module table has the same set of "audit trail" columns that the pipelines write, in addition to the module-specific fields the LLM produces. All are nullable so the same table can be written by either pipeline.

Column	Type	Written by	Notes
Message_ID	SingleLineText (UNIQUE, required)	both pipelines	Natural key, stripped of angle brackets.
Date	DateTime	live (email Date header)	The live pipeline sets this to the email Date: header. The historical pipeline currently does not set it — to keep both pipelines writing to the same table, the column is nullable. A future schema migration can backfill from the LLM-extracted per-module date if needed.
Raw_S3_Link	URL	live	s3://… of the raw .eml in SeaweedFS.
Papra_Link	URL	historical (when attachments exist)	URL of the first Papra document for the email, or null.
Extracted_At	DateTime	both	Set to now_iso() at upsert time. Used to detect stale rows.

Module tables

There are exactly four module tables, one per category. The table name in NocoDB equals the module_name the pipeline uses in NOCODB_MODULE_TABLES. Keep them stable.

finance — Finance / P&L

Receipts, invoices, bank transactions, credit-card statements, subscription renewals, refunds, fees. Anything that affects the personal P&L.

Field contract mirrors .opencode/commands/inboxos:extract-finance.md.

Column	Type	Source	Notes
Message_ID	SingleLineText (UNIQUE, required)	both pipelines	Natural key.
Direction	SingleSelect	LLM	income / expense / refund / transfer / unknown.
Counterparty	SingleLineText	LLM	Vendor / customer / payer / payee name.
Amount	Decimal	LLM	Numeric; positive.
Currency	SingleLineText	LLM	ISO-4217, uppercase. null unless explicitly priced.
Transaction_Date	Date	LLM	Date of the transaction (not the email date), YYYY-MM-DD.
Category	SingleLineText	LLM	Free-form but stable tag: e.g. software, payroll, travel, tax, marketing, cloud.
Reference	SingleLineText	LLM	Invoice / receipt / order / transaction ID.
Notes	LongText	LLM	One-sentence human summary, ≤ ~200 chars.

crm — Personal CRM

Any person-to-person email worth remembering: networking, recruiters, friends, vendors, prospects, customer support threads. Captures the relationship, not just the message.

Field contract mirrors .opencode/commands/inboxos:extract-crm.md.

Column	Type	Source	Notes
Message_ID	SingleLineText (UNIQUE, required)	both pipelines	Natural key.
Contact_Name	SingleLineText	LLM	Best guess at full name.
Contact_Email	Email	LLM	Single email address (not a list).
Organization	SingleLineText	LLM	Company / org.
Role	SingleLineText	LLM	Title or role.
Channel	SingleSelect	LLM	inbound / outbound / intro / reply / scheduling / other.
Intent	SingleSelect	LLM	sales / partnership / recruiting / support / social / intro / other.
Last_Contacted	Date	LLM	YYYY-MM-DD.
Summary	LongText	LLM	One-sentence human summary, ≤ ~200 chars.
Action_Needed	LongText	LLM	Short string describing the next action, or null.

intel — Daily News / Intel

Newsletters, market briefings, security digests, product news, RSS digests. The point is to be able to search "what did I read about X last month" without trawling Gmail.

Field contract mirrors .opencode/commands/inboxos:extract-intel.md.

Column	Type	Source	Notes
Message_ID	SingleLineText (UNIQUE, required)	both pipelines	Natural key.
Source	SingleLineText	LLM	Publication / newsletter name.
Topic	SingleSelect	LLM	markets / ai / security / industry / policy / tech / other.
Headline	LongText	LLM	Single most important headline.
Summary	LongText	LLM	1–3 sentence digest, ≤ ~600 chars.
Items	LongText	LLM	JSON-encoded array of up to 5 short bullet strings. Stored as a JSON-encoded string in a LongText column — NocoDB's native array columns are not stable across versions; storing as text keeps the contract portable.
Issued_Date	Date	LLM	YYYY-MM-DD.
Read_Priority	SingleSelect	LLM	must / should / skim / archive.

promotions — Promotions / Marketing

Sales, discount codes, free-shipping offers, product launches. The point is "do not pay full price if you don't have to".

Field contract mirrors .opencode/commands/inboxos:extract-promotions.md.

Column	Type	Source	Notes
Message_ID	SingleLineText (UNIQUE, required)	both pipelines	Natural key.
Brand	SingleLineText	LLM	Sending brand / company / product.
Offer_Type	SingleSelect	LLM	discount / bogo / freebie / launch / sale / event / newsletter / other.
Headline	LongText	LLM	Main promotional claim.
Discount_Pct	Number	LLM	Numeric percent if explicit, else null.
Discount_Code	SingleLineText	LLM	Promo code string, or null.
Valid_Until	Date	LLM	YYYY-MM-DD if explicit deadline.
Category	SingleSelect	LLM	apparel / software / hardware / food / travel / services / other.
Worth_Saving	Checkbox	LLM	True only when there is a concrete, time-bound, generous offer.
Notes	LongText	LLM	One-sentence summary, ≤ ~200 chars.

papra_index — Papra write audit trail (REQ-INB-19)

Per-message audit trail for every Papra write intent. One row per Papra write decision; populated by the helper pipelines.common.papra_index_upsert (and the test cases in pipelines/_tests/test_papra_index_offline.py). The table is the only column-count change in v1.1: the four module tables (finance, crm, intel, promotions) are unchanged from the Phase 1.1 freeze recorded in docs/phase-1.1-verification/inb-28-report.md.

Column	Type	Source	Notes
Message_ID	SingleLineText (UNIQUE, required)	pipeline	Natural key. One row per Papra write intent.
papra_document_id	SingleLineText (nullable)	pipeline	The returned Papra document id; null when dry_run=true or when the write failed.
module	SingleLineText (nullable)	pipeline	The module that triggered the write, or null for attachment writes.
kind	SingleSelect	pipeline	attachment / body. Which Papra write path produced the row.
decision_at	DateTime (required)	pipeline	now_iso() at decision time.
outcome	SingleSelect	pipeline	ok / failed / dry_run. The final outcome.
error	LongText (nullable)	pipeline	Error string when outcome=failed; null otherwise.

The schema-change procedure in §"Schema changes (process)" applies: scripts/apply_schemas.py is idempotent on the four module tables (it leaves them untouched) and creates papra_index on first run (or no-ops on subsequent runs). The --check mode verifies the new table's existence and Message_ID UNIQUE constraint.

How the pipelines write these tables

The historical pipeline code that consumes this contract:

# pipelines/historical_pipeline.py::process_one_message
modules = classify(opencode_cfg, summary, logger)        # inboxos:classify
for module in modules:
    extracted = extract_for_module(opencode_cfg, module, summary, papra_link, logger)
    # extracted is the LLM's JSON output, with:
    #   "Message_ID": <set by the pipeline from the email header>
    #   "Papra_Link": <set by the pipeline if attachments were uploaded>
    upsert_result = nocodb_upsert(nocodb_cfg, table_id, extracted, message_id)

Important consequences of this contract:

1. The LLM prompt never fills Message_ID reliably. The pipeline overwrites Message_ID from the email header (or the LLM's value if the prompt happens to include it). This is the idempotency key.
2. Raw_S3_Link is currently written only by the live pipeline. The historical pipeline could also set it, but currently does not — the column is nullable so both pipelines can write to the same table.
3. The LLM extractor is expected to fill the module-specific fields exactly as listed above. If it returns extra fields, NocoDB will reject the row. If it returns fewer, the missing fields are stored as null.
4. The Items column in intel is a JSON-encoded string, not a NocoDB multi-select, on purpose — it keeps the contract portable and lets us change item boundaries without schema migrations.

Schema changes (process)

Schema changes are versioned here, not in n8n or the workflow files. The procedure is:

1. Edit this document.
2. Update MODULES in scripts/apply_schemas.py so a re-run is a no-op on the changed columns (add, not rename; if a rename is needed, file a separate migration issue).
3. Update the corresponding .opencode/commands/inboxos:extract-*.md file so the LLM's output matches the new column.
4. Re-run python scripts/apply_schemas.py against NocoDB. It is idempotent.
5. Commit the change in the Gitea repo (the same change must propagate to the prompt file if it adds / removes / renames fields).
6. Open a PR; reviewers should look for:
   • Column additions that the LLM prompt does not yet produce (regressions).
   • Column type changes that would orphan existing data.
   • Message_ID constraint violations.
   • SingleSelect option list changes that would invalidate existing values.

Verification

The schema is considered applied and verified when:

1. python scripts/apply_schemas.py --check exits 0 (the script verifies the tables and unique constraints exist and match).
2. A single test email flows through pipelines/historical_pipeline.py against NocoDB and produces one upserted row in at least one module table, retrievable by ?where=(Message_ID,eq,…).
3. A second pass with the same Message_ID produces {"action": "patch"} on the same row, confirming the upsert is idempotent.

Curated views (REQ-INB-41)

The NocoDB base ships with 4 curated views, one per module. The views are seeded by scripts/apply_schemas.py --seed-views (v1.4 REQ-INB-41).

Table	View title	Filter
finance	Open invoices 30d	(status,eq,open) AND (due_date,le,today+30d)
crm	New contacts 7d	(created_at,ge,today-7d)
intel	Intel watchlist alerts	(priority,eq,high)
promotions	Promotions opt-out queue	(opt_out,eq,true)

The view seed is idempotent: re-running apply_schemas.py --seed-views updates the existing views; if a view with the same name exists but a different filter, the script updates the filter (does not create a duplicate).

After running the script, the views appear in the NocoDB left-nav under each module table.

Open alignment work

None. Both pipelines (pipelines/live_pipeline.py and pipelines/historical_pipeline.py) read the same NOCODB_MODULE_TABLES env var, call the same inboxos:classify prompt, and then call the same inboxos:extract-<module> prompt per matched module. The NocoDB schema here matches the field contract that those prompts declare.