Database Schema

Overview

The system uses two separate PostgreSQL databases:

• HQ Database (pos_hq): Master data + aggregated store data. Used by hq-server.
• Store Database (pos_store): Local copy of master data + store-specific transactional data. Used by store-server. Each store has its own database instance.

Both databases share the same schema (defined via Drizzle ORM in apps/hq-server/src/db/schema/ and apps/store-server/src/db/schema/). The store database is populated through sync.

Numeric Conventions

All monetary amounts and quantities use integers to avoid floating-point errors:

Type	Storage	Example
Money	Cents (integer)	$12.99 = 1299
Tax rates	Basis points (integer)	8.25% = 825
Quantities	Multiplied by 1000 (integer)	2.5 units = 2500

These conventions are enforced across the entire stack: database columns use bigint mode number, shared types use number, and the frontend formats them for display.

Schema Diagram

products ──────── departments
    |                  |
    +── tax_groups ── tax_group_rates ── tax_rates
    |
    +── product_suppliers ── suppliers
    |
    +── product_specials
    |
    +── store_product_dynamic ── stores
    |
    +── sale_items ── sales ── sale_payments
    |       |
    |       +── sale_item_taxes
    |
    +── inventory
    |
    +── inventory_adjustments
    |
    +── transfer_items ── transfers
    |
    +── purchase_order_items ── purchase_orders
    |
    +── worksheet_items ── worksheets ── worksheet_stores

users ── user_store_roles ── roles ── role_permissions ── permissions

sales_reps
customers
tenders
register_sessions
monthly_closings
sync_outbox / sync_status / sync_log / sequences

Table Reference

Products Module

products

Column	Type	Description
id	uuid (PK)	Auto-generated
sku	varchar(50)	Unique stock keeping unit
barcode	varchar(100)	Optional barcode
name	varchar(255)	Product name
description	text	Optional description
department_id	uuid (FK)	Reference to departments
cost_price	bigint	Cost in cents
sell_price	bigint	Sell price in cents
tax_group_id	uuid (FK)	Reference to tax_groups
is_active	boolean	Soft delete flag
track_stock	boolean	Whether to track inventory
updated_at	timestamptz	Last modification
sync_version	integer	Incremented on each change for sync

departments

Column	Type	Description
id	uuid (PK)	Auto-generated
name	varchar(100)	Department name
parent_id	uuid	Self-referencing for hierarchy
sort_order	integer	Display order
updated_at	timestamptz	Last modification
sync_version	integer	Sync tracking

tax_rates

Column	Type	Description
id	uuid (PK)	Auto-generated
name	varchar(100)	Tax name (e.g., "State Tax")
rate	integer	Rate in basis points (825 = 8.25%)
is_compound	boolean	Whether this tax compounds on others
is_active	boolean	Active flag
sync_version	integer	Sync tracking

tax_groups

Groups multiple tax rates together. A product references a tax group, and the group contains the ordered list of rates to apply.

Column	Type	Description
id	uuid (PK)	Auto-generated
name	varchar(100)	Group name (e.g., "Standard Tax")
description	text	Optional
is_active	boolean	Active flag
sync_version	integer	Sync tracking

tax_group_rates

Join table: which tax rates belong to which group, in what order.

Column	Type	Description
id	uuid (PK)	Auto-generated
tax_group_id	uuid (FK)	Reference to tax_groups
tax_rate_id	uuid (FK)	Reference to tax_rates
sort_order	integer	Application order (compound taxes apply after earlier rates)

Unique constraint on (tax_group_id, tax_rate_id).

suppliers

Column	Type	Description
id	uuid (PK)	Auto-generated
name	varchar(255)	Supplier name
contact_info	text	Contact details
payment_terms	text	Payment terms
sync_version	integer	Sync tracking

product_suppliers

Many-to-many: which suppliers provide which products.

Column	Type	Description
id	uuid (PK)	Auto-generated
product_id	uuid (FK)	Reference to products
supplier_id	uuid (FK)	Reference to suppliers
supplier_sku	varchar(100)	Supplier's SKU for this product
cost	bigint	Cost from this supplier in cents
is_preferred	boolean	Preferred supplier flag
sort_order	integer	Display order

Unique constraint on (product_id, supplier_id).

product_specials

Time-limited special pricing for products.

Column	Type	Description
id	uuid (PK)	Auto-generated
product_id	uuid (FK)	Reference to products
special_price	bigint	Special price in cents
start_date	timestamptz	When the special starts
end_date	timestamptz	When the special ends
description	varchar(255)	Optional description
is_active	boolean	Active flag
sync_version	integer	Sync tracking

Sales Module

sales

Column	Type	Description
id	uuid (PK)	Auto-generated
receipt_number	varchar(50)	Unique receipt number
cashier_id	uuid (FK)	Reference to users
terminal_id	varchar(20)	Terminal identifier
status	varchar(20)	completed, voided, refunded, or parked
subtotal	bigint	Subtotal in cents
tax_total	bigint	Total tax in cents
discount_total	bigint	Total discount in cents
grand_total	bigint	Grand total in cents
store_id	uuid	Store that created this sale
customer_id	uuid (FK)	Optional customer reference
sales_rep_id	uuid (FK)	Optional sales representative
created_at	timestamptz	Sale timestamp
synced	boolean	Whether this sale has been synced to HQ

sale_items

Column	Type	Description
id	uuid (PK)	Auto-generated
sale_id	uuid (FK)	Reference to sales
product_id	uuid	Product sold
product_name	varchar(255)	Snapshot of product name at sale time
quantity	bigint	Quantity sold (x1000)
unit_price	bigint	Unit price in cents
tax_rate	integer	Effective tax rate in basis points
tax_amount	bigint	Tax amount in cents
discount_amount	bigint	Discount in cents
line_total	bigint	Line total in cents
original_price	bigint	Price before any override
cost_price	bigint	Cost at time of sale
price_source	integer	1=regular, 2=manual, 3=discount_percent, 4=discount_fixed, 5=promotion
stock_after	bigint	Stock level after this sale

sale_item_taxes

Breakdown of individual tax rates applied to each sale item.

Column	Type	Description
id	uuid (PK)	Auto-generated
sale_item_id	uuid (FK)	Reference to sale_items
tax_rate_name	varchar(100)	Name of the tax rate
rate	integer	Rate in basis points
tax_amount	bigint	Tax amount in cents
is_compound	boolean	Whether this was a compound tax
sort_order	integer	Application order

sale_payments

Column	Type	Description
id	uuid (PK)	Auto-generated
sale_id	uuid (FK)	Reference to sales
method	varchar(20)	Tender code (e.g., CASH, CARD)
amount	bigint	Payment amount in cents
reference	varchar(255)	Payment reference (e.g., card last 4 digits)

Users Module

users

Column	Type	Description
id	uuid (PK)	Auto-generated
username	varchar(50)	Unique login name
pin_hash	varchar(255)	bcrypt hash of PIN or password
display_name	varchar(100)	Human-readable name
is_active	boolean	Active flag
sync_version	integer	Sync tracking

permissions

Column	Type	Description
id	uuid (PK)	Auto-generated
code	varchar(100)	Unique permission code (e.g., products.view)
module	varchar(50)	Module name (e.g., products)
name	varchar(100)	Human-readable name
description	text	Description

roles

Column	Type	Description
id	uuid (PK)	Auto-generated
name	varchar(100)	Role name (e.g., "Administrator")
description	text	Role description
is_system	boolean	System roles cannot be deleted
sync_version	integer	Sync tracking

role_permissions

Join table with composite PK (role_id, permission_id).

user_store_roles

Assigns roles to users, optionally scoped to a specific store.

Column	Type	Description
id	uuid (PK)	Auto-generated
user_id	uuid (FK)	Reference to users
store_id	uuid	Null = global (HQ-level) role
role_id	uuid (FK)	Reference to roles

Stores Module

stores

Column	Type	Description
id	uuid (PK)	Auto-generated
code	varchar(10)	Unique store code (e.g., ST01)
name	varchar(255)	Store name
address	text	Physical address
is_active	boolean	Active flag
last_sync_at	timestamptz	Last successful sync timestamp
sync_version	integer	Sync tracking

store_product_dynamic

Per-store product data: local prices, stock levels, reorder points. Composite PK (store_id, product_id).

Column	Type	Description
store_id	uuid (FK)	Reference to stores
product_id	uuid (FK)	Reference to products
quantity	bigint	Current stock (x1000)
sell_price	bigint	Store-specific sell price override (cents), null = use product default
cost_price	bigint	Store-specific cost override (cents)
reorder_point	bigint	Reorder trigger level (x1000), default 10000
restock_level	bigint	Target restock level (x1000), default 50000
last_sold_at	timestamptz	Last sale of this product at this store
last_received_at	timestamptz	Last inventory receipt
sync_version	integer	Sync tracking

Important: store_product_dynamic rows are created by the sync pull endpoint when a store first pulls products. They are never created directly by HQ operations.

purchase_orders

Column	Type	Description
id	uuid (PK)	Auto-generated
order_number	varchar(50)	Unique order number
supplier_id	uuid	Supplier
store_id	uuid	Which store this PO is for
status	varchar(20)	draft, submitted, approved, sent, received, cancelled
total	bigint	Total in cents
notes	text	Notes
created_by	uuid (FK)	User who created
approved_by	uuid	User who approved
sync_version	integer	Sync tracking

purchase_order_items

Column	Type	Description
id	uuid (PK)	Auto-generated
purchase_order_id	uuid (FK)	Reference to purchase_orders
product_id	uuid (FK)	Reference to products
quantity_ordered	bigint	Ordered quantity (x1000)
quantity_received	bigint	Received quantity (x1000)
unit_cost	bigint	Unit cost in cents

Inventory Module

inventory

Simple stock tracking table. PK is product_id (one row per product).

Column	Type	Description
product_id	uuid (PK/FK)	Reference to products
quantity	bigint	Current stock level (x1000)
low_stock_threshold	bigint	Alert threshold (x1000), default 10000

inventory_adjustments

Column	Type	Description
id	uuid (PK)	Auto-generated
product_id	uuid (FK)	Product adjusted
quantity_change	bigint	Change amount (x1000), positive or negative
reason	varchar(20)	Reason code (e.g., received, damaged, count)
reference_id	uuid	Optional reference (e.g., transfer ID)
created_by	uuid (FK)	User who made the adjustment
store_id	uuid	Store where adjustment occurred
synced	boolean	Whether synced to HQ

Transfers Module

transfers

Column	Type	Description
id	uuid (PK)	Auto-generated
from_store_id	uuid	Source store
to_store_id	uuid	Destination store
status	varchar(30)	draft, sent, received, cancelled
notes	text	Notes
created_by	uuid (FK)	User who created
sent_at	timestamptz	When shipped
received_at	timestamptz	When received
received_by	uuid (FK)	User who received
synced	boolean	Whether synced to HQ

transfer_items

Column	Type	Description
id	uuid (PK)	Auto-generated
transfer_id	uuid (FK)	Reference to transfers
product_id	uuid (FK)	Product transferred
quantity_sent	bigint	Quantity sent (x1000)
quantity_received	bigint	Quantity received (x1000)

Register Module

register_sessions

Column	Type	Description
id	uuid (PK)	Auto-generated
terminal_id	varchar(20)	Terminal identifier
opened_by	uuid (FK)	User who opened
closed_by	uuid (FK)	User who closed
opening_amount	bigint	Opening cash in cents
closing_amount	bigint	Closing cash counted in cents
expected_amount	bigint	System-calculated expected cash
difference	bigint	Variance (closing - expected)
store_id	uuid	Store
opened_at	timestamptz	Open timestamp
closed_at	timestamptz	Close timestamp
synced	boolean	Whether synced to HQ

monthly_closings

Column	Type	Description
id	uuid (PK)	Auto-generated
year	integer	Year
month	integer	Month (1-12)
status	varchar(10)	open or closed
total_sales	bigint	Total sales in cents
total_tax	bigint	Total tax in cents
total_refunds	bigint	Total refunds in cents
total_discounts	bigint	Total discounts in cents
store_id	uuid	Store

Other Tables

customers

Column	Type	Description
id	uuid (PK)	Auto-generated
code	varchar(20)	Unique customer code
first_name	varchar(100)	First name
last_name	varchar(100)	Last name
email	varchar(255)	Email
phone	varchar(30)	Phone
tax_id	varchar(50)	Tax identification number
address	text	Address
notes	text	Notes
is_active	boolean	Active flag
origin_store_id	uuid	Store where customer was created
sync_version	integer	Sync tracking

tenders

Payment method types (e.g., Cash, Credit Card, Check).

Column	Type	Description
id	uuid (PK)	Auto-generated
code	varchar(20)	Unique code (e.g., CASH)
name	varchar(100)	Display name
type	varchar(20)	Type category (e.g., cash, card, other)
is_active	boolean	Active flag
sort_order	integer	Display order
opens_drawer	boolean	Whether this tender opens the cash drawer
requires_reference	boolean	Whether a reference number is required
sync_version	integer	Sync tracking

sales_reps

Column	Type	Description
id	uuid (PK)	Auto-generated
code	varchar(20)	Unique rep code
first_name	varchar(100)	First name
last_name	varchar(100)	Last name
phone	varchar(30)	Phone
email	varchar(255)	Email
is_active	boolean	Active flag
sync_version	integer	Sync tracking

worksheets and worksheet_items

Worksheets support bulk product changes with an approval workflow: draft -> submitted -> approved -> applied (or rejected).

Types: new_product, price_change, product_update, tax_change, deactivate.

worksheet_stores

Which stores a worksheet applies to. Has an applied flag per store.

Sync Tables

See Sync System for detailed explanation.

sync_outbox (Store DB)

Transactional outbox for store-to-HQ push. Entries are created in the same transaction as the business operation, then a background process pushes them to HQ.

sync_status (HQ DB)

Aggregate counters per (store_id, entity_type, direction). Upserted on each sync operation. Never grows unbounded.

sync_log (HQ DB)

Detailed log of sync operations. Failed entries remain until resolved or retried from the HQ dashboard. Successful entries can be cleaned up.

sequences (Store DB)

Key-value store tracking the last sync version pulled for each entity type. Used by the pull service to request only changes since the last pull.

Migrations

Migrations are managed by Drizzle Kit:

# HQ server migrations
pnpm --filter hq-server exec node --import tsx node_modules/drizzle-kit/bin.cjs generate

# Store server migrations
pnpm --filter store-server exec node --import tsx node_modules/drizzle-kit/bin.cjs generate

Migration files are stored in apps/hq-server/src/db/migrations/ and applied automatically on server startup.

sync_version Column

Most master data tables include a sync_version integer column. This is incremented every time a row is updated. The sync pull endpoint uses this to return only rows that changed since the store's last pull (WHERE sync_version > $sinceVersion). This avoids transferring the entire dataset on each sync cycle.