Database

Megabyte Island uses PostgreSQL for all persistent state.

Schema

Located at backend/src/db/schema.sql. Apply with:

psql $DATABASE_URL -f backend/src/db/schema.sql

`users`

Column	Type	Notes
`id`	UUID PK	Auto-generated
`email`	TEXT UNIQUE	User’s login email
`password_hash`	TEXT	bcrypt hash (12 rounds)
`stripe_customer_id`	TEXT	Set on registration
`created_at`	TIMESTAMPTZ

`subscriptions`

Column	Type	Notes
`id`	UUID PK
`user_id`	UUID FK → users
`stripe_subscription_id`	TEXT UNIQUE
`status`	TEXT	`active`, `cancelled`, etc.
`current_period_end`	TIMESTAMPTZ
`created_at`	TIMESTAMPTZ

`agents`

Column	Type	Notes
`id`	UUID PK
`user_id`	UUID FK → users
`agent_type`	TEXT	`whatsapp`, `web`, `brain`
`do_agent_id`	TEXT UNIQUE	Agent ID
`encrypted_access_key`	TEXT	AES-256-GCM ciphertext
`status`	TEXT	`provisioning`, `active`, `error`
`created_at`	TIMESTAMPTZ

Useful admin queries

List all agents by user:

SELECT u.email, a.agent_type, a.status, a.created_at
FROM agents a
JOIN users u ON u.id = a.user_id
ORDER BY a.created_at DESC;

Mark a stuck agent as error:

UPDATE agents SET status = 'error'
WHERE id = '<uuid>' AND status = 'provisioning';

Count active subscriptions:

SELECT COUNT(*) FROM subscriptions WHERE status = 'active';

Backups

Use pg_dump for regular backups:

pg_dump $DATABASE_URL > backup_$(date +%Y%m%d).sql

For production, set up automated backups via Managed Databases or pg_dump via cron.

Connection pooling

The backend uses pg.Pool with default settings (max 10 connections). For high traffic, configure PG_POOL_MAX and consider using PgBouncer in front of PostgreSQL.