Multi-tenant, Offline-First POS Platform — Database Schema Summary
| Schema | Table | Purpose |
|---|---|---|
| dbo | Partner | Channel partner registry (BCPOS Group house partner + external partners). IsHousePartner flag. Merchants belong to exactly one partner. |
| dbo | PartnerUser | Maps users to partners with PartnerRole (PartnerAdmin/PartnerSupport). UQ on UserId — one partner per user. |
| dbo | Merchant | Merchant registry (PublicId, Code, Name). PartnerId FK (NOT NULL) + nullable MerchantGroupId. IndustryTemplate (Shop/Pharmacy/Hardware). 3 verticals. |
| dbo | DatabaseServer | SQL Server instances hosting merchant databases |
| dbo | DatabaseRecord | Logical databases with Elastic Pool assignment |
| dbo | MerchantDatabase | Maps each merchant to its operational database |
| Schema | Table | Purpose |
|---|---|---|
| dbo | ApplicationUser | ASP.NET Core Identity (extended with FirstName, LastName). Users can be merchant users, partner users, or SystemAdmin. |
| dbo | UserMerchant | Maps users to merchants with UserRole (SystemAdmin/Admin/Manager/Cashier) |
| Schema | Table | Purpose |
|---|---|---|
| dbo | ModuleDefinition | Registry of 31+ available modules (seeded) |
| dbo | MerchantModule | Which modules are active per merchant |
| dbo | MerchantFeatureFlag | Granular feature toggles per merchant |
| Schema | Table | Purpose |
|---|---|---|
| dbo | DeviceRegistration | POS terminals, kiosks, legacy connectors (IoT Hub link) |
| dbo | SystemAuditLog | System-level events (merchant creation, user management, partner actions). MerchantId + PartnerId FKs for scoped audit queries. |
| Schema | Table | Purpose |
|---|---|---|
| dbo | MerchantGroup | Multi-company group registry. Links merchants owned by the same business owner for shared loyalty/gift cards/reporting. See loyalty-group-architecture. |
| dbo | GroupLoyaltyProgram | Group-level loyalty program definition: PointsPerDollar, RedemptionRate, MinPointsToRedeem. Multiple programs per group. |
| dbo | GroupLoyaltyProgramMerchant | Junction: which merchants participate in which group loyalty program. Per-merchant opt-in. |
| dbo | GroupLoyaltyAccount | Loyalty member accounts shared across group. Identified by phone number. PointsBalance cached. |
| dbo | GroupLoyaltyTransaction | Append-only ledger: Earn, Redeem, Adjust, Void, Expire. Tracks SourceMerchantId per transaction. |
| Schema | Table | Purpose |
|---|---|---|
| dbo | Location | Physical stores / branches |
| dbo | LocationGroup | Logical groupings for reporting/pricing |
| dbo | LocationGroupMember | Junction: Location ↔ LocationGroup |
| dbo | Terminal | Individual POS registers |
| dbo | Employee | Staff (replaces separate Cashier + SalesRep) |
| dbo | EmployeeRole | Employee role assignments |
| Schema | Table | Purpose |
|---|---|---|
| dbo | Category | Hierarchical categories (ParentCategoryId). Root categories = departments. |
| dbo | ProductCategory | Junction: Product ↔ Category (N:M). Primary category lives on Product.CategoryId and is also represented here (ADR-0044). |
| dbo | Supplier | Vendor master |
| dbo | Product | Master product record. Includes MSRP, TrackSerialNumbers, and lifecycle Status enum (Draft / Active / Inactive — replaces IsActive boolean per ADR-0065). |
| dbo | ProductOption | Dimension definitions (Size, Color, Material) |
| dbo | ProductOptionValue | Values per dimension (S, M, L, XL) |
| dbo | ProductVariant | SKU-level combinations (Size+Color). Includes PriceLowerBound/PriceUpperBound for override guardrails. |
| dbo | ProductVariantOptionValue | Junction: Variant ↔ OptionValue |
| dbo | ProductBarcode | Secondary/alternate barcodes per variant. Barcode format (UPC-A, EAN-13, ISBN, PLU, Internal, Custom) is never stored — computed from the value via BarcodeHelper.DetectType(). See ADR-0047. |
| dbo | ProductPrice | Price tiers (Retail, Wholesale, VIP) |
| dbo | PriceTier | Price tier definitions |
| dbo | ProductSupplier | Product ↔ Supplier with costs, lead times |
| dbo | ProductImage | Multiple images per product |
| dbo | ProductAlert | POS pop-up messages per product: age verification (21+/18+), warnings, info notes. AlertType via ref.AlertType. Maps from RMH ItemMessage. |
| dbo | KitComponent | Bundle / kit items |
| dbo | UnitOfMeasure | EA, LB, FT, CS, etc. |
| dbo | UnitOfMeasureConversion | Conversion factors between UoMs |
| dbo | SerialNumber | Serial number registry with Status lifecycle (Available/Sold/Returned/Unavailable). Up to 3 S/N per unit. Linked to ProductVariant, Location, SaleLineItem, PurchaseReceiptLine. See schema-tenant §6.11. |
| Schema | Table | Purpose |
|---|---|---|
| dbo | TaxGroup | Named bundle of rates a product can be assigned to (e.g., "PR IVU Standard"). Has Description and IsActive. |
| dbo | TaxRate | Individual tax row reusable across groups (state IVU, municipal, federal excise). Code is unique per merchant. Carries IsCompounding (RMS Tax.IncludePreviousTax), ShowOnReceipt, IsIncludedInPrice. |
| dbo | TaxGroupRate | M:N junction TaxGroup ↔ TaxRate. Per-group Priority drives compounding base + receipt order. ADR-0063. |
| dbo | CustomerTaxProfile | B2B classification (e.g., "Reseller PR SC 2914"). RequiresCert, IsActive. Triggers conditional remap of a sale line's TaxGroup. |
| dbo | CustomerTaxProfileMap | One mapping rule on a profile. FromTaxGroupId = null is the catch-all. Specific match wins over catch-all. |
| Schema | Table | Purpose |
|---|---|---|
| dbo | Customer | Master customer (tier, tax exemption, loyalty, DefaultDiscountPercent) |
| dbo | CustomerAddress | Billing / shipping addresses |
| dbo | CustomerStats | Cached lifetime totals (sales, visits) |
| Schema | Table | Purpose |
|---|---|---|
| dbo | Sale | Master transaction (Status, Type, Totals) |
| dbo | SaleLineItem | Line items in a sale |
| dbo | SalePayment | Payment methods applied to sale |
| dbo | SaleTax | Tax detail per rate per sale |
| dbo | ReasonCode | Reasons for returns, voids, discounts |
| dbo | ReturnTracking | Cross-store return quantity guard |
| Schema | Table | Purpose |
|---|---|---|
| dbo | PaymentMethod | Cash, Card, Check, AccountCharge, GiftCard |
| Schema | Table | Purpose |
|---|---|---|
| dbo | Order | Customer orders (Quote, Layaway, BackOrder, etc.) |
| dbo | OrderLineItem | Line items in an order |
| dbo | OrderPayment | Deposits / payments collected on order |
| dbo | OrderHistory | Audit trail of order status changes |
| Schema | Table | Purpose |
|---|---|---|
| dbo | Shift | Batch/shift with 22+ denormalized totals |
| dbo | ShiftCount | Tender counts per shift by payment method |
| dbo | ShiftTaxTotal | Tax totals per rate per shift |
| dbo | CashMovement | Drops, Payouts, Pickups |
| dbo | NonSaleEvent | Drawer opens, X/Z reports, blind closeouts |
| dbo | ShiftSegment | Terminal session within a floating shift. Tracks which terminal an employee used and when (start/end). Enables terminal movement history. |
| dbo | DailySales | Pre-aggregated daily totals by 5 dimensions (Category, Supplier, Register, Cashier, SalesRep) + LocationId. Generated locally, synced to cloud. |
| Schema | Table | Purpose |
|---|---|---|
| dbo | LocationProduct | Per variant per location (qty, price, cost, tax) |
| dbo | InventoryMovement | Append-only movement ledger |
| dbo | PurchaseOrder | Purchase order header |
| dbo | PurchaseOrderLine | PO line items (qty, UoM, cost) |
| dbo | PurchaseReceipt | Receiving document (separate from PO) |
| dbo | PurchaseReceiptLine | Receipt lines with actual qty, cost variance |
| dbo | Distribution | Warehouse → store allocation header |
| dbo | DistributionLine | Per-item allocation |
| dbo | DistributionAllocation | Per-store qty + acknowledgement |
| dbo | TransferOrder | Store ↔ store transfer (two-sided) |
| dbo | TransferOrderLine | Transfer line items |
| dbo | TransferConfirmation | Receiver acknowledges (partial/full/rejected) |
| dbo | StockCount | Physical count header |
| dbo | StockCountItem | Items counted with variance |
| dbo | InventoryAdjustment | Adjustment document with reason code |
| dbo | AdjustmentLine | Adjustment line items |
| Schema | Table | Purpose |
|---|---|---|
| dbo | PriceChange | Price change campaign (Draft → Approved → Applied) |
| dbo | PriceChangeLine | Per-item price change in campaign |
| dbo | PriceChangeLocation | Which locations get the price change |
| dbo | CostChange | Supplier cost updates, receiving variance |
| Schema | Table | Purpose |
|---|---|---|
| dbo | GiftCard | Gift card master (balance, expiry, status) |
| dbo | GiftCardActivity | Activation, reload, redemption, adjustment |
| Schema | Table | Purpose |
|---|---|---|
| dbo | AccountGroup | Account classifications (Contractor, Government) |
| dbo | Account | Charge account (credit limit, payment terms) |
| dbo | AccountContact | Many-to-many: Customer ↔ Account |
| dbo | AccountLedger | Charges, payments, credit/debit memos |
| dbo | AccountLedgerDetail | Payment application (which charge was paid) |
| dbo | AccountStatement | Periodic statement generation |
| Schema | Table | Purpose |
|---|---|---|
| dbo | LoyaltyProgram | Program definitions (point values, tiers) |
| dbo | LoyaltyAccount | Customer enrollment in loyalty |
| dbo | LoyaltyTransaction | Points earned / redeemed / adjusted |
| Schema | Table | Purpose |
|---|---|---|
| dbo | Promotion | Campaign (date range, active flag) |
| dbo | PromotionRule | Rule type (% off, Buy X Get Y, fixed price) |
| dbo | Coupon | Coupon codes with redemption tracking |
| Schema | Table | Purpose |
|---|---|---|
| dbo | Currency | Currency codes and names |
| dbo | ExchangeRate | Daily exchange rates (immutable, date-versioned) |
| Schema | Table | Purpose |
|---|---|---|
| dbo | TimeEntry | Employee time clock (clock in/out, break minutes denormalized from TimeBreak, JobRoleId, photo URLs + override audit per ADR-0041) |
| dbo | TimeBreak | Discrete break punches per shift — start/end timestamps, paid vs unpaid (ADR-0046) |
| dbo | JobRole | Employee roles with pay rates (cashier, server, bartender) |
| Schema | Table | Purpose |
|---|---|---|
| dbo | CustomFieldDefinition | Merchant-defined custom fields per entity |
| dbo | MerchantSetting | Merchant-level operational config |
| dbo | LocationSetting | Per-location config (overrides merchant) |
| dbo | TerminalSetting | Per-terminal config (overrides location) |
| Schema | Table | Purpose |
|---|---|---|
| dbo | DocumentSequence | Atomic numbering (PO-000142, RCV-000089) |
| Schema | Table | Purpose |
|---|---|---|
| audit | AuditEntry | Data-level audit trail (who changed what, JSON diff) |
| Schema | Table | Purpose |
|---|---|---|
| sync | SyncLog | Sync session records (timestamp, device, direction) |
| sync | SyncCommand | Prioritized command queue for offline ops |
| sync | ChangeLog | Entity-level change tracking (delta sync) |
| sync | DeviceSyncState | Per-device sync watermarks. Tracks last successful sync timestamp per entity type per device. |
| Schema | Table | Purpose |
|---|---|---|
| dbo | LabelSize | Physical label dimensions (width, height, DPI). Atomic IsDefault promotion. |
| dbo | LabelTemplate | ZPL/TSPL label templates with placeholder merge fields. Location-scoped pricing. |
| dbo | PrintJob | Print job queue for label batch printing. |
| Schema | Table | Purpose |
|---|---|---|
| dbo | AiCreditAccount | Per-merchant AI credit balance. Links to AiCreditPlan. |
| dbo | AiCreditPlan | Credit plan definitions (Free, Pro, Enterprise). Monthly allocation, per-credit cost. |
| dbo | AiCreditTransaction | Append-only ledger of credit allocations, usages, top-ups, and adjustments. |
| dbo | AiRequestLog | Every AI completion request — prompt, response, token counts, cost, latency. |
| dbo | AiPrompt | Reusable AI prompt templates. System and merchant-scoped. |
| Schema | Table | Purpose |
|---|---|---|
| ref | SaleStatus | InProgress, Held, Completed, Voided |
| ref | SaleType | Sale, Return, Exchange |
| ref | OrderStatus | Draft, Confirmed, InProgress, ReadyForPickup, Completed, Cancelled |
| ref | OrderType | Quote, Layaway, BackOrder, WorkOrder, SpecialOrder |
| ref | ShiftStatus | Open, Closed |
| ref | GiftCardStatus | Pending, Active, Expired, Suspended |
| ref | AccountStatus | Active, Suspended, Closed |
| ref | TimeEntryStatus | Open, Closed, Adjusted |
| ref | PaymentMethodType | Cash, Card, Check, AccountCharge, GiftCard, External |
| ref | CommissionType | None, PercentOfSale, PercentOfProfit, Fixed |
| ref | AddressType | Billing, Shipping |
| ref | MovementType | Sale, Return, Receipt, Adjustment, TransferOut, TransferIn, Count, Distribution |
| ref | DocumentType | PO, Receipt, Distribution, Transfer, Adjustment, StockCount, PriceChange |
| ref | ReasonCodeCategory | Return, Discount, Adjustment, Void, PriceOverride, AR_* |
| ref | PurchaseOrderStatus | Draft, Submitted, PartiallyReceived, FullyReceived, Closed, Cancelled |
| ref | PurchaseReceiptStatus | Draft, Completed, Cancelled |
| ref | DistributionStatus | Draft, Allocated, Shipped, Completed, Cancelled |
| ref | TransferOrderStatus | Draft, Submitted, Shipped, InTransit, PartiallyReceived, Received, Closed, Cancelled |
| ref | TransferConfirmationType | Shipped, Received, PartialReceived, Rejected |
| ref | AdjustmentStatus | Draft, Completed, Cancelled |
| ref | StockCountStatus | InProgress, Completed, Cancelled |
| ref | PriceChangeStatus | Draft, PendingApproval, Approved, Applied, Cancelled, Rejected |
| ref | CostChangeType | SupplierIncrease, SupplierDecrease, ManualAdjustment, ReceivingVariance |
| ref | AccountLedgerEntryType | Charge, Payment, CreditMemo, DebitMemo, FinanceCharge |
| ref | PaymentTermsType | DueOnReceipt, NetDays, EndOfMonth |
| ref | CreditLimitEnforcement | Warn, Block, None |
| ref | PromotionRuleType | PercentOff, AmountOff, BuyXGetY, FixedPrice |
| ref | PromotionTargetType | Product, Category, EntireSale |
| ref | GiftCardActivityType | Activation, Reload, Redemption, Adjustment, Expiration |
| ref | LoyaltyTransactionType | Earn, Redeem, Adjust |
| ref | NonSaleEventType | NoSale, Aborted, XReport, ZReport, ZZReport, BlindCloseout |
| ref | SyncCommandStatus | Pending, InProgress, Completed, Failed, Cancelled |
| ref | SyncDirection | Upload, Download |
| ref | ChangeType | Insert, Update, Delete |
| ref | BarcodeType | Auto-detected barcode format: UPC_A(0), EAN_13(1), EAN_8(2), ISBN(3), Custom(9) |
| ref | SerialNumberStatus | Available(0), Sold(1), Returned(2), Unavailable(3) |
| ref | AlertType | AgeVerification(0), Warning(1), Info(2) |