Data spine

Status: phase 1 is built: the four tables and the core code that keeps their rules (server/core/links.js, queue.js, outbox.js, review.js). No integration uses them yet. Issue #56 tracks the rest.

The data spine is the set of tables and jobs that every integration uses. Records come in from an integration, get a Flow identity, and go out from Flow's own records. Each part works the same way for every adapter, so a new integration adds an adapter and no new plumbing.

flowchart LR
  api[Integration API] -- poll or webhook --> inbox[Inbox]
  inbox --> mirror[Mirror<br/>adapter tables]
  mirror --> idmap[Identity map]
  idmap --> core[Core records]
  core -- intended write --> outbox[Outbox]
  outbox -- adapter, through its governor --> api
  core -- conflict --> review[Review queue]
  mirror -- conflict --> review
  review -. recheck .-> mirror
  queue[Job queue] -. runs .-> inbox & mirror & idmap & outbox

What exists today #

Phase 1: the tables #

Phase 1 adds four core tables (migration 0005_data_spine) and the core code that keeps their rules. It changes no behaviour: no integration uses them until phase 3 (#58). All are in server/db/schema.js, so every installation creates them at start (#40).

external_links (identity map, #57) #

• One active link per (organisation_id, system, entity, external_id) and per (organisation_id, system, entity, flow_id): two partial unique indexes WHERE state = 'active'.
• A link with matched_by = 'name' is proposed until a person confirms it. A proposed link opens a review item.
• A link is never deleted. A replaced link is superseded, so history stays.

background_jobs (job queue, #6) #

• A worker claims with SELECT … FOR UPDATE SKIP LOCKED ordered by run_after, and sets a lease. A lease that runs out makes the job claimable again, so a dead process never holds a job.
• A failed attempt waits longer each time (1 minute, doubling, at most 6 hours). After max_attempts the job is dead and opens a review item.
• One scheduler row per recurring kind puts the next job in the queue when the last one ends. This replaces per-adapter loops over time; the Seazona scheduler moves onto it in phase 3.
• No new service: the queue is PostgreSQL only (VISION.md).

outbox (#56) #

• A core action writes its outbox row in the same transaction as the core change, so a write is never lost and never made without its record.
• The adapter sends it through its governor, reads the result back, and records an external_links row with matched_by = 'created_by_flow'.
• An unknown outcome is ambiguous. The adapter settles it by a lookup before it sends again, as Xero draft pushes do today.
• xero_invoice_pushes and obligation_writebacks stay. In phase 4 each new write goes through outbox, and those tables keep their evidence role.

review_items (review queue, #59) #

• One open item per (organisation_id, kind, entity, flow_id): a partial unique index. A repeat finding updates the evidence.
• A review item only alerts. It shows the difference (for example the Seazona price and the Xero price) and never writes to any system. The person fixes it where they choose: in Xero, Seazona, Flow, or any of them. Mark resolved rechecks that one finding: if the difference is gone the item closes; if not, it stays open with the new evidence.

What the code guarantees #

Tests: test/data-spine.test.js, against PostgreSQL, synthetic data.

Links from an earlier installation (phase 2) #

prior_links holds the earlier app's Seazona order to Xero invoice links (server/import/at-start.js). Each start adds the ones Flow does not hold, while FLOW_IMPORT_EARLIER_SCHEMA is set. A link a person made there is person; one the earlier app matched by itself is imported. When the pipeline (#58) first makes the job's obligation, it reads the link with priorLinkFor and checks it against the Xero mirror before it counts as evidence. So history links cost no Xero calls.

Organisation on adapter tables (phase 2) #

The Seazona tables are installation-wide today. Phase 2 adds organisation_id to each, filled from the installation's one organisation, so the identity map can join them. An adapter migration adds the column; no row is dropped.

Who owns each field #

Each fact about a billed job has one owner. Flow compares only the work fields when a job changes, and never writes to another system to settle a difference. The table is FIELD_OWNERS in server/core/ledger.js.

When a job changes after its invoice #

When Flow drafts an invoice it keeps the job facts it used on the obligation (draft_basis: lines, totals, customer, status, time of the report). This is the merge base. When the work system reports a change later, Flow compares three sides, field by field (compareThreeWays):

flowchart LR
  base[Basis<br/>facts at draft] --> cmp{Compare<br/>each work field}
  work[Work system now] --> cmp
  draft[Flow draft now] --> cmp
  cmp -- work system only, draft not sent --> offer[Review + alert<br/>Adopt changes]
  cmp -- both, or invoice sent --> conflict[Review + alert only]
  cmp -- Flow only, or back to basis --> none[Nothing]

A job whose status moves back from a billable status (observeWithdrawn):

The status is stored, so the same status reported again does nothing, and a hold a person released stays released. An invoice drafted before the basis was kept uses the last report as its basis.

Rules #

• Every table above is append-friendly: a row changes state, and is never deleted by the spine.
• Every write to another system has an outbox row first.
• A sync cursor moves only after the page it read is stored.
• Each job kind has a share of an integration's daily call budget (#45). Low lanes wait first; outbox sends and people's actions wait last.
• No silent overwrite. When two systems disagree, Flow opens a review item.

Later phases #