Database Schema
JustCall uses Supabase PostgreSQL with a multi-tenant architecture.
Entity Relationship Diagram
Core Tables
accounts
Top-level tenant (Kunde/Mandant).
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key |
| name | text | Account name |
| slug | text | URL-friendly identifier |
| stripe_customer_id | text | Stripe customer ID |
| billing_email | text | Billing contact |
| twilio_account_sid | text | Twilio subaccount SID |
| created_at | timestamptz | Creation timestamp |
restaurants
Restaurant/branch within an account.
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key |
| account_id | uuid | FK to accounts |
| name | text | Restaurant name |
| phone | text | Restaurant phone |
| address | text | Physical address |
| voice_enabled | boolean | Voice ordering active |
| whatsapp_enabled | boolean | WhatsApp ordering active |
| minimum_order_value | decimal | Minimum for delivery |
| default_delivery_fee | decimal | Standard delivery charge |
| system_prompt_override | text | Custom AI prompt |
| vad_threshold | decimal | VAD sensitivity |
| vad_silence_duration_ms | integer | Silence before response |
| logging_level | text | Debug logging level |
orders
Customer orders.
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key |
| restaurant_id | uuid | FK to restaurants |
| order_number | text | Human-readable number |
| status | text | pending/confirmed/preparing/ready/delivered/cancelled |
| order_type | text | pickup/delivery |
| customer_name | text | Customer name |
| customer_phone | text | Phone number |
| delivery_address | text | Address (if delivery) |
| delivery_fee | decimal | Charged fee |
| subtotal | decimal | Sum of items |
| total | decimal | Final amount |
| items | jsonb | Order items snapshot |
| call_session_id | uuid | FK to call_sessions |
| created_at | timestamptz | Order time |
menu_categories
Menu sections.
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key |
| restaurant_id | uuid | FK to restaurants |
| name | text | Category name |
| sort_order | integer | Display order |
| is_active | boolean | Visible in menu |
menu_items
Individual dishes.
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key |
| category_id | uuid | FK to menu_categories |
| name | text | Item name |
| description | text | Description |
| price | decimal | Price in CHF |
| is_available | boolean | Currently orderable |
| allergens | text[] | Allergen list |
| image_url | text | Optional image |
call_sessions
Voice call records.
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key |
| restaurant_id | uuid | FK to restaurants |
| twilio_call_sid | text | Twilio call ID |
| status | text | active/completed/failed |
| caller_phone | text | Customer phone |
| started_at | timestamptz | Call start |
| ended_at | timestamptz | Call end |
| duration_seconds | integer | Call length |
| order_id | uuid | FK to orders (if created) |
delivery_zones
Delivery coverage areas.
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key |
| restaurant_id | uuid | FK to restaurants |
| name | text | Zone name |
| type | text | polygon/plz |
| geojson | jsonb | Polygon coordinates |
| plz_codes | text[] | Postal codes |
| delivery_fee | decimal | Zone-specific fee |
| minimum_order | decimal | Zone-specific minimum |
| estimated_minutes | integer | Delivery time |
kb_documents
Knowledge base articles.
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key |
| restaurant_id | uuid | FK to restaurants |
| title | text | Document title |
| content | text | Full text content |
| source_type | text | manual/faq/menu |
| is_active | boolean | Include in RAG |
kb_chunks
Vectorized document chunks for RAG.
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key |
| document_id | uuid | FK to kb_documents |
| content | text | Chunk text |
| embedding | vector(1536) | OpenAI embedding |
| metadata | jsonb | Extra data |
Indexes
Key indexes for performance:
-- Order lookups
CREATE INDEX idx_orders_restaurant_status ON orders(restaurant_id, status);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
-- Menu queries
CREATE INDEX idx_menu_items_category ON menu_items(category_id);
CREATE INDEX idx_menu_categories_restaurant ON menu_categories(restaurant_id);
-- RAG search
CREATE INDEX idx_kb_chunks_embedding ON kb_chunks USING ivfflat (embedding vector_cosine_ops);
-- Session lookups
CREATE INDEX idx_call_sessions_restaurant ON call_sessions(restaurant_id);
CREATE INDEX idx_call_sessions_status ON call_sessions(status);
JSONB Schemas
orders.items
[
{
"name": "Margherita",
"price": 16.50,
"quantity": 2,
"notes": "extra scharf"
}
]
delivery_zones.geojson
{
"type": "Polygon",
"coordinates": [[[8.5, 47.3], [8.6, 47.3], [8.6, 47.4], [8.5, 47.4], [8.5, 47.3]]]
}