Saltar al contenido principal

Restaurant

In the restaurant industry, business models are typically categorized by their service style, price point, and operational flow. Based on the architecture we are building—which includes real-time WebSockets, physical table management, kitchen routing, and third-party delivery integration—we can support almost every major category.

Document version: 1.2 · Last updated: 2026-03-24 · See Related artifacts for migration files and specs. Normative MVP = Phases 1–3; see Quick start scope.

Quick start (MVP)​

For Phase 1–3 implementation (Foundation, Bills & splits, Kitchen & print):

  1. Read Schema status and migration requirements
  2. Add migrations per Reference schema
  3. See MVP scope for in-scope vs optional
  4. Follow Implementation phases table

Key tables: order, order_item, dining_area, dining_table, order_bill, order_bill_mapping, order_bill_payment, kitchen_station, product_station_assignment, print_job. Add sale.session_id if missing.

Scope of this document:

Part A: Design decisions​

Canonical terminology​

  • Bill vs check: In this document we use bill for the sub-invoice (what the guest pays). Tables: order_bill, order_bill_mapping. Industry terms "split check" and "check" refer to the same concept.
  • Merged tables: We recommend Option B — order.parent_order_id — for combined tables (one parent order; merged orders reference it). Option A (order_tables many-to-many) is possible if you need multiple tables per order without a single "primary" order.

Shared catalogs vs restaurant-only tables​

Design principle: Keep table structures for the restaurant module separate from the retail document flow, but share common catalogs. No duplicate product or inventory; one source of truth for master data.

RoleTablesNotes
Shared (catalogs & drawer)business, location, user, product, category, inventory, inventory_detail, inventory_ledger, currency, tax_definition, unit_of_measure, supplier, brand, customer, booking_platform, document_counter, parameter_catalog, entity_parameter, payment_method, production_run, production_run_input, production_run_output, register, cash_register_session, safe, cash_movementCatalogs: used by both retail and restaurant (e.g. order_item → product). Drawer/session: same register, session, safe and cash_movement are used for both retail sales and restaurant bill payments — one drawer, one shift; closing and Z reports include both.
Retail-only (documents)saleRetail sales document (sale_detail, payment_detail, session_id — add via migration if not present). Restaurant does not create sales for orders; it uses order_bill + order_bill_payment linked to the same session.
Restaurant-only (documents / flow)dining_area, dining_table, order, order_item, order_bill, order_bill_mapping, order_bill_payment, kitchen_station, product_station_assignment, print_job, and optionally product_modifier_group, product_modifier, product_recipeRestaurant-specific structures. Bill payments use order_bill_payment (Option A). They reference shared catalogs and use the same register/session/safe.
  • order_item stores product_id (FK to shared product); modifiers/notes are on the order item. Recipe and inventory deduction use shared product and inventory (and optional product_recipe / production_run).
  • Pricing: Restaurant can use product.price (and optional future product_location_price) from the shared catalog; no separate “restaurant product” table.
  • Reporting: Shared catalogs allow unified reporting (e.g. sales by product across retail sales and restaurant orders) while keeping document models (sale vs order/bill) separate.

Table of contents​

Part A: Canonical terminology, Shared catalogs, Schema status, MVP scope, Reference schema
Part B: FlowPOS structures, How restaurant integrates, Processes
Part C: Restaurant types · Core structures · Split checks · Offline/print · Bridge app Part D: Multi-branch · People and variations · Supported vs gaps · Kiosks Phase 6 · Implementation phases · API/WebSocket · Related artifacts

Schema status and migration requirements​

This section records schema decisions and pending migrations so the document and codebase stay in sync.

Tables to add via migrations​

Migration scopeTables / changesNotes
Restaurant coreorder, order_item, dining_area, dining_table, order_bill, order_bill_mapping, order_bill_payment, kitchen_station, product_station_assignment, print_jobRestaurant tables are not in the onboarding migration; add them in a new migration file.
Existing sale tableAdd session_id (FK to cash_register_session)Links retail sales to the active cash register session for reconciliation. Add via ALTER TABLE sale ADD COLUMN session_id uuid REFERENCES cash_register_session(id).
product_recipeNew tableLinks product_id to ingredient_ids (products with inventory_type = 'RawMaterial'). Requires its own migration and must be consistent with production_run, inventory_ledger, and production_run_input / production_run_output.

Bill payment integration (chosen: Option A)​

Option A is the chosen approach for restaurant bill payments:

  • Add order_bill_payment (or equivalent) with: order_bill_id, amount, payment_method_id (FK to payment_method), cash_register_session_id, optional safe_id.
  • Session totals and closing logic include these payments directly; no sale document is created for bill payments.
  • Shift closing and Z reports must include retail sales (sale) plus restaurant bill payments (order_bill_payment) for the session.

Enum naming convention​

Migrations must follow the existing FlowPOS enum naming pattern (e.g. sale_status, inventory_types). Use plural/suffixed names:

ConceptEnum name in migrations
Order statusorder_statuses
Order item statusorder_item_statuses
Order typeorder_types
Dining table statusdining_table_statuses
Bill statusorder_bill_statuses
Print job statusprint_job_statuses

Define TypeScript enums that map to these DB enums and use them consistently in the application layer.

MVP scope​

ScopePhaseIn scopeOut of scope (optional)
Phase 1–3Foundation, Bills, Kitchendining_area, dining_table, order, order_item, order_bill, order_bill_mapping, order_bill_payment, kitchen_station, product_station_assignment, print_job, sale.session_idorder_guest, order_party, product_recipe, modifiers
Phase 4Bridge appKDS + printer profiles—
Phase 5People & aliasesOptional: order_guest, order_party, order_item.seat_no, dining_table.alias, order.order_alias—
Phase 6KioskOptional: device, kiosk_config, kiosk_session, fulfillment tables—

Reference schema​

Canonical SQL for core restaurant tables (use in migrations):

-- Enums (use order_statuses, order_item_statuses, order_types, dining_table_statuses, order_bill_statuses, print_job_statuses)
CREATE TYPE order_statuses AS ENUM ('draft','sent_to_kitchen','partially_served','served','paid','cancelled');
CREATE TYPE order_item_statuses AS ENUM ('pending','preparing','ready','served','cancelled');
CREATE TYPE order_types AS ENUM ('dine_in','take_out','delivery_internal','delivery_third_party');
CREATE TYPE dining_table_statuses AS ENUM ('available','occupied','reserved','dirty');
CREATE TYPE order_bill_statuses AS ENUM ('pending','paid','cancelled');
CREATE TYPE print_job_statuses AS ENUM ('pending','sent','printed','failed');

-- dining_area (groups tables per location)
CREATE TABLE dining_area (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  business_id uuid NOT NULL REFERENCES business(id) ON DELETE CASCADE,
  location_id uuid NOT NULL REFERENCES location(id) ON DELETE CASCADE,
  name varchar NOT NULL,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid REFERENCES "user"(id) ON DELETE SET NULL,
  updated_at timestamptz,
  updated_by uuid REFERENCES "user"(id) ON DELETE SET NULL
);

-- dining_table
CREATE TABLE dining_table (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  business_id uuid NOT NULL REFERENCES business(id) ON DELETE CASCADE,
  area_id uuid NOT NULL REFERENCES dining_area(id) ON DELETE CASCADE,
  table_number varchar NOT NULL,
  capacity int NOT NULL DEFAULT 4,
  status dining_table_statuses NOT NULL DEFAULT 'available',
  metadata jsonb DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid REFERENCES "user"(id) ON DELETE SET NULL,
  updated_at timestamptz,
  updated_by uuid REFERENCES "user"(id) ON DELETE SET NULL
);

-- order
CREATE TABLE "order" (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  business_id uuid NOT NULL REFERENCES business(id) ON DELETE CASCADE,
  location_id uuid NOT NULL REFERENCES location(id) ON DELETE CASCADE,
  status order_statuses NOT NULL DEFAULT 'draft',
  order_type order_types NOT NULL DEFAULT 'dine_in',
  table_id uuid REFERENCES dining_table(id) ON DELETE SET NULL,
  waiter_id uuid REFERENCES "user"(id) ON DELETE SET NULL,
  parent_order_id uuid REFERENCES "order"(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  updated_at timestamptz,
  updated_by uuid REFERENCES "user"(id) ON DELETE SET NULL
);

-- order_item (unit_price_snapshot and tax_amount_snapshot for audit-safe billing; set when item is added)
CREATE TABLE order_item (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id uuid NOT NULL REFERENCES "order"(id) ON DELETE CASCADE,
  product_id uuid NOT NULL REFERENCES product(id) ON DELETE CASCADE,
  status order_item_statuses NOT NULL DEFAULT 'pending',
  course_number int NOT NULL DEFAULT 1,
  quantity quantity_dec NOT NULL DEFAULT 1,
  unit_price_snapshot money_minor NOT NULL,
  tax_amount_snapshot money_minor NOT NULL DEFAULT 0,
  modifiers jsonb DEFAULT '[]',
  notes text,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid REFERENCES "user"(id) ON DELETE SET NULL
);

-- order_bill
CREATE TABLE order_bill (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id uuid NOT NULL REFERENCES "order"(id) ON DELETE CASCADE,
  business_id uuid NOT NULL REFERENCES business(id) ON DELETE CASCADE,
  bill_number varchar(50),
  status order_bill_statuses NOT NULL DEFAULT 'pending',
  subtotal money_minor NOT NULL,
  tax_amount money_minor NOT NULL DEFAULT 0,
  tip_amount money_minor NOT NULL DEFAULT 0,
  total money_minor NOT NULL,
  is_exported_to_fel boolean DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- order_bill_mapping
CREATE TABLE order_bill_mapping (
  bill_id uuid NOT NULL REFERENCES order_bill(id) ON DELETE CASCADE,
  order_item_id uuid NOT NULL REFERENCES order_item(id) ON DELETE CASCADE,
  split_quantity quantity_dec NOT NULL,
  PRIMARY KEY (bill_id, order_item_id)
);

-- order_bill_payment (Option A: direct payments, no sale document)
CREATE TABLE order_bill_payment (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_bill_id uuid NOT NULL REFERENCES order_bill(id) ON DELETE CASCADE,
  amount money_minor NOT NULL,
  payment_method_id uuid NOT NULL REFERENCES payment_method(id) ON DELETE RESTRICT,
  cash_register_session_id uuid NOT NULL REFERENCES cash_register_session(id) ON DELETE RESTRICT,
  safe_id uuid REFERENCES safe(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid REFERENCES "user"(id) ON DELETE SET NULL
);

-- kitchen_station
CREATE TABLE kitchen_station (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  business_id uuid NOT NULL REFERENCES business(id) ON DELETE CASCADE,
  location_id uuid NOT NULL REFERENCES location(id) ON DELETE CASCADE,
  name varchar NOT NULL,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid REFERENCES "user"(id) ON DELETE SET NULL,
  updated_at timestamptz,
  updated_by uuid REFERENCES "user"(id) ON DELETE SET NULL
);

-- product_station_assignment (routes products or categories to kitchen stations for send-to-kitchen)
CREATE TABLE product_station_assignment (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  business_id uuid NOT NULL REFERENCES business(id) ON DELETE CASCADE,
  location_id uuid NOT NULL REFERENCES location(id) ON DELETE CASCADE,
  station_id uuid NOT NULL REFERENCES kitchen_station(id) ON DELETE CASCADE,
  product_id uuid REFERENCES product(id) ON DELETE CASCADE,
  category_id uuid REFERENCES category(id) ON DELETE CASCADE,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid REFERENCES "user"(id) ON DELETE SET NULL,
  CONSTRAINT product_station_assignment_source CHECK (product_id IS NOT NULL OR category_id IS NOT NULL)
);
CREATE INDEX idx_product_station_assignment_station ON product_station_assignment (station_id);
CREATE INDEX idx_product_station_assignment_product ON product_station_assignment (product_id) WHERE product_id IS NOT NULL;
CREATE INDEX idx_product_station_assignment_category ON product_station_assignment (category_id) WHERE category_id IS NOT NULL;

-- print_job
CREATE TABLE print_job (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_item_id uuid REFERENCES order_item(id) ON DELETE SET NULL,
  station_id uuid NOT NULL REFERENCES kitchen_station(id) ON DELETE CASCADE,
  status print_job_statuses NOT NULL DEFAULT 'pending',
  attempts int NOT NULL DEFAULT 0,
  last_error text,
  created_at timestamptz NOT NULL DEFAULT now(),
  printed_at timestamptz
);

-- Indexes for list/filter performance (normative MVP)
CREATE INDEX idx_dining_area_location ON dining_area (location_id);
CREATE INDEX idx_dining_table_area_status ON dining_table (area_id, status);
CREATE INDEX idx_order_location_status ON "order" (location_id, status);
CREATE INDEX idx_order_table ON "order" (table_id) WHERE table_id IS NOT NULL;
CREATE INDEX idx_order_created_at ON "order" (created_at);
CREATE INDEX idx_order_item_order ON order_item (order_id);
CREATE INDEX idx_order_bill_order ON order_bill (order_id);
CREATE INDEX idx_order_bill_payment_bill ON order_bill_payment (order_bill_id);
CREATE INDEX idx_order_bill_payment_session ON order_bill_payment (cash_register_session_id);
CREATE INDEX idx_kitchen_station_location ON kitchen_station (location_id);
CREATE INDEX idx_print_job_station_status ON print_job (station_id, status);

Session close logic: system_expected_amount = opening_amount + sum(sale totals where sale.session_id = session) + sum(order_bill_payment.amount where order_bill_payment.cash_register_session_id = session) + sum(cash_movement amounts). FEL is per order_bill, not per order_bill_payment; one DTE per paid bill.

Register and restaurant: Bill payments that use cash must link to cash_register_session_id (and optionally safe_id for safe drops). Card payments may also link to session for reconciliation. All payments that affect the drawer use the same register / cash_register_session as retail.

dining_area and location: dining_area has location_id; each area belongs to one location. Multi-branch: each location has its own dining areas and tables.

Part B: Core flows​

Existing FlowPOS structures and processes (drawer & session — shared)​

These structures and behaviours already exist in FlowPOS and are shared by both retail and the restaurant module: same register, session, safe and cash movements. The restaurant model uses them for bill payments and shift closing; it does not replace them. One drawer, one shift; closing and Z reports include retail sales and restaurant bill payments.

Tables (existing)​

TablePurpose
registerPOS terminal per location. Columns: business_id, location_id, name, is_active, optional safe_id. One open cash register session per register at a time.
cash_register_sessionShift for a cashier at a register. Columns: register_id, cashier_id, status (open/closed), opened_at, closed_at, opening_amount, closing_amount, system_expected_amount, difference, discrepancy fields, cash_denomination_breakdown (json). Immutable once closed.
safePhysical cash safe per location. Columns: business_id, location_id, name, is_active. Used for safe drops and cash movements.
cash_movementIn-session movements. Columns: session_id, type (cash_in, cash_out, safe_drop, tip_in, tip_out, expense), amount, optional safe_id. All movements belong to a session.
saleRetail sales document. Columns: business_id, location_id, customer_id, status, sale_detail (json: items), payment_detail (json: payments by method), cost_detail, optional session_id (FK to cash_register_session; add via migration if not present), document_number, FEL fields.

Behaviours (existing)​

  • Session linking: When creating a sale, if session_id is not provided, the backend can resolve an open session by createdBy (cashier) and locationId and attach it. Sales are thus tied to the active shift for reconciliation.
  • Parameter: REQUIRE_CASH_REGISTER_SESSION_FOR_ADD_DOCUMENT (entity parameter per business/location/document type). When true, the PWA (e.g. SalePage) blocks creating a new sale until there is at least one open cash register session for the current cashier and location. This enforces “no sale without an open drawer.”
  • Safe and movements: Cash movements (cash in/out, safe drop, tips, expense) reference session_id and optionally safe_id. Registers can have a default safe_id for safe drops. Deleting a safe is blocked if any cash movement references it.
  • Document number: document_counter is used to generate document_number per business and document type (e.g. SALE).
  • FEL: Sale has FEL-related columns; certification is triggered per sale document.

Processes (existing, unchanged)​

  1. Open session: Cashier selects location and register, enters opening cash (and optional denomination breakdown). System creates cash_register_session with status open.
  2. During shift: Sales created with optional/auto session_id; cash movements (cash in/out, safe drop, etc.) recorded with session_id and optional safe_id.
  3. Close session: Cashier enters counted cash; system computes system_expected_amount from session’s sales and movements, compares to closing_amount, records difference and optional discrepancy reason/approval. Session becomes closed and immutable. Z report and session PDF can be generated.

Restaurant features must not remove or bypass these flows. Payments against restaurant bills (order_bill) that involve cash should integrate with the same session (and safe where applicable) so that closing and Z reports remain correct.

How restaurant integrates with existing flows​

  • Retail vs restaurant document model

    • Retail: One sale = one document with sale_detail (items) and payment_detail (payments). Optionally linked to one cash_register_session via sale.session_id.
    • Restaurant: One order = many order_items over time. One order can have many order_bills (split checks). Each bill is a sub-invoice; payments apply to a bill, not directly to the order. So: Order → Order_bill(s) → Payment(s) per bill.

  • Cash and session integration

    • When a guest pays an order_bill (especially with cash), that payment should be tied to the same cash register session and drawer as retail. Chosen approach: Option A (see Schema status and migration requirements).
      • Option A (chosen): Add order_bill_payment with order_bill_id, amount, payment_method_id (FK to payment_method), cash_register_session_id, optional safe_id. Session totals and closing logic include these payments directly. No sale document is created for bill payments.
    • Shift closing and Z reports must include all cash (retail sales + restaurant bill payments) for the session.

  • FEL

    • Each order_bill that requires a DTE gets its own FEL certificate (same pattern as one DTE per sale). Existing FEL/certifier integration applies per bill; no change to retail sale FEL.

  • Document numbers

    • Use document_counter for a new document type (e.g. ORDER_BILL or BILL) to generate bill_number for order_bill, so bill numbers are unique per business and auditable.

  • Entities and parameters

    • Location and business stay as they are. Restaurant orders and tables are scoped by business_id and, where needed, location_id (e.g. dining_area per location).
    • Optional entity parameters for restaurant (e.g. require open session before accepting bill payment, or default order type) can follow the same pattern as REQUIRE_CASH_REGISTER_SESSION_FOR_ADD_DOCUMENT (used today in the retail Sale flow: the PWA blocks creating a sale when the parameter is true and there is no open cash register session for the current cashier and location).

  • Register, session, safe, cash_movement (shared)

    • Register, cash_register_session, safe and cash_movement are used by both retail and the restaurant module. The same physical drawer and shift handle retail sales and restaurant bill payments; session totals and closing must include both. No separate restaurant drawer unless the business explicitly adds another register.

Processes (retail vs restaurant)​

ProcessRetail (existing)Restaurant (to add)
DocumentSale: one-off basket → sale_detail + payment_detail → one document, one FEL (if applicable).Order: ongoing basket; order_item(s) added over time; one or more order_bill(s); each bill can be paid separately; each paid bill → one FEL (if applicable).
SessionSale optionally/automatically linked to open cash_register_session (cashier + location).Bill payment (cash/card) should link to same cash_register_session (and safe when applicable) so closing includes restaurant cash.
Shift openCashier opens session on a register; opening amount recorded.Same; no change.
Shift closeCashier counts cash; system compares to expected (sales + movements); discrepancy handling; session closed.Same; expected amount must include restaurant bill payments that are tied to this session.
SafeCash movements (safe_drop, etc.) reference safe_id.Restaurant cash payments that go to a safe use the same safe and movement types.
FELOne DTE per sale when required.One DTE per order_bill when required (split checks = multiple DTEs).

Structures at a glance​

LayerShared (catalogs)Retail-onlyRestaurant-only
Tenant / branchbusiness, location——
People / orguser, customer, supplier——
Products & inventoryproduct, category, inventory, inventory_detail, inventory_ledger, production_run*——
Pricing / taxcurrency, tax_definition, unit_of_measure, payment_method——
POS / drawerregister, cash_register_session, safe, cash_movement—Same drawer/session/safe for both retail and restaurant bill payments.
Sales document—sale (sale_detail, payment_detail, session_id)—
Space——dining_area, dining_table
Order——order, order_item (reference shared product)
Bills / splits——order_bill, order_bill_mapping
Menu / modifiersproduct, category—product_modifier_group, product_modifier, product_recipe (optional; reference shared product)
Kitchen——kitchen_station, product_station_assignment, print_job
Numbering / configdocument_counter, parameter_catalog, entity_parameter—Same counters/params; add e.g. ORDER_BILL type

* Production runs are shared (e.g. bakery); restaurant orders consume products that may come from shared inventory and production.

Part C: Domain model (mix: normative concepts + future/exploratory)​

1. Types of Restaurants (Industry Standard) (context only)​

  • Full-Service (Fine & Casual Dining): High focus on table management, multi-course pacing, and split checks.
  • Quick Service (QSR / Fast Food): Focus on speed, counter service, and immediate kitchen notification.
  • Fast Casual: A hybrid where you order at the counter but sit down; food is brought to you.
  • Ghost Kitchens (Dark Kitchens): 100% delivery-focused, often running multiple "brands" out of one kitchen.
  • Bars & Pubs: Focus on "open tabs" and high-volume beverage inventory.
  • Bakeries & Cafes: High volume of small items, often involving raw material processing (flour to bread).

2. Which ones can we support with our structure?​

Our current PostgreSQL + NestJS architecture is specifically designed to handle the most complex scenarios, making it highly versatile:

A. Full-Service & Casual Dining​

  • Supported by: The dining_area and dining_table structures.
  • Why: We can manage physical table layouts, track "occupied" status in real-time via WebSockets, and handle complex "Split Checks" through the order_bill and order_bill_mapping tables.

B. Ghost Kitchens & Delivery-First Models​

  • Supported by: The booking_platform table and third-party integration layer (Uber Eats, Rappi).
  • Why: The structure includes commission_percentage and external mapping fields, allowing a single kitchen to track orders coming from multiple digital storefronts simultaneously.

C. Bakeries & Production-Heavy Cafes​

  • Supported by: The production_run and inventory_types structures.
  • Why: We have a robust system for tracking "Raw Materials" and "Finished Goods". The production_run table (supporting baking, mixing, and assembly) allows these businesses to track the transformation of ingredients into sellable products, which is a major pain point in standard POS systems.

D. Quick Service (QSR)​

  • Supported by: The "Hybrid" Bridge App (KDS + Printer).
  • Why: By bypassing table management and sending orders directly to the kitchen_station queue, the system supports the high-speed "Order → Pay → Prepare" flow.

E. Bars & High-Volume Beverage​

  • Supported by: The order_item status tracking (pending → ready → served).
  • Why: Waiters can keep an order "Open" while continuously adding items via the WebSocket-enabled PWA, with the Bar station KDS receiving the drink orders instantly.

Summary: Our "Niche" Strength​

While we can support any of these, our structure is strongest for "Production-Retail-Service" hybrids.

Example: A restaurant that makes its own bread (Production), sells wine by the bottle (Retail), and offers a sit-down menu (Service). Most POS systems handle one or two of these well; our schema handles all three in a single source of truth.

To transition your system from a retail-centric model to a robust restaurant platform using the PostgreSQL + WebSockets approach, you add new layers for physical space, menu customization, and order state. Existing retail structures (sale, register, cash_register_session, safe, cash_movement) stay as-is; see Existing FlowPOS structures (drawer & session) and How restaurant integrates.

Below are the new structures and the architectural plan, while keeping real-time performance.

1. Physical Space & Table Management​

In a restaurant, the "Order" is often tied to a physical location before it is tied to a customer.

  • dining_area Table: Groups tables (e.g., "Terrace", "Main Room", "Bar").
  • dining_table Table:
    • id, business_id, area_id.
    • table_number (String).
    • capacity (Int).
    • status: (Enum dining_table_statuses: available, occupied, reserved, dirty).
    • metadata: jsonb (For X/Y coordinates to render the map in your React PWA).

2. The "Menu" Layer (Modifiers & Recipes)​

Unlike retail products, restaurant items are highly customizable.

  • product_modifier_group Table: (e.g., "Toppings", "Sides", "Doneness").
    • min_selection, max_selection (For UI validation).
  • product_modifier Table: (e.g., "Extra Cheese", "Medium Well").
    • price_adjustment: (Uses your money_minor domain).
  • product_recipe Table:
    • Links a product_id (finished good) to various ingredient_ids (products with inventory_type = 'RawMaterial').
    • Logic: When an order is sent/paid, the system deducts ingredients from stock (or via production_run).
    • Migration: product_recipe is not in the onboarding migration. Add it in its own migration and ensure consistency with production_run, inventory_ledger, production_run_input, and production_run_output.

3. Real-time Order & Check Management​

This is where you replace Firebase. By using a jsonb column for "modifiers" and "notes," you get the flexibility of NoSQL inside the safety of PostgreSQL.

Order lifecycle (state diagram):

stateDiagram-v2
  [*] --> draft
  draft --> sent_to_kitchen: Send to kitchen
  sent_to_kitchen --> partially_served: Some items ready
  partially_served --> served: All items served
  served --> paid: All bills paid
  draft --> cancelled: Cancel
  sent_to_kitchen --> cancelled: Cancel
  paid --> [*]
  cancelled --> [*]

Bill payment flow:

sequenceDiagram
  participant Waiter as PWA (Waiter)
  participant API as NestJS API
  participant DB as PostgreSQL
  participant Session as cash_register_session

  Waiter->>API: POST /order-bills/:id/payments
  API->>DB: Insert order_bill_payment (session_id, amount, payment_method)
  API->>DB: Update order_bill.status = paid
  API->>Session: Session totals now include order_bill_payment
  API->>Waiter: 200 OK

Split check flow:

flowchart LR
  O[Order] --> OI1[order_item 1]
  O --> OI2[order_item 2]
  O --> OI3[order_item 3]
  OI1 --> OB1[order_bill A]
  OI2 --> OB1
  OI2 --> OB2[order_bill B]
  OI3 --> OB2
  OB1 --> OBP1[order_bill_payment]
  OB2 --> OBP2[order_bill_payment]
  OBP1 --> S[Session]
  OBP2 --> S
  • order Table updates:
    • status: (Enum order_statuses: draft, sent_to_kitchen, partially_served, served, paid, cancelled).
    • order_type: (Enum order_types: dine_in, take_out, delivery_internal, delivery_third_party).
    • table_id: Nullable (for non-dine-in).
    • waiter_id: Link to user.id.
  • order_item Table:
    • status: (Enum order_item_statuses: pending, preparing, ready, served, cancelled — see Reference schema).
    • course_number: (Int: 1 for Drinks, 2 for Starters, 3 for Mains).
    • unit_price_snapshot, tax_amount_snapshot: (money_minor) — set when item is added for audit-safe billing.
    • modifiers: jsonb (Stores the array of selected product_modifier objects).
    • notes: text (e.g., "Allergy to nuts").
  • Split checks (bills): Use order_bill and order_bill_mapping (see Split checks & merged tables). Each bill is a sub-invoice; payments link to order_bill.id.

4. Kitchen & Printing Logic​

  • kitchen_station Table: (e.g., "Cold Kitchen", "Pizza Oven", "Bar").
  • product_station_assignment: Mapping products or categories to stations.
  • print_job Table (Queue for your React Native Desktop App):
    • When an order is "Sent to Kitchen," NestJS creates records here.
    • The Desktop App polls or receives a WebSocket event, prints, and marks as printed.

5. Third-Party Integrations (Uber Eats, Rappi)​

To manage external platforms alongside your internal operations:

  • external_platform_mapping Table:
    • product_id (Internal).
    • platform_id (References booking_platform.id).
    • external_sku: The ID used by Uber/Rappi.
  • external_order_metadata Table:
    • Stores platform-specific info like courier_name, estimated_arrival, and platform_order_number.

6. The "PostgreSQL + WebSockets" Alternative​

How it works without Firebase:

  1. NestJS Gateway: Create a WebSocket Gateway in NestJS.
  2. State Sync: When a waiter adds an item, the PWA sends a add_item event via WebSocket.
  3. Persistence: NestJS immediately updates the PostgreSQL order_item table.
  4. Broadcast: Upon successful DB update, NestJS "emits" the updated order state to all connected clients in that business_id (Waiters see the table as "occupied," Kitchen sees a new ticket).
  5. Local Storage: The PWA can use IndexedDB to store the draft locally if the connection drops, and "replay" the events once the WebSocket reconnects.

This architecture ensures that your PostgreSQL database remains the single source of truth for both active service and final financial reporting in Metabase.

This is the most common point of failure in a restaurant. Because you are using a Queue-based approach (print_job table) instead of a direct "fire and forget" message, you have a built-in safety net.

Here is how you should handle these "Offline" scenarios to ensure no burger is ever forgotten.

1. The "State-Based" Print Queue​

Don't just delete a job once it's sent. Use a status lifecycle in your print_job table.

Updated print_job Schema:

  • id: UUID.
  • order_item_id: Link to the food.
  • station_id: Which printer it belongs to.
  • status: (Enum: pending, sent, printed, failed).
  • attempts: Integer (increment every time the Desktop App tries to print).
  • last_error: Text (e.g., "Out of paper", "Printer not found").

2. Scenario A: The App is Closed (KDS Offline)​

Since the app is closed, it won't receive the WebSocket "New Job" event.

  • The "Startup Sync": When the React Native Desktop App is opened, the first thing it should do is a "Catch-up" API call: GET /print-jobs?status=pending&station_id=XYZ. It fetches everything it missed while it was closed.
  • The "Heartbeat" (Backend Awareness):
    • The Desktop App should send a "ping" to the NestJS server every 30 seconds.
    • NestJS stores this in a durable store (kitchen_station.last_seen_at in MVP; Redis/table also valid in future variants).
    • Result: If a waiter tries to send an order and the station hasn't been seen in 2 minutes, the PWA shows a warning: "⚠️ Warning: Pizza Kitchen is offline. Order will be queued."

3. Scenario B: The Printer is Off / Out of Paper (App is Open)​

In this case, the Desktop App receives the WebSocket event but the hardware fails.

  1. Hardware Error Catching: Your React Native app (using a library like react-native-esc-pos or a native bridge) will receive an error from the printer driver.
  2. Status Update: The Desktop App immediately calls the backend: PATCH /print-jobs/:id { status: 'failed', last_error: 'No paper' }.
  3. Real-time Alert: NestJS receives this update and emits a WebSocket event to the Waiters and the Manager Dashboard.
    • Notification: "Printer 'Bar' is out of paper! 1 pending job."

4. Handling "Zombie" Jobs (The Reconciliation Logic)​

What if the app received the job, the printer started printing, but the power went out halfway through?

  • Acknowledgment (ACK) Pattern:
    1. Backend sends job via WebSocket.
    2. Desktop App prints.
    3. Desktop App sends an "ACK" back to Backend.
    4. Only then does the Backend mark the job as printed.
  • The Cleanup Job: Every 5 minutes, a Cron job in NestJS looks for jobs that have been in sent status for more than 2 minutes but never moved to printed. It resets them to pending so the Desktop App can try again.

5. Local Redundancy (The "Last Resort")​

Since you are building the Desktop App in React Native/Electron, you can use a local database (like SQLite or WatermelonDB) inside the app.

  • If the App is open but the Internet goes down, the app can still display the orders on the KDS screen because it already received them via WebSocket (or had them in its local buffer).
  • Once the printer is turned back on, the app loops through its local "Unprinted" list and clears the queue.

Summary of Workflow for Reliability​

ProblemSolution
App ClosedGET /pending-jobs on startup + Backend Heartbeat check.
Printer OffApp catches driver error -> Updates DB to failed -> Alerts Waiter.
Internet DownApp stores jobs in local SQLite buffer; prints once reconnected.
Power OutageCron job resets sent jobs back to pending for retry.

By implementing the Heartbeat and the Status Lifecycle, you turn a "broken printer" from a restaurant nightmare into a simple, trackable system notification.

To support the dynamic "merging and splitting" typical of restaurant operations, the current structures need a Parent-Child relationship for Orders and a Junction Table for Items.

In a retail POS, 1 Order = 1 Payment = 1 Invoice. In a restaurant, you need a layer between the "What was eaten" and "Who is paying."

Here is how to update your structures to handle these complex scenarios:

1. Handling "Split Checks" (1 Order to N Bills)​

To split a check (by item or by amount), you should not duplicate the items. Instead, introduce a bill structure that acts as a container for specific items from the order.

  • order_bill Table:
    • id: UUID.
    • order_id: References order.id.
    • status: (Enum: pending, paid, cancelled).
    • total_amount: money_minor.
    • tax_amount: money_minor.
    • fel_uuid: (To store the specific Guatemala FEL certificate for this part of the split).
  • order_bill_mapping Table (The Junction):
    • bill_id: References order_bill.id.
    • order_item_id: References order_item.id.
    • split_quantity: quantity_dec (e.g. split a 1L bottle of wine between 2 bills).

Workflow: When the server clicks "Split Check," the PWA allows them to select items. The backend creates two order_bill records. Items are linked to these bills. When one bill is paid, the items on that bill are marked as "Paid," but the main order remains "Open" until all bills are settled.

2. Handling "Combined Tables" (N Tables to 1 Order)​

Sometimes a large group pushes three tables together. Your current order table likely has a table_id. To support merging, you have two choices:

  • Option A (Flexible): Change order.table_id to a many-to-many relationship via order_tables.
  • Option B (Simple, recommended): Add a parent_order_id to the order table.
    • When Table 5 joins Table 6, you mark Order #6 as "Merged" and set its parent_order_id to Order #5. All new items are then added to the Parent Order.

Recommendation: Use Option B unless you need to query "all orders that used this table" without following parent links. Option B keeps implementation and reporting simpler.

3. Updated Schema for Splits and Merges (Future / optional — MVP uses Reference schema only)​

For MVP (Phases 1–3) the canonical order_statuses enum does not include merged. Merged tables are represented by order.parent_order_id only (Option B). If you add a distinct merged status later (Phase 5+), use:

-- Optional: add 'merged' to order_statuses only if needed for reporting (not in MVP reference schema)
ALTER TYPE order_statuses ADD VALUE 'merged';

The following is redundant with the Reference schema order_bill/order_bill_mapping; use the Reference schema for migrations.

-- Structure to handle the actual Bill generation (the fiscal trigger)
CREATE TABLE order_bill (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id uuid REFERENCES "order"(id),
  business_id uuid REFERENCES business(id),
  bill_number varchar(50),  -- Internal tracking
  subtotal money_minor NOT NULL,
  tip_amount money_minor DEFAULT 0,
  total money_minor NOT NULL,
  is_exported_to_fel boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

-- Junction: which items (and how much) belong to which bill. Supports splitting 1 item across 2 bills.
CREATE TABLE order_bill_mapping (
  bill_id uuid REFERENCES order_bill(id),
  order_item_id uuid REFERENCES order_item(id),
  split_quantity quantity_dec NOT NULL,
  PRIMARY KEY (bill_id, order_item_id)
);

4. Logic for "The Move" (Moving Items Between Tables)​

Waiters often move a guest from the "Bar" to a "Table" once it becomes available.

  • The Structure Support: Because your order_item is linked to an order_id, you simply need a transfer_items function in your NestJS service.
  • The WebSocket Trigger: When items are moved, the NestJS server emits a table_update event. The Bar KDS removes the items, and the Dining Room KDS displays them (or alerts the waiter to move the physical drinks).

5. Why this is better than "Copy-Pasting" Data​

  1. Inventory Integrity: If you split a check, you aren't "creating" new items. You are just assigning who pays for them. Your inventory deduction (via the product_recipe or production_run) only happens once.
  2. Audit Trail: In Metabase, you can see that Order #100 was split into 3 Bills. This prevents "Employee Theft" where a waiter might delete an item from one bill and try to pocket the cash.
  3. FEL Compliance: In Guatemala, each split bill requires its own DTE (Documento Tributario Electrónico). This structure allows you to generate N different invoices for 1 single table session.

Summary: By adding the order_bill and order_bill_mapping tables, you gain the ability to handle Split by Item, Split by Seat, and Equal Splits, all while keeping your core order and order_item tables as the single source of truth for the kitchen.

[Future — Phase 4] In the context of a restaurant, you should definitely build one single app (a unified "Kitchen & Hardware Module") rather than two separate ones.

However, you should design it with "Mode Switches" or "Profiles."

Here is why a single app is the better architectural move for your stack, and how to handle the overlap you mentioned.

1. Why a Single App?​

  • Shared Hardware Bridge: Both the KDS and the Printer need to talk to the local network. By having one React Native Desktop app, you only have to manage one connection to the NestJS WebSocket and one set of local hardware drivers.
  • Easier Deployment: When you update your printing logic or the way you handle order JSONs, you only have to deploy one update to the restaurant's computers.
  • Device Versatility: Many small restaurants use a single tablet or touch-screen PC at the bar. They want to see the orders (KDS) and have that same device trigger the receipt printer.

2. The "Profile" Approach​

Instead of separate apps, let the user configure the app's Role upon login:

  • Role A: Printer-Only (Hidden): The app runs in the system tray. It has no UI. It just listens for print_job events and sends them to the thermal printer.
  • Role B: KDS-Only: A full-screen interactive UI for the chef. It displays the tickets.
  • Role C: Hybrid (KDS + Print): The chef sees the ticket on the screen, and the moment they "Accept" or "Complete" the ticket, the app automatically triggers a "Pick-up Slip" on the attached printer.

3. Handling the "Same Product" Scenario​

You mentioned that sometimes you need to both print and display the same product. In your PostgreSQL schema, this is handled by routing rules rather than app logic.

Example: The "Pizza" Workflow

  1. NestJS Logic: An order arrives with a "Pepperoni Pizza."
  2. Routing: Your backend sees that Category: Pizza is mapped to Station: Pizza_Oven.
  3. Action: NestJS creates a kitchen_ticket (for the KDS) and a print_job (for the printer) both assigned to the Pizza_Oven station ID.
  4. The App: The app at the Pizza station receives both. It displays the pizza on the screen so the chef can see the "No Onions" note, and it prints a sticky label to put on the pizza box once it’s done.

4. Technical Implementation in React Native (Desktop)​

Since you are using React Native for Desktop, you can leverage a single code base with conditional rendering:

JavaScript

`// Simplified Logic const App = () => { const { role } = useConfiguration(); // 'kds', 'printer', or 'hybrid'

return ( {/Hardware layer is always running in the background/} 

  {/* UI layer only shows if it's a KDS or Hybrid */}
  {(role === 'kds' || role === 'hybrid') && <KDS_Interface />}
  
  {/* If it's printer-only, show a simple status dashboard or minimize to tray */}
  {role === 'printer' && <PrinterStatusMiniView />}
</View>

); }`

5. Strategic Advantage for Retail​

Because you also have a Retail version, this "Hardware App" becomes your "Peripheral Bridge."

  • In Retail mode, the app handles the Barcode Scanner and the Cash Drawer.
  • In Restaurant mode, the same app handles the KDS and Kitchen Printers.

Verdict: Build one app called "FlowPOS Bridge" or "FlowPOS Station." It reduces your maintenance cost by 50% and allows for the "Hybrid" scenarios that restaurants love.

Yes, the current structures are fundamentally designed around locations (branches) to handle variations in stock, production, and operations.

The database schema explicitly includes a location table that acts as a child of the business, allowing each branch to have its own physical address, tax number, and active status (available to buy/sell).

Here is how the system currently accounts for branch-level variations:

1. Multi-Branch Operations​

  • Production Variation: The production_run table includes a location_id. This means you can track where a "Baking" or "Assembly" run is happening. A recipe made at the "Central Kitchen" branch can be tracked differently than one made at a "Small Express" branch.
  • Location States: Each branch can be independently "frozen" (e.g., for a physical inventory count) without affecting the others. This is critical for restaurant chains where one branch might be closed for maintenance while others remain open.

2. Location-Specific Inventory​

  • Stock Tracking: Although the full stock table details aren't shown in the snippet, the system is designed to link inventory and production events to specific location_id values.
  • Operational Control: You can toggle whether a specific branch is available_to_sell or available_to_buy. This allows you to have "Warehouse-only" locations that don't appear in the POS or "Front-of-house" branches that don't receive raw supplies.

3. What we need to add for Full Branch Variation​

While the foundation is there, to fully support a complex restaurant chain, we should ensure the following enhancements are added to the "PostgreSQL + WebSockets" structure:

  • Location-Specific Pricing: Currently, the product table is linked to the business_id. We should add a product_location_price table if you want the same burger to cost more in a "Zone 10" branch than in a "Rural" branch.
  • Regional Menus: If certain locations have different menus (e.g., a "Breakfast" branch vs. a "Late Night" bar branch), we will use a junction table between menu_category and location_id.
  • Regional Tax Rules: The tax_definition includes a country_id, but adding a location_id link would allow the system to handle different municipal taxes if the business expands across different regions.

Summary: The structure already treats the Location as a primary entity for production and physical state. Expanding this to pricing and menus will be a straightforward addition to the existing PostgreSQL schema.

Part D: Advanced (future / exploratory unless referenced by MVP)​

A restaurant POS can vary a lot depending on service style (full service, fast casual, bar, delivery, catering). Below are common variations/features you can support, including the “people/alias” ideas you mentioned.

People, guests, and aliases​

1) Guest count and seats​

  • guest_count at order level (simple)
  • Seat-based ordering: each item can be assigned to a seat number (seat_no)
    • Great for splitting checks and tracking who ordered what

2) Associate people with an order​

Different “people roles” can be linked:

  • server_id (waiter)
  • cashier_id
  • host_id
  • runner_id
  • bartender_id
  • kitchen_lead_id
  • optional: customer_id (if known)

Implementation pattern:

  • Either columns on order
  • Or a generic order_party table: order_id, role, user_id/customer_id

3) Aliases / nicknames​

You can allow:

  • Table alias: “VIP”, “Birthday”, “Team A”, “Smith Family”
  • Guest alias per seat: “Mom”, “Kid 1”, “Allergy—Peanuts”
  • Order alias: “To-go John”, “Uber #9281”

Schema ideas:

  • dining_table.alias (optional) + order.table_alias_snapshot
  • order_guest table: order_id, seat_no, guest_name (alias), notes, allergies

Snapshotting matters so history doesn’t change if you rename later.

Service-flow variations​

4) Courses & pacing​

  • Course routing: drinks → appetizers → mains → dessert
  • “Fire course” / “Hold” logic:
    • items can be hold_until_fired = true
    • kitchen releases when server triggers

5) Order lifecycle differences​

Some restaurants want:

  • sent_to_kitchen step

    Others do:

  • auto-send to kitchen on item add

    Bars might do:

  • send to bar immediately, kitchen later

So you might support a setting:

  • send_mode: manual | auto

6) Tabs / open checks (bar style)​

  • One guest opens a tab, adds items over time, pays at end
  • Pre-auth card (optional)
  • Transfer tab to another server

Payment and check variations​

7) Split checks in multiple ways​

  • Split by seat
  • Split by items
  • Split equally
  • Split by percent
  • Split by course

This affects data model:

  • order_bill + order_bill_mapping with split_quantity is the most flexible

8) Tips and gratuity rules​

  • Tips per check, per payment
  • Auto-gratuity for large parties
  • Tip pooling and distribution rules

9) Multiple tenders / partial payments​

  • One check paid with 2 cards + cash
  • Partial payment with remaining balance open

Discounts, comps, and promos​

10) Comps & voids (audit-heavy)​

Restaurants care about:

  • void item (before send)
  • cancel item (after send)
  • comp item (free but still counted)

Each needs:

  • reason code
  • authorizer user id (manager)
  • timestamps

11) Happy hour / time-based pricing​

Price rules based on:

  • time window
  • day of week
  • dining area (bar vs dining room)

Kitchen operations variations​

12) Station routing rules​

  • By product
  • By category
  • By modifier (e.g., “medium rare” routes to grill)
  • By dining area (patio has its own printer)

13) Kitchen Display System vs printing​

  • KDS screen (queue + timers)

  • Printers (tickets)

    Many places want both.

14) Expo & running​

  • “Ready” items become a queue for runners
  • Track who delivered (runner_id)
  • “Bump” flow (kitchen marks done, expo approves)

Delivery and off-premise variations​

15) Takeout / delivery timing​

  • promise time / pickup time
  • driver assignment
  • order packing status

16) Third-party marketplaces​

  • mapping SKUs
  • commission and payout tracking
  • special instructions handling

Reservations & seating variations​

17) Reservations + waitlist​

  • reservation linked to table + time
  • walk-in waitlist with SMS notify
  • table turn time metrics

18) Table transfer / merge / split​

  • move order from table A → table B
  • merge two tables into one party
  • split party across tables

This is why “table alias snapshot” + seat numbers are useful.

Operational controls (manager needs)​

19) Shift management & cash drawer​

  • open/close shift
  • cash drop
  • drawer reconciliation per cashier

20) Permissions & approvals​

  • manager approval for:
    • comps
    • discounts above threshold
    • reopen closed check
    • void after send

A clean way to model “people + aliases” (simple & scalable)​

If you want one flexible pattern:

  • order
    • guest_count
    • table_alias_snapshot
  • order_guest
    • order_id, seat_no, guest_alias, notes, allergies_jsonb
  • order_party
    • order_id, role, user_id (server/runner/bartender)

That covers:

  • assigning people to order
  • nicknames
  • seat-based split
  • audit trail

If you tell me what style you’re targeting first (full service restaurant vs fast casual vs bar), I can recommend the smallest MVP set of these variations so you don’t overbuild.

Mostly yes — your structure already supports a lot of those variations. A few need small extension tables/fields (especially seats/guests, split checks, and multi-staff roles).

✅ Already supported by your current structure​

Table + area + floor map​

  • dining_area, dining_table with status and metadata jsonb covers:
    • floor plan coordinates
    • table status (available/occupied/dirty/reserved)
    • assigning an order to a physical table

Assigning staff to an order​

  • order.waiter_id covers the common case (server assigned).

Item-level customization​

  • order_item.modifiers jsonb + notes supports:
    • “no onions”, “allergy”, “extra cheese”
    • future modifier schema changes without migrations

Kitchen workflow​

  • order_item.status supports:
    • pending → preparing → ready → served

Station + printing​

  • kitchen_station, product_station_assignment, print_job supports:
    • routing items to stations and printers/KDS-like flows

Third-party delivery​

  • external_platform_mapping + external_order_metadata supports:
    • Uber/Rappi SKU mapping + order metadata

⚠️ Partially supported (works, but you’ll want 1–2 extra fields)​

Aliases (table/person/order)​

You can do this, but decide where you want the data to live:

Table alias

  • Add: dining_table.alias (optional)

    or store it in dining_table.metadata.

Order alias

  • Add: order.alias (e.g., “Birthday party”, “To-go John”)

Person/guest alias

  • Needs a table (see next section).

❌ Not fully supported yet (recommended additions)​

1) Seat-based ordering / assigning people to items​

If you want “Seat 1 ordered this” or guest nicknames:

Add order_guest

  • id
  • order_id
  • seat_no (int)
  • guest_alias (text) // “Mom”, “Kid”, “VIP”
  • notes / allergies (text or jsonb)

Then add to order_item:

  • guest_id or seat_no

This unlocks:

  • split by seat
  • tracking allergies per person
  • item ownership

2) Multiple staff roles on the same order​

Right now you only have waiter_id. If you want bartender/runner/host:

Add order_party

  • order_id
  • role (enum: server, runner, bartender, host, cashier, etc.)
  • user_id

This avoids adding 10 nullable columns to order.

3) Split checks (the “correct” flexible way)​

Use order_bill (sub-invoice) and order_bill_mapping with split_quantity (see Canonical terminology). Same concept as "check" + "check_item"; payments link to order_bill.id. This supports:

  • split by seat
  • split by items
  • split evenly / percent
  • partial payments cleanly

4) Table moves, merges, split parties​

You can do basic moves by updating order.table_id.

If you want history/audit:

  • add order_table_history (optional)

Quick verdict​

✅ Yes, your structure supports the variations at a good baseline.

To fully cover “people + aliases + split-check + multi-role staff” you should add:

  1. order_guest (+ order_item.guest_id/seat_no)
  2. order_party
  3. order_bill + order_bill_mapping with split_quantity (qty-based split)

If you want, I can propose the exact columns + constraints for those 3 additions so they match your existing style (multi-tenant business_id, audit fields, status enums).

[Future — Phase 6] Yes — the model can support self-service kiosks, but you need a few extra structures to handle:

  • device identity & pairing
  • customer session / cart ownership
  • menu availability by kiosk/location/time
  • payments (intent, retries, failures)
  • order routing (send to kitchen, pickup screens)

Below is a complete, clean set of tables you can add (Postgres), designed to work with what you already described (dining_area, dining_table, order, order_item, modifiers, kitchen stations, print jobs, external platforms, WebSockets).

0) Key idea​

Kiosk orders are just orders where:

  • order.channel = 'kiosk'
  • order.customer_session_id points to a kiosk session/cart
  • no waiter is required
  • payment is usually attempted before the order is “paid/sent”
-- Device + sessions
CREATE TYPE device_type AS ENUM ('kiosk','kds','printer','pos_terminal');
CREATE TYPE device_status AS ENUM ('active','inactive','maintenance','blocked');

CREATE TYPE kiosk_session_status AS ENUM ('active','abandoned','checked_out','expired');

-- Order + items (use plural enum names per FlowPOS convention: order_statuses, order_item_statuses, order_types)
CREATE TYPE order_channel AS ENUM ('pos','kiosk','mobile_app','web','external_platform');
CREATE TYPE order_types AS ENUM ('dine_in','take_out','delivery_internal','delivery_third_party');

CREATE TYPE order_statuses AS ENUM (
  'draft','sent_to_kitchen','partially_served','served','paid','cancelled'
);

CREATE TYPE order_item_statuses AS ENUM ('pending','preparing','ready','served','cancelled');

-- Payments
CREATE TYPE payment_status AS ENUM ('pending','authorized','captured','failed','cancelled','refunded','voided');
CREATE TYPE payment_method_type AS ENUM ('cash','card_present','card_not_present','wallet','voucher','gift_card');

-- Pickup / fulfillment
CREATE TYPE fulfillment_status AS ENUM ('created','in_kitchen','ready','picked_up','cancelled');

2) Device registry (works for kiosks + printer app + KDS)​

device​

CREATE TABLE device (
  id uuid PRIMARY KEY,
  business_id uuid NOT NULL,
  location_id uuid NOT NULL,

  device_type device_type NOT NULL,
  name text NOT NULL,
  status device_status NOT NULL DEFAULT 'active',

  -- For pairing / auth
  public_key text NULL,
  pairing_code text NULL,
  pairing_expires_at timestamptz NULL,

  metadata jsonb NOT NULL DEFAULT '{}'::jsonb, -- screen size, OS, etc.
  last_seen_at timestamptz NULL,

  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid NULL
);

CREATE INDEX idx_device_business_location ON device (business_id, location_id);
CREATE INDEX idx_device_type_status ON device (device_type, status);
CREATE INDEX idx_device_last_seen ON device (last_seen_at);

3) Kiosk configuration​

kiosk_config​

One row per kiosk device (or you can store in device.metadata, but config table is cleaner).

CREATE TABLE kiosk_config (
  kiosk_id uuid PRIMARY KEY REFERENCES device(id) ON DELETE CASCADE,

  menu_id uuid NULL, -- optional if you manage menus as a separate entity
  allow_dine_in boolean NOT NULL DEFAULT true,
  allow_take_out boolean NOT NULL DEFAULT true,

  default_order_type order_types NOT NULL DEFAULT 'take_out',

  -- If dine-in is enabled, allow selecting table from map/QR
  allow_table_selection boolean NOT NULL DEFAULT false,
  require_table_for_dine_in boolean NOT NULL DEFAULT false,

  -- Payment behavior
  require_payment_before_send boolean NOT NULL DEFAULT true,
  tip_enabled boolean NOT NULL DEFAULT true,
  default_tip_percent numeric NULL,

  -- Pickup experience
  show_order_number_on_completion boolean NOT NULL DEFAULT true,

  metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

4) Kiosk customer “cart session”​

kiosk_session​

CREATE TABLE kiosk_session (
  id uuid PRIMARY KEY,
  business_id uuid NOT NULL,
  location_id uuid NOT NULL,
  kiosk_id uuid NOT NULL REFERENCES device(id) ON DELETE RESTRICT,

  status kiosk_session_status NOT NULL DEFAULT 'active',

  -- Optional: link to created order once checkout begins
  order_id uuid NULL,

  -- Useful for “resume cart” or debugging
  started_at timestamptz NOT NULL DEFAULT now(),
  last_activity_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL,

  -- Optional: customer contact (for pickup notifications)
  customer_name text NULL,
  customer_phone text NULL,

  metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE INDEX idx_kiosk_session_kiosk_status ON kiosk_session (kiosk_id, status);
CREATE INDEX idx_kiosk_session_activity ON kiosk_session (last_activity_at);

5) Menu availability (optional but very useful for kiosks)​

If you don’t have a menu system yet, this is a lightweight “what can kiosk sell right now”.

CREATE TABLE menu (
  id uuid PRIMARY KEY,
  business_id uuid NOT NULL,
  name text NOT NULL,
  is_active boolean NOT NULL DEFAULT true,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE menu_item (
  id uuid PRIMARY KEY,
  menu_id uuid NOT NULL REFERENCES menu(id) ON DELETE CASCADE,

  product_id uuid NOT NULL, -- references product table in your system
  sort_order int NOT NULL DEFAULT 0,

  is_available boolean NOT NULL DEFAULT true,
  available_from time NULL,
  available_to time NULL,

  metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE INDEX idx_menu_item_menu_sort ON menu_item (menu_id, sort_order);
CREATE INDEX idx_menu_item_product ON menu_item (product_id);

kiosk_menu_assignment​

CREATE TABLE kiosk_menu_assignment (
  kiosk_id uuid NOT NULL REFERENCES device(id) ON DELETE CASCADE,
  menu_id uuid NOT NULL REFERENCES menu(id) ON DELETE CASCADE,
  PRIMARY KEY (kiosk_id, menu_id)
);

6) Orders (extend your existing order table)​

Add only what kiosks need.

order additions​

ALTER TABLE "order"
  ADD COLUMN channel order_channel NOT NULL DEFAULT 'pos',
  ADD COLUMN kiosk_id uuid NULL REFERENCES device(id),
  ADD COLUMN kiosk_session_id uuid NULL REFERENCES kiosk_session(id),
  ADD COLUMN order_alias text NULL,          -- “Pickup John”, “Party VIP”
  ADD COLUMN guest_count int NULL,
  ADD COLUMN table_alias_snapshot text NULL, -- snapshot of table label/alias at order time
  ADD COLUMN version int NOT NULL DEFAULT 1; -- for WS concurrency

Keep your existing:

  • status (order_statuses)
  • order_type (order_types)
  • table_id (nullable)
  • waiter_id (nullable for kiosk)

For kiosks: waiter_id is NULL, channel='kiosk', kiosk_session_id is set.

7) Order items (keep what you proposed)​

Your structure already supports kiosk item customization.

ALTER TABLE order_item
  ADD COLUMN base_unit_price bigint NULL,        -- money_minor
  ADD COLUMN modifiers_total bigint NOT NULL DEFAULT 0,
  ADD COLUMN final_unit_price bigint NULL,       -- base + modifiers
  ADD COLUMN seat_no int NULL;                   -- if you support seat-based later

You already planned:

  • modifiers jsonb (snapshot)
  • notes text
  • status (order_item_statuses)
  • course_number

8) Payments (kiosk checkout needs this)​

Even if you already have payments for retail, kiosks benefit from a clean “intent” layer.

payment​

CREATE TABLE payment (
  id uuid PRIMARY KEY,
  business_id uuid NOT NULL,
  location_id uuid NOT NULL,

  order_id uuid NOT NULL,
  status payment_status NOT NULL DEFAULT 'pending',
  method payment_method_type NOT NULL,

  amount bigint NOT NULL, -- money_minor
  tip_amount bigint NOT NULL DEFAULT 0,
  currency_code text NOT NULL, -- or iso_currency domain

  -- Gateway fields (Stripe/Adyen/etc.)
  provider text NULL,
  provider_payment_id text NULL,
  provider_raw jsonb NOT NULL DEFAULT '{}'::jsonb,

  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_payment_order ON payment (order_id);
CREATE INDEX idx_payment_status ON payment (status);

Tracks retries/failures without corrupting main payment row.

CREATE TABLE payment_attempt (
  id uuid PRIMARY KEY,
  payment_id uuid NOT NULL REFERENCES payment(id) ON DELETE CASCADE,
  status payment_status NOT NULL,
  error_code text NULL,
  error_message text NULL,
  provider_raw jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_payment_attempt_payment ON payment_attempt (payment_id);

9) Fulfillment / pickup flow (kiosk-friendly)​

Kiosks usually show an order number and a pickup screen.

fulfillment_ticket​

CREATE TABLE fulfillment_ticket (
  id uuid PRIMARY KEY,
  business_id uuid NOT NULL,
  location_id uuid NOT NULL,
  order_id uuid NOT NULL,

  ticket_number text NOT NULL, -- e.g. A102
  status fulfillment_status NOT NULL DEFAULT 'created',

  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),

  UNIQUE (business_id, location_id, ticket_number)
);

CREATE INDEX idx_fulfillment_order ON fulfillment_ticket (order_id);
CREATE INDEX idx_fulfillment_status ON fulfillment_ticket (status);

10) Kitchen dispatch + printing (you already have it)​

Your kitchen_station, product_station_assignment, and print_job tables fit kiosks perfectly.

One tweak: make print_job generic with device routing.

  • id
  • business_id, location_id
  • station_id (optional)
  • target_device_id (printer device)
  • source_type = 'order'
  • source_id = order id
  • payload jsonb (render-ready ticket lines)
  • status = pending/printed/failed
  • created_at, printed_at, error

11) Optional but powerful: order events (audit + recovery)​

This helps WS reliability and debugging.

order_event​

CREATE TABLE order_event (
  id uuid PRIMARY KEY,
  business_id uuid NOT NULL,
  location_id uuid NOT NULL,
  order_id uuid NOT NULL,

  event_type text NOT NULL, -- item_added, status_changed, payment_captured, etc.
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,

  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_order_event_order ON order_event (order_id, created_at);

Does this cover kiosk-specific variations?​

Yes:

  • Self-service flow: kiosk_session owns the cart → creates order on checkout → payment → send to kitchen → pickup ticket.
  • Aliases: order_alias, table_alias_snapshot, optional customer_name.
  • No waiter: waiter_id nullable; order.channel='kiosk'.
  • Real-time UI: WS broadcasts order.updated, fulfillment.updated, table.updated.
  • Offline resilience: PWA stores draft events locally; session can be recovered by kiosk_session.

If you want, I can also provide a minimal MVP subset (the smallest set of tables/fields to ship kiosks in one iteration) vs the full version above.

Note (Phase 6): Kiosk tables (device, kiosk_config, kiosk_session, menu, menu_item, payment, fulfillment_ticket) are optional and belong to Phase 6. Use enum names order_statuses, order_types, order_item_statuses consistently; kiosk-specific enums (order_channel, device_type, kiosk_session_status, etc.) are additive.

Backend module architecture​

The restaurant backend module follows hexagonal architecture (ports & adapters) with strict layer boundaries.

Module structure​

apps/backend/src/restaurant/
├── restaurant.module.ts              # Root module
├── restaurant.gateway.ts             # WebSocket gateway (/restaurant)
├── modules/
│   ├── dining.module.ts              # Dining areas & tables
│   ├── kitchen.module.ts             # Kitchen stations, print jobs, assignments
│   └── order.module.ts               # Orders, items, bills, payments
├── domain/                           # Ports (interfaces) — no framework deps
│   ├── order-repository.domain.ts
│   ├── order-item-repository.domain.ts
│   ├── order-bill-repository.domain.ts
│   ├── dining-area-repository.domain.ts
│   ├── dining-table-repository.domain.ts
│   ├── kitchen-station-repository.domain.ts
│   └── print-job-repository.domain.ts
├── application/                      # Use cases / services
│   ├── orders.service.ts
│   ├── order-bill.service.ts
│   ├── order-bill-payment.service.ts
│   ├── dining-area.service.ts
│   ├── dining-table.service.ts
│   ├── kitchen.service.ts
│   └── ...
├── infrastructure/                   # Adapters (Kysely repositories)
│   ├── order.repository.ts
│   ├── order-item.repository.ts
│   ├── order-bill.repository.ts
│   └── ...
└── interfaces/                       # HTTP controllers, DTOs, queries
    ├── orders.controller.ts
    ├── order-bills.controller.ts
    ├── dining.controller.ts
    ├── kitchen.controller.ts
    ├── menus.controller.ts
    ├── reservations.controller.ts
    └── dtos/

Dependency injection pattern​

Domain interfaces use Symbol-based DI tokens (framework-agnostic):

// domain/order-repository.domain.ts
export const ORDER_REPOSITORY = Symbol("ORDER_REPOSITORY");
export interface IOrderRepository { ... }

// modules/order.module.ts
{ provide: ORDER_REPOSITORY, useClass: OrderRepository }

// application/orders.service.ts
@Inject(ORDER_REPOSITORY) private readonly orderRepo: IOrderRepository

Swagger / OpenAPI​

All controllers have @ApiOperation, @ApiParam, and @ApiQuery decorators. All DTOs have @ApiProperty / @ApiPropertyOptional. The Swagger UI is available at /api when running in development.

API and WebSocket overview​

REST endpoints​

Orders​

MethodPathPurpose
POST/ordersCreate order
GET/ordersList orders (paginated)
GET/orders/:idGet order by ID
PATCH/orders/:idUpdate order
DELETE/orders/:idDelete order
PATCH/orders/:id/customerAttach customer to order
POST/orders/:id/itemsAdd order item
GET/orders/:id/itemsList order items
PATCH/orders/:id/items/:itemIdUpdate order item
DELETE/orders/:id/items/:itemIdRemove order item
POST/orders/:id/items/:itemId/voidVoid order item
POST/orders/:id/items/:itemId/compComp order item
POST/orders/:id/items/:itemId/fireFire order item to kitchen
POST/orders/:id/items/fire-courseFire all items by course
POST/orders/:id/send-to-kitchenSend order to kitchen
POST/orders/:id/collect-paymentCollect payment (without bill)
GET/orders/:id/station-completionStation completion status
GET/orders/station-completion/batchBatch station completion
GET/orders/held-counts/batchBatch held item counts
GET/orders/guests/batchBatch get guests

Order discounts​

MethodPathPurpose
POST/orders/:id/items/:itemId/discountApply line-item discount
DELETE/orders/:id/items/:itemId/discountRemove line-item discount
POST/orders/:id/discountApply cart-level discount
DELETE/orders/:id/discountRemove cart-level discount

Order bundles​

MethodPathPurpose
GET/orders/:id/bundles/evaluateEvaluate eligible bundles
POST/orders/:id/bundles/applyApply bundle to order
DELETE/orders/:id/bundles/removeRemove bundle from order
POST/orders/:id/bundles/add-comboAdd combo as order items

Order bills & payments​

MethodPathPurpose
POST/order-billsCreate bill (split)
GET/order-billsList bills by orderId
GET/order-bills/:idGet bill by ID
POST/order-bills/:id/paymentsRecord bill payment
GET/order-bills/:id/qr-codeGenerate QR code for bill
POST/order-bills/validate-qrValidate scanned QR code
POST/orders/:id/dispatchMark order as dispatched

Kitchen & print​

MethodPathPurpose
POST/kitchen-stationsCreate kitchen station
GET/kitchen-stationsList kitchen stations
PATCH/kitchen-stations/:idUpdate kitchen station
DELETE/kitchen-stations/:idDelete kitchen station
POST/kitchen-stations/:id/heartbeatStation heartbeat
GET/kitchen-stations/healthStation health status
GET/kitchen-stations/loadStation workload distribution
GET/kitchen-stations/:id/aggregated-itemsAggregated items for KDS
GET/analyticsKitchen analytics
GET/print-jobsList print jobs
PATCH/print-jobs/:idUpdate print job status

Dining​

MethodPathPurpose
POST/dining-areasCreate dining area
GET/dining-areasList dining areas
GET/dining-areas/:idGet dining area by ID
PATCH/dining-areas/:idUpdate dining area
DELETE/dining-areas/:idDelete dining area
POST/dining-tablesCreate dining table
GET/dining-tablesList dining tables
GET/dining-tables/:idGet dining table by ID
PATCH/dining-tables/:idUpdate dining table
DELETE/dining-tables/:idDelete dining table
MethodPathPurpose
POST/menusCreate menu
GET/menusList menus
GET/menus/:idGet menu by ID
PATCH/menus/:idUpdate menu
DELETE/menus/:idDelete menu
POST/menus/:menuId/itemsCreate menu item
GET/menus/:menuId/itemsList menu items
GET/menus/:menuId/items/:itemIdGet menu item
PATCH/menus/:menuId/items/:itemIdUpdate menu item
DELETE/menus/:menuId/items/:itemIdDelete menu item
POST/locations/:locationId/menusAssign menu to location
DELETE/locations/:locationId/menus/:menuIdUnassign menu
GET/locations/:locationId/menusList location menus

Other restaurant resources​

MethodPathPurpose
CRUD/reservationsReservation management
CRUD/waitlist-entriesWaitlist management
CRUD/price-rulesTime/day-based price rules
CRUD/product-modifier-groupsModifier group management
CRUD/product-modifiersModifier management
CRUD/product-station-assignmentsProduct-to-station routing
CRUD/external-platform-mappingsDelivery platform mappings
CRUD/orders/:orderId/guestsOrder guest management
CRUD/orders/:orderId/partyOrder party (server/host)

PWA form routes (MVP)​

To make restaurant pages reachable in the existing PWA form router (MainPage), use:

  • /forms/restaurantDining
  • /forms/restaurantOrders

If menu entries are remote-configured (e.g. pwaMenu), include these two route paths so users can open dining and order/bill flows directly.

WebSocket events​

EventEmitted whenPayload
order.createdNew order createdorder
order.updatedOrder or items changedorder
order.sent_to_kitchenOrder sent to kitchenorder, print_jobs
order_item.updatedItem status changedorder_item
table.updatedTable status changeddining_table
print_job.newNew print job for stationprint_job
print_job.updatedJob printed/failedprint_job

Scope events by business_id and location_id so clients subscribe only to their location.

Implementation phases​

PhaseScopeKey deliverables
1. FoundationSpace + core order modeldining_area, dining_table; order / order_item status, order_type, table_id, waiter_id; modifiers/notes (jsonb).
2. Bills & splitsPayment and FELorder_bill, order_bill_mapping, order_bill_payment (Option A); optional order.parent_order_id for merged tables. Integrate with existing: bill payments via order_bill_payment link to cash_register_session_id and optional safe_id; use document_counter for bill numbers; one FEL per paid bill when required.
3. Kitchen & printReliabilitykitchen_station, product_station_assignment, print_job (lifecycle: pending → sent → printed/failed); startup sync + heartbeat; ACK + cron for zombie jobs.
4. Bridge appKDS + printerSingle "FlowPOS Bridge" app with profiles: printer-only, KDS-only, hybrid.
5. People & aliasesOptionalorder_guest, order_party; order_item.seat_no; dining_table.alias, order.order_alias / table_alias_snapshot.
6. Location & kioskMulti-branch / self-serviceLocation-specific pricing/menus (if needed); kiosk: device, kiosk_config, kiosk_session, order.channel, fulfillment/payment tables.

Before implementing: See Schema status and migration requirements. Restaurant tables (order, order_item, dining_area, dining_table, order_bill, order_bill_mapping, order_bill_payment, etc.), sale.session_id, and product_recipe must be added via migrations. Use enum names order_statuses, order_item_statuses, order_types, etc.

ArtifactPath / notes
Feature specspecs/010-restaurant/ (create if not exists)
Migrationspackages/backend/database/src/migrations/ — add e.g. YYYY-MM-DD-restaurant-core.mjs, YYYY-MM-DD-sale-session-id.mjs, YYYY-MM-DD-product-recipe.mjs
Existing onboarding2024-06-02t23:35:16.678z-onboarding-tables.mjs
Production runs2026-02-12t12:00:00.000z-production-runs.mjs
Retail sale flowapps/frontend-pwa/.../SalePage.tsx, apps/backend/src/sales/

Document version: 1.2 · Last updated: 2026-03-24 · Changelog: v1.0 — Initial improvements. v1.1 — Scope labels (Normative MVP vs Future); normalized payment_method_id; order_statuses MVP excludes merged (parent_order_id only); order_item_statuses includes cancelled; added product_station_assignment DDL and indexes to Reference schema; added order_item.unit_price_snapshot and tax_amount_snapshot for audit-safe billing; Related artifacts path specs/010-restaurant/. v1.2 — Added backend module architecture section (hexagonal, domain interfaces, DI tokens); expanded REST endpoint table with all 80+ endpoints including discounts, bundles, QR codes, batch operations, kitchen analytics; documented Swagger/OpenAPI coverage.