pos — module #14, first BUILT module of the sell path after pricing

12 tables, 183 columns — schema-locked 2026-07-07, Remediation Phase 4 applied 2026-07-08 (up from 9 tables / 143 cols — see Remediation Phase 4 below); reopened 2026-07-10 for Header/Line Remediation fix #8, a bug fix (table/column counts unchanged — see the dedicated subsection below); reopened again 2026-07-20 for the Gap-Fill Batch (A2 parked carts + A4 gift receipt, PROJECT_DECISIONS #74) — +2 tables (parked_cart/parked_cart_line) / +1 column (sale_line.is_gift), up from 10 tables / 160 cols — see the dedicated subsection below. pos is the sell path's transaction spine: in-store, offline-first sales — registers, register sessions (cash-drawer shifts), sales, line items, tenders, refunds, and (as of the Gap-Fill Batch) parked/held carts. Down from v1's 19 tables / 280 cols (see the Consolidation Pass in module_spec/pos.md for the 10 deferred tables and why). Depends on platform (tenant ownership, legal_entity, fiscal-period flag trigger), identity (actor FKs throughout), multi_loc (site — register/sale site scope), inventory (item_variant — the sold thing, and stock.available_qty read-only for no-oversell netting), pricing (price_rule — the resolved-price snapshot target, honoring Hard Contract 1 verbatim), crm (customer — nullable, walk-in sales supported), and shared (currency — ISO-governed currency on every money-bearing row).

PROJECT_DECISIONS entries: #27 (original lock), #74 (Gap-Fill Batch, 2026-07-20 — parked carts + gift receipt).

Global rules for this schema:

  • Uniform tenant-scoping — 11 of 12 tables carry tenant_id NOT NULL FK → platform.tenant, no mixed-scope case anywhere in this module (the Remediation Phase 4 addition, tender_type_catalog, is the sole EXCEPTION — see its own section below, it is deliberately global reference data with no tenant_id and no RLS, matching the established catalog-table convention). The other 11 (including the Gap-Fill Batch's parked_cart/parked_cart_line) all have RLS enabled with a permissive tenant-isolation policy named <table>_tenant_isolation, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid. Plain index on tenant_id on all 11 tenant-scoped tables.
  • updated_at is trigger-maintained via platform.set_updated_at() on 8 of the 12 tables (verified live: register, register_session, sale, sale_payment, sale_refund — the original 5 with a real update lifecycle — plus tender_type_catalog, added Remediation Phase 4, plus parked_cart/parked_cart_line, added the Gap-Fill Batch 2026-07-20). The 4 without it (register_cash_entry, sale_line, sale_refund_line, pos_sync_conflict) are append-only or insert-then-status-only shapes with no general-purpose updated_at column at all.
  • Soft delete on 4 of 12 tables (register, sale, sale_payment, sale_refund all carry deleted_at timestamptz, nullable; register_session does NOT — see its own section for why). register_cash_entry, sale_line, sale_refund_line, pos_sync_conflict, tender_type_catalog, parked_cart, parked_cart_line have no deleted_at (append-only/write-once/status-tracked, or — for tender_type_catalog — deactivated via is_active instead; parked_cart's own terminal status value 'discarded' is its functional equivalent of a soft delete, guarded by trg_parked_cart_guard_status — see below).
  • Offline-sync quintet on 3 tables — sale, sale_payment, sale_refund (the 3 client-creatable tables) each carry client_uuid/origin/sync_status/idempotency_key/synced_at. See the dedicated Offline-Sync section below.
  • Agent-as-actor attribution, continuing the canonical pattern — every *_actor_id column targets identity.actor (the polymorphic root), never identity.identity_user directly.
  • Autonomy treatment is per-table, not uniform. register (a tiny, rarely-changing hardware-config catalog, directly analogous to pricing.price_level) gets no autonomy columns at all. register_session, sale, sale_payment, sale_refund get the full autonomy column set (actor attribution, automation_source, the full review seam review_status/review_reason/reviewed_by_actor_id/reviewed_at, decision_provenance) — POS is overwhelmingly human-initiated/system-recorded, and the review seam exists specifically for an agent flagging an already-completed transaction as anomalous (large discount, void pattern, cash variance at close), never for autonomously creating one. register_cash_entry gets automation_source only (cheap Reporting query surface, no review seam — a cash event is a historical fact). sale_line/sale_refund_line get no autonomy columns at all — a line's own creation is 100% deterministic, inheriting provenance from its parent row. pos_sync_conflict gets its own dedicated seam (status/resolved_by_actor_id/resolution_note/resolved_at) but no automation_source — a conflict is always system-detected, never agent- or human-authored, and detail jsonb already IS this table's decision provenance.
  • v1 baseline vs. this build — a genuine consolidation, not just a delta. v1's docs/old/schema/schema_modules/schema_pos.md specified 19 tables / 280(-or-278, internally inconsistent) cols. This build's original delta: table count down from 19 to 9 (10 tables deferred, see module_spec/pos.md), column count down from 280 to 138 — the first module in this sequence where consolidation shrinks the row instead of growing it (every prior module lock in this sequence — crm, inventory, ai, pricing — only ever ADDED columns to an existing placeholder row). Remediation Phases 1–4 have since grown the module back to 10 tables / 160 columns (see the dated Remediation subsections below); the 2026-07-20 Gap-Fill Batch grew it further to 12 tables / 183 columns (see the dedicated subsection below) — all of this is additive growth on top of the leaner base, not a reversal of the consolidation itself.
  • Immutable-gross totals, no in-place mutation post-completion. sale.total_minor_units is set once and never mutated after a sale completes; refunds are always a NEW sale_refund row, never a rewrite of sale. sale.status deliberately has no 'refunded'/'partially_refunded' value.
  • Pricing's Hard Contract 1 honored verbatim on sale_line. See dedicated section below.
  • Fiscal-period awareness, added Remediation Phase 4. sale, sale_refund, and register_cash_entry all now carry business_date and are flagged (never rejected) when that date falls inside a closed platform.accounting_period. See the Remediation Phase 4 section below.
  • Parked carts never reserve stock, added Gap-Fill Batch 2026-07-20. pos.parked_cart/parked_cart_line are a pure UI/workflow hold-and-resume mechanism for an in-progress register transaction — no inventory.stock_reservation row is ever created for one, a deliberate v1-carried-forward decision (documented in a table comment on pos.parked_cart itself). See the dedicated section below.
  • Gift-receipt intent flag, added Gap-Fill Batch 2026-07-20. sale_line.is_gift persists a line-level (not sale-level) gift-receipt intent, letting one basket mix gift and non-gift items. Rendering (hiding price on a printed/emailed receipt) and the gift-return bearer-credit mechanism are explicitly out of scope here — see the dedicated section below.

Cross-Phase Foreign Keys (pos)

Column Target Notes
register.tenant_id, register_session.tenant_id, register_cash_entry.tenant_id, sale.tenant_id, sale_line.tenant_id, sale_payment.tenant_id, sale_refund.tenant_id, sale_refund_line.tenant_id, pos_sync_conflict.tenant_id platform.tenant NOT NULL on 9 of 10 tables. tender_type_catalog (Remediation Phase 4) has no tenant_id — it is global reference data.
sale.entity_id platform.legal_entity.id Nullable. Added Remediation Phase 4 (Item 15) — see below.
sale_payment.tender_type_id pos.tender_type_catalog.id Nullable. Added Remediation Phase 4 (Item 17a) — additive-interim alongside the pre-existing tender-type CHECK-enum, not yet kept in sync. See below.
register.site_id, sale.site_id multi_loc.site.id NOT NULL on both, ON DELETE RESTRICT. register_session/sale_payment/sale_refund inherit site scope via register/sale, not a duplicated column.
register_session.register_id pos.register.id NOT NULL, ON DELETE RESTRICT. Intra-schema.
sale.register_session_id pos.register_session.id NOT NULL, ON DELETE RESTRICT. Immutable after insert — see Triggers section.
register_cash_entry.register_session_id pos.register_session.id NOT NULL.
sale.customer_id crm.customer.id Nullable, ON DELETE RESTRICT — anonymous/walk-in sales fully supported.
sale.currency_code, sale_line.currency_code, sale_payment.currency_code shared.currency.iso_code NOT NULL on all 3, ON DELETE RESTRICT, char(3).
sale_line.item_variant_id inventory.item_variant.id NOT NULL, ON DELETE RESTRICT. The sold thing.
sale_line.resolving_price_rule_id pricing.price_rule.id Nullable (NULL when no rule matched, bare base_price_cents used), ON DELETE RESTRICT. Part of Hard Contract 1.
sale_id on sale_line, sale_payment, sale_refund pos.sale.id NOT NULL on all 3. Intra-schema.
sale_refund.sale_payment_id pos.sale_payment.id Nullable — which tender is being refunded, if known.
sale_refund_line.sale_refund_id pos.sale_refund.id NOT NULL. Intra-schema.
sale_refund_line.sale_line_id pos.sale_line.id Nullable (Remediation Phase 3 — the no-receipt-refund path). Composite FK as of 2026-07-10 (Header/Line Remediation fix #8's bundled upgrade): FOREIGN KEY (sale_line_id, tenant_id) REFERENCES pos.sale_line (id, tenant_id), replacing a prior bare single-column FK. Intra-schema.
pos_sync_conflict.sale_id pos.sale.id Nullable — the affected sale, if any.
every *_actor_id column identity.actor Nullable throughout. Cross-schema, enforced.
sale.signature_ref — (deferred forward-ref) Plain nullable uuid, no FK — files schema doesn't exist in v2.
sale_payment.stripe_payment_intent_id, sale_payment.charge_account_ref — (deferred forward-refs) Plain nullable text, no FK — Payments/Billing don't exist in v2 yet.
sale_payment.gift_card_id billing.gift_card (id, tenant_id) REAL composite FK since 2026-07-18 (Phase 3 stored-value build, PROJECT_DECISIONS #71) — was a deferred forward-ref, now enforced.
sale_payment.store_credit_id billing.store_credit_account (id, tenant_id) REAL composite FK since 2026-07-18 (Phase 3 stored-value build, PROJECT_DECISIONS #71) — was a deferred forward-ref, now enforced.
parked_cart.site_id multi_loc.site (id, tenant_id) NOT NULL, composite FK. Added Gap-Fill Batch, 2026-07-20 (PROJECT_DECISIONS #74).
parked_cart.register_id pos.register (id, tenant_id) NOT NULL, composite FK. Required pos.register to gain a prerequisite UNIQUE(id, tenant_id) (register_id_tenant_id_unique) this same pass, migration 20260720000002_pos_reopen_register_unique.sql. Added Gap-Fill Batch.
parked_cart.customer_id crm.customer.id Nullable, bare (non-composite) FK — mirrors sale.customer_id's own existing precedent. Added Gap-Fill Batch.
parked_cart.resumed_sale_id pos.sale (id, tenant_id) Nullable, composite FK. Set if and only if status='resumed' — see chk_parked_cart_resumed_requires_sale. Intra-schema. Added Gap-Fill Batch.
parked_cart.parked_by_actor_id identity.actor Nullable. Added Gap-Fill Batch.
parked_cart_line.parked_cart_id pos.parked_cart (id, tenant_id) NOT NULL, composite FK. Intra-schema. Added Gap-Fill Batch.
parked_cart_line.item_variant_id inventory.item_variant (id, tenant_id) NOT NULL, composite FK. Deliberately NOT inventory.stock_reservation — a parked cart never reserves stock, see below. Added Gap-Fill Batch.

pos.register (8 cols)

A physical/virtual till at a site. Rarely changes — mirrors pricing.price_level's own "no autonomy columns" precedent: a till's existence isn't something AI proposes.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy register_tenant_isolation.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Column Type Nullable Default Constraints / Notes
id UUID NOT NULL gen_random_uuid() PK
tenant_id UUID NOT NULL — FK → platform.tenant
site_id UUID NOT NULL — FK → multi_loc.site, ON DELETE RESTRICT
label text NOT NULL — Unique per tenant while not deleted — see indexes
status text NOT NULL 'active' CHECK IN (active,inactive)
created_at timestamptz NOT NULL now()
updated_at timestamptz NOT NULL now() Trigger-maintained
deleted_at timestamptz nullable — Soft delete

CHECK constraints: chk_register_status.

Indexes: PK on id; plain index on tenant_id; plain index on site_id; register_tenant_id_label_unique (UNIQUE, tenant_id+label, WHERE deleted_at IS NULL).

pos.register_session (18 cols)

One open→closed shift per register. No deleted_at — a permanent operational record, closed via closed_at, never soft-deleted (mirrors identity.identity_session's own precedent: ended_at, no deleted_at).

review_status/decision_provenance exist for the one plausible autonomy touch this table has: an agent flagging a large cash variance at close (never auto-correcting the drawer).

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy register_session_tenant_isolation.

No soft delete — permanent record, terminal state is closed_at, not deleted_at.

updated_at: trigger-maintained via platform.set_updated_at().

Column Type Nullable Default Constraints / Notes
id UUID NOT NULL gen_random_uuid() PK
tenant_id UUID NOT NULL — FK → platform.tenant
register_id UUID NOT NULL — FK → pos.register, ON DELETE RESTRICT
opened_by_actor_id UUID nullable — FK → identity.actor
closed_by_actor_id UUID nullable — FK → identity.actor
status text NOT NULL 'open' CHECK IN (open,closed)
opening_float_minor_units bigint NOT NULL 0
closing_counted_minor_units bigint nullable — Set only when status='closed' — see CHECK below
automation_source text NOT NULL 'human' CHECK IN (human,agent,system,seed)
review_status text NOT NULL 'not_required' CHECK IN (not_required,pending,approved,rejected)
review_reason text nullable —
reviewed_by_actor_id UUID nullable — FK → identity.actor. Cannot equal opened_by_actor_id when both set
reviewed_at timestamptz nullable —
decision_provenance jsonb nullable — Cites this register's typical variance pattern via memory_refs when an agent flags a cash discrepancy
opened_at timestamptz NOT NULL now()
closed_at timestamptz nullable —
created_at timestamptz NOT NULL now()
updated_at timestamptz NOT NULL now() Trigger-maintained

CHECK constraints: chk_register_session_status; chk_register_session_closed_consistency ((status='closed' AND closed_at IS NOT NULL AND closing_counted_minor_units IS NOT NULL) OR (status='open' AND closed_at IS NULL)); chk_register_session_automation_source; chk_register_session_review_status; chk_register_session_reviewer_not_creator.

Indexes: PK on id; plain index on tenant_id; register_session_register_id_open_unique (UNIQUE, register_id, WHERE status='open' — the module's hottest read path: find-the-currently-open-session-for-a-register, doubling as the "at most one open session per register" invariant); partial index on review_status WHERE ='pending'.

pos.register_cash_entry (15 cols) — append-only

Cash-drawer event log (paid-in/paid-out/count). Mirrors ai.agent_execution's own shape: no updated_at, a cash event is a historical fact. Gained its first-ever full review seam in Remediation Phase 4 (Item 14) — a necessary mid-build discovery, not a planned addition; see below.

Tenant-scoped, append-only. tenant_id NOT NULL FK → platform.tenant. No updated_at, no deleted_at. RLS enabled — permissive policy register_cash_entry_tenant_isolation.

Column Type Nullable Default Constraints / Notes
id UUID NOT NULL gen_random_uuid() PK
tenant_id UUID NOT NULL — FK → platform.tenant
register_session_id UUID NOT NULL — FK → pos.register_session
entry_type text NOT NULL — CHECK IN (paid_in,paid_out,count)
amount_minor_units bigint NOT NULL — CHECK != 0
note text nullable —
automation_source text NOT NULL 'human' CHECK IN (human,agent,system,seed)
created_by_actor_id UUID nullable — FK → identity.actor
created_at timestamptz NOT NULL now()
business_date date NOT NULL CURRENT_DATE Added Remediation Phase 4 (Item 14). Drives the closed-fiscal-period flag trigger — see below
review_status text NOT NULL 'not_required' CHECK IN (not_required,pending,approved,rejected). Added Remediation Phase 4 — the full review seam this table needed before the flag trigger's review_status='pending' mechanism could exist at all
review_reason text nullable — Added Remediation Phase 4
reviewed_by_actor_id UUID nullable — FK → identity.actor. Added Remediation Phase 4
reviewed_at timestamptz nullable — Added Remediation Phase 4
decision_provenance jsonb nullable — Added Remediation Phase 4

CHECK constraints: chk_register_cash_entry_entry_type; chk_register_cash_entry_amount_nonzero; chk_register_cash_entry_automation_source; chk_register_cash_entry_review_status (Remediation Phase 4).

Indexes: PK on id; plain index on tenant_id; plain index on register_session_id; partial index on review_status WHERE ='pending' (register_cash_entry_review_status_pending_idx, Remediation Phase 4).

Column-count note: 9 cols pre-Phase-4 → 15 (+6: business_date + the 5-column review seam review_status/review_reason/reviewed_by_actor_id/reviewed_at/decision_provenance).

pos.sale (27 cols) — THE offline-first table

The transaction header. client_uuid is the row's TRUE identity from the client's point of view (device-generated, stable across retries), not the server-assigned id. total_minor_units is immutable-gross forever; status has no 'refunded' value — see Immutable-Total section below.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy sale_tenant_isolation.

Soft delete: deleted_at timestamptz, nullable (needed for the client_uuid partial-unique — see indexes).

updated_at: trigger-maintained via platform.set_updated_at().

Column Type Nullable Default Constraints / Notes
id UUID NOT NULL gen_random_uuid() PK
tenant_id UUID NOT NULL — FK → platform.tenant
site_id UUID NOT NULL — FK → multi_loc.site, ON DELETE RESTRICT
register_session_id UUID NOT NULL — FK → pos.register_session, ON DELETE RESTRICT. Immutable after insert — see Triggers
customer_id UUID nullable — FK → crm.customer, ON DELETE RESTRICT. Anonymous/walk-in fully supported
client_uuid UUID NOT NULL — Device-generated. See Offline-Sync section
origin text NOT NULL 'online' CHECK IN (online,offline)
sync_status text NOT NULL 'synced' CHECK IN (synced,local_only,conflict)
idempotency_key text nullable — MUST derive deterministically from client_uuid — build requirement, not DB-enforced
synced_at timestamptz nullable —
status text NOT NULL 'open' CHECK IN (open,completed,voided) — deliberately no 'refunded' value
total_minor_units bigint NOT NULL 0 CHECK >= 0. Immutable-gross once set — see reconciliation formula below
currency_code char(3) NOT NULL — FK → shared.currency.iso_code, ON DELETE RESTRICT
signature_ref UUID nullable — Forward-ref, no FK — files schema doesn't exist in v2
created_by_actor_id UUID nullable — FK → identity.actor
automation_source text NOT NULL 'human' CHECK IN (human,agent,system,seed) — realistically almost always human/system
review_status text NOT NULL 'not_required' CHECK IN (not_required,pending,approved,rejected) — for agent-flagged anomalies
review_reason text nullable —
reviewed_by_actor_id UUID nullable — FK → identity.actor. Cannot equal created_by_actor_id when both set
reviewed_at timestamptz nullable —
decision_provenance jsonb nullable — e.g. {"reason":"discount % (35%) exceeds this cashier's 90-day average (8%) by 4x","evidence":{"cashier_actor_id":"...","discount_pct":35,"cashier_avg_pct":8},"confidence":0.78,"memory_refs":[]}
created_at timestamptz NOT NULL now()
updated_at timestamptz NOT NULL now() Trigger-maintained
deleted_at timestamptz nullable — Soft delete
sale_number text NOT NULL — Register-session-prefixed, client-generated. See Remediation Phase 3 section below
business_date date NOT NULL CURRENT_DATE Added Remediation Phase 4 (Item 14) via a 3-step migration (add nullable → backfill created_at::date for then-189 rows → SET NOT NULL DEFAULT CURRENT_DATE). Drives the closed-fiscal-period flag trigger — see below
entity_id UUID nullable — FK → platform.legal_entity. Added Remediation Phase 4 (Item 15), part of that item's 10-table rollout

Reconciliation formula (documented, not DB-enforced): total_minor_units = sum(sale_line.charged_amount_minor_units) + sum(sale_line.tax_amount_cents).

CHECK constraints (9): chk_sale_origin; chk_sale_sync_status; chk_sale_origin_sync_status_consistency ((origin='online' AND sync_status='synced') OR (origin='offline' AND sync_status IN ('local_only','conflict','synced'))); chk_sale_status; chk_sale_total_nonnegative; chk_sale_automation_source; chk_sale_review_status; chk_sale_reviewer_not_creator.

Indexes: PK on id; plain indexes on tenant_id, site_id, register_session_id, client_uuid; partial index on customer_id WHERE NOT NULL; partial index on review_status WHERE ='pending'; sale_tenant_id_client_uuid_unique (UNIQUE, tenant_id+client_uuid, WHERE deleted_at IS NULL — the offline-idempotency dedup index; write path is INSERT ... ON CONFLICT (tenant_id, client_uuid) DO NOTHING); sale_tenant_id_sale_number_unique (UNIQUE, tenant_id+sale_number, WHERE deleted_at IS NULL — added Remediation Phase 3, see below); sale_tenant_id_business_date_idx (tenant_id+business_date, added Remediation Phase 4).

Deviation from the earlier design draft, found during build: the PROPOSE+STOP report listed a search_vector (generated tsvector, GIN) column "matching the precedent applied to item/item_variant/customer" — DROPPED during build. Unlike those 3 tables, sale has no free-text field to feed it; the only candidate was status, a 3-value enum, which would produce a near-useless index. Logged to OPEN_ITEMS as a deferred add-later item.

pos.sale_line (15 cols) — honors Pricing's Hard Contract 1 verbatim

Line items. Write-once — no updated_at, no deleted_at. No independent autonomy columns — a line's own creation is 100% deterministic (driven by what the cashier scanned and what resolvePrice() returned); inherits provenance from the parent sale row.

Pricing's locked Hard Contract 1, honored verbatim — the 6 required snapshot fields below are the historical record of what resolvePrice() returned at sale time, never re-derived from the live (possibly-since-superseded) price_rule row. Live-tested: after the resolving price_rule is superseded to a new price, sale_line's snapshot amounts remain unchanged.

Tenant-scoped, write-once. tenant_id NOT NULL FK → platform.tenant. No updated_at, no deleted_at. RLS enabled — permissive policy sale_line_tenant_isolation.

Column Type Nullable Default Constraints / Notes
id UUID NOT NULL gen_random_uuid() PK
tenant_id UUID NOT NULL — FK → platform.tenant
sale_id UUID NOT NULL — FK → pos.sale
item_variant_id UUID NOT NULL — FK → inventory.item_variant, ON DELETE RESTRICT
resolved_amount_minor_units bigint NOT NULL — Hard Contract 1, field 1: pre-rounding resolved amount
charged_amount_minor_units bigint NOT NULL — Hard Contract 1, field 2: post-rounding actual charge
currency_code char(3) NOT NULL — Hard Contract 1, field 3. FK → shared.currency.iso_code, ON DELETE RESTRICT
tax_treatment text NOT NULL — Hard Contract 1, field 4. CHECK IN (inclusive,exclusive)
resolving_price_rule_id UUID nullable — Hard Contract 1, field 5. FK → pricing.price_rule, ON DELETE RESTRICT. NULL when no rule matched (bare base_price_cents used)
resolved_quantity numeric NOT NULL — Hard Contract 1, field 6. CHECK > 0. Needed since one rule can cover multiple min_qty tiers via separate rows
tax_amount_cents bigint NOT NULL 0
tax_rate numeric NOT NULL 0
has_guarantee boolean NOT NULL false Forward-compat flag, mirrors item_variant.has_guarantee
created_at timestamptz NOT NULL now()
is_gift boolean NOT NULL false Added Gap-Fill Batch, 2026-07-20 (A4, PROJECT_DECISIONS #74) — line-level gift-receipt intent flag, letting one basket mix gift and non-gift items. Persisted intent only: no CHECK, no trigger, no rendering logic. Hiding the price on a printed/emailed receipt is the future receipt-rendering/notifications build's own work; the gift-return bearer-credit mechanism (crediting whoever presents the gift receipt, not necessarily the original purchaser) is returns' own future work. Migration: packages/db/migrations/20260720000004_pos_parked_carts_and_gift_receipt.sql

CHECK constraints: chk_sale_line_tax_treatment; chk_sale_line_resolved_quantity_positive; chk_sale_line_amounts_nonnegative (resolved_amount_minor_units >= 0 AND charged_amount_minor_units >= 0 AND tax_amount_cents >= 0). is_gift carries no CHECK — a plain boolean, deliberately (see column note above).

Indexes: PK on id; plain indexes on tenant_id, sale_id, item_variant_id; sale_line_id_tenant_id_unique (UNIQUE, id+tenant_id — added 2026-07-10, Header/Line Remediation fix #8's prerequisite for fix #6, orders.order_line.sale_line_id → pos.sale_line, itself still DEFERRED; also the target of sale_refund_line.sale_line_id's own composite-FK upgrade in the same migration — see below).

pos.sale_payment (27 cols)

One row per tender applied to a sale. Same offline-sync shape as sale. status covers the real Stripe PaymentIntent/Terminal vocabulary. Full vs. partial refund is deliberately NOT tracked via status (mirrors Stripe's own PaymentIntent/Refund object separation) — partial-refund amounts live entirely on the linked sale_refund row.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy sale_payment_tenant_isolation.

Soft delete: deleted_at timestamptz, nullable (needed for the client_uuid partial-unique).

updated_at: trigger-maintained via platform.set_updated_at().

Column Type Nullable Default Constraints / Notes
id UUID NOT NULL gen_random_uuid() PK
tenant_id UUID NOT NULL — FK → platform.tenant
sale_id UUID NOT NULL — FK → pos.sale
client_uuid UUID NOT NULL — Device-generated. See Offline-Sync section
origin text NOT NULL 'online' CHECK IN (online,offline)
sync_status text NOT NULL 'synced' CHECK IN (synced,local_only,conflict)
idempotency_key text nullable —
synced_at timestamptz nullable —
payment_method text NOT NULL — CHECK IN (card,cash,check,charge_account,gift_card,store_credit,reward) — matches v1's own value list exactly (an earlier draft's tender_type rename was reverted). gift_card/store_credit are now BACKED tenders (2026-07-18, Phase 3 stored-value build) — real instrument + FK + atomic ledger guard. reward remains gated shut — see chk_sale_payment_no_unbacked_tender_type below (narrowed 2026-07-18 to reward-only)
status text NOT NULL 'pending' CHECK IN (pending,requires_action,processing,authorized,captured,failed,canceled,refunded,queued_offline) — real Stripe PaymentIntent/Terminal vocabulary
charged_amount_minor_units bigint NOT NULL — CHECK >= 0
currency_code char(3) NOT NULL — FK → shared.currency.iso_code, ON DELETE RESTRICT
stripe_payment_intent_id text nullable — Forward-ref, no FK — Payments module doesn't exist in v2 yet
charge_account_ref text nullable — Forward-ref, no FK — into future billing.ar_charge
gift_card_id UUID nullable — Composite FK → billing.gift_card (id, tenant_id) since 2026-07-18 (sale_payment_gift_card_tenant_fkey) — was a deferred forward-ref
store_credit_id UUID nullable — Composite FK → billing.store_credit_account (id, tenant_id) since 2026-07-18 (sale_payment_store_credit_tenant_fkey) — was a deferred forward-ref
created_by_actor_id UUID nullable — FK → identity.actor
automation_source text NOT NULL 'human' CHECK IN (human,agent,system,seed)
review_status text NOT NULL 'not_required' CHECK IN (not_required,pending,approved,rejected)
review_reason text nullable —
reviewed_by_actor_id UUID nullable — FK → identity.actor. Cannot equal created_by_actor_id when both set
reviewed_at timestamptz nullable —
decision_provenance jsonb nullable —
created_at timestamptz NOT NULL now()
updated_at timestamptz NOT NULL now() Trigger-maintained
deleted_at timestamptz nullable — Soft delete
tender_type_id UUID nullable — FK → pos.tender_type_catalog. Added Remediation Phase 4 (Item 17a) — additive-interim alongside the pre-existing payment_method CHECK-enum, not yet kept in sync. See below

CHECK constraints (12): chk_sale_payment_payment_method; chk_sale_payment_status; chk_sale_payment_origin; chk_sale_payment_sync_status; chk_sale_payment_origin_sync_status_consistency; chk_sale_payment_amount_nonnegative; chk_sale_payment_automation_source; chk_sale_payment_review_status; chk_sale_payment_reviewer_not_creator; chk_sale_payment_no_unbacked_tender_type (added 2026-07-07, widened 2026-07-08, narrowed 2026-07-18 — see below); its companion chk_sale_payment_stored_value_ref_matches_method (added 2026-07-07, strengthened 2026-07-18); and NEW chk_sale_payment_stored_value_online_only (2026-07-18).

Indexes: PK on id; plain indexes on tenant_id, sale_id, client_uuid; partial index on review_status WHERE ='pending'; sale_payment_tenant_id_client_uuid_unique (UNIQUE, tenant_id+client_uuid, WHERE deleted_at IS NULL); sale_payment_id_tenant_id_unique (UNIQUE, id+tenant_id, added 2026-07-18 as the prerequisite for billing.gift_card_transaction/store_credit_transaction's own composite FKs).

tender_type_id — Remediation Phase 4, Item 17a. See the dedicated pos.tender_type_catalog section and the Remediation Phase 4 subsection below for the full additive-interim pattern (new catalog + new nullable FK, old CHECK-enum column left completely unchanged, disclosed as not yet kept in sync).

POS never calls the Stripe API directly and never processes Stripe webhooks — both are Payments' exclusive responsibility; PaymentsService writes the outcome back into status after processing a webhook.

chk_sale_payment_no_unbacked_tender_type — NARROWED 2026-07-18 (Phase 3 stored-value build, PROJECT_DECISIONS #71). History: added 2026-07-07 as chk_sale_payment_no_unvalidated_stored_value_tender (payment_method NOT IN ('gift_card','store_credit'), closing the real live bug the 19-to-9 functionality audit found — an unvalidated stored-value tender was silently accepted with zero balance check); widened 2026-07-08 (Remediation Phase 3, Item 11) to also block reward. Now: CHECK (payment_method != 'reward'). gift_card/store_credit were REMOVED from this gate only after billing.gift_card/billing.store_credit_account were built and real composite FKs were added to gift_card_id/store_credit_id (the OPEN_ITEMS row-179 contract: real FK validation replaces the gate, never just a CHECK drop). reward stays blocked — Part 0.3 of the stored-value build investigated whether rewards (locked since #56/#60) backs a reward TENDER and found it does not: zero pos→rewards linkage column, no points→money bridge, chk_loyalty_point_ledger_redeem_requires_reward_option structurally forces every redemption through a pre-configured reward_option, and loyalty_account is consumer-layer-only. Tracked in OPEN_ITEMS with the precise unlock trigger. Live-reproduced both ways: a gift_card tender with a real card is now ACCEPTED; reward is still REJECTED.

chk_sale_payment_stored_value_ref_matches_method — STRENGTHENED 2026-07-18 from its original one-directional form (payment_method IN ('gift_card','store_credit') OR (gift_card_id IS NULL AND store_credit_id IS NULL), added 2026-07-07 after independent Section 4 verification found the original gate alone left a residual gap — a non-stored-value tender could carry a fabricated ref, live-reproduced pre-fix as payment_method='cash' + a random gift_card_id accepted with zero complaint). Now full coherence: CHECK ((payment_method = 'gift_card' AND gift_card_id IS NOT NULL AND store_credit_id IS NULL) OR (payment_method = 'store_credit' AND store_credit_id IS NOT NULL AND gift_card_id IS NULL) OR (payment_method NOT IN ('gift_card','store_credit') AND gift_card_id IS NULL AND store_credit_id IS NULL)). A stored-value tender now REQUIRES its matching instrument ref — closing the forward direction of the same silent-acceptance class (a gift_card tender with a NULL gift_card_id would otherwise be "tender-tagged" with zero linkage). Live-reproduced: a refless gift_card tender is rejected; a cash tender smuggling a gift_card_id is still rejected.

chk_sale_payment_stored_value_online_only — NEW, 2026-07-18. CHECK (payment_method NOT IN ('gift_card','store_credit') OR origin = 'online'). Honors v1's own explicit offline-first boundary ("Online-required: charge account validation, gift card balance lookup") — an offline redemption cannot hold the atomic balance guard (the ledger write happens at sync time, potentially after the customer left), so the tender itself is rejected at the register unless online. Live-reproduced: an offline gift_card tender is rejected.

pos.sale_refund (24 cols)

Always a NEW row — never mutates sale or sale_payment. approved_by_actor_id is the v1-carried-forward manager-PIN sign-off on issuing the refund itself, distinct from reviewed_by_actor_id (an agent-flagged-anomaly review seam). sale_id is nullable as of Remediation Phase 4 — see the dedicated subsection below for the anonymous-walk-in-return decision this closes.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy sale_refund_tenant_isolation.

Soft delete: deleted_at timestamptz, nullable (needed for the client_uuid partial-unique).

updated_at: trigger-maintained via platform.set_updated_at().

Column Type Nullable Default Constraints / Notes
id UUID NOT NULL gen_random_uuid() PK
tenant_id UUID NOT NULL — FK → platform.tenant
sale_id UUID nullable (Remediation Phase 4 — relaxed from NOT NULL) — FK → pos.sale. See chk_sale_refund_identification below
sale_payment_id UUID nullable — FK → pos.sale_payment — which tender is being refunded, if known
client_uuid UUID NOT NULL — Device-generated. See Offline-Sync section
origin text NOT NULL 'online' CHECK IN (online,offline)
sync_status text NOT NULL 'synced' CHECK IN (synced,local_only,conflict)
idempotency_key text nullable —
synced_at timestamptz nullable —
refunded_amount_minor_units bigint NOT NULL — CHECK > 0
reason text nullable —
created_by_actor_id UUID nullable — FK → identity.actor
approved_by_actor_id UUID nullable — FK → identity.actor. Manager-PIN sign-off (v1 precedent)
automation_source text NOT NULL 'human' CHECK IN (human,agent,system,seed)
review_status text NOT NULL 'not_required' CHECK IN (not_required,pending,approved,rejected)
review_reason text nullable —
reviewed_by_actor_id UUID nullable — FK → identity.actor. Cannot equal created_by_actor_id when both set
reviewed_at timestamptz nullable —
decision_provenance jsonb nullable —
created_at timestamptz NOT NULL now()
updated_at timestamptz NOT NULL now() Trigger-maintained
deleted_at timestamptz nullable — Soft delete
tax_refunded_amount_cents bigint NOT NULL 0 CHECK >= 0. Plain positive POS-layer magnitude — added Remediation Phase 3, see below
business_date date NOT NULL CURRENT_DATE Added Remediation Phase 4 (Item 14), 0 rows at build time. Drives the closed-fiscal-period flag trigger — see below

Build requirement, documented, not DB-enforced: the refund-issuance path MUST verify sum(sale_refund.refunded_amount_minor_units) for a given sale_id, including the refund being created, never exceeds sum(sale_payment.charged_amount_minor_units) for that same sale (requires summing a variable number of prior rows — not expressible as a same-row CHECK). When sale_id IS NULL (an anonymous walk-in return, Remediation Phase 4), this check is structurally inapplicable — there is no sale to sum payments against; the anonymous case relies entirely on the service-layer audit controls described below, not this aggregate check.

CHECK constraints (10): chk_sale_refund_origin; chk_sale_refund_sync_status; chk_sale_refund_origin_sync_status_consistency; chk_sale_refund_amount_positive; chk_sale_refund_automation_source; chk_sale_refund_review_status; chk_sale_refund_reviewer_not_creator; chk_sale_refund_tax_refunded_nonnegative (Remediation Phase 3); chk_sale_refund_identification (CHECK (sale_id IS NOT NULL OR reason IS NOT NULL) — added Remediation Phase 4, closing the anonymous-walk-in-return decision; see below).

Indexes: PK on id; plain indexes on tenant_id, sale_id, client_uuid; partial index on sale_payment_id WHERE NOT NULL; partial index on review_status WHERE ='pending'; sale_refund_tenant_id_client_uuid_unique (UNIQUE, tenant_id+client_uuid, WHERE deleted_at IS NULL); sale_refund_tenant_id_business_date_idx (tenant_id+business_date, added Remediation Phase 4).

The anonymous walk-in return — DECIDED = ALLOW, Remediation Phase 4. Phase 3 (Item 10) relaxed sale_refund_line.sale_line_id to nullable (the no-receipt-refund LINE path) but deliberately left sale_refund.sale_id itself NOT NULL, flagging the fuller question — a refund with no sale context at all — as an open, UNDECIDED human go/no-go, not resolved or bundled into that build. That decision is now made: ALLOW. sale_id is relaxed to nullable (confirmed 0 live rows at build time, zero backfill risk); the new chk_sale_refund_identification CHECK requires a refund to identify itself SOME way — a linked sale, or a documented reason (the pre-existing nullable reason column, reused rather than adding a new one) — never neither. Audit controls a real anonymous-return flow needs (a REQUIRED, not just present, reason at the UI layer; manager/actor attribution; an approval gate for high-value anonymous refunds) are explicitly a SERVICE-LAYER requirement, NOT schema-enforced — logged to OPEN_ITEMS, matching the same schema/service split this codebase has used throughout (e.g. Phase 3 Item 8's credit-limit enforcement, Item 12's agent kill-switch precedence chain). The resulting site/register-context gap — an anonymous refund carries no way to know which site it happened at, since the only current path to a site is sale_id → sale.site_id — is also disclosed, not schema-expanded beyond what was decided. Live-reproduced: a refund with both sale_id and reason NULL is rejected; a refund with sale_id NULL and reason set (e.g. "anonymous walk-in return, no receipt") is now ACCEPTED — previously impossible; a refund with a valid sale_id and NULL reason is unaffected (pre-existing behavior). Migration: packages/db/migrations/20260708280000_phase4_close_anonymous_return.sql. PROJECT_DECISIONS #40.

pos.sale_refund_line (10 cols) — write-once, DB-enforced append-only (2026-07-10)

Which sale_line(s) a refund applies to, OR — as of Remediation Phase 3 — which item_variant a no-receipt refund line identifies, when there is no historical sale_line to point to. Same treatment as sale_line — no updated_at, no deleted_at. As of 2026-07-10 (Header/Line Remediation fix #8), this table's long-documented "write-once" claim is finally DB-enforced, not just descriptive — see the dedicated subsection below.

Tenant-scoped, write-once, append-only. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy sale_refund_line_tenant_isolation. REVOKE UPDATE, DELETE FROM authenticated + trigger trg_sale_refund_line_append_only (added 2026-07-10 — see below).

Column Type Nullable Default Constraints / Notes
id UUID NOT NULL platform.uuid_generate_v7() PK
tenant_id UUID NOT NULL — FK → platform.tenant
sale_refund_id UUID NOT NULL — FK → pos.sale_refund
sale_line_id UUID nullable — Composite FK → pos.sale_line (sale_line_id, tenant_id) → (id, tenant_id). Relaxed from NOT NULL to nullable in Remediation Phase 3 — see below. Upgraded from a bare single-column FK to composite in the 2026-07-10 Header/Line Remediation (fix #8's bundled opportunistic fix) — see below
refunded_quantity numeric NOT NULL — CHECK > 0
refunded_amount_minor_units bigint NOT NULL — CHECK >= 0
created_at timestamptz NOT NULL now()
tax_amount_cents bigint NOT NULL 0 CHECK >= 0. Added Remediation Phase 3 — mirrors sale_line.tax_amount_cents
tax_rate numeric NOT NULL 0 Added Remediation Phase 3 — mirrors sale_line.tax_rate
item_variant_id UUID nullable — FK → inventory.item_variant, ON DELETE RESTRICT. Added Remediation Phase 3 — the no-receipt-refund alternative identifier, see below

CHECK constraints: chk_sale_refund_line_quantity_positive; chk_sale_refund_line_amount_nonnegative; chk_sale_refund_line_tax_amount_nonnegative (Remediation Phase 3); chk_sale_refund_line_identification (Remediation Phase 3 — sale_line_id IS NOT NULL OR item_variant_id IS NOT NULL, at least one identifier always required).

Indexes: PK on id; plain indexes on tenant_id, sale_refund_id, sale_line_id.

PK-generation note: id's DEFAULT is platform.uuid_generate_v7() (Remediation Phase 2, unchanged by Phase 3 — this table was already 1 of the 3 append-only ledgers converted in that earlier phase, though its own append-only enforcement did not actually exist until the 2026-07-10 fix below — see the correction there).

Header/Line Remediation, Fix #8 (2026-07-10) — a real bug, now closed. This table was documented as write-once/immutable since its original build, but had zero DB enforcement — live-reproduced pre-fix: a plain UPDATE and a DELETE against an existing row both succeeded with zero error. Unlike its sibling sale_line (covered by Remediation Phase 1's append-only sweep, 20260708140000_phase1_append_only_ledgers.sql), sale_refund_line was never actually included in that migration — the gap this fix closes. Closed via REVOKE UPDATE, DELETE ON pos.sale_refund_line FROM authenticated + a new trigger, trg_sale_refund_line_append_only, reusing platform.reject_append_only_mutation() verbatim — zero new PL/pgSQL, matching sale_line's own precedent exactly (see the Triggers section below). Live-reproduced post-fix: the identical UPDATE/DELETE are now rejected. Bundled in the same migration, not part of fix #8 itself: (1) a prerequisite UNIQUE(id, tenant_id) on pos.sale_line for a future, still-DEFERRED fix (orders.order_line.sale_line_id → pos.sale_line) — see the sale_line section above; (2) the opportunistic composite-FK upgrade on this table's own sale_line_id noted in the column table above, closing a bare (non-composite) cross-tenant-exposure FK that predated this fix. Migration: packages/db/migrations/20260710000000_headerline_pos_fix8.sql. No table added, no column added or removed — pos stays 10 tables / 160 columns. Test coverage: apps/api/src/pos/__tests__/pos-schema.spec.ts, new sections L/M/N. See PROJECT_DECISIONS #46.

pos.pos_sync_conflict (10 cols) — the offline collision queue

Created when an offline sync produces something that cannot be auto-resolved. status is itself the human-in-the-loop seam. No automation_source — a conflict is always system-detected, never agent- or human-authored. detail jsonb already IS this table's decision provenance. No updated_at (status changes tracked via resolved_at instead), no deleted_at.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy pos_sync_conflict_tenant_isolation.

Column Type Nullable Default Constraints / Notes
id UUID NOT NULL gen_random_uuid() PK
tenant_id UUID NOT NULL — FK → platform.tenant
conflict_type text NOT NULL — CHECK IN (stock_oversell,duplicate_sale,price_mismatch,payment_dup,other)
sale_id UUID nullable — FK → pos.sale — the affected sale, if any
detail jsonb nullable — Example: {"variant_id":"...", "qty_sold_offline":2, "qty_available_at_sync":0, "conflicting_sale_ids":["...","..."], "conflicting_client_uuids":["...","..."], "sale_created_at_per_device":{"...":"2026-07-07T10:03:00Z","...":"2026-07-07T10:04:15Z"}}
status text NOT NULL 'open' CHECK IN (open,resolved,ignored)
resolved_by_actor_id UUID nullable — FK → identity.actor
resolution_note text nullable —
resolved_at timestamptz nullable —
created_at timestamptz NOT NULL now()

CHECK constraints: chk_pos_sync_conflict_conflict_type; chk_pos_sync_conflict_status; chk_pos_sync_conflict_resolved_consistency ((status='open' AND resolved_by_actor_id IS NULL AND resolved_at IS NULL) OR (status != 'open' AND resolved_at IS NOT NULL)).

Indexes: PK on id; plain index on tenant_id; partial index on sale_id WHERE NOT NULL; pos_sync_conflict_tenant_id_status_open_idx (tenant_id+status, WHERE status='open' — the pending-conflict sweep, this module's real hygiene-agent candidate).

pos.tender_type_catalog (7 cols) — NEW table, Remediation Phase 4, global reference data

Added Remediation Phase 4 (Item 17a), the POS side of the codebase-wide enum→catalog additive-interim pattern: a new catalog table + a new, independently-nullable FK column (sale_payment.tender_type_id) on the consuming table, while the OLD CHECK-enum column (sale_payment.payment_method) stays completely unchanged. This interim gap — the new FK column is not yet constrained to stay in sync with the legacy CHECK-enum column — is a disclosed, deliberate scaffolding step, not an oversight; see Remediation Phase 4 below.

Global reference data — the sole EXCEPTION to this module's otherwise-uniform tenant scoping. No tenant_id, no RLS — matches the established precedent of identity.permission/admin.setting_definition/tax.jurisdiction_level_catalog's sibling design. No deleted_at (rows are deactivated via is_active, never deleted).

updated_at: trigger-maintained via platform.set_updated_at().

Column Type Nullable Default Constraints / Notes
id UUID NOT NULL gen_random_uuid() PK
code text NOT NULL — Unique — see indexes
name text NOT NULL — Display name
category text nullable — e.g. card, cash, credit, stored_value
is_active boolean NOT NULL true Deactivation, not deletion
created_at timestamptz NOT NULL now()
updated_at timestamptz NOT NULL now() Trigger-maintained

Seeded rows (7), matching the existing payment_method CHECK-enum exactly: card, cash, charge_account, check, gift_card, reward, store_credit.

Indexes: PK on id; tender_type_catalog_code_unique (UNIQUE, code).

Cross-cutting NOTE, independently caught during Phase 4 verification, NOT introduced by this phase: like every other global-reference table in this codebase (shared.currency, admin.setting_definition, etc.), tender_type_catalog grants full INSERT/SELECT/UPDATE/DELETE to the authenticated role with no RLS restricting who can mutate it — live-reproduced: a plain tenant-scoped session can DELETE FROM pos.tender_type_catalog WHERE code='cash' and it succeeds. This is a systemic gap in the Postgres role/grant model (no distinct "platform-admin-only, tenant-read-only" role exists yet), not something Phase 4 introduced — it simply inherited an existing pattern shared by all catalog tables, old and new. Logged to OPEN_ITEMS for whoever owns the grant model, not fixed here.

pos.parked_cart (13 cols) — NEW table, Gap-Fill Batch, 2026-07-20

A held register cart: paused mid-transaction and later either resumed into a real sale or discarded. Pure UI/workflow convenience — never an inventory hold, see the dedicated no-stock-reservation note below. status is a 3-value lifecycle (parked/resumed/discarded), with resumed/discarded both terminal, guarded by trg_parked_cart_guard_status (see Triggers section below).

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy parked_cart_tenant_isolation.

No soft delete — the terminal status='discarded' value is this table's functional equivalent, guarded (not free-form) by trg_parked_cart_guard_status.

updated_at: trigger-maintained via platform.set_updated_at().

Column Type Nullable Default Constraints / Notes
id UUID NOT NULL gen_random_uuid() PK
tenant_id UUID NOT NULL — FK → platform.tenant
site_id UUID NOT NULL — Composite FK → multi_loc.site (id, tenant_id)
register_id UUID NOT NULL — Composite FK → pos.register (id, tenant_id) — required a new prerequisite UNIQUE(id, tenant_id) on register, added the same pass (migration 20260720000002_pos_reopen_register_unique.sql)
parked_by_actor_id UUID nullable — FK → identity.actor
customer_id UUID nullable — Bare FK → crm.customer.id — mirrors sale.customer_id's own existing precedent; an anonymous walk-in cart can be parked too
label text nullable — Cashier-facing identifier for the held cart (e.g. "Smith — patio set")
note text nullable —
status text NOT NULL 'parked' CHECK IN (parked,resumed,discarded) — resumed/discarded are terminal, see Triggers
resumed_sale_id UUID nullable — Composite FK → pos.sale (id, tenant_id). Set if and only if status='resumed' — see CHECK below
expires_at timestamptz nullable — Advisory only — no automatic expiry/discard sweep exists yet (service-layer gap, see OPEN_ITEMS)
created_at timestamptz NOT NULL now()
updated_at timestamptz NOT NULL now() Trigger-maintained

CHECK constraints: chk_parked_cart_status; chk_parked_cart_resumed_requires_sale ((status='resumed' AND resumed_sale_id IS NOT NULL) OR (status<>'resumed' AND resumed_sale_id IS NULL)).

Indexes: PK on id; parked_cart_id_tenant_id_unique (UNIQUE, id+tenant_id — the prerequisite for parked_cart_line's own composite FK into this table); parked_cart_tenant_id_idx (plain, tenant_id); parked_cart_register_status_idx (register_id+status, WHERE status='parked' — the hot "what's currently parked at this register" read path); parked_cart_expires_at_idx (expires_at, WHERE status='parked' AND expires_at IS NOT NULL — the future expiry-sweep's own candidate index, already in place even though the sweep itself isn't built).

Table comment (live, COMMENT ON TABLE): "Parked carts NEVER reserve stock (v1 decision) — unit_price_snapshot on parked_cart_line is advisory only, re-priced at resume time by PosService."

Why parked carts never reserve stock — deliberate, not an oversight. No inventory.stock_reservation row is ever created for a parked cart, and this migration touches no inventory table at all. This extends the module's own pre-existing DR-D reasoning (no POS-side reservation table full stop, since a POS sale calls InventoryService.completeSale() directly rather than reserve-then-fulfill) one step further: a parked cart is even further from a reservation than a completing sale, since it carries no promise to the customer or the business that the stock will still be available at resume time — it is purely a register-side UI/workflow convenience. inventory.stock_reservation.source_type's CHECK still does not include 'pos', confirmed live at this same build. If stock is gone by the time a parked cart is resumed, the resume path simply fails the same way ringing a fresh sale on an out-of-stock item would — no new failure mode. See module_spec/pos.md §10 DR-O for the full rationale.

pos.parked_cart_line (9 cols) — NEW table, Gap-Fill Batch, 2026-07-20

Line items of a parked cart. unit_price_snapshot is advisory only — a parked cart can sit for hours or days, so the price is re-resolved for real (resolvePrice()/PosService) at resume time, never trusted from this stale snapshot.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy parked_cart_line_tenant_isolation.

updated_at: trigger-maintained via platform.set_updated_at() (unlike sale_line, its post-completion analog — a parked cart's lines are still mutable pre-resume, e.g. a cashier adjusting quantity on a held cart).

Column Type Nullable Default Constraints / Notes
id UUID NOT NULL gen_random_uuid() PK
tenant_id UUID NOT NULL — FK → platform.tenant
parked_cart_id UUID NOT NULL — Composite FK → pos.parked_cart (id, tenant_id). Intra-schema
item_variant_id UUID NOT NULL — Composite FK → inventory.item_variant (id, tenant_id)
qty numeric NOT NULL — CHECK > 0
unit_price_snapshot bigint nullable — Advisory only — re-priced for real at resume time by PosService, never authoritative
note text nullable —
created_at timestamptz NOT NULL now()
updated_at timestamptz NOT NULL now() Trigger-maintained

CHECK constraints: chk_parked_cart_line_qty_positive.

Indexes: PK on id; plain indexes on tenant_id, parked_cart_id.

The offline-sync design — the defining constraint

sale, sale_payment, sale_refund each carry client_uuid (device-generated, the row's TRUE identity from the client's point of view), origin, sync_status, idempotency_key, synced_at, plus UNIQUE (tenant_id, client_uuid) WHERE deleted_at IS NULL. Write path: INSERT ... ON CONFLICT (tenant_id, client_uuid) DO NOTHING.

idempotency_key MUST be deterministically derived from client_uuid (e.g. idempotency_key = client_uuid::text) and passed unchanged to inventory.stock_movement.idempotency_key on every retry — a documented build requirement, not DB-enforced. idempotency_key itself carries no unique constraint in pos — it's a pass-through value forwarded to inventory.stock_movement's own, separately-enforced unique index, not a locally-unique key here.

NULL-distinctness class, explicitly checked (not assumed): client_uuid is NOT NULL on all 3 tables, so the (tenant_id, client_uuid) unique needs no COALESCE/two-partial-index split — unlike pricing.price_rule's nullable-actor idempotency fix, there is no nullable second dimension in this unique key.

TWO DISTINCT offline problems, kept separate:

  1. Duplicate-sale replay (the identical logical sale arrives twice, network retry) — solved entirely by the client_uuid UNIQUE dedup index. Live-tested (regression tests B1/B1b): a retried sync with the same client_uuid is a no-op via ON CONFLICT; a bare retry INSERT with no ON CONFLICT clause is rejected with a 23505 unique violation on sale_tenant_id_client_uuid_unique — proving the DB-level constraint itself, not just the app-level pattern.
  2. Genuine two-different-sale oversell (two DIFFERENT offline devices, two DIFFERENT client_uuids, each independently sell the last unit) — NOT caught by #1's dedup at all (live-tested, regression test B2: two different client_uuids both persist as distinct rows). Requires InventoryService.completeSale() to detect, at sync-processing time, that applying this sale's stock decrement would push inventory.stock.available_qty negative, and instead of allowing negative stock, create a pos_sync_conflict row (conflict_type='stock_oversell'). This is a documented service-layer build requirement — InventoryService does not exist yet, so only the schema's ability to represent the resulting conflict row is built and tested (regression test B3), not the detection logic itself.

chk_<table>_origin_sync_status_consistency on sale/sale_payment/sale_refund: an online-created row must have sync_status='synced'; an offline-created row may be local_only/conflict/synced depending on where it is in its lifecycle. Live-tested (regression test B4).

The reservation / no-oversell path (free, via inventory)

inventory.stock.available_qty (existing generated column, on_hand_qty - reserved_qty) already nets out Orders' reservations. inventory.stock_reservation.source_type CHECK does not include 'pos' (confirmed live) — reservations are Orders' own mechanism. A POS sale calls InventoryService.completeSale() directly; no POS-side reservation table was built. Reinforced by the Gap-Fill Batch, 2026-07-20: pos.parked_cart/parked_cart_line (a held, not-yet-completed cart) also creates no inventory.stock_reservation row — a parked cart is even further from a reservation than a completing sale, since it makes no availability promise at all. See the dedicated pos.parked_cart section above.

The immutable-total model

sale.total_minor_units is immutable-gross forever, as originally completed. Refunds are always a NEW, linked sale_refund row, never a mutation of sale itself. sale.status deliberately has no 'refunded'/'partially_refunded' value. "How much has been refunded" and "net customer-paid position" are always computed by summing linked sale_refund rows at query time (a Reporting concern), never by mutating sale. Live-tested (regression test D1): inserting a sale_refund row leaves sale.total_minor_units unchanged.

Triggers

trg_pos_sale_requires_open_session — the one genuinely new trigger function this module introduces (all other trigger behavior in this schema is the shared platform.set_updated_at() function reused unchanged), calling pos.validate_sale_requires_open_session():

CREATE OR REPLACE FUNCTION pos.validate_sale_requires_open_session()
RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    IF NOT EXISTS (
      SELECT 1 FROM pos.register_session
      WHERE id = NEW.register_session_id AND status = 'open'
    ) THEN
      IF NEW.origin = 'offline' THEN
        NEW.review_status := 'pending';
        NEW.review_reason := COALESCE(NEW.review_reason, 'offline sale synced after register session closed');
      ELSE
        RAISE EXCEPTION 'pos.sale.register_session_id must reference a register_session with status = ''open''';
      END IF;
    END IF;
  ELSIF TG_OP = 'UPDATE' THEN
    IF NEW.register_session_id != OLD.register_session_id THEN
      RAISE EXCEPTION 'pos.sale.register_session_id is immutable after insert';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_pos_sale_requires_open_session
  BEFORE INSERT OR UPDATE OF register_session_id ON pos.sale
  FOR EACH ROW EXECUTE FUNCTION pos.validate_sale_requires_open_session();

Why a trigger, not a CHECK. A same-row CHECK cannot compare against a different row's status. Three distinct guards, added across three passes: the original build (2026-07-07) covered INSERT only — a sale can only be created against a currently-open session. Independent Section 4 verification the same day live-reproduced a real gap: after a sale was inserted against an open session, closing that session and then UPDATE pos.sale SET register_session_id = <a different, now-closed session> succeeded silently, with zero DB-level protection. The fix is not "revalidate the new session is still open" (that would still allow silently reassigning a completed sale to a different open session, equally wrong) — it's that register_session_id is immutable after insert, matching the design's own stated intent ("a sale belongs to whichever session was open when it was created, permanently") which was previously only a documented assumption, not DB-enforced. Patched the same day via packages/db/migrations/20260707010000_pos_sale_session_immutable.sql. Live-tested (regression test E1/E2/E3): insert against a closed session rejected; insert against an open session succeeds; UPDATE ... SET register_session_id to any different session (open or closed) is rejected.

Third pass, 2026-07-19 (gap-validation fix): the INSERT-against-a-closed-session rejection is now flag-not-reject for origin='offline'. A 2026-07-19 gap-validation pass (Flow 1) found this trigger's original all-or-nothing RAISE EXCEPTION was a genuine, live-reproduced hard blocker for the module's own offline-first charter: an offline sale rung while its register session was genuinely open, but synced only AFTER that session closed (a common real-world timing pattern — a batch sync at end of shift, or a spotty-connectivity delay), was rejected outright and PERMANENTLY STRANDED, since register_session_id is immutable after insert (the guard directly above) and there is no other schema-level recovery path. Fixed via packages/db/migrations/20260719000001_pos_offline_session_gate_flag_not_reject.sql, reusing this codebase's own established flag-not-reject precedent for the identical timing class — platform.flag_closed_period_business_date() ("a genuine June-30 sale can still sync in July; rejecting it would silently lose the sale") — down to the exact same NEW.review_reason := COALESCE(NEW.review_reason, ...) idiom so an existing caller-supplied reason is never clobbered. origin='online' against a closed session is unchanged — still a hard reject, since an online sale has no offline-timing excuse; it's a caller bug, not a timing artifact. Live-reproduced (regression tests E4–E9): an offline sale against a closed session is now accepted with review_status='pending' and a fixed, greppable review_reason; an online sale against a closed session still rejects with the exact original error text; the open-session path is unaffected for both origins; a replayed duplicate (tenant_id, client_uuid) offline row still resolves as a silent ON CONFLICT DO NOTHING no-op (this BEFORE INSERT trigger fires regardless of the eventual conflict resolution, but since the offline branch never RAISEs, the trigger always completes normally, so the uniqueness conflict is still free to silently drop the duplicate afterward); register_session_id immutability holds regardless of which path flagged the row.

set_updated_at (via platform.set_updated_at()) fires BEFORE UPDATE on 6 of the 10 tables (verified live: register, register_session, sale, sale_payment, sale_refund, plus tender_type_catalog — added Remediation Phase 4, the module's first global-reference table with a real update lifecycle via is_active). The 4 without it: register_cash_entry, sale_line, sale_refund_line, pos_sync_conflict (append-only or write-once/status-tracked shapes with no general-purpose updated_at).

platform.flag_closed_period_business_date() — added Remediation Phase 4 (Item 14), a shared trigger function (owned by platform, consumed here). FLAG-NOT-REJECT by design: sets review_status='pending' (never raises) when NEW.business_date falls within a status='closed' platform.accounting_period for the same tenant — deliberately non-blocking, since pos's own offline-sync design means a genuine June-30 sale can still land in July after June's period has already closed; rejecting it would silently lose the sale, while flagging it preserves both signals. Consumed by 3 triggers in this module:

CREATE TRIGGER trg_sale_flag_closed_period
  BEFORE INSERT OR UPDATE OF business_date ON pos.sale
  FOR EACH ROW EXECUTE FUNCTION platform.flag_closed_period_business_date();

CREATE TRIGGER trg_sale_refund_flag_closed_period
  BEFORE INSERT OR UPDATE OF business_date ON pos.sale_refund
  FOR EACH ROW EXECUTE FUNCTION platform.flag_closed_period_business_date();

-- register_cash_entry: BEFORE INSERT ONLY -- see the pre-build correction below.
CREATE TRIGGER trg_register_cash_entry_flag_closed_period
  BEFORE INSERT ON pos.register_cash_entry
  FOR EACH ROW EXECUTE FUNCTION platform.flag_closed_period_business_date();

Why register_cash_entry's trigger is BEFORE INSERT only, while sale/sale_refund's are BEFORE INSERT OR UPDATE OF business_date — a pre-build correction, not an inconsistency. register_cash_entry is already fully append-only — Phase 1's own trg_register_cash_entry_append_only trigger rejects every UPDATE unconditionally — so an OR UPDATE OF business_date clause on the new flag trigger would be dead code (it could never fire). sale and sale_refund are not append-only in the same way, so a post-insert business_date correction is plausible there and the UPDATE clause is kept. Live-confirmed via pg_get_triggerdef that trg_register_cash_entry_flag_closed_period is genuinely BEFORE INSERT only, and independently re-verified by live-testing that an UPDATE attempt on a freshly-inserted register_cash_entry row is still rejected outright by the pre-existing append-only trigger — proving the correction was substantively correct, not merely stylistic.

Live-tested: a pos.sale inserted with business_date inside a closed platform.accounting_period is ACCEPTED but flagged review_status='pending'; one outside any closed period is unaffected (review_status='not_required'). Same flag behavior independently confirmed on pos.sale_refund. Migration: packages/db/migrations/20260709000000_phase4_item14_fiscal_periods.sql.

trg_sale_refund_line_append_only — added 2026-07-10 (Header/Line Remediation fix #8), closing a real gap: sale_refund_line was documented write-once/immutable since its original build but had zero DB enforcement. Reuses the shared platform.reject_append_only_mutation() function verbatim — zero new PL/pgSQL — the same function pos.sale_line's own append-only trigger (trg_sale_line_append_only) and pos.register_cash_entry's own (trg_register_cash_entry_append_only) already use, both added earlier by Remediation Phase 1's cross-schema append-only sweep (20260708140000_phase1_append_only_ledgers.sql) — a sweep that, it turns out, missed sale_refund_line itself; that omission is exactly what this fix closes.

REVOKE UPDATE, DELETE ON pos.sale_refund_line FROM authenticated;

CREATE TRIGGER trg_sale_refund_line_append_only
  BEFORE UPDATE OR DELETE ON pos.sale_refund_line
  FOR EACH ROW EXECUTE FUNCTION platform.reject_append_only_mutation();

Belt-and-suspenders, matching Remediation Phase 1's own established pattern: the REVOKE stops the authenticated role; the BEFORE trigger stops every role, including a service_role/superuser bypass connection, since a BEFORE trigger fires regardless of who owns the write. Live-reproduced: pre-fix, a plain UPDATE/DELETE against an existing sale_refund_line row both succeeded with zero error; post-fix, both are rejected. Migration: packages/db/migrations/20260710000000_headerline_pos_fix8.sql. See the dedicated subsection under pos.sale_refund_line above and PROJECT_DECISIONS #46.

trg_parked_cart_guard_status — NEW, Gap-Fill Batch, 2026-07-20 (calling pos.guard_parked_cart_status()). A terminal-state guard on pos.parked_cart.status: tolerates a same-value re-write (idempotent retry from a flaky register), but rejects any transition OUT of a terminal value (resumed, discarded).

CREATE FUNCTION pos.guard_parked_cart_status() RETURNS trigger AS $$
BEGIN
  IF NEW.status IS NOT DISTINCT FROM OLD.status THEN
    RETURN NEW;
  END IF;
  IF OLD.status IN ('resumed', 'discarded') THEN
    RAISE EXCEPTION 'pos.parked_cart.status cannot transition out of terminal state %', OLD.status;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_parked_cart_guard_status
  BEFORE UPDATE OF status ON pos.parked_cart
  FOR EACH ROW EXECUTE FUNCTION pos.guard_parked_cart_status();

Why this mirrors notifications.delivery_attempt's own monotonic-guard shape, rather than a new invention. No existing pos trigger matched this exact shape (a status column with 2 of N values terminal, tolerating a same-value re-write, rejecting any other transition out of a terminal value) — register_session.status and pos_sync_conflict.status are both status columns but neither has this guard. notifications.delivery_attempt was the closest existing codebase match, so its idiom was reused (not its code — this is a new, pos-owned function, since Postgres triggers are schema-owned and the terminal-value sets differ). The same batch independently gave orders.order_payment an identically-shaped guard (trg_order_payment_guard_status, for its own new 'forfeited' terminal status) — confirming this is now a reusable idiom for "a status column with terminal values," not a one-off. Live-reproduced (part of the Gap-Fill Batch's 24 named scenarios): a same-value re-write of status='parked' is a silent no-op; resumed→anything and discarded→anything are both rejected; parked→resumed (with resumed_sale_id set) and parked→discarded both succeed. Migration: packages/db/migrations/20260720000004_pos_parked_carts_and_gift_receipt.sql. See module_spec/pos.md §10 DR-O and PROJECT_DECISIONS #74.

pos — Design Patterns Summary

Column-count reconciliation

Table Cols
register 8
register_session 18
register_cash_entry 15
sale 27
sale_line 15
sale_payment 27
sale_refund 24
sale_refund_line 10
pos_sync_conflict 10
tender_type_catalog 7
parked_cart 13
parked_cart_line 9
Total 183

Verified live via information_schema.columns GROUP BY, schema pos: 12 tables, 183 columns, matching the per-table sum exactly (up from 10 tables / 160 columns via the 2026-07-20 Gap-Fill Batch — see the dedicated subsection below for the +2 tables / +23 columns; up from 9 tables / 143 columns before that, via Remediation Phase 4 below). CHECK-constraint and trigger counts as of Remediation Phase 4: RLS enabled on all 9 pre-existing tenant-scoped tables (tender_type_catalog, the 10th table, is deliberately global reference data with no RLS — see its own section above); 7 triggers total (set_updated_at × 6 — the pre-existing 5 plus tender_type_catalog's own — + trg_pos_sale_requires_open_session × 1, unchanged since its same-day patch to also cover UPDATE OF register_session_id), plus the 3 new platform.flag_closed_period_business_date()-consuming triggers (trg_sale_flag_closed_period, trg_sale_refund_flag_closed_period, trg_register_cash_entry_flag_closed_period) counted separately since the function itself is owned by platform, not pos. +1 trigger, 2026-07-10 (Header/Line Remediation fix #8): trg_sale_refund_line_append_only, reusing platform.reject_append_only_mutation() — see the Triggers section above and the dedicated subsection below. +3 more, 2026-07-20 (Gap-Fill Batch): set_updated_at extended to parked_cart and parked_cart_line (+2), plus the wholly new trg_parked_cart_guard_status (+1) — see the Triggers section above and the dedicated subsection below.

Remediation Phase 2 (2026-07-08)

register_cash_entry.id, sale_line.id, and sale_refund_line.id — the module's 3 append-only ledger tables — had their DEFAULT changed from gen_random_uuid() to platform.uuid_generate_v7() (Remediation Phase 2, Item 6; sale_line is 1 of the plan's 4 named "hot ledgers"). UUIDv7 is time-ordered, keeping future time-range partitioning possible on these append-only ledgers without a PK rewrite — something impossible once data lands on a random UUIDv4 PK. pos.pos_sync_conflict was considered but explicitly EXCLUDED — its status column is an explicit mutable open/resolved/ignored human-in-the-loop workflow field per its own Drizzle source comment, not append-only. DEFAULT-only change: no column or table count change (138 cols unchanged). Full cross-module record: PROJECT_DECISIONS #38.

Remediation Phase 3 (2026-07-08)

Three separate Phase 3 items touched pos, all same-day, all cross-referenced in PROJECT_DECISIONS #39. Net effect: 138 → 143 columns (+5), no tables added or dropped.

Item 9 — refund tax capture (shared with tax; see tax's own PROJECT_DECISIONS #39 entry for the tax.tax_calculation reversal-sign-convention side). sale_refund_line gained tax_amount_cents (bigint, NOT NULL DEFAULT 0, CHECK chk_sale_refund_line_tax_amount_nonnegative >= 0) and tax_rate (numeric, NOT NULL DEFAULT 0), mirroring sale_line's own tax_amount_cents/tax_rate pattern. sale_refund gained tax_refunded_amount_cents (bigint, NOT NULL DEFAULT 0, CHECK chk_sale_refund_tax_refunded_nonnegative >= 0). All 3 are plain positive POS-layer magnitudes — the reversal sign convention (is a refund a negation of the original tax) lives entirely in tax.tax_calculation, not here. Migration: packages/db/migrations/20260708220000_phase3_item9_refund_tax_reversal.sql.

Item 10 — sale_number + the no-receipt-refund path. sale gained sale_number (text, NOT NULL) — this restores an unlogged v1 erosion: v1 had this column (docs/old/schema/schema_modules/schema_pos.md: "Sequential per tenant per site; format configurable"), and v2's initial build dropped it with no logged reason. It is deliberately not a global gapless sequence (that would need a live DB round-trip to allocate the next number, defeating this module's own offline-first design) — instead it is register-session-prefixed and client-generated, convention "<register.label>-<session-local-sequence>" (e.g. "Register 1-0042"). pos.register.label is confirmed live NOT NULL + UNIQUE(tenant_id, label) WHERE deleted_at IS NULL, so the prefix is always available and collision-free. The DB enforces only UNIQUE(tenant_id, sale_number) WHERE deleted_at IS NULL (new index sale_tenant_id_sale_number_unique), mirroring v1's own uniqueness shape exactly — the format itself is an application-layer convention, not DB-enforced beyond uniqueness. sale had 156 live rows at build time, requiring a 3-step migration (ADD COLUMN nullable → UPDATE backfill to 'LEGACY-' || id::text → ALTER COLUMN SET NOT NULL); a single-step ADD COLUMN ... NOT NULL would have failed immediately against those rows.

Also Item 10: sale_refund_line.sale_line_id relaxed from NOT NULL to nullable (0 live rows at build time — zero backfill risk), and sale_refund_line gained item_variant_id (uuid, nullable, FK → inventory.item_variant.id, ON DELETE RESTRICT) as the alternative identifier for a no-receipt refund line — a customer returning merchandise with no receipt and no identifiable historical sale_line. New CHECK chk_sale_refund_line_identification: sale_line_id IS NOT NULL OR item_variant_id IS NOT NULL (at least one identifier always required). This does NOT enable a fully anonymous walk-in return — sale_refund.sale_id itself is unchanged, still NOT NULL; a real sale/transaction context is still required, only the specific line within it can now go unidentified. Whether to also relax sale_refund.sale_id (a true anonymous-walk-in-return capability) is a separate, undecided go/no-go explicitly flagged for the architect's own decision — it was NOT resolved or bundled into this build; logged to OPEN_ITEMS as an open human-decision item. Migration: packages/db/migrations/20260708230000_phase3_item10_sale_number_and_no_receipt_refund.sql.

Item 11 — reward tender fail-closed (the stock_movement/produced-stock half of this item belongs to inventory's own doc, not repeated here). The existing fail-closed tender CHECK on sale_payment was renamed and widened: chk_sale_payment_no_unvalidated_stored_value_tender → chk_sale_payment_no_unbacked_tender_type, now blocking payment_method IN ('gift_card','store_credit','reward') instead of just the first two. reward is a valid payment_method enum value with no backing rewards subsystem at all — worse than gift_card/store_credit, which at least carry an unenforced forward-ref column — and was previously silently accepted with zero validation, the same bug class as the 2026-07-07 gift_card/store_credit fix. Confirmed live: 0 rows with payment_method='reward' existed at build time — a pure narrowing of an already fail-closed CHECK, zero rows affected. Migration: packages/db/migrations/20260708240000_phase3_item11_reward_tender_and_produced_stock.sql.

Remediation Phase 4 (2026-07-08)

The final phase of the approved 4-phase remediation plan (PROJECT_DECISIONS #40), covering items 14–20 (futureproofing) plus closing the one decision Phase 3 deliberately left open (the anonymous walk-in return). pos was touched by 4 of these: the anonymous-return closure, Item 14 (fiscal periods), Item 15 (legal entity), and Item 17a (enum→catalog). Net effect on this module: 9 → 10 tables, 143 → 160 columns (+1 table, +17 columns). All changes additive — zero DROP TABLE/DROP COLUMN.

Closing the Phase 3 open decision — Anonymous Walk-In Return: DECIDED = ALLOW. pos.sale_refund.sale_id relaxed from NOT NULL to nullable (confirmed 0 live rows at build time, zero backfill risk); new CHECK chk_sale_refund_identification: (sale_id IS NOT NULL OR reason IS NOT NULL) — a refund must identify itself SOME way, a linked sale or a documented no-receipt reason, never neither. Audit controls a real anonymous-return flow needs (reason quality, manager/actor attribution, an approval gate for high-value anonymous refunds) are explicitly a SERVICE-LAYER requirement, NOT schema-enforced — logged to OPEN_ITEMS, not built here. Live-reproduced: a refund with both sale_id and reason NULL is rejected; a refund with sale_id NULL and reason set is now ACCEPTED (previously impossible); a refund with a valid sale_id and NULL reason is unaffected. Migration: packages/db/migrations/20260708280000_phase4_close_anonymous_return.sql. See the dedicated subsection under pos.sale_refund above for full detail.

Item 14 — Fiscal periods. pos.sale gained business_date (date, NOT NULL DEFAULT CURRENT_DATE) via a 3-step migration (add nullable → backfill created_at::date for then-189 rows → SET NOT NULL DEFAULT CURRENT_DATE); pos.sale_refund gained it directly (0 rows, zero backfill risk). pos.register_cash_entry ALSO gained business_date plus its first-ever full review seam (review_status/review_reason/reviewed_by_actor_id/reviewed_at/decision_provenance, +5 cols) — a necessary mid-build discovery, not a planned addition: the new platform.flag_closed_period_business_date() trigger's review_status='pending' mechanism was literally impossible without this seam existing first. The shared trigger function (owned by platform, consumed here) is FLAG-NOT-REJECT by design — it sets review_status='pending' and never raises, since pos's offline-first design means a genuine June-30 sale can still land in July after June's accounting period has already closed, and rejecting it would silently lose the sale. Fires BEFORE INSERT OR UPDATE OF business_date on sale/sale_refund, but BEFORE INSERT ONLY on register_cash_entry — a pre-build correction, since Phase 1's own pre-existing append-only trigger there already rejects every UPDATE unconditionally, making an OR UPDATE clause dead code. See the dedicated Triggers subsection above for the full mechanism and live-test results. Migration: packages/db/migrations/20260709000000_phase4_item14_fiscal_periods.sql.

Item 15 — Legal entity. pos.sale gained entity_id (uuid, nullable, FK → platform.legal_entity.id) as part of that item's 10-table rollout across the codebase (the other 9: platform.contract, platform.billing_account, admin.compliance_document, tax.tax_calculation, billing.ar_account, billing.vendor_payable, purchasing.vendor_invoice, purchasing.purchase_order, orders.order_header) — financial/legal-document-bearing HEADER tables that could plausibly differ per legal entity within one tenant. platform.legal_entity itself is 1:N from platform.tenant, letting a tenant incorporate a 2nd LLC without splitting into two tenants; a partial-unique index enforces at most one is_primary=true row per tenant. pos.sale was included as a header table (per-sale legal-entity attribution is plausible for a multi-entity retailer); sale_line, sale_payment, and sale_refund were NOT given their own entity_id — the disclosed scoping rule is header-only, a line/payment/refund's entity is always inherited via its parent sale's FK. Migration: packages/db/migrations/20260709010000_phase4_item15_legal_entity.sql.

Item 17a — Enum→catalog, additive interim (POS tender types). New table pos.tender_type_catalog (7 cols, 7 seeded rows matching the existing payment_method CHECK-enum exactly: card, cash, charge_account, check, gift_card, reward, store_credit) + new column pos.sale_payment.tender_type_id (uuid, nullable, FK → pos.tender_type_catalog.id). This is the additive-interim pattern applied identically across all 4 of Item 17's sub-items (payment terms in shared, tax jurisdiction levels in tax, integration providers in admin) — a new catalog + a new, independently-nullable FK column on the consuming table, while the OLD CHECK-enum column (sale_payment.payment_method, and its companion fail-closed CHECKs chk_sale_payment_no_unbacked_tender_type/chk_sale_payment_stored_value_ref_matches_method) stays completely unchanged. This interim gap — the new tender_type_id FK is not yet constrained to stay in sync with the legacy payment_method enum — is disclosed durably in the Drizzle TypeScript source itself (pos/catalog.ts), not just a migration-file SQL comment that could rot if migrations are ever squashed. tender_type_catalog is deliberately global reference data (no tenant_id, no RLS), matching identity.permission/admin.setting_definition/tax.jurisdiction_level_catalog's established sibling design — the sole such table in this otherwise uniformly tenant-scoped module. Migration: packages/db/migrations/20260709030000_phase4_item17_enum_to_catalog.sql.

Cross-cutting NOTE (not introduced by this phase, independently caught during Phase 4 verification): tender_type_catalog, like every other global-reference table in this codebase old and new, grants full INSERT/SELECT/UPDATE/DELETE to the authenticated role with no RLS restricting who can mutate it — a systemic grant-model gap, logged to OPEN_ITEMS, not fixed in this pass.

Independently verified by 2 separate adversarial lenses (constraint correctness/migration safety, and scope discipline/disclosure integrity) — both CLEAN, zero BLOCKERs. Dedicated regression tests were added to pos-schema.spec.ts (+10 tests, sections H–K): the anonymous-return CHECK (all 3 cases), tender_type_catalog (seed + FK + nullability), the flag-not-reject trigger on sale/sale_refund/register_cash_entry (including the register_cash_entry append-only-blocks-UPDATE proof), and sale.entity_id — 34/34 total. Full cross-module record: PROJECT_DECISIONS #40.

Header/Line Remediation — Fix #8 (2026-07-10)

The first of 3 reopens (POS → Purchasing → Platform) executing the coordinated, 6-module "Header/Line Remediation" design (vrida-header-line-remediation-design-2026-07-10.md, based on vrida-header-line-pattern-audit-2026-07-10.md) — a systemic sweep for missing header/line reconciliation, unenforced immutability claims, and bare (non-composite) cross-tenant FKs across the codebase. Only §6b of that design lands here; Purchasing and Platform are separate, later reopens under the same effort, not detailed in this doc. Net effect on pos: zero tables, zero columns added or removed — 10 tables / 160 columns, unchanged. Only constraints and 1 trigger changed. Full cross-module record: PROJECT_DECISIONS #46.

The bug (documented-but-unenforced immutability), now closed. pos.sale_refund_line was documented write-once/immutable since its original build, but had zero DB enforcement — unlike its sibling sale_line, which Remediation Phase 1's append-only sweep (20260708140000_phase1_append_only_ledgers.sql) actually covered, sale_refund_line was never included in that migration. Live-reproduced pre-fix: a plain UPDATE and DELETE against an existing row both succeeded with zero error. Closed via REVOKE UPDATE, DELETE ON pos.sale_refund_line FROM authenticated + a new trigger, trg_sale_refund_line_append_only, reusing platform.reject_append_only_mutation() verbatim — see the Triggers section above. Live-reproduced post-fix: both are now rejected.

2 bundled additions, taken opportunistically because POS was already reopened, neither part of fix #8 itself:

  1. Prerequisite for fix #6 (orders.order_line.sale_line_id → pos.sale_line, DEFERRED, not built this pass): pos.sale_line gained UNIQUE (id, tenant_id) (sale_line_id_tenant_id_unique) — confirmed missing by independent verification during the design phase, not assumed. Free to add (sale_line's PK on id alone already guarantees uniqueness); unblocks fix #6 whenever it's picked back up.
  2. Opportunistic fix, found during adversarial verification of the design (not one of the original 14 fixes): pos.sale_refund_line.sale_line_id was itself a bare, non-composite FK to pos.sale_line — the exact cross-tenant-exposure bug class this whole remediation effort exists to close. Upgraded to composite: FOREIGN KEY (sale_line_id, tenant_id) REFERENCES pos.sale_line (id, tenant_id) (constraint sale_refund_line_sale_line_id_tenant_fkey, replacing the dropped bare sale_refund_line_sale_line_id_fkey). NULL sale_line_id (the no-receipt-refund path, Remediation Phase 3 Item 10) trivially satisfies a composite FK under Postgres's default MATCH SIMPLE — only a set, cross-tenant sale_line_id is rejected.

Migration: packages/db/migrations/20260710000000_headerline_pos_fix8.sql. Schema files: packages/db/src/schema/pos/sale.ts (saleLine gains the unique(...)), packages/db/src/schema/pos/payment.ts (saleRefundLine's FK converted from a bare .references() to a table-level foreignKey({...})). Verification: live-reproduced and test-confirmed, full apps/api suite green (816/816) — new coverage in apps/api/src/pos/__tests__/pos-schema.spec.ts (sections L/M/N) plus a compatibility fix in apps/api/src/tax/__tests__/tax-schema.spec.ts (its own "K. Refund tax reversal" cleanup now tolerates the append-only rejection instead of assuming an unconditional DELETE would succeed).

Gap-Fill Batch (2026-07-20) — A2 parked carts + A4 gift receipt

2026-07-20, architect-authorized fix pass against 6 of the 2026-07-19 gap-validation report's confirmed gaps (vrida-gap-validation-2026-07-19.md), 2 of which land in pos; the other 4 (A1 category/brand-scoped pricing, B3 direct receiving, B6 buy-X-get-Y offers, B10 forfeited deposits) are documented in their own modules' schema docs, not repeated here. Net effect on pos: 10 → 12 tables, 160 → 183 columns (+2 tables — parked_cart 13 cols, parked_cart_line 9 cols — + 1 column, sale_line.is_gift). Migration: packages/db/migrations/20260720000004_pos_parked_carts_and_gift_receipt.sql. Full cross-module record: PROJECT_DECISIONS #74.

A2 — parked carts. New tables pos.parked_cart/parked_cart_line (full detail in their own sections above). pos.register gained a prerequisite UNIQUE(id, tenant_id) (register_id_tenant_id_unique, migration 20260720000002_pos_reopen_register_unique.sql) for parked_cart.register_id's own composite FK. Parked carts never reserve stock — a deliberate v1-carried-forward decision, documented in a live COMMENT ON TABLE, not an oversight; see the dedicated no-stock-reservation note under pos.parked_cart above. The terminal-state guard trigger (trg_parked_cart_guard_status) deliberately mirrors notifications.delivery_attempt's own monotonic-guard shape rather than inventing a new one — see the Triggers section above.

A4 — gift receipt. pos.sale_line gained is_gift boolean NOT NULL DEFAULT false — a line-level (not sale-level) persisted-intent flag, letting one basket mix gift and non-gift items. Receipt rendering (hiding price for gift-flagged lines) and the gift-return bearer-credit mechanism are explicitly out of scope for this column — they remain the future receipt-rendering/notifications build's and returns' own work, respectively.

Collision found and resolved before build (per PROJECT_DECISIONS #74's own collision-detection-before-build discipline): goods_receipt_line.purchase_order_line_id (a receiving-schema column, part of B3, not pos) required relaxing beyond the task's own originally-scoped column list — architect-ruled, not silently improvised; see receiving.md/PROJECT_DECISIONS #74 for that detail. No pos-specific naming or shape collision was found against live schema state for A2/A4.

Live reproduction and tests. All of A2/A4's scenarios were part of the batch's 24 named live-reproduced scenarios on a throwaway fixture tenant (including the full park→resume and park→discard walks, the terminal-state rejections, and same-value no-op tolerance). 11 new tests were added to apps/api/src/pos/__tests__/pos-schema.spec.ts for this batch; 1 pre-existing assertion in the same file (the exhaustive table-count check) was corrected from 10 to 12 tables, since the schema change itself made the old count stale — disclosed as a required correction, not a stylistic edit. Independently verified at lock gate by a separate agent instance alongside the other 5 touched modules; see PROJECT_DECISIONS #74 for the full verification record.

v1 deviations, 6 tables still deferred (4 of the original 10 built 2026-07-18 — see below; see OPEN_ITEMS.md for full text + concrete triggers)

sale_line_tax (full multi-jurisdiction tax breakdown — flat tax_amount_cents/tax_rate on sale_line covers this pass; a 2026-07-07 audit upgraded this row's severity — the reduction is an unrecoverable capability loss for stacked-jurisdiction remittance/audit, not a simplification, and is now flagged as a PRE-CUSTOMER decision for any multi-jurisdiction tax location, not open-ended), receipt, layaway_payment, sale_template, sale_template_line, guarantee (terms already live on inventory.item_variant; sale_line.has_guarantee kept as a forward-compat flag — note the guarantee.signature_ref → files.file.id seam named in CROSS_MODULE_CONTRACTS.md cannot be honored until guarantee itself is built).

gift_card, gift_card_transaction, store_credit, store_credit_transaction — BUILT 2026-07-18 (Phase 3 stored-value build, PROJECT_DECISIONS #71), relocated to billing (a stored-value instrument is a tenant liability, billing's charter — a deliberate reversal of v1's own pos placement). See billing.md for the full table sections. sale_payment.gift_card_id/.store_credit_id are now real composite FKs into them (see the sale_payment section above).

Column-level erosions found by the 2026-07-07 19-to-9 functionality audit (see OPEN_ITEMS.md for full text + triggers)

Every v1 table that survived as a BUILT v2 table did not necessarily survive with all of its columns — a real gap the table-level OPEN_ITEMS rule didn't check for (now closed, see SCHEMA_DESIGN_RUNBOOK.md Section 6 item 12's column/enum-granularity extension). Found and logged: sale lost cart hold/resume (held_at/hold_expires_at), B2B/contractor fields (po_number/job_reference/delivery_date/pickup_window), and the loyalty-points receipt snapshot (points_earned/points_redeemed); sale_line lost the comp/sample/replacement line_type distinction and manual price-override tracking (price_override/price_override_reason, line-level discount-approval columns); register lost hardware-pairing config (stripe_reader_id/printer_config/drawer_config); register_cash_entry.entry_type narrowed from 6 values to 3 ('open'/'close' are likely redundant with register_session's own opening/closing columns; 'drop'/'payout' — two distinct business reasons in v1 — are merged into one 'paid_out' value; 'no_sale' has no v2 equivalent at all).

Cart hold/resume — RESOLVED 2026-07-20 (Gap-Fill Batch A2), via a different shape than this erosion originally described. Rather than restoring sale.held_at/hold_expires_at (columns on an already-created sale row), the capability landed as a wholly separate pre-sale table pair, pos.parked_cart/parked_cart_line — a held cart becomes a real sale only at resume, rather than an existing sale row being paused. See the dedicated pos.parked_cart section and the Gap-Fill Batch subsection above; PROJECT_DECISIONS #74. The B2B/contractor-field and loyalty-points-snapshot erosions on this same row remain open/deferred, unaffected by this closure.

Service layer

No POSService exists yet — schema-only this pass, same pattern as every other module's deferred service layer.

JSONB columns

register_session.decision_provenance, sale.decision_provenance, sale_payment.decision_provenance, sale_refund.decision_provenance, register_cash_entry.decision_provenance (added Remediation Phase 4 — all following the codebase-wide decision_provenance convention — reason/evidence/confidence/memory_refs), pos_sync_conflict.detail (documented example shape above).

Open items carried forward (see OPEN_ITEMS.md for full text)

  1. 10 deferred v1 tables (see above), each with its own concrete trigger.
  2. Rewards/Offers seams stay unhonored — both flagged stale in CROSS_MODULE_CONTRACTS.md, neither module exists in v2.
  3. sale_payment.status's Stripe-vocabulary-derived enum may need revisiting once Payments' actual Terminal integration is built against a real webhook payload.
  4. sale.search_vector deviation — dropped during build (no free-text field to index meaningfully), add if/when a customer-name-join or receipt-number search becomes a real requirement.
  5. InventoryService.completeSale()'s two-device-oversell detection — a documented service-layer build requirement, not yet implemented (no InventoryService exists yet).
  6. The refund-sum-never-exceeds-payments check — a documented service-layer build requirement, not yet implemented.
  7. idempotency_key-must-derive-from-client_uuid — a documented build requirement for whoever writes the sync-processing code.
  8. No POSService yet — schema-only this pass, same pattern as every other module's deferred service layer.
  9. RESOLVED 2026-07-18 for gift_card/store_credit (Phase 3 stored-value build, PROJECT_DECISIONS #71) — real composite FKs now exist from gift_card_id/store_credit_id to billing.gift_card/billing.store_credit_account, and the gate narrowed accordingly. The reward half of this row is now its own dedicated OPEN_ITEMS row (see OPEN_ITEMS.md) — rewards does not back a reward TENDER (no linkage column, no points→money bridge); unlock trigger: a sale_payment → loyalty_point_ledger reference + a point-valuation decision.
  10. sale_line_tax's tax-decomposition loss — UPGRADED 2026-07-07 to a PRE-CUSTOMER decision (not open-ended): must be resolved before the first customer transacts in a multi-jurisdiction/stacked-tax location, or when a real Tax module is built.
  11. sale column erosions — cart hold/resume (RESOLVED 2026-07-20 via pos.parked_cart/parked_cart_line, Gap-Fill Batch — a different shape than the original erosion, see above), B2B/contractor fields, loyalty-points snapshot (still open) — each its own OPEN_ITEMS row, found by the 2026-07-07 19-to-9 audit.
  12. sale_line column erosions — comp/sample/replacement line-type distinction, price-override tracking — each its own OPEN_ITEMS row.
  13. register column erosion — hardware-pairing config (stripe_reader_id/printer_config/drawer_config).
  14. register_cash_entry.entry_type narrowing — 6 v1 values collapsed to 3; no_sale has no v2 equivalent at all.
  15. Anonymous walk-in returns — RESOLVED = ALLOW, Remediation Phase 4 (was an open, UNDECIDED human go/no-go in Remediation Phase 3, Item 10). sale_refund.sale_id is now nullable; chk_sale_refund_identification requires a linked sale OR a documented reason, never neither. See the dedicated subsection under pos.sale_refund above and PROJECT_DECISIONS #40. Closed; no further schema action pending.
  16. tax_amount_cents/tax_rate/tax_refunded_amount_cents reversal-sign convention (Remediation Phase 3, Item 9) — these pos-side columns are plain positive magnitudes; the actual refund-vs-original sign convention lives in tax.tax_calculation, not here. No pos-side action pending, cross-referenced for completeness.
  17. Anonymous-return service-layer audit controls (Remediation Phase 4) — a reason REQUIRED (not just present) at the UI layer, manager/actor attribution, and an approval gate for high-value anonymous refunds are all documented service-layer requirements, not schema-enforced. Trigger: when the refund-issuance service path is built (same trigger as DR-2/open item 6 above).
  18. Anonymous-return site/register-context gap (Remediation Phase 4) — an anonymous refund (sale_id IS NULL) carries no way to know which site it happened at, since the only current path to a site is sale_id → sale.site_id. Disclosed, not schema-expanded beyond what was decided. Trigger: if per-refund site attribution becomes a real requirement for anonymous returns.
  19. chk_sale_payment_no_unbacked_tender_type/chk_sale_payment_stored_value_ref_matches_method vs. the new tender_type_id/tender_type_catalog (Remediation Phase 4, Item 17a) — the new FK column and catalog are an additive-interim scaffold, deliberately NOT yet kept in sync with the legacy payment_method CHECK-enum or its fail-closed companion CHECKs. Trigger: when the tender-type enum→catalog cutover is actually executed (not just scaffolded) — the replacement must resolve both mechanisms into one, not leave two parallel, un-synced representations indefinitely.
  20. tender_type_catalog grant-model gap (Remediation Phase 4, cross-cutting, not introduced by this phase) — like every other global-reference catalog table in this codebase, it grants full INSERT/SELECT/UPDATE/DELETE to the authenticated role with no RLS restricting who can mutate it. Trigger: when a distinct "platform-admin-only, tenant-read-only" role/grant model is designed for catalog tables generally (not a pos-specific fix).
  21. platform.accounting_period's "1 open/closed status per date range" is tenant-scoped but has no cross-module enforcement that every reviewable table actually respects the flag — register_cash_entry's flag trigger's review_status='pending' state has no consuming resolution workflow yet (mirrors open item 6/17 above: the flag exists, the review/resolution UI and service logic do not). Trigger: when POSService/an admin review UI is built.
  22. parked_cart.expires_at sweep/auto-discard (Gap-Fill Batch, 2026-07-20) — expires_at is a nullable advisory column with a supporting partial index already in place, but no automatic expiry/discard mechanism exists yet. Trigger: when POSService's parked-cart resume/discard path is built.
  23. sale_line.is_gift rendering + gift-return bearer-credit mechanism (Gap-Fill Batch, 2026-07-20) — the flag itself is built and persists intent only. Receipt rendering (hiding price for gift-flagged lines) is the future receipt-rendering/notifications build's own work; the gift-return bearer-credit mechanism (crediting whoever presents the gift receipt, not necessarily the original purchaser) is returns' own future work. See PROJECT_DECISIONS #74.
Last modified: Jul 14, 2026, 1:33 PM PT
← Previousplatform
On this page
Esc
↑↓ navigate↵ openEsc close