Data model

Flow keeps its records in PostgreSQL. The schema is in server/db/schema.js; migrations are in server/db/migrations/ and run when the server starts.

Flow's tables, the integrations' included, live in the PostgreSQL schema flow, not public (migration 0021). Each of Flow's connections searches flow, public (FLOW_SEARCH_PATH in server/db/index.js), and the migration sets the same default for Flow's role (and for the database when Flow owns it), so SQL names tables without a schema. A transaction pooler (Neon's -pooler hosts) does not keep a session's SET, but every new session gets the role's default. The pooler's sessions from before the move keep the old one, so the migration closes this role's idle sessions and the pooler opens new ones (tested against PgBouncer in transaction mode). The reason is Replit: its publish compares the workspace's public schema with production's and offers to make production match, deletes included (docs/REPLIT.md, "The workspace database"). The migration journals stay in drizzle.

erDiagram
  organisations ||--o{ contacts : has
  organisations ||--o{ price_items : has
  organisations ||--o{ invoices : issues
  contacts ||--o{ invoices : "is billed on"
  invoices ||--|{ invoice_lines : has
  price_items |o--o{ invoice_lines : "fills"
  invoices ||--o{ invoice_versions : "was sent as"
  people |o--o{ people : "invited"
  organisations ||--o{ billing_obligations : tracks
  contacts |o--o{ billing_obligations : "is billed for"
  billing_obligations |o--o| invoices : "is invoiced by"
  billing_obligations ||--o{ obligation_events : "is explained by"
  billing_obligations ||--o{ obligation_payments : "is paid by"
  billing_obligations ||--o{ obligation_writebacks : "reports back by"
  organisations ||--o{ external_links : "links its records by"
  organisations ||--o{ background_jobs : runs
  organisations ||--o{ outbox : "writes out through"
  organisations ||--o{ review_items : "asks a person through"
  organisations ||--o{ prior_links : "carries over from an earlier app"
  billing_obligations ||--o{ link_suggestions : "may be billed by"
  billing_obligations ||--o{ link_rejections : "is never billed by"
  organisations ||--o{ pipeline_cursors : "reads on from"
  organisations ||--o| invoicing_settings : "drafts and sends by"
  organisations ||--o{ invoice_rules : "checks ready jobs by"
  contacts ||--o| customer_billing_settings : "overrides rules by"
  organisations ||--o{ automations : "runs"
  organisations ||--o{ job_fields : "describes its jobs by"
  job_fields ||--o{ job_field_values : "holds"
  job_fields ||--o{ job_field_events : "is explained by"
  organisations ||--o| job_field_settings : "colours its field groups by"
  organisations ||--o{ captures : "stores a form photo as"

external_links, background_jobs, outbox and review_items are the data spine every integration shares (docs/DATA_SPINE.md). prior_links holds job to accounting record links from an earlier installation, one per job and system, until the job becomes an obligation.

link_suggestions, link_rejections and pipeline_cursors belong to the pipeline that links each job to its accounting invoice (docs/JOB_INVOICE_LINKS.md). billing_obligations.job_date is the day of the work that the matcher compares with invoice dates.

invoicing_settings and customer_billing_settings hold the invoicing rules and send modes (docs/INVOICING_RULES.md). billing_obligations.ready_at and ready_by record Flow's own "ready to invoice" stage: when, and which person or automation. billing_obligations.source_status is the work system's status. automations holds each organisation's choices for Flow's routines, such as marking a job ready from that status (docs/AUTOMATIONS.md). invoice_rules holds the checks each lab builds on a ready job: When, If, Then (docs/INVOICE_RULES.md).

job_fields, job_field_values and job_field_events hold the lab's custom fields for its jobs, each job's values, and who changed them (docs/CUSTOM_FIELDS.md). A value is keyed by the work system's job, (source_system, source_job_ref), so a job needs no obligation to have one. job_field_settings holds one row per organisation: each type group's colour, and when Flow made the Assignee field.

captures is one photo of a handwritten lab form. The bytes live in the object storage adapter. The row keeps the key, size, type and checksum, the empty or typed proposal, and the decision (applied or discarded). A new capture is ready: this slice does not call a model and does not change a job. The photo is deleted when someone decides, and a capture with no decision is deleted after 7 days.

contacts is Flow's own customer or supplier (docs/CONTACTS.md): name, first and last name, email, phone, mobile, account number, tax number, tax rate, address, practitioners (people at the practice, up to 30) and whether the contact is active. One name per kind in an organisation. An integration's copy of the same person stays in that integration's tables and links through external_links.

people lists who may sign in and their role (owner, admin, staff). One installation serves one lab, so it is installation-wide.

Each integration adapter owns its own tables, named after it (seazona_…). They exist only once the adapter has been on at a start, and they are not in server/db/schema.js (docs/INTEGRATIONS.md).

Billing obligation ledger #

One billable job in a work system is one billing_obligations row, keyed by (organisation_id, source_system, source_job_ref). It stores what the work system reported (a case number, never patient data), what the accounting system reported, and how that evidence matched. server/core/ledger.js gives each obligation one view from that evidence only:

stateDiagram-v2
  [*] --> pending: job observed
  pending --> ready: accounting checked (not found), lines mapped, report fresh
  ready --> drafted: guarded draft (live check finds no invoice)
  drafted --> invoiced: accounting reports authorised
  invoiced --> paid: payments cover the total
  paid --> [*]: paid status written back to the work system
  ready --> held: operator hold
  held --> ready: released
  drafted --> review_required: job changed, or evidence ambiguous or in conflict
  invoiced --> review_required: invoice voided or deleted

• Accounting evidence counts only when it matches the job exactly (flow_invoice, source_invoice_ref or accounting_ref). Any other match makes the obligation ambiguous, for a person to resolve.
• A payment counts only with an exact match and a confirmed invoice. A part payment shows as part_paid.
• When the payments cover the total, an obligation_writebacks row holds the paid status for the work system, with the evidence: the invoice link and each payment (date, amount, reference, reconciled or not). A failed write-back stays and is tried again. It never changes the accounting record.

Invoicing API #

The web UI reads and changes invoices through /api/invoicing (server/api/invoicing.js). Every route needs a signed-in, active person. organisationId (query or body) names the organisation; it may be left out while the installation has exactly one. Money is integer cents. Answers are { ok: true, … }, or { ok: false, code, error } with 400, 403, 404 or 409.

A line is { priceItemId?, description?, quantity?, unitAmountCents?, taxCode? }; a price item fills what the line leaves out.

Storage #

Flow has two places for data, and only two:

The object storage adapter has one interface (put, get, delete, signed link) with one implementation per host (server/core/storage.js): a local folder for a single machine or tests, S3-compatible storage, and Replit Object Storage. A published Replit app keeps no files between publishes, so it never uses the local folder; on Replit, captures stay off until FLOW_STORAGE is replit or s3. The server image is read-only, so an unset FLOW_STORAGE leaves captures off there too. Set FLOW_STORAGE=local with a writable FLOW_STORAGE_DIR when the photo should live on that machine. An S3 link is a presigned GET. Replit's client has no signed URL, so that link is a Flow link: Flow checks the signature, reads the bytes and checks the checksum before it sends them. Logs and error reports get a capture id and an error code, never the photo or what was read or typed. The reader (server/core/reader.js) sends the photo through one AI port (server/core/ai.js). The only client is Amazon Bedrock in Sydney or Melbourne, and only with an au. inference profile. The first model reads every capture. The larger model reads again only when case number, practice or work is below the sure line (0.8 unless FLOW_READER_SURE_LINE says otherwise). The model ids, region and keys come from the environment. The capture stores the fields, the model, the token counts and the cost in micros. A call that fails is tried twice more, then the capture is a problem a person can type. No credentials and a turned-off reader do the same, with the reason "reader turned off". The model does not see the jobs list and does not write.

The matcher (server/core/capture-match.js) is Flow code. It scores each job on the Jobs board against the read or typed fields: case number 60 (50 for a common misread such as O for 0), practice 12, dentist 8, due date within 3 days 10, work or teeth in the job's lines 10. It keeps up to 5 jobs at 20 points or more. One job at 60 or more, 20 ahead of the next, is sure, and the proposal updates it with each field that differs. Close jobs propose an update and the person picks the job. No job proposes a new one. A job is <work system>:<its id there>, for example seazona:4821.

Flow uses no other database: no document store, key-value store or cache server. A change that seems to need one says why in its pull request, and shows that PostgreSQL (jsonb, an index, a materialized view, LISTEN/ NOTIFY) cannot do the job. Fewer moving parts keep a self-hosted installation low maintenance.

Rules #

Change the schema #

1. Edit server/db/schema.js.
2. Run npm run db:generate and commit the new migration.
3. Put rules Drizzle cannot express (triggers, functions) in a custom migration: npx drizzle-kit generate --custom --name <what>.
4. Never edit a migration that is on main. Add a new one.
5. A migration only adds: no drop, no rename. npm run db:generate then guards it (scripts/db-guard-migrations.js): IF NOT EXISTS on each new table, column and index, and an "already exists" catch on each constraint, so it also runs on a database that already has them. A hand-written migration needs the same; test/migrations-guarded.test.js fails without it. The guard was added to migrations 0010 on and to every adapter's migrations after they shipped; it changes nothing for a database that ran them before. It is a second safety net: on Replit, if a person approves "Development database changes detected" by mistake, publishing adds a release's tables before Flow starts, and Flow still starts (docs/REPLIT.md, "The workspace database"). Removal is planned as expand, then contract (docs/REPLIT.md, "Later: removing a table or column"). The guard also turns drizzle-kit's REFERENCES "public"."t" into "flow"."t" in core migrations from 0021 on and in every adapter's.
6. flow-server doctor checks a database against the build: each journal's applied migrations, and every table and column the latest snapshot and each adapter declare (server/db/doctor.js). flow-server migrate brings a database up to the build, adapters' tables included, without starting Flow.

Tests #

Tests that need PostgreSQL read FLOW_TEST_DATABASE_URL. Setup drops and drops flow and recreates the whole public schema, so point it only at a throwaway database. For the same reason npm test runs one test file at a time. Outside CI those tests skip when it is unset; CI runs them against a PostgreSQL service.