cloudinit-bot
37d21fb430
---ci--- project: inboxos phase: 1 milestone: v1.4 status: execute wave: 2 task: T2 ---/ci---
248 lines
14 KiB
Markdown
248 lines
14 KiB
Markdown
# 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.
|