pos — Phase 8

Schema locked 2026-06-10. 19 tables, 280 cols (+1 col added 2026-06-10: tip_amount_cents on pos.sale; +1 col added 2026-06-11: search_vector on pos.sale — Search FTS touch). Group A (8 tables: sale, sale_line, sale_line_tax, sale_payment, sale_refund, sale_refund_line, receipt, pos_sync_conflict) — 152 cols (sale_line_tax is append-only: 8 cols). Group B (11 tables: register, register_session, register_cash_entry, layaway_payment, gift_card, gift_card_transaction, store_credit, store_credit_transaction, sale_template, sale_template_line, guarantee) — 128 cols.

(Seam closure re-note 2026-06-12 — pos.sale.points_earned/points_redeemed seam closed at rewards lock. rewards.points_ledger.sale_id → pos.sale FK is DONE. POS stays locked — no column or constraint change.)

POS is the only true offline-first module. Idempotency and sync state are pervasive across sale and sale_payment. Design rules:

• client_uuid: client-generated UUID v4, set offline, stable across retries — the offline identity of the record.
• idempotency_key: server-side dedup key; carried through to inventory.stock_movement.idempotency_key so the inventory decrement seam aligns.
• sync_status: 'synced' / 'local_only' / 'conflict' on every offline-capable table.
• Online sales decrement stock immediately via InventoryService at completion. Offline sales decrement on sync. Collision (two tills sold the last unit offline) creates a pos_sync_conflict row for manager resolution.
• Cashier ID on every sale; approver FK on every manager-approved action. No shift/labor tables here — Admin/HR owns those.

Cross-Phase FK note — pos

FK column	Target	Status
sale.tenant_id	platform.tenant	Phase 1 locked — enforced
sale.site_id, sale_refund.site_id	multi_loc.site	Phase 7 locked — enforced
sale.cashier_id, sale.voided_by, sale.discount_approved_by, sale_refund.cashier_id, sale_refund.manager_approved_by, sale_line.discount_approved_by, pos_sync_conflict.resolved_by	identity.identity_user	Phase 7 locked — enforced
sale.customer_id	crm.customer	Phase 7 locked — enforced; NULLABLE (anonymous sales)
sale_line.variant_id, sale_refund_line.variant_id, pos_sync_conflict.variant_id	inventory.item_variant	Phase 7 locked — enforced
sale_payment.sale_id, sale_line.sale_id, etc.	intra-pos	same schema — enforced when Group B exists
sale.tax_exemption_cert_id	crm.customer_tax_certificate	crm.customer_tax_certificate now exists (added 2026-06-10). sale.tax_exemption_cert_id remains text by design: POS is offline-first and references the cert by value (the certificate number entered or scanned at the till), which survives offline where a synchronous UUID FK lookup would not. The text value should match a customer_tax_certificate.certificate_number; reconciliation is service-layer, not an enforced FK.
sale_payment.charge_account_ref	billing A/R	FORWARD-REF — Billing not yet built. POS stores the charge-account reference as text; Billing owns the A/R entry + credit validation. When Billing locks, add FK.
sale_payment.gift_card_id	pos.gift_card	Intra-pos forward-ref — Group B table. Column is plain UUID; FK added when Group B exists.
sale_payment.store_credit_id	pos.store_credit	Intra-pos forward-ref — Group B table. Same pattern.
sale_line.guarantee_id, sale_refund.guarantee_id, sale_refund_line	pos.guarantee	Intra-pos forward-ref — Group B table. Same pattern.
sale.points_earned, sale.points_redeemed	Consumer Layer rewards	SEAM CLOSED 2026-06-12 — rewards locked; ledger in rewards.points_ledger (points_ledger.sale_id → pos.sale FK DONE). POS records receipt snapshots; rewards owns the ledger + balance.
pos_sync_conflict.sale_payment_id	pos.sale_payment	intra-pos — enforced

---

pos.sale — transaction header

The root record for every POS transaction. Created when the cashier opens a new sale; committed on completeSale. Carries all cart-level metadata, tax exemption, discount approvals, offline sync state, and reward seam columns.

Tenant-scoped. Transactional — carries site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
sale_number	text	NOT NULL	—	Human-facing receipt/sale number. Sequential per tenant per site; format configurable.
client_uuid	UUID	NOT NULL	—	Client-generated UUID v4. Set on device at sale creation; stable across offline retries. Used for offline idempotency.
register_id	UUID	nullable	—	FK → pos.register (Group B). NULL for non-register sales. Intra-pos forward-ref.
register_session_id	UUID	nullable	—	FK → pos.register_session (Group B). Intra-pos forward-ref.
site_id	UUID	NOT NULL	—	FK → multi_loc.site
cashier_id	UUID	NOT NULL	—	FK → identity.identity_user — cashier who opened the sale
customer_id	UUID	nullable	—	FK → crm.customer — NULL = anonymous walk-in sale
sale_type	text	NOT NULL	'sale'	CHECK IN ('sale','layaway')
status	text	NOT NULL	'active'	CHECK IN ('active','held','completed','voided','refunded','partially_refunded')
subtotal_cents	bigint	NOT NULL	—	Sum of sale_line.line_total_cents before tax
discount_total_cents	bigint	NOT NULL	—	Sum of all line and cart-level discounts
tax_total_cents	bigint	NOT NULL	—	Sum of sale_line_tax.tax_amount_cents across all lines
total_cents	bigint	NOT NULL	—	subtotal_cents − discount_total_cents + tax_total_cents. Does NOT include tip.
tip_amount_cents	bigint	NOT NULL	0	Terminal tip added at the card reader. NOT included in total_cents; full charged amount = total_cents + tip_amount_cents. On split-tender sales, tip associates with the card tender (sale_payment of type card_present). Mirrors payments.payment_intent.tip_amount_cents for the card portion.
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
tax_exempt	boolean	NOT NULL	false	Tax exemption flag for this sale
tax_exemption_cert_id	text	nullable	—	Cert identifier (text). crm.customer_tax_certificate now exists (2026-06-10) but this stays text by design: POS is offline-first and references the cert by value (survives offline; a UUID FK lookup would not). The text value should match a customer_tax_certificate.certificate_number; reconciliation is service-layer, not an enforced FK.
discount_approved_by	UUID	nullable	—	FK → identity.identity_user — manager who approved a cart-level discount
po_number	text	nullable	—	Customer's PO number (B2B/contractor)
job_reference	text	nullable	—	Contractor job/project reference
delivery_date	date	nullable	—	For delivery sales
pickup_window	text	nullable	—	Customer-chosen pickup window (free text or structured time slot)
note	text	nullable	—	Cart-level free-text note
signature_ref	text	nullable	—	R2 key for PNG signature (charge accounts, large purchases, age-restricted items, guarantees — Path B in-app canvas)
points_earned	integer	nullable	—	Receipt snapshot — points earned on this sale. Ledger in rewards.points_ledger (seam closed 2026-06-12).
points_redeemed	integer	nullable	—	Receipt snapshot — points redeemed at this sale. Ledger in rewards.points_ledger (seam closed 2026-06-12).
held_at	timestamptz	nullable	—	When the sale was placed on hold
hold_expires_at	timestamptz	nullable	—	Auto-expire time for held sale (default: held_at + 24h, configurable)
voided_by	UUID	nullable	—	FK → identity.identity_user — manager who voided this sale
void_reason	text	nullable	—	Required when status = 'voided'; service-enforced (CHECK would require subquery)
completed_at	timestamptz	nullable	—	Timestamp of completeSale
origin	text	NOT NULL	'online'	CHECK IN ('online','offline') — set at creation; immutable
sync_status	text	NOT NULL	'synced'	CHECK IN ('synced','local_only','conflict')
idempotency_key	text	nullable	—	Server-side dedup key for offline-synced sales. Carried to inventory.stock_movement.idempotency_key.
synced_at	timestamptz	nullable	—	When this sale was synced from device to server
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
search_vector	tsvector	NOT NULL	—	GENERATED ALWAYS AS (to_tsvector('english', coalesce(sale_number,'') || ' ' || coalesce(po_number,'') || ' ' || coalesce(job_reference,''))) STORED. (FTS touch 2026-06-11 — Search module.) Note: no customer name snapshot on pos.sale — customer name search routes through CRM. Maintained by Postgres; never written directly.

41 columns. (+1 tip_amount_cents added 2026-06-10 for Payments Terminal tip support. FTS touch 2026-06-11: +1 search_vector. Was 40 cols.)

Indexes:

• PK on id
• on (tenant_id)
• on (site_id)
• on (register_session_id)
• on (customer_id)
• on (cashier_id)
• on (status)
• on (sync_status) WHERE sync_status != 'synced' — offline monitor query
• on (tenant_id, site_id, completed_at) — end-of-day and date-range sales report queries
• UNIQUE on (tenant_id, sale_number) WHERE deleted_at IS NULL
• UNIQUE on (tenant_id, client_uuid) WHERE deleted_at IS NULL — offline idempotency dedup (tenant-scoped for correctness and index selectivity)
• GIN on (search_vector) — full-text search (tsvector)
• GIN on (sale_number gin_trgm_ops) — fuzzy / partial / prefix sale number search (pg_trgm)

---

pos.sale_line — line items

One row per item in a sale. Carries resolved price, applied discounts, line totals, and the intra-pos forward-ref to guarantee for guarantee-creating lines.

Tenant-scoped. Transactional (inherits site_id context from sale).

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
sale_id	UUID	NOT NULL	—	FK → pos.sale
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
line_type	text	NOT NULL	'sale'	CHECK IN ('sale','comp','sample','replacement') — 'comp'/'sample'/'replacement' tracked separately for accounting
comp_reason	text	nullable	—	Required when line_type != 'sale'; service-enforced
quantity	numeric	NOT NULL	—	Supports fractional quantities (weight/volume-priced items)
unit_price_cents	bigint	NOT NULL	—	Resolved price from PricingService at checkout time
price_override	boolean	NOT NULL	false	True when cashier manually overrode the price
price_override_reason	text	nullable	—	Required when price_override = true; service-enforced
discount_amount_cents	bigint	NOT NULL	0	Line-level absolute discount amount
discount_percent	numeric	nullable	—	Line-level percentage discount (informational; discount_amount_cents is the applied value)
discount_approved_by	UUID	nullable	—	FK → identity.identity_user — manager who approved this line-level discount
line_subtotal_cents	bigint	NOT NULL	—	unit_price_cents × quantity (before discount)
line_total_cents	bigint	NOT NULL	—	line_subtotal_cents − discount_amount_cents (after discount, before tax; tax is on sale_line_tax)
weight_volume	numeric	nullable	—	Measured weight or volume for weight/volume-priced items (e.g. lbs of soil)
substitution_note	text	nullable	—	Note when cashier substituted this variant for another
guarantee_id	UUID	nullable	—	FK → pos.guarantee (Group B). Set when this line created a guarantee at sale. Intra-pos forward-ref; FK added when Group B exists.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

21 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (sale_id)
• on (variant_id)
• on (line_type)

---

pos.sale_line_tax — per-line multi-jurisdiction tax

One row per tax jurisdiction per sale line. Supports state + county + city stacking (feature 1.18). Append-only at sale completion — no updated_at / deleted_at.

Tenant-scoped. Append-only — no updated_at, no deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
sale_line_id	UUID	NOT NULL	—	FK → pos.sale_line
jurisdiction_type	text	NOT NULL	—	CHECK IN ('state','county','city','district','special')
jurisdiction_name	text	NOT NULL	—	Display name, e.g. 'California', 'Los Angeles County'
tax_rate	numeric	NOT NULL	—	Rate as decimal, e.g. 0.0875 for 8.75%
tax_amount_cents	bigint	NOT NULL	—	ROUND(line_total_cents × tax_rate)
created_at	timestamptz	NOT NULL	now()

8 columns. (Append-only — no updated_at / deleted_at.)

Indexes:

• PK on id
• on (tenant_id)
• on (sale_line_id)
• on (jurisdiction_type)

---

pos.sale_payment — payment tender rows

One row per tender applied to a sale. Split tender = multiple rows. Carries offline sync state (client_uuid, idempotency_key, sync_status) because payments are the highest-risk offline write.

Tenant-scoped. Transactional — carries offline sync state.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
sale_id	UUID	NOT NULL	—	FK → pos.sale
client_uuid	UUID	NOT NULL	—	Client-generated UUID v4 — offline-stable identity for this payment row
payment_method	text	NOT NULL	—	CHECK IN ('card','cash','check','charge_account','gift_card','store_credit','reward')
amount_cents	bigint	NOT NULL	—	Amount applied to this tender
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
change_given_cents	bigint	NOT NULL	0	Cash change returned to customer
stripe_payment_intent_id	text	nullable	—	Stripe PaymentIntent ID (card payments). PaymentsService owns; POS stores reference.
stripe_offline_intent_id	text	nullable	—	Stripe offline payment intent ID for queued offline card payments
check_number	text	nullable	—	Check number for 'check' payments
gift_card_id	UUID	nullable	—	FK → pos.gift_card (Group B). Intra-pos forward-ref; FK added when Group B exists.
store_credit_id	UUID	nullable	—	FK → pos.store_credit (Group B). Intra-pos forward-ref; FK added when Group B exists.
charge_account_ref	text	nullable	—	Seam: charge-account A/R reference. Billing module (not yet built) owns A/R entry + credit validation. Stored as text; FK added when Billing locks.
status	text	NOT NULL	'captured'	CHECK IN ('pending','authorized','captured','failed','refunded','queued_offline')
sync_status	text	NOT NULL	'synced'	CHECK IN ('synced','local_only','conflict')
idempotency_key	text	nullable	—	Server-side dedup key for offline payment sync
queued_at	timestamptz	nullable	—	When offline card payment was queued on device
synced_at	timestamptz	nullable	—	When this payment was synced to server
sync_error	text	nullable	—	Last sync error message from PaymentsService retry
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

23 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (sale_id)
• on (payment_method)
• on (status)
• on (sync_status) WHERE sync_status != 'synced' — offline queue sweep
• UNIQUE on (tenant_id, client_uuid) WHERE deleted_at IS NULL — offline idempotency dedup (tenant-scoped for correctness and index selectivity)

---

pos.sale_refund — refund header

Root record for a refund or exchange. Links to the original sale. Carries sync state (refunds can be initiated offline). Guarantee-claim refunds carry guarantee_id.

Tenant-scoped. Transactional — carries site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
refund_number	text	NOT NULL	—	Human-facing refund reference number
original_sale_id	UUID	NOT NULL	—	FK → pos.sale — the sale being refunded
exchange_sale_id	UUID	nullable	—	FK → pos.sale — new sale created for an exchange; NULL for non-exchange refunds
site_id	UUID	NOT NULL	—	FK → multi_loc.site — site where refund was processed (may differ from original sale site)
cashier_id	UUID	NOT NULL	—	FK → identity.identity_user — cashier processing the refund
refund_type	text	NOT NULL	—	CHECK IN ('full','partial','exchange','guarantee_claim')
refund_method	text	NOT NULL	—	CHECK IN ('original_tender','cash','store_credit','gift_card')
reason_code	text	nullable	—	Refund reason (required over configurable $ threshold; service-enforced)
without_receipt	boolean	NOT NULL	false	True for no-receipt returns; manager approval required
manager_approved_by	UUID	nullable	—	FK → identity.identity_user — required for large refunds, no-receipt returns
total_refunded_cents	bigint	NOT NULL	—	Sum of sale_refund_line.refund_amount_cents
guarantee_id	UUID	nullable	—	FK → pos.guarantee (Group B). Set for guarantee-claim refunds. Intra-pos forward-ref; FK added when Group B exists.
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	—	Server-side dedup key for offline-synced refunds
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

20 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (original_sale_id)
• on (site_id)
• on (cashier_id)
• on (refund_type)
• UNIQUE on (tenant_id, refund_number) WHERE deleted_at IS NULL

---

pos.sale_refund_line — refund line items

One row per item being refunded. Carries restock decision and restocking fee per item.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
sale_refund_id	UUID	NOT NULL	—	FK → pos.sale_refund
original_sale_line_id	UUID	nullable	—	FK → pos.sale_line — NULL for without-receipt refunds where no original line can be identified
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
quantity	numeric	NOT NULL	—	Quantity being refunded
refund_amount_cents	bigint	NOT NULL	—	Refund value for this line (may be less than original line total if restocking fee applied)
restock_decision	text	NOT NULL	'restock'	CHECK IN ('restock','write_off','discount_restock') — drives InventoryService call on refund processing
restocking_fee_cents	bigint	NOT NULL	0	Fee retained from refund; configurable per category
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

12 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (sale_refund_id)
• on (original_sale_line_id)
• on (variant_id)

---

pos.receipt — receipt delivery tracking

One row per receipt delivery attempt per sale. Tracks delivery method, status, reprints, and gift-receipt flag. Supports receipt archive / 7-year retention (feature 15.2).

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
sale_id	UUID	NOT NULL	—	FK → pos.sale
receipt_type	text	NOT NULL	'sale'	CHECK IN ('sale','refund','gift','reprint')
delivery_method	text	nullable	—	CHECK (delivery_method IS NULL OR delivery_method IN ('print','email','sms','none'))
delivered_to	text	nullable	—	Email address or phone number receipts was sent to
delivery_status	text	nullable	—	CHECK (delivery_status IS NULL OR delivery_status IN ('pending','sent','failed'))
is_gift_receipt	boolean	NOT NULL	false	Gift receipts hide prices; show return-eligible items only
reprint_count	integer	NOT NULL	0	Incremented on each reprint; supports audit trail
delivered_at	timestamptz	nullable	—	When delivery succeeded
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

13 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (sale_id)
• on (receipt_type)

---

pos.pos_sync_conflict — offline collision queue

Created when an offline sync produces a collision that cannot be auto-resolved — e.g., two tills sold the last unit of the same variant offline. Manager reviews and resolves.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	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 — sale involved in conflict
sale_payment_id	UUID	nullable	—	FK → pos.sale_payment — payment involved in conflict
variant_id	UUID	nullable	—	FK → inventory.item_variant — variant involved (e.g. stock_oversell)
detail	JSONB	nullable	—	Conflict-specific detail. Shape varies by conflict_type: e.g. {"qty_sold_offline": 2, "qty_available_at_sync": 0, "other_sale_id": "..."} for stock_oversell.
status	text	NOT NULL	'open'	CHECK IN ('open','resolved','ignored')
resolution_note	text	nullable	—	Manager's resolution note
resolved_by	UUID	nullable	—	FK → identity.identity_user — manager who resolved
resolved_at	timestamptz	nullable	—	When resolved
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

14 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (status) WHERE status = 'open' — manager dashboard query
• on (sale_id)

---

Group A column summary

Table	Columns
pos.sale	40
pos.sale_line	21
pos.sale_line_tax	8
pos.sale_payment	23
pos.sale_refund	20
pos.sale_refund_line	12
pos.receipt	13
pos.pos_sync_conflict	14
Group A total	151

Note: sale_line_tax is append-only (8 cols — no updated_at/deleted_at). All other Group A tables carry the full created_at / updated_at / deleted_at standard triple.

Intra-pos forward-refs (Group A → Group B)

The following FKs are defined within the pos schema but target Group B tables not yet created. All are plain UUID columns now; FK constraints added when Group B tables exist in the same migration:

Column	Target (Group B)
sale.register_id	pos.register
sale.register_session_id	pos.register_session
sale_line.guarantee_id	pos.guarantee
sale_payment.gift_card_id	pos.gift_card
sale_payment.store_credit_id	pos.store_credit
sale_refund.guarantee_id	pos.guarantee

Design notes

Offline idempotency model: client_uuid (device-set UUID v4) is the primary dedup key for sale and sale_payment. When an offline device syncs, the server performs a INSERT ... ON CONFLICT (client_uuid) DO NOTHING pattern (or equivalent upsert) before processing. idempotency_key is the server-side dedup key threaded through to inventory.stock_movement.idempotency_key — if the same sale arrives twice (network retry), the inventory decrement does not double-fire.

Void CHECK: status = 'voided' requires voided_by IS NOT NULL AND void_reason IS NOT NULL. Enforced at service layer rather than a DB CHECK (a CHECK would need a multi-column constraint that references nullable UUIDs — valid but verbose; service enforcement is sufficient and avoids a migration to relax if business rules change).

comp/sample/replacement lines: line_type != 'sale' rows carry zero or nominal unit_price_cents and are flagged separately so accounting can report comps, samples, and replacements distinctly from revenue-generating sales (feature 10.17).

sale_line_tax append-only: Tax jurisdiction rows are immutable after sale completion. No updated_at / deleted_at — tax records are legal records; corrections are new rows, not updates.

receipt.reprint_count: Incremented on each reprint. Supports the "bulk reprint at end of day" feature (5.13) and the receipt archive requirement (15.2, 7-year retention).

Migration ordering (Group A): pos.sale → pos.sale_line → pos.sale_line_tax → pos.sale_payment → pos.sale_refund → pos.sale_refund_line → pos.receipt → pos.pos_sync_conflict. Group B tables migrate after Group A in the same phase; intra-pos FK constraints added in Group B migration step.

Deferred items (explicit triggers):

• L-2 — pos.sale_line: add (tenant_id, line_type, created_at) composite index when accounting-module period queries for comp/sample lines are defined.
• M-2 — pos.register_session: add service-layer close invariants documentation (closed_by / closing_cash_cents / expected_cash_cents / variance_cents / closed_at all non-null when status = 'closed') when POSService.closeRegister() is implemented.
• F-2 — pos.sale_payment.status: add 'cancelled' to CHECK when Payments module maps Stripe PaymentIntent cancellation events.

---

Group B — register / cash / stored value / layaway / templates / guarantee

Group B populated 2026-06-10. 11 tables, 128 cols. All intra-pos forward-ref FKs from Group A are now resolvable — see updated Cross-Phase FK note below.

---

pos.register — physical till

One row per physical POS register per site. Carries hardware config (Stripe Terminal reader, receipt printer, cash drawer). Register count is tier-capped (Starter: 1, Pro: 10, Enterprise: unlimited).

Tenant-scoped. Master data — no site_id duplication beyond the FK.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
site_id	UUID	NOT NULL	—	FK → multi_loc.site
name	text	NOT NULL	—	Human-facing register name, e.g. "Register 1", "Garden Center till"
stripe_reader_id	text	nullable	—	Stripe Terminal reader ID (BBPOS WisePOS E or compatible). Set at pairing; owned by PaymentsService.
printer_config	JSONB	nullable	—	Bluetooth printer pairing config. Shape: {"device_id": "...", "model": "Star SM-L200", "connection": "bluetooth"}
drawer_config	JSONB	nullable	—	Cash drawer connection config. Shape: {"connection_type": "pulse_via_printer" | "bluetooth", "device_id": "..."}
status	text	NOT NULL	'active'	CHECK IN ('active','inactive')
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

11 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (site_id)
• UNIQUE on (tenant_id, site_id, name) WHERE deleted_at IS NULL

---

pos.register_session — open/close cycle

One row per register open/close shift. Tracks opening and closing cash counts, expected cash (derived from cash movements), and variance. Every cash entry in register_cash_entry links here.

Tenant-scoped. Transactional — carries site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
register_id	UUID	NOT NULL	—	FK → pos.register
site_id	UUID	NOT NULL	—	FK → multi_loc.site — denormalized for fast site-level queries
opened_by	UUID	NOT NULL	—	FK → identity.identity_user — cashier who opened the register
closed_by	UUID	nullable	—	FK → identity.identity_user — cashier who closed; NULL while session is open
opening_cash_cents	bigint	NOT NULL	—	Cash declared at open
closing_cash_cents	bigint	nullable	—	Cash counted at close; NULL while session is open
expected_cash_cents	bigint	nullable	—	Opening cash + cash sales − drops − payouts; computed at close by POSService
variance_cents	bigint	nullable	—	closing_cash_cents − expected_cash_cents; positive = overage, negative = shortage
status	text	NOT NULL	'open'	CHECK IN ('open','closed')
opened_at	timestamptz	NOT NULL	—	Wall-clock open time
closed_at	timestamptz	nullable	—	Wall-clock close time; NULL while open
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

16 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (register_id)
• on (site_id)
• on (status) WHERE status = 'open' — active session lookup
• on (opened_by)

---

pos.register_cash_entry — drawer audit trail

Append-only log of every cash event on a register session: open count, mid-day count, close count, cash drop, payout, no-sale open. Manager approval captured for drops and payouts. No updated_at / deleted_at — drawer events are audit records.

Tenant-scoped. Append-only — no updated_at, no deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	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 ('open','mid_count','close','drop','payout','no_sale')
amount_cents	bigint	NOT NULL	0	Amount counted or moved. For 'drop' and 'payout': amount removed from drawer. For 'open'/'mid_count'/'close': amount counted in drawer.
performed_by	UUID	NOT NULL	—	FK → identity.identity_user — cashier performing the action
approved_by	UUID	nullable	—	FK → identity.identity_user — manager PIN required for 'drop', 'payout', 'no_sale'
reason	text	nullable	—	Required for 'payout'; reason for removing cash for a non-sale expense
note	text	nullable	—	Free-text note (optional for all types)
created_at	timestamptz	NOT NULL	now()

10 columns. (Append-only — no updated_at / deleted_at.)

Indexes:

• PK on id
• on (tenant_id)
• on (register_session_id)
• on (entry_type)

---

pos.layaway_payment — layaway installment schedule

The payment schedule (plan) for a layaway sale. One row per installment. Actual money received is always a sale_payment row; sale_payment_id links the schedule entry to the tender when paid. The layaway sale header, deposit, and pickup status live on pos.sale (sale_type = 'layaway').

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
sale_id	UUID	NOT NULL	—	FK → pos.sale — must be a sale with sale_type = 'layaway'; service-enforced
sequence	integer	NOT NULL	—	Installment order (1 = deposit, 2 = second payment, etc.)
due_date	date	nullable	—	Scheduled due date for this installment; NULL for unscheduled deposits
amount_due_cents	bigint	NOT NULL	—	Amount required for this installment
amount_paid_cents	bigint	NOT NULL	0	Amount actually paid; set when sale_payment_id is linked
sale_payment_id	UUID	nullable	—	FK → pos.sale_payment — the actual tender row; NULL until payment received
status	text	NOT NULL	'scheduled'	CHECK IN ('scheduled','paid','overdue','cancelled')
paid_at	timestamptz	nullable	—	When payment was received
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

13 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (sale_id)
• on (status)
• on (due_date)

---

pos.gift_card — bearer instrument

A gift card is a bearer instrument — redeemable by whoever holds the code, not tied to a customer. Balance is maintained here as a running total; the immutable ledger is gift_card_transaction. Default expiry: never (per state law, configurable per tenant).

Tenant-scoped. Master data.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
code	text	NOT NULL	—	Card number or code (barcode / QR value for physical; link token for digital)
format	text	NOT NULL	'physical'	CHECK IN ('physical','digital')
original_amount_cents	bigint	NOT NULL	—	Face value at issuance
balance_cents	bigint	NOT NULL	—	Current remaining balance; updated by POSService on every transaction
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
issued_at	timestamptz	NOT NULL	—	When the card was activated
expires_at	timestamptz	nullable	—	NULL = never expires (tenant default per state law). Configurable per tenant.
is_active	boolean	NOT NULL	true	False = voided, lost/stolen replacement in progress
issued_sale_id	UUID	nullable	—	FK → pos.sale — the sale at which this card was issued; NULL for bulk-issued cards
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

14 columns.

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, code) WHERE deleted_at IS NULL — code lookup at redemption
• on (is_active)

---

pos.gift_card_transaction — gift card ledger

Append-only immutable ledger of every gift card balance event. balance_after_cents is snapshotted at write time so each row is self-contained for audit. No updated_at / deleted_at.

Tenant-scoped. Append-only — no updated_at, no deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
gift_card_id	UUID	NOT NULL	—	FK → pos.gift_card
transaction_type	text	NOT NULL	—	CHECK IN ('issue','redeem','reload','expire','void','adjust')
amount_cents	bigint	NOT NULL	—	Signed: positive adds balance, negative deducts. 'issue' and 'reload' are positive; 'redeem', 'expire', 'void' are negative.
balance_after_cents	bigint	NOT NULL	—	gift_card.balance_cents snapshot after this transaction — self-contained audit row
sale_id	UUID	nullable	—	FK → pos.sale — present for 'redeem' and 'issue' (sale context)
performed_by	UUID	nullable	—	FK → identity.identity_user — NULL for automated expiry
note	text	nullable	—	Free text; required for 'adjust' and 'void'
created_at	timestamptz	NOT NULL	now()

10 columns. (Append-only — no updated_at / deleted_at.)

Indexes:

• PK on id
• on (tenant_id)
• on (gift_card_id, created_at) — balance history timeline
• on (sale_id)

---

pos.store_credit — customer store-credit account

A store-credit account is customer-tied (unlike gift cards). One account per customer per tenant. Balance maintained here; immutable ledger in store_credit_transaction. Issued from refunds; redeemed at sale.

Tenant-scoped. Master data.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	NOT NULL	—	FK → crm.customer
balance_cents	bigint	NOT NULL	0	Current balance; updated by POSService on every transaction
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
is_active	boolean	NOT NULL	true	False = account closed
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

9 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (customer_id)
• UNIQUE on (tenant_id, customer_id) WHERE deleted_at IS NULL — one account per customer

---

pos.store_credit_transaction — store credit ledger

Append-only immutable ledger of every store-credit event. Carries both sale_id (redemption context) and sale_refund_id (issuance-from-refund context) — both nullable, one or neither set per row.

Tenant-scoped. Append-only — no updated_at, no deleted_at.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
store_credit_id	UUID	NOT NULL	—	FK → pos.store_credit
transaction_type	text	NOT NULL	—	CHECK IN ('issue','redeem','void','adjust','expire')
amount_cents	bigint	NOT NULL	—	Signed: positive adds balance, negative deducts
balance_after_cents	bigint	NOT NULL	—	store_credit.balance_cents snapshot after this transaction
sale_id	UUID	nullable	—	FK → pos.sale — present for 'redeem'
sale_refund_id	UUID	nullable	—	FK → pos.sale_refund — present for 'issue' (issued from a refund)
performed_by	UUID	nullable	—	FK → identity.identity_user — NULL for automated expiry
note	text	nullable	—	Required for 'adjust' and 'void'
created_at	timestamptz	NOT NULL	now()

11 columns. (Append-only — no updated_at / deleted_at.)

Indexes:

• PK on id
• on (tenant_id)
• on (store_credit_id, created_at) — balance history timeline
• on (sale_id)
• on (sale_refund_id)

---

pos.sale_template — contractor recurring worklist header

A saved cart template for recurring contractor orders (e.g. "Smith property weekly maintenance"). Applied via POSService.applyTemplate() to create a new sale pre-populated with lines. Optionally customer-tied; tenant-unique name.

Tenant-scoped. Master data.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
name	text	NOT NULL	—	Template name, e.g. "Smith property weekly"
customer_id	UUID	nullable	—	FK → crm.customer — NULL for generic (non-customer-specific) templates
description	text	nullable	—	Optional description
is_active	boolean	NOT NULL	true	Inactive templates are hidden from the apply-template UI
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

9 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (customer_id)
• UNIQUE on (tenant_id, name) WHERE deleted_at IS NULL

---

pos.sale_template_line — template line items

One row per item in a sale template. Mirrors sale_line structure minus pricing and tax (prices resolved fresh via PricingService when the template is applied, not stored here).

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
sale_template_id	UUID	NOT NULL	—	FK → pos.sale_template
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
quantity	numeric	NOT NULL	—	Default quantity for this line; cashier can adjust when applying
note	text	nullable	—	Optional line note carried over to the sale line when template is applied
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

9 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (sale_template_id)
• on (variant_id)

---

pos.guarantee — guarantee instance

One row per guarantee issued at sale. Vertical-neutral: guarantee_type is a free-text label (driven by item_variant.guarantee_terms.type, e.g. 'plant_guarantee'). terms_snapshot preserves the catalog-level terms at sale time so the guarantee is immutable to catalog changes. Claimed guarantees link back to the refund that processed the claim.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
sale_id	UUID	NOT NULL	—	FK → pos.sale — the sale at which this guarantee was issued
sale_line_id	UUID	NOT NULL	—	FK → pos.sale_line — the specific line item covered
customer_id	UUID	nullable	—	FK → crm.customer — NULL for anonymous sales (guarantee registered without a customer)
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant — denormalized for fast guarantee lookup by item
guarantee_type	text	nullable	—	Type label from item_variant.guarantee_terms.type, e.g. 'plant_guarantee'. Nullable for guarantees issued before type was defined.
issued_date	date	NOT NULL	—	Sale date
expires_date	date	NOT NULL	—	issued_date + guarantee_terms.duration_days; computed by POSService at sale
terms_snapshot	JSONB	nullable	—	Snapshot of item_variant.guarantee_terms at time of sale. Shape: {"duration_days": 365, "type": "plant_guarantee", "notes": "..."}. Immutable after creation.
signature_ref	text	nullable	—	R2 key for the digital signature PNG captured at sale (Path B canvas signature)
status	text	NOT NULL	'active'	CHECK IN ('active','claimed','expired','void')
claimed_refund_id	UUID	nullable	—	FK → pos.sale_refund — the refund that processed this guarantee claim; NULL until claimed
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

16 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (sale_id)
• on (sale_line_id)
• on (customer_id)
• on (variant_id)
• on (status)
• on (expires_date) — expiry sweep for scheduled status updates

---

Group B column summary

Table	Cols	Append-only?
pos.register	11	—
pos.register_session	16	—
pos.register_cash_entry	10	✅
pos.layaway_payment	13	—
pos.gift_card	14	—
pos.gift_card_transaction	10	✅
pos.store_credit	9	—
pos.store_credit_transaction	11	✅
pos.sale_template	9	—
pos.sale_template_line	9	—
pos.guarantee	16	—
Group B total	128

---

Full pos schema column summary

Group	Tables	Cols
Group A	8	150
Group B	11	128
pos total	19	278

---

Intra-pos forward-ref FKs — resolved by Group B

All Group A columns that were plain UUIDs pending Group B targets are now resolvable. Add FK constraints in the Group B migration step:

Group A column	Target (now exists)	Status
sale.register_id	pos.register	✅ Resolved — FK addable
sale.register_session_id	pos.register_session	✅ Resolved — FK addable
sale_line.guarantee_id	pos.guarantee	✅ Resolved — FK addable
sale_payment.gift_card_id	pos.gift_card	✅ Resolved — FK addable
sale_payment.store_credit_id	pos.store_credit	✅ Resolved — FK addable
sale_refund.guarantee_id	pos.guarantee	✅ Resolved — FK addable

---

Full migration ordering (Group A → Group B)

pos.register → pos.register_session → pos.register_cash_entry → pos.gift_card → pos.store_credit → pos.sale_template → pos.sale → pos.sale_line → pos.sale_line_tax → pos.sale_payment → pos.sale_refund → pos.sale_refund_line → pos.receipt → pos.pos_sync_conflict → pos.layaway_payment → pos.sale_template_line → pos.guarantee → (add intra-pos FK constraints for resolved forward-refs)

---