Data model
Source:
g2labs-in/billuu-docs@9f1dcd7·content/architecture/data-model.md· updated 2026-09-16
Two stores, not one
Billuu keeps two kinds of data apart.
| Store | Postgres schema | Holds | Registered in |
|---|---|---|---|
| Control plane | public | Tenants, users, organizations, subscriptions and platform RBAC. | CONTROL_MODELS in src/utils/constants.js |
| Tenant | one schema per tenant | Restaurants, menus, orders, inventory and money. | TENANT_MODELS in src/utils/constants.js |
src/utils/constants.js fixes the names. PUBLIC_SCHEMA is 'public' and TENANT_SCHEMA_PREFIX is 'tenant_'.
The tenant schema name is tenant_<organizationId>. UnifiedTenantMiddleware.resolveOrganizationContext in src/core/tenant/unifiedTenantMiddleware.js builds it, and authService.generateAccessToken puts the same string in the token as the schema claim.
This split is why the backend has one rule about database access. AGENTS.md forbids importing raw Sequelize models into a controller. A controller calls databaseExecutor.executeWithTenant, databaseExecutor.readWithTenant or databaseExecutor.executeInPublic, and never runs SET search_path. The same file says associations belong in src/architecture/centralAssociations.js.
Control plane tables
| Table | Purpose | Created by |
|---|---|---|
plans | Subscription plans and their limits. | src/migrations/legacy/control-plane/001_init_control_plane.sql |
businesses | The billing entity. Carries owner_id, status and subscription_plan. | same |
users | Every non super admin login. Carries business_id, organization_id, outlet_id, outlet_ids, role, role_id, panel_type and token_version. | same |
tenant_registry | Maps a business_id to its schema name, unique on both columns. | same |
subscriptions | One row per business, with plan_id, status and the current period. | same |
tenant_connections | Alternate database connection for a business, with the password stored encrypted. | same |
super_admin_users | Platform operators. Separate table, separate login path. | same |
audit_logs | Platform level audit trail. | same |
tenant_migration_log | One row per tenant migration run, with a started, completed or failed status. | same |
cluster_metadata | Database host and port records. | same |
organizations | The tenant itself. Self referencing through parent_organization_id. | src/migrations/legacy/20250413_saas_multi_tenancy_setup.sql |
outlet_users | A user to outlet assignment. | same |
permission_overrides | GRANT, DENY or INHERIT overrides at GLOBAL, ORGANIZATION, OUTLET or USER scope. | src/migrations/legacy/20250415_permission_overrides_and_hierarchy.sql |
user_menu_permissions | One row per user per organization holding a JSONB menu map. | src/migrations/legacy/20250424100000-create-user-menu-permissions.js |
organizations gained parent_organization_id, hierarchy_level, hierarchy_path, permission_cascade_enabled and permission_mode in src/migrations/legacy/20250415_permission_overrides_and_hierarchy.sql. permission_mode is one of INHERIT, ISOLATED or OVERRIDE, and that is how a franchise parent controls its children.
Two identifiers run alongside each other on users. business_id names the billing entity, and organization_id names the tenant whose schema holds the data. authService falls back from one to the other.
Tenant schema, core and RBAC
| Table | Purpose | Created by |
|---|---|---|
outlets | The root of tenant data. Carries business_id, organization_id, parent_outlet_id, is_head_office and menu_version. | src/migrations/tenant/001_create_outlets.js |
table_areas | Grouping for tables. Created before tables because tables references it. | same |
tables | A physical table. | same |
categories | Menu categories. | same |
products | Menu items. | same |
roles, permissions, role_permissions | Per tenant RBAC. | src/migrations/tenant/002_rbac_tables.js |
roles is unique on (organization_id, name) and carries is_system, which marks a role that cannot be deleted. permissions is unique on key. role_permissions is unique on (role_id, permission_id).
Tenant schema, business operations
| Table | Purpose | Created by |
|---|---|---|
customers, customer_ledger, customer_transactions | Customer record, running balance and individual entries. | src/migrations/tenant/003_business_tables.js |
settings | Per business settings such as app_name. | same |
billing_configs | Invoice header, tax rates, paper size and the UPI id. | same, extended by src/migrations/tenant/040_add_upi_id_to_billing_configs.js |
operation_timings, timings | Opening hours by day. | same |
feature_flags | Feature flag records, keyed by name. | same |
web_contents | Web content records. | src/migrations/tenant/008_content_tables.js |
audit_logs | Tenant level audit trail. Carries user_id and user_name. | same |
roll_trackings | Roll tracking entries. | src/migrations/tenant/009_tracking_tables.js |
staff_menu_assignments | A menu assignment per staff member, keyed by staff_id. | src/migrations/tenant/010_staff_tables.js |
schema_versions | Applied schema version for the tenant. | same |
Tenant schema, orders and payments
| Table | Purpose | Created by |
|---|---|---|
orders | The order header. status defaults to KOT_SENT and is constrained to the list in src/models/orderModel.js. | src/migrations/tenant/005_order_tables.js |
order_items | One line per product. Carries product_variant_id, variant_snapshot and ingredient_cost. | same, extended by src/migrations/tenant/023_add_product_variants.js and src/migrations/tenant/037_add_ingredient_cost_to_order_items.js |
payments | Payment against an order. Manual UPI payments add screenshot, utr_number, verification_status, verified_by and verified_at. | same, extended by src/migrations/tenant/030_manual_payment_verification.js |
purchases, purchase_items | Supplier purchases and their lines. | same |
product_types | Product grouping with category_id, icon and color. | same |
product_variants | Size or portion variants of a product. | src/migrations/tenant/023_add_product_variants.js |
table_sessions | One dining session per table, with status and payment_status. | src/migrations/tenant/027_qr_table_ordering.js, extended by src/migrations/tenant/028_guest_sessions_and_refactor.js and src/migrations/tenant/029_fix_table_sessions_columns.js |
guest_sessions | Guest sessions, referenced by orders.guest_session_id and payments.guest_session_id. | src/migrations/tenant/028_guest_sessions_and_refactor.js |
Tenant schema, inventory
| Table | Purpose | Created by |
|---|---|---|
inventory_categories | Grouping for raw materials. | src/migrations/tenant/004_inventory_tables.js |
inventory_items | A raw material, with unit, sku, current_stock, minimum_stock and cost_per_unit. Constrained so current_stock cannot go below zero. | same |
inventory | Per outlet stock summary. Constrained so quantity cannot go below zero. | same |
inventory_transactions | Every stock movement. Holds both inventory_id and inventory_item_id, and either may be null. | same |
inventory_action_approvals | Manager review queue for stock actions. | same |
stock_transactions | Stock movements against an item. | same |
suppliers | Supplier records. Created before inventory_items because inventory_items references it. | same |
wastages | Recorded wastage. | same |
recipes, recipe_items | A recipe per product, and its ingredient lines. | same |
inventory_sales | Inventory sale records. | same |
product_availability | Per product and outlet availability cache, unique on (product_id, outlet_id). | src/migrations/tenant/020_product_availability_cache.js |
product_availability_projections | Per product, outlet and business projection with can_make and availability_status. | src/migrations/tenant/019_enterprise_inventory_platform.sql |
stock_alerts | Open stock alerts, unique on (outlet_id, item_id, alert_type) while unresolved. | src/migrations/tenant/021_stock_alerts.js |
modifier_options, modifier_recipes | A menu modifier and the stock it consumes. | src/migrations/tenant/022_modifier_recipes.js |
inventory_item_price_history | Price over time, keyed by effective_from. | src/migrations/tenant/036_inventory_price_history.js |
stock_transfers | A transfer between two outlets, with separate source and destination columns. | src/migrations/tenant/035_stock_transfers.js |
The same migration adds the eleven tables below. Each one supports the event driven inventory model.
| Table | Purpose |
|---|---|
inventory_reservations | Stock held for a cart or order until expires_at. |
inventory_batches | Batches with expiry, unique on (batch_no, business_id, outlet_id). |
production_batches, production_consumptions | A batch of in house production and the ingredients it consumed. |
purchase_orders, goods_receipts | The purchase workflow. goods_receipts.purchase_order_id points back at purchase_orders. |
product_ingredient_dependencies, product_combo_dependencies | Denormalized graphs so availability can be computed without walking recipes. |
sales_velocity, ingredient_consumption_rates | Rolling counts per product or item over a period. |
order_item_recipe_snapshots | The recipe as it stood when the item was sold. |
Tenant schema, money and partners
| Table | Purpose | Created by |
|---|---|---|
accounts | Cash, bank and other accounts, with opening_balance and current_balance. | src/migrations/tenant/007_accounting_tables.js |
account_transactions | Movements against an account. | same |
expenses, expense_types | Spending and its classification. | same |
incomes | Recorded income. | same |
membership_plans | Membership tiers. | src/migrations/tenant/006_partner_tables.js |
partner_types, partner_memberships, partner_wallets | Partner classification, enrolment and stored balance. | same |
One name appears in two places. src/migrations/legacy/013_create_expense_types_table.sql also creates a table called expense_types, and it keys that one by brand_id instead of business_id. Treat the tenant copy from src/migrations/tenant/007_accounting_tables.js as the current one.
Tenant schema, notifications
| Table | Purpose | Created by |
|---|---|---|
device_tokens | Push tokens per user. | src/migrations/tenant/026_create_notification_tables.js |
notifications | Notification records. | same |
notification_delivery_logs | Delivery attempts per notification and device token. | same |
Relationships
Very few foreign keys are declared in the tenant schema. The dump in src/db/schema/tenant_schema.sql contains twelve. Everything else is an application level relationship carried by a business_id column and an outlet_id column, declared as a Sequelize association in src/architecture/centralAssociations.js, and scoped on read through src/utils/entityScopeRegistry.js. Treat the columns, not a constraint, as the source of truth for most joins, and scope every query by outlet.
The declared foreign keys are these.
| Child column | Parent | On delete |
|---|---|---|
order_items.order_id | orders.id | CASCADE |
order_items.product_variant_id | product_variants.id | SET NULL |
products.category_id | categories.id | CASCADE |
products.outlet_id | outlets.id | CASCADE |
products.default_variant_id | product_variants.id | SET NULL |
product_variants.product_id | products.id | CASCADE |
recipes.product_variant_id | product_variants.id | CASCADE |
inventory_item_price_history.inventory_item_id | inventory_items.id | CASCADE |
modifier_recipes.modifier_option_id | modifier_options.id | CASCADE |
notification_delivery_logs.notification_id | notifications.id | CASCADE |
production_consumptions.production_batch_id | production_batches.id | CASCADE |
goods_receipts.purchase_order_id | purchase_orders.id | CASCADE |
The diagrams below show the intended graph. Only the arrows in the table above are enforced by a database constraint, and the rest are carried by business_id and outlet_id columns.
erDiagram outlets ||--o{ categories : "groups menu" outlets ||--o{ products : "sells at" categories ||--o{ products : "fk_products_category" outlets ||--o{ tables : "floor" table_areas ||--o{ tables : "groups" products ||--o{ product_variants : "sizes" products ||--o{ recipes : "made from" recipes ||--o{ recipe_items : "lists" inventory_categories ||--o{ inventory_items : "groups" inventory_items ||--o{ recipe_items : "consumed by" inventory_items ||--o{ inventory_transactions : "moves" orders ||--o{ order_items : "fk_order_items_order" product_variants ||--o{ order_items : "sold as" orders ||--o{ payments : "settled by" outlets ||--o{ table_sessions : "seated" guest_sessions ||--o{ orders : "optional"The control plane keeps its own graph.
erDiagram businesses ||--o{ users : "employs" businesses ||--o| tenant_registry : "names one schema" businesses ||--o| subscriptions : "subscribes" plans ||--o{ subscriptions : "sold as" businesses ||--o| tenant_connections : "may point at" organizations ||--o{ organizations : "parent of" users ||--o{ outlet_users : "assigned" users ||--o{ user_menu_permissions : "menu overrides" organizations ||--o{ permission_overrides : "org scope" users ||--o{ permission_overrides : "user scope" users ||--o{ audit_logs : "writes"Design decisions worth knowing
outlet_users has no foreign key to outlets or roles. The model header in src/control_plane_models/outletUserModel.js states the reason. The table moved to public so multi outlet switching stays stable, and it keeps no hard foreign key to tenant tables. src/migrations/legacy/fix_outlet_users_fk.js drops the two constraints that used to point at outlets and roles, and the model comment records that the cross schema references use constraints: false.
outlets.organization_id has no foreign key either. The comment in src/migrations/legacy/20250413_saas_multi_tenancy_setup.sql gives the reason. organizations sits in the control plane and outlets sits in the tenant schema, so the reference cannot be enforced in one database schema.
permission_overrides.outlet_id has no foreign key for the same reason. The model comment in src/control_plane_models/permissionOverrideModel.js records it as a cross schema reference.
products and product_variants reference each other. product_variants.product_id points at products, and products.default_variant_id points back at product_variants. Both come from src/migrations/tenant/023_add_product_variants.js. The first is CASCADE and the second is SET NULL, so deleting a variant clears the default rather than failing.
outlets.menu_version is a cache key, not a setting. The comment in src/migrations/tenant/001_create_outlets.js says it increments on every product or category change so the customer app can detect a stale menu. src/migrations/tenant/031_add_menu_version_to_outlets.js adds it to tenants created before the column existed.
Two availability tables exist. src/migrations/tenant/020_product_availability_cache.js creates product_availability, a cached answer unique on (product_id, outlet_id). src/migrations/tenant/019_enterprise_inventory_platform.sql creates product_availability_projections, an event driven projection unique on (product_id, outlet_id, business_id). They are separate tables with overlapping names, and a tenant schema ends up with both.
inventory_transactions allows a null inventory_id. The header of src/migrations/tenant/034_nullable_inventory_transaction_id.js explains why. A transaction for a raw material has no row in inventory, so the old NOT NULL constraint forced writers to use a zero UUID placeholder, and that placeholder broke any query joining the two tables. The migration drops the constraint and rewrites the placeholders to null.
Two check constraints exist to keep bad rows out. inventory_reservations carries chk_reservation_item_product, which requires at least one of item_id or product_id, because a reservation may target either. product_combo_dependencies carries chk_combo_not_self, which forbids a product depending on itself.
sales_velocity and ingredient_consumption_rates are materialized tables. The comments in src/migrations/tenant/019_enterprise_inventory_platform.sql call them materialized view tables. They hold precomputed counts keyed by period_days, not raw events.
Unique constraints that Sequelize helpers could not express are added with raw SQL. src/migrations/tenant/002_rbac_tables.js runs ALTER TABLE twice per table to drop and re-add roles_org_name_unique and role_perm_unique.
user_menu_permissions.permissions is JSONB, not a join table. The migration src/migrations/legacy/20250424100000-create-user-menu-permissions.js stores the whole menu map in one column, keyed unique on (user_id, organization_id). This is the table behind GET /api/me/menu-permissions.
schema_versions exists twice. public.schema_versions tracks the control plane and is created in src/migrations/legacy/control-plane/001_init_control_plane.sql. The tenant schema_versions is created in src/migrations/tenant/010_staff_tables.js and carries version and migration_name per tenant.
users.outlet_ids is a JSONB array next to the outlet_users table. Both describe the same assignment. authService.enrichUserContext reads outlet_ids from the user row first and only then falls back to outlet_users, so the two can disagree and the column wins.
One model declares a table that no migration creates. src/models/purchaseOrderItemModel.js maps to purchase_order_items, and a search of src/migrations for that name finds no CREATE TABLE. Do not assume the table exists in a live tenant schema.
Sources
AGENTS.mdat the workspace rootbrewlabs-billu-backend/src/architecture/centralAssociations.jsbrewlabs-billu-backend/src/control_plane_models/index.jsbrewlabs-billu-backend/src/control_plane_models/outletUserModel.jsbrewlabs-billu-backend/src/control_plane_models/permissionOverrideModel.jsbrewlabs-billu-backend/src/core/tenant/unifiedTenantMiddleware.jsbrewlabs-billu-backend/src/db/schema/control_plane_schema.sqlbrewlabs-billu-backend/src/db/schema/tenant_schema.sqlbrewlabs-billu-backend/src/migrations/legacy/20250413_saas_multi_tenancy_setup.sqlbrewlabs-billu-backend/src/migrations/legacy/20250415_permission_overrides_and_hierarchy.sqlbrewlabs-billu-backend/src/migrations/legacy/20250424100000-create-user-menu-permissions.jsbrewlabs-billu-backend/src/migrations/legacy/control-plane/001_init_control_plane.sqlbrewlabs-billu-backend/src/migrations/legacy/fix_outlet_users_fk.jsbrewlabs-billu-backend/src/migrations/tenant/001_create_outlets.jsbrewlabs-billu-backend/src/migrations/tenant/002_rbac_tables.jsbrewlabs-billu-backend/src/migrations/tenant/003_business_tables.jsbrewlabs-billu-backend/src/migrations/tenant/004_inventory_tables.jsbrewlabs-billu-backend/src/migrations/tenant/005_order_tables.jsbrewlabs-billu-backend/src/migrations/tenant/006_partner_tables.jsbrewlabs-billu-backend/src/migrations/tenant/007_accounting_tables.jsbrewlabs-billu-backend/src/migrations/tenant/008_content_tables.jsbrewlabs-billu-backend/src/migrations/tenant/009_tracking_tables.jsbrewlabs-billu-backend/src/migrations/tenant/010_staff_tables.jsbrewlabs-billu-backend/src/migrations/tenant/019_enterprise_inventory_platform.sqlbrewlabs-billu-backend/src/migrations/tenant/020_product_availability_cache.jsbrewlabs-billu-backend/src/migrations/tenant/021_product_availability_enhanced.jsbrewlabs-billu-backend/src/migrations/tenant/021_stock_alerts.jsbrewlabs-billu-backend/src/migrations/tenant/022_modifier_recipes.jsbrewlabs-billu-backend/src/migrations/tenant/023_add_product_variants.jsbrewlabs-billu-backend/src/migrations/tenant/024_update_recipe_constraints.jsbrewlabs-billu-backend/src/migrations/tenant/026_create_notification_tables.jsbrewlabs-billu-backend/src/migrations/tenant/027_qr_table_ordering.jsbrewlabs-billu-backend/src/migrations/tenant/028_guest_sessions_and_refactor.jsbrewlabs-billu-backend/src/migrations/tenant/029_fix_table_sessions_columns.jsbrewlabs-billu-backend/src/migrations/tenant/030_manual_payment_verification.jsbrewlabs-billu-backend/src/migrations/tenant/031_add_menu_version_to_outlets.jsbrewlabs-billu-backend/src/migrations/tenant/034_nullable_inventory_transaction_id.jsbrewlabs-billu-backend/src/migrations/tenant/035_stock_transfers.jsbrewlabs-billu-backend/src/migrations/tenant/036_inventory_price_history.jsbrewlabs-billu-backend/src/migrations/tenant/037_add_ingredient_cost_to_order_items.jsbrewlabs-billu-backend/src/migrations/tenantMigrationEngine.jsbrewlabs-billu-backend/src/models/orderModel.jsbrewlabs-billu-backend/src/models/purchaseOrderItemModel.jsbrewlabs-billu-backend/src/services/authService.jsbrewlabs-billu-backend/src/utils/constants.jsbrewlabs-billu-backend/src/utils/entityScopeRegistry.js