architecture

Database Schema

Name: BurnerByte
Author: Ali Jaradat

PostgreSQL schema overview — tables, indexes, and triggers.

Overview

BurnerByte uses PostgreSQL 16 with 50 migrations managing 36 tables, 74 indexes, and 8 triggers.

Table	Purpose
`users`	User accounts with email, password hash and display name
`sessions`	Active JWT refresh sessions with IP, user agent, expiry
`password_reset_tokens`	Time-limited password reset tokens
`email_verification_tokens`	Time-limited email verification tokens

Table	Purpose
`organizations`	Orgs with name, slug, settings JSON
`org_memberships`	User ↔ org relationship with role (owner, admin, member)
`teams`	Teams within an org
`team_memberships`	User ↔ team relationship with role
`domains`	Registered domains with MX/TXT verification status
`domain_assignments`	Domain ↔ team mapping with access level and settings
`domain_verification_history`	Per-check MX/TXT/SPF verification results over time

Table	Purpose
`inboxes`	Temporary inboxes with address, TTL, domain assignment
`emails`	Received emails with from, to, subject, body, headers
`attachments`	Email attachments with S3 path, content type, size

Table	Purpose
`webhooks`	Webhook subscriptions with URL, events, HMAC secret
`webhook_delivery_logs`	Delivery attempts with event, payload, response status/body, response time, success flag, attempt number, and idempotency key
`api_keys`	Scoped API keys with hashed key, prefix, scopes

Table	Purpose
`audit_logs`	Activity log with actor, action, resource, IP
`invites`	Org invitations with token, email, role, expiry
`invite_team_assignments`	Team + role assignments attached to a pending invite
`notifications`	User notifications with type, inbox_id, read status
`setup_state`	One-time setup wizard completion state
`system_configs`	Runtime config overrides (`mailer`, `storage`, `sso`, `platform`); the first three are encrypted at rest

Table	Purpose
`roles`	Named roles with label, description, rank and scope (`org` or `team`)
`permissions`	Individual permission definitions (e.g., `inboxes.create`)
`role_permissions`	Role ↔ permission mapping

Table	Purpose
`sso_providers`	Configured OIDC providers (name, client ID/secret, endpoints)
`sso_domain_mappings`	Email-domain → provider auto-routing rules
`user_sso_identities`	Links a user to an external provider identity (subject)

Table	Purpose
`org_analytics_counters`	Running org-level counters
`team_analytics_counters`	Running team-level counters
`daily_email_stats`	Per-day org email totals
`daily_team_email_stats`	Per-day, per-team email totals
`daily_domain_email_stats`	Per-day, per-domain email totals
`daily_sender_domain_stats`	Per-day sender-domain breakdown
`hourly_email_stats`	Per-hour totals for peak-hour charts

Managed with golang-migrate. Run with:

bash

make migrate-up    # Apply all pendingmake migrate-down  # Rollback last migrationmake migrate-test  # Test up/down for each migration