Database Architecture

Overview

Single PostgreSQL RDS instance with five schemas:

Schema	Product	Key Entities
statuspages	Statux Pages	Status pages, components, incidents, subscribers, maintenance windows
alerts	Statux Alerts	Alerts, alert events, incidents, teams, schedules, escalation policies, integrations (Slack/Teams/Discord), workflows
synthetics	Statux Synthetics	Checks, check results, relays, aggregations, webhooks
insights	Statux Insights	Analyses, RCA documents, action items, webhook configs
core	Shared / Platform	Organizations, users, subscriptions, product roles, SCIM tokens, analytics

Core Schema Entities

The core schema contains entities shared across all products:

Table	Description	Key Columns
core.organizations	Organization records	name, slug, branding
core.organization_members	Org membership	role, invite_status, invite_token, invited_at, accepted_at
core.users	User profiles (synced from Cognito)	cognito_sub, email, name, is_employee
core.subscriptions	Legacy per-user subscription records	plan, status, stripe_customer_id
core.org_product_subscriptions	Per-product subscription per org	product, plan, status, trial dates
core.org_product_roles	Per-product role assignments	product, role, user_id, organization_id
core.scim_tokens	SCIM 2.0 provisioning tokens	token_hash (SHA256), revoked_at
core.analytics_events	Product usage telemetry	event_type, product, metadata
core.user_activities	Last-seen tracking	last_active_at, product

RDS Configuration

• Engine: PostgreSQL 15
• Instance: db.t3.micro (prod)
• Multi-AZ: Enabled
• Encryption: Enabled at rest
• Backups: 7-day retention
• Host: statux-prod-rds-main.c4540ua8ceif.us-east-1.rds.amazonaws.com

VPC Isolation

Database VPC (10.1.0.0/16) is fully private:

• No internet gateway
• Accessed via VPC peering from Application VPC
• Bastion access via Access VPC (SSM port forwarding)

Connecting to Production DB

There is no SSH bastion. Use SSM port forwarding through an API EC2 instance:

# Find a running API instance
aws autoscaling describe-auto-scaling-groups \
  --auto-scaling-group-name statux-prod-asg-alerts-api \
  --query 'AutoScalingGroups[0].Instances[0].InstanceId' --output text

# Start port forwarding
aws ssm start-session \
  --target <instance-id> \
  --document-name AWS-StartPortForwardingSessionToRemoteHost \
  --parameters '{"host":["statux-prod-rds-main.c4540ua8ceif.us-east-1.rds.amazonaws.com"],"portNumber":["5432"],"localPortNumber":["5432"]}'

# Connect (in another terminal)
PGSSLMODE=require psql -h localhost -p 5432 -U <user> -d statux

DB password is in Secrets Manager (e.g., statux/alerts-api-config -> DATABASE_PASSWORD).

Migrations

Each product manages its own migrations via TypeORM:

npm run migration:generate:statuspages   # Generate migration
npm run migration:run:statuspages        # Run migrations
npm run migration:generate:alerting
npm run migration:run:alerting
npm run migration:generate:synthetics
npm run migration:run:synthetics

note

The Platform API (core schema) does not use TypeORM migrations. Schema changes are applied idempotently with IF NOT EXISTS SQL statements.