Skip to main content
Version: v1.0.0(int)

Layer 1 — Data (v1.0.0)

Single Postgres instance. One shared database. Two logical schemas inside that database mark the boundary between platform data (the tenant catalog and identity layer) and org data (everything tenant-scoped). Tenant rows still carry org_id for shared-DB isolation.

That boundary is the only structural change in v1.0.0. Physical multi-DB belongs to a later stage; this layer is what makes it cheap when it arrives.

What lives where​

Platform schema holds what every tenant needs to know about every other tenant — the tenant catalog itself, the identity model, and the invitation flow. None of this is private to one tenant. Workflow status types live in the org schema as Postgres ENUM types — see the Org schema overview for the full list.

Org schema holds everything that is private to one tenant — every customer, every order, every ledger entry, every contact. Each row carries org_id so the shared-DB instance can isolate tenants today.

Why two schemas (mechanism)​

We use Postgres's native CREATE SCHEMA to draw the boundary. Connections set search_path TO org, platform so most application queries reference unqualified table names and Postgres resolves them in the right place. Cross-schema foreign keys are fully supported — org.purchase_orders.org_id references platform.organizations.id natively.

The schema namespace is the boundary. It's enforceable: a future "tenant role" can be granted access only to org.*. It's portable: a future migration to one-DB-per-tenant moves the org schema into a per-tenant database (and drops the org_id column there) without rewriting the application — queries, FKs, and naming all survive.

We considered and rejected:

  • Prefix-only separation (platform_users, org_purchase_orders in one flat schema). No enforcement, no migration leverage.
  • Schema-per-tenant (org_acme.*, org_globex.*). Heavy today (DDL runs N times per migration, observability fragments), and we don't need its isolation in the shared-DB phase.
  • Separate physical databases now. Out of scope for v1.0.0.
  • Row-level security on org_id. Solves a different problem (defense-in-depth on the application FK), not the platform/org split.

What the future physical split looks like​

When we go physical:

  • platform.* becomes its own database. No row changes.
  • Each tenant gets a database containing only their org schema. The org_id column on every tenant table becomes redundant (one tenant per DB) and is dropped in that migration. Per-tenant unique constraints (UNIQUE(org_id, name)) become single-column unique constraints.
  • Cross-schema FKs become application-level invariants enforced by the platform DB (e.g., when an org is created, its tenant DB is provisioned).

The application changes are limited to connection routing — by-tenant connection string lookup at the start of each request. SQL stays the same.

Key design choices​

The shape of this layer was set by a small set of cross-cutting decisions worth surfacing here. Each shapes the per-entity pages:

  • Platform / org schema split via Postgres schemas + search_path. The boundary above. Lets us go to per-tenant DBs later without rewriting application SQL.
  • Identity is split from membership. platform.users (one row per Firebase identity) + platform.organization_users (one row per (user, org) pair, role lives here). One person can belong to many orgs cleanly.
  • organizations carries identity only. Branding, billing, legal name, and other tenant-private settings live in org.organization_settings. Keeps the platform DB minimal under physical split.
  • org_id everywhere on tenant tables. Required for shared-DB isolation today; dropped at physical-split time. Default placeholder removed — every insert sets org_id explicitly. Every org_id FK uses ON DELETE RESTRICT — an org with any referencing row cannot be hard-deleted. Deactivation (is_active = false) is the normal "stop using" path; a separate admin-only purge operation handles GDPR / account-closure by deleting tenant data in dependency order, then the org row.
  • No status type tables. PO / WO / SO / fulfillment statuses are Postgres ENUMs (po_status, wo_status, order_status, fulfillment_status) on org.*, stored directly on the row. Same pattern as the existing inventory event-type ENUMs.
  • No DB-stored colors anywhere. Display attributes (background/text colors, sort metadata for badges) live in the application layer. Configurable lookup rows have a default palette generated at render time.
  • No user_preferences table at v1. Deliberately deferred until there's a real per-user preference to store.
  • Materials and SKUs stay separate — different shapes, different lifecycles. bom_items keeps the polymorphic material_id XOR input_sku_id shape.
  • Work orders snapshot their recipe at creation. When a work_order_items row is created, the corresponding output SKU's bom_items rows are copied into work_order_inputs. Future BOM edits don't retroactively change in-flight WOs.
  • Customers and vendors stay separate. Asymmetric in this product (customers carry the rich classification model; vendors are flat). A future overlap case can be solved with a soft FK rather than restructuring.
  • Contacts are org-private. No cross-tenant contacts pool. entity_contacts is polymorphic — entity_type is an ENUM, entity_id is application-enforced (no DB FK).
  • Lifecycle dates match statuses. Every lifecycle date column on a transactional table corresponds to a value in its workflow status enum. No orphan dates.
  • Derived values are computed on read, not stored. No order_value on sales_orders, no costs on purchase_orders, no default unit price on skus, no default unit cost on materials. Aggregates and pre-fill suggestions (last price, weighted-average cost, etc.) are computed from the underlying lines and order history per response. Keeps data fresh and avoids the drift class of bugs when inputs change.
  • Archive + delete policy. Master-data and entity tables expose both is_active (archive) and delete. Transactional headers (PO/WO/SO) only support delete; cancellation is a status, not a flag. Line items, receipts, and append-only logs only support delete.

Out of scope for v1​

The L1 schema deliberately doesn't model these. Listed here so future readers know what's missing on purpose, not by accident. Each will get a real design pass when there's a real product need.

  • Multi-currency. v1 is implicitly single-currency. No currency column anywhere — not on parties, not on transactional headers, not on the ledger. When multi-currency support lands it needs full coverage (transaction-time snapshot, ledger currency, reporting currency on the org, conversion rates) — half-coverage is worse than no coverage.
  • Customer-specific pricing (per-customer SKU price overrides, contracted prices).
  • Vendor-specific costs (preferred vendor, vendor catalog, supplier-specific material costs).
  • Returns / RMAs / refunds.
  • Tax / VAT on orders.
  • Lot / batch / serial / expiry on inventory.
  • Per-WO BOM override — work orders snapshot the recipe at creation time (via org.work_order_inputs), but there's no UI to edit that snapshot. Per-WO substitutions are deferred.
  • Granular permissions beyond the four-role enum (owner, admin, member, viewer).
  • Subscription / billing tier / quotas at the org level.
  • Multiple shipments per SO (carrier, tracking, partial dispatches).
  • Multiple payments per SO (partial pay, payment methods).
  • User-level preferences (theming, layouts, defaults). No platform.user_preferences table.
  • User-action audit log — deferred to a separate design pass (issue #358).

Source of truth: the migration chain​

The schema is defined by an append-only chain of SQL migrations under database/migrations/ — 001_initial.sql, 002_*.sql, and so on. The chain is the schema. There is no separate "current schema" file kept in sync alongside the migrations; that duplication would just drift.

How it works:

  • To stand up a fresh database, replay every file from 001 onward in filename order.
  • To bring an existing database forward, replay only the files it hasn't seen. A runner tracks applied versions in a schema_migrations table and never replays a file twice.
  • Existing data is preserved across deploys. A migration like ALTER TABLE orders ADD COLUMN priority INT DEFAULT 0 runs once per database and leaves every other row untouched.

Rules:

  1. Append-only. Once merged, a migration is immutable. Need to change something? Add a new migration that does it forward.
  2. Forward-only. No down-migrations.
  3. Destructive changes need care. Dropping a column or table is permanent. Review carefully, backup first, run in staging.

Pharus today:

001_initial.sql is the L1 baseline (#368). It collapses the previous chain into one file because pilots cutover at the v1.0.0 weekend release, so preserving pre-#368 history wasn't useful. From here onward, every schema change is a new numbered file. A real migrations runner gets wired up alongside the deployment work later; locally we lean on Postgres's docker-entrypoint-initdb.d, which replays the chain on a fresh data volume.

Where to go next​

  • Platform schema — tenant catalog, identity, invitations. Overview ERD + one page per entity.
  • Org schema — tenant-scoped tables. Overview cluster diagram + one page per entity (customers, vendors, products, materials, locations, contacts, BOM, PO/WO/SO clusters, ledgers).