# InboxOS — NocoDB Schema Design

**Status:** Phase 2 deliverable for [INB-4](/INB/issues/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:

```python
# 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.