Data model — POS collections

Purpose: an entity-relationship view of the POS Appwrite collections, grouped by domain. Status: as of 2026-06-19. Authoritative definitions: ../business-rules/pos-domain-model.md. Exact attributes are fixed in the AppwriteData tickets.

Mermaid ER (key relationships)

erDiagram
    MERCHANT ||--o{ STORE : "groups (merchantId)"
    STORE ||--|| STORE_TEAM : "is an Appwrite Team"
    STORE ||--o{ TERMINAL : has
    STORE ||--o{ EMPLOYEE : staffs
    AUTH_USER ||--|| EMPLOYEE : "profile (userId)"
    STORE_TEAM ||--o{ EMPLOYEE : "membership role = POS role"
    TERMINAL ||--o{ SHIFT : runs
    EMPLOYEE ||--o{ SHIFT : opens
    SHIFT ||--o{ CASH_MOVEMENT : records

    CATEGORY ||--o{ PRODUCT : groups
    PRODUCT ||--o{ VARIANT : has
    VARIANT ||--o{ BARCODE : "scanned by"
    PRODUCT }o--o{ TAX_RATE : "taxed by"

    VARIANT ||--o{ STOCK_LEVEL : "stocked per store"
    STORE ||--o{ STOCK_LEVEL : holds
    STOCK_LEVEL ||--o{ STOCK_MOVEMENT : "ledger of"

    CART ||--o{ LINE_ITEM : contains
    CART ||--o{ DISCOUNT : applies
    LINE_ITEM }o--|| VARIANT : "of"
    CART ||--o| SALES_ORDER : "finalized as"
    SALES_ORDER ||--o{ ORDER_PAYMENT : "paid by"
    SALES_ORDER ||--o{ TRANSACTION : "ledgered as"
    ORDER_PAYMENT }o--o| PAYMENT_INTENT : "via"
    SALES_ORDER ||--o| RECEIPT : "issues"

    STORE_TEAM ||--o{ AUDIT_LOG : "scopes (RLS)"

    SHIFT ||--o| SHIFT_REPORT : "frozen Z-report"
    STORE ||--o{ DAILY_SALES_ROLLUP : "aggregated per business date"

STORE_TEAM is the store’s Appwrite Team; AUTH_USER is the Appwrite Auth user. POS role = the user’s STORE_TEAM membership role (no pos_roles/pos_permissions collections).

Domain grouping

Domain	Collections
catalog	categories, products, variants, barcodes, tax_rates
foundations	merchants, merchant_settings, merchant_themes, stores (+storeTeamId,merchantId), terminals, audit_log
inventory	stock_levels, stock_movements
sales	carts, line_items, discounts, sales_orders, customers, order_payments, transactions, payment_intents, receipts
staff	employees (profile), shifts, cash_movements, approvals — roles via store-team membership; no pos_roles/pos_permissions
reporting	shift_reports (frozen Z-report snapshot), daily_sales_rollups (incremental per-store/day aggregate) — derived & reconcilable, not source of truth
wallet (Track K)	merchant_balances, balance_movements, deposits, charges, payments — merchant wallet ledger; see ../business-rules/pos-domain-model.md §wallet
notifications	notifications, notification_reads
config/platform	meta, feature_flags

value_added_services (see ../business-rules/value-added-services.md)

Partly built (2026-07-12). providers, provider_products, serialized_units are live in the config. service_fulfillments + pin_vouchers are still to build (documented but not yet in the schema), and the VAS field additions below are not yet applied to line_items/transactions.

Domain	Collections
value_added_services	providers ✅ (carriers/billers/PIN brands, synced mirror), provider_products ✅ (denomination/price mirror), serialized_units ✅ (chips/SIM inventory units), service_fulfillments ⬜ (external-call record + reconciliation), pin_vouchers ⬜ (PIN custody/delivery)

Plus field additions to existing collections: products (kind, providerId, isVirtual, amountMode, referenceType, isSerialized), line_items (kind, providerId, providerProductId, recipientRef, denomination, serviceReference; variantId optional), transactions (type += REFUND/REVERSAL, status += PENDING_EXTERNAL, externalProviderId, externalFolio, externalStatusCode).

Notes

• Tenant scoping: every tenant-owned document is permission-scoped to its store team (Role.team(storeTeamId)); rows also carry storeTeamId + merchantId. Merchant is grouping data, not a permission boundary. (Updated 2026-06-19: store=team.)
• Append-only ledgers: stock_movements and transactions are append-only; current state (stock_levels) is reconciled from movements via a durable workflow.
• Idempotency: sales documents carry clientId for offline-safe sync. See ../business-rules/offline-and-idempotency.md.
• New vs legacy: products, transactions, payment_intents, audit_log are new Appwrite collections distinct from the legacy loan/financial entities, which stay in TypeORM until their own migration.
• Reporting persistence (hybrid): shift_reports is a frozen snapshot written by the shift-close workflow; daily_sales_rollups is an incremental aggregate maintained on order finalize/void. Both key on the store-local business date (stores.timezone) and are reconcilable by replaying the transactions ledger — they are never the source of truth. Voids/refunds must decrement the affected rollup. See ../business-rules/reporting.md.