Database Schema

Purpose

The SaaS platform uses a single PostgreSQL instance with two tiers of databases: the gateway database (wank_saas) managing users, slices, sessions, and telemetry; and per-slice databases (wa_slice_N) each containing the CRM data for one user (contacts, chats, messages, media).

Data Model

Gateway Database: `wank_saas`

All gateway tables live in the gateway schema.

gateway.users

Column	Type	Notes
id	SERIAL PK
email	TEXT UNIQUE	Lowercase, used for login
password_hash	TEXT	bcrypt cost 12
slice_id	INTEGER FK → slices	Assigned slice (null if waiting)
stripe_customer	TEXT	Stripe customer ID
subscription_status	TEXT	trial, active, past_due, cancelled
subscription_id	TEXT	Stripe subscription ID
subscription_ends_at	TIMESTAMPTZ
is_admin	BOOLEAN	Admin dashboard access
created_at	TIMESTAMPTZ
last_login_at	TIMESTAMPTZ	Updated on each login

gateway.slices

Column	Type	Notes
id	SERIAL PK
port	INTEGER UNIQUE	Backend port (5001, 5003, …)
status	TEXT	available, assigned, suspended, destroying
user_id	INTEGER FK → users	Assigned user (null if available)
wa_connected	BOOLEAN	WhatsApp session active
wa_phone	TEXT	Connected phone number
last_health_at	TIMESTAMPTZ	Last monitor health check
created_at	TIMESTAMPTZ
storage_bytes	BIGINT	Disk usage tracked by monitor

Cross-references: users.slice_id → slices.id and slices.user_id → users.id (added after both tables exist to avoid circular dependency).

gateway.sessions

Column	Type	Notes
id	TEXT PK	256-bit random hex (64 chars)
user_id	INTEGER FK → users
expires_at	TIMESTAMPTZ	30-day rolling expiry
created_at	TIMESTAMPTZ

gateway.telemetry (per-slice time-series)

Column	Type	Notes
id	BIGSERIAL PK
slice_id	INTEGER FK → slices
timestamp	TIMESTAMPTZ	Collected every 60s
rss_bytes	BIGINT	Container memory (from docker stats)
wa_state	TEXT	WhatsApp connection state
cpu_percent	NUMERIC	Container CPU %
disk_bytes	BIGINT	Slice disk usage (from du)

Index: idx_telemetry_slice_time on (slice_id, timestamp DESC)

gateway.server_telemetry (server-level time-series)

Column	Type	Notes
id	BIGSERIAL PK
timestamp	TIMESTAMPTZ	Collected every 60s
ram_used_bytes	BIGINT
ram_total_bytes	BIGINT
swap_used_bytes	BIGINT
swap_total_bytes	BIGINT
cpu_percent	REAL
disk_used_bytes	BIGINT
disk_total_bytes	BIGINT
active_slices	INTEGER	Assigned slice count
total_slices	INTEGER	All slices count

Index: idx_server_telemetry_time on (timestamp DESC)

Per-Slice Databases: `wa_slice_N`

Each slice gets its own database with the CRM schema (contacts, chats, messages, media metadata). Schema applied from /home/ubuntu/whatsapp/deploy/saas/slice-schema.sql.

Database	User	Purpose
`wank_saas`	`wank_gateway`	Gateway: users, slices, sessions, telemetry
`wa_slice_1`	`wa_slice`	Slice 1 CRM data
`wa_slice_2`	`wa_slice`	Slice 2 CRM data
`wa_slice_N`	`wa_slice`	Slice N CRM data

Telemetry Retention

Pruned hourly by the monitor process
Retention: 7 days for both telemetry tables

Status

Live with telemetry flowing every 60 seconds. Gateway schema at /home/chas-watkins/code/WhatsApp/gateway/schema.sql.