Database Schema

Overview

RestAI uses PostgreSQL 17 with Drizzle ORM for type-safe database operations. The schema is designed for multi-tenancy with strict data isolation between organizations.

Multi-Tenant Architecture

Every table includes organization_id to ensure complete tenant isolation:

-- All queries are automatically scoped
SELECT * FROM orders 
WHERE organization_id = $currentOrganization 
  AND branch_id = $currentBranch;

Schema Organization

The schema is modular and split into logical domains:

packages/db/src/schema/
├── index.ts              # Exports all schemas
├── enums.ts              # PostgreSQL enums
├── tenants.ts            # Organizations & branches
├── auth.ts               # Users & authentication
├── tables.ts             # Table management
├── menu.ts               # Menu items & modifiers
├── orders.ts             # Orders & order items
├── inventory.ts          # Inventory & recipes
├── payments.ts           # Payments & invoices
├── loyalty.ts            # Loyalty programs & points
├── coupons.ts            # Coupons & promotions
└── staff.ts              # Staff management

Core Tables

Organizations (Tenants)

Schema
Plans
Settings

export const organizations = pgTable("organizations", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 255 }).notNull(),
  slug: varchar("slug", { length: 100 }).unique().notNull(),
  logo_url: text("logo_url"),
  plan: planEnum("plan").default("free").notNull(),
  is_active: boolean("is_active").default(true).notNull(),
  settings: jsonb("settings").default({}).notNull(),
  created_at: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  updated_at: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});

export const planEnum = pgEnum("plan", [
  "free",      // Limited features
  "starter",   // Small restaurants
  "pro",       // Medium businesses
  "enterprise" // Large chains
]);

// settings JSONB field example
{
  "branding": {
    "primaryColor": "#FF6B35",
    "secondaryColor": "#004E89"
  },
  "features": {
    "loyalty": true,
    "inventory": true,
    "analytics": false
  }
}

Branches

export const branches = pgTable(
  "branches",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    organization_id: uuid("organization_id")
      .notNull()
      .references(() => organizations.id, { onDelete: "cascade" }),
    name: varchar("name", { length: 255 }).notNull(),
    slug: varchar("slug", { length: 100 }).notNull(),
    address: text("address"),
    phone: varchar("phone", { length: 20 }),
    timezone: varchar("timezone", { length: 50 })
      .default("America/Lima")
      .notNull(),
    currency: varchar("currency", { length: 3 })
      .default("PEN")
      .notNull(),
    tax_rate: integer("tax_rate").default(1800).notNull(), // 18.00%
    is_active: boolean("is_active").default(true).notNull(),
    settings: jsonb("settings").default({}).notNull(),
    created_at: timestamp("created_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    updated_at: timestamp("updated_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
  },
  (table) => [
    unique("branches_org_slug_unique").on(
      table.organization_id, 
      table.slug
    ),
  ]
);

Tax rate is stored as basis points (1800 = 18.00%) to avoid floating-point precision issues.

Authentication & Users

Users

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  organization_id: uuid("organization_id")
    .notNull()
    .references(() => organizations.id, { onDelete: "cascade" }),
  email: varchar("email", { length: 255 }).unique().notNull(),
  password_hash: text("password_hash").notNull(),
  name: varchar("name", { length: 255 }).notNull(),
  role: userRoleEnum("role").notNull(),
  is_active: boolean("is_active").default(true).notNull(),
  created_at: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});

Refresh Tokens

export const refreshTokens = pgTable("refresh_tokens", {
  id: uuid("id").primaryKey().defaultRandom(),
  user_id: uuid("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  token_hash: text("token_hash").notNull(), // Argon2 hashed
  expires_at: timestamp("expires_at", { withTimezone: true }).notNull(),
  created_at: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});

Table Management

Spaces (Dining Areas)

export const spaces = pgTable(
  "spaces",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    branch_id: uuid("branch_id")
      .notNull()
      .references(() => branches.id, { onDelete: "cascade" }),
    organization_id: uuid("organization_id")
      .notNull()
      .references(() => organizations.id, { onDelete: "cascade" }),
    name: varchar("name", { length: 255 }).notNull(),
    description: text("description"),
    floor_number: integer("floor_number").default(1).notNull(),
    is_active: boolean("is_active").default(true).notNull(),
    sort_order: integer("sort_order").default(0).notNull(),
  },
  (table) => [
    unique("spaces_branch_name_unique").on(table.branch_id, table.name),
  ]
);

Tables

export const tables = pgTable(
  "tables",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    branch_id: uuid("branch_id")
      .notNull()
      .references(() => branches.id, { onDelete: "cascade" }),
    organization_id: uuid("organization_id")
      .notNull()
      .references(() => organizations.id, { onDelete: "cascade" }),
    space_id: uuid("space_id").references(() => spaces.id, {
      onDelete: "set null",
    }),
    number: integer("number").notNull(),
    capacity: integer("capacity").default(4).notNull(),
    qr_code: varchar("qr_code", { length: 100 }).unique().notNull(),
    status: tableStatusEnum("status").default("available").notNull(),
    position_x: integer("position_x").default(0).notNull(),
    position_y: integer("position_y").default(0).notNull(),
  },
  (table) => [
    unique("tables_branch_number_unique").on(
      table.branch_id, 
      table.number
    ),
  ]
);

Table Sessions

export const tableSessions = pgTable(
  "table_sessions",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    table_id: uuid("table_id")
      .notNull()
      .references(() => tables.id, { onDelete: "cascade" }),
    branch_id: uuid("branch_id")
      .notNull()
      .references(() => branches.id, { onDelete: "cascade" }),
    organization_id: uuid("organization_id")
      .notNull()
      .references(() => organizations.id, { onDelete: "cascade" }),
    customer_name: varchar("customer_name", { length: 255 }).notNull(),
    customer_phone: varchar("customer_phone", { length: 20 }),
    token: text("token").notNull(),
    status: sessionStatusEnum("status").default("active").notNull(),
    started_at: timestamp("started_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    ended_at: timestamp("ended_at", { withTimezone: true }),
  },
  (table) => [
    index("idx_sessions_table_status").on(table.table_id, table.status),
    index("idx_sessions_branch").on(table.branch_id),
  ]
);

export const menuCategories = pgTable("menu_categories", {
  id: uuid("id").primaryKey().defaultRandom(),
  branch_id: uuid("branch_id")
    .notNull()
    .references(() => branches.id, { onDelete: "cascade" }),
  organization_id: uuid("organization_id")
    .notNull()
    .references(() => organizations.id, { onDelete: "cascade" }),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  image_url: text("image_url"),
  sort_order: integer("sort_order").default(0).notNull(),
  is_active: boolean("is_active").default(true).notNull(),
});

export const menuItems = pgTable(
  "menu_items",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    category_id: uuid("category_id")
      .notNull()
      .references(() => menuCategories.id, { onDelete: "cascade" }),
    branch_id: uuid("branch_id")
      .notNull()
      .references(() => branches.id, { onDelete: "cascade" }),
    organization_id: uuid("organization_id")
      .notNull()
      .references(() => organizations.id, { onDelete: "cascade" }),
    name: varchar("name", { length: 255 }).notNull(),
    description: text("description"),
    price: integer("price").notNull(), // stored in cents
    image_url: text("image_url"),
    is_available: boolean("is_available").default(true).notNull(),
    sort_order: integer("sort_order").default(0).notNull(),
    preparation_time_min: integer("preparation_time_min"),
  },
  (table) => [
    index("idx_menu_items_branch").on(table.branch_id),
    index("idx_menu_items_category").on(table.category_id),
  ]
);

All monetary values are stored as integers in cents to avoid floating-point arithmetic issues. For example, $12.50 is stored as 1250.

Modifiers

export const modifierGroups = pgTable("modifier_groups", {
  id: uuid("id").primaryKey().defaultRandom(),
  branch_id: uuid("branch_id")
    .notNull()
    .references(() => branches.id, { onDelete: "cascade" }),
  organization_id: uuid("organization_id")
    .notNull()
    .references(() => organizations.id, { onDelete: "cascade" }),
  name: varchar("name", { length: 255 }).notNull(),
  min_selections: integer("min_selections").default(0).notNull(),
  max_selections: integer("max_selections").default(1).notNull(),
  is_required: boolean("is_required").default(false).notNull(),
});

Orders

Orders Table

export const orders = pgTable(
  "orders",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    organization_id: uuid("organization_id")
      .notNull()
      .references(() => organizations.id, { onDelete: "cascade" }),
    branch_id: uuid("branch_id")
      .notNull()
      .references(() => branches.id, { onDelete: "cascade" }),
    table_session_id: uuid("table_session_id").references(
      () => tableSessions.id,
      { onDelete: "set null" }
    ),
    customer_id: uuid("customer_id").references(() => customers.id, {
      onDelete: "set null",
    }),
    order_number: varchar("order_number", { length: 20 }).notNull(),
    type: orderTypeEnum("type").default("dine_in").notNull(),
    status: orderStatusEnum("status").default("pending").notNull(),
    customer_name: varchar("customer_name", { length: 255 }),
    subtotal: integer("subtotal").notNull().default(0),
    tax: integer("tax").notNull().default(0),
    discount: integer("discount").notNull().default(0),
    total: integer("total").notNull().default(0),
    notes: text("notes"),
    inventory_deducted: boolean("inventory_deducted")
      .default(false)
      .notNull(),
  },
  (table) => [
    index("idx_orders_branch_status").on(table.branch_id, table.status),
    index("idx_orders_table_session").on(table.table_session_id),
    index("idx_orders_customer").on(table.customer_id),
    index("idx_orders_created_at").on(table.created_at),
  ]
);

Order Items

export const orderItems = pgTable(
  "order_items",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    order_id: uuid("order_id")
      .notNull()
      .references(() => orders.id, { onDelete: "cascade" }),
    menu_item_id: uuid("menu_item_id")
      .notNull()
      .references(() => menuItems.id, { onDelete: "restrict" }),
    name: varchar("name", { length: 255 }).notNull(), // snapshot
    unit_price: integer("unit_price").notNull(), // snapshot in cents
    quantity: integer("quantity").notNull().default(1),
    total: integer("total").notNull(), // in cents
    notes: text("notes"),
    status: orderItemStatusEnum("status").default("pending").notNull(),
  },
  (table) => [index("idx_order_items_order").on(table.order_id)]
);

Order items store snapshots of the name and price at order time. This ensures historical accuracy even if menu prices change later.

Order Item Modifiers

export const orderItemModifiers = pgTable("order_item_modifiers", {
  id: uuid("id").primaryKey().defaultRandom(),
  order_item_id: uuid("order_item_id")
    .notNull()
    .references(() => orderItems.id, { onDelete: "cascade" }),
  modifier_id: uuid("modifier_id")
    .notNull()
    .references(() => modifiers.id, { onDelete: "restrict" }),
  name: varchar("name", { length: 255 }).notNull(), // snapshot
  price: integer("price").notNull().default(0), // snapshot in cents
});

Inventory

export const inventoryItems = pgTable("inventory_items", {
  id: uuid("id").primaryKey().defaultRandom(),
  branch_id: uuid("branch_id")
    .notNull()
    .references(() => branches.id, { onDelete: "cascade" }),
  organization_id: uuid("organization_id")
    .notNull()
    .references(() => organizations.id, { onDelete: "cascade" }),
  category_id: uuid("category_id").references(
    () => inventoryCategories.id,
    { onDelete: "set null" }
  ),
  name: varchar("name", { length: 255 }).notNull(),
  unit: varchar("unit", { length: 50 }).notNull(),
  current_stock: numeric("current_stock", { precision: 10, scale: 3 })
    .default("0")
    .notNull(),
  min_stock: numeric("min_stock", { precision: 10, scale: 3 })
    .default("0")
    .notNull(),
  cost_per_unit: integer("cost_per_unit").default(0).notNull(),
});

Loyalty System

export const customers = pgTable("customers", {
  id: uuid("id").primaryKey().defaultRandom(),
  organization_id: uuid("organization_id")
    .notNull()
    .references(() => organizations.id, { onDelete: "cascade" }),
  name: varchar("name", { length: 255 }).notNull(),
  email: varchar("email", { length: 255 }),
  phone: varchar("phone", { length: 20 }),
  birth_date: date("birth_date"),
  created_at: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});

Entity Relationship Diagram

Indexes & Performance

Critical Indexes

-- Orders: Frequently queried by branch and status
CREATE INDEX idx_orders_branch_status 
  ON orders(branch_id, status);

-- Orders: Date range queries for reports
CREATE INDEX idx_orders_created_at 
  ON orders(created_at);

-- Table Sessions: Active sessions by table
CREATE INDEX idx_sessions_table_status 
  ON table_sessions(table_id, status);

-- Menu Items: Browse by branch
CREATE INDEX idx_menu_items_branch 
  ON menu_items(branch_id);

Query Patterns

import { db } from "@restai/db";
import { orders } from "@restai/db/schema";
import { eq, and, inArray } from "drizzle-orm";

const activeOrders = await db
  .select()
  .from(orders)
  .where(
    and(
      eq(orders.branch_id, branchId),
      inArray(orders.status, ["pending", "confirmed", "preparing"])
    )
  )
  .orderBy(orders.created_at);

Database Migrations

Generate Migration
Run Migration
Push Schema
Drizzle Studio

bun run db:generate

Generates SQL migration files from schema changes.

bun run db:migrate

Applies pending migrations to the database.

bun run db:push

Directly pushes schema changes (development only).

bun run db:studio

Opens visual database explorer at https://local.drizzle.studio.

Best Practices

Always Include Tenant IDs: Every query must filter by organization_id and branch_id
Use Transactions: For operations that modify multiple tables (e.g., creating order with items)
Snapshot Pricing: Always copy current prices to order items, never reference menu prices
Soft Deletes: Use is_active flags instead of hard deletes for audit trail
Timestamps: Include created_at and updated_at on all tables
Foreign Key Actions: Use onDelete: "cascade" for dependent data, set null for references

Always wrap multi-table operations in database transactions to ensure data consistency.

Get Started

Core Features

Deployment

Architecture

Database Schema

Overview

Multi-Tenant Architecture

Schema Organization

Core Tables

Organizations (Tenants)

Branches

Authentication & Users

Users

Refresh Tokens

Table Management

Spaces (Dining Areas)

Tables

Table Sessions

Menu System

Menu Categories

Menu Items

Modifiers

Orders

Orders Table

Order Items

Order Item Modifiers

Inventory

Loyalty System

Entity Relationship Diagram

Indexes & Performance

Critical Indexes

Query Patterns

Database Migrations

Best Practices

Build docs developers (and LLMs) love

Get Started

Core Features

Deployment

Architecture

​Overview

​Multi-Tenant Architecture

​Schema Organization

​Core Tables

​Organizations (Tenants)

​Branches

​Authentication & Users

​Users

​Refresh Tokens

​Table Management

​Spaces (Dining Areas)

​Tables

​Table Sessions

​Menu System

​Menu Categories

​Menu Items

​Modifiers

​Orders

​Orders Table

​Order Items

​Order Item Modifiers

​Inventory

​Loyalty System

​Entity Relationship Diagram

​Indexes & Performance

​Critical Indexes

​Query Patterns

​Database Migrations

​Best Practices

Build docs developers (and LLMs) love

Overview

Multi-Tenant Architecture

Schema Organization

Core Tables

Organizations (Tenants)

Branches

Authentication & Users

Users

Refresh Tokens

Table Management

Spaces (Dining Areas)

Tables

Table Sessions

Menu System

Menu Categories

Menu Items

Modifiers

Orders

Orders Table

Order Items

Order Item Modifiers

Inventory

Loyalty System

Entity Relationship Diagram

Indexes & Performance

Critical Indexes

Query Patterns

Database Migrations

Best Practices