Database Schema

The AppMecca database contains 66 tables as of migration 055. Most use UUID primary keys and TIMESTAMPTZ timestamps. Nine tables carry an updated_at trigger that auto-updates on row modification.

There is no environment column

Every AppMecca environment has its own RDS database, so an environment column held the same value on every row of every table. Migration 010_drop_environment_column dropped it from ~31 tables and reshaped every composite UNIQUE constraint that had used it as the leading column. Any query, index or ON CONFLICT clause that still references environment is stale — the static guard at appmecca-api/tests/unit/test_no_dropped_environment_column.py fails CI if one is reintroduced.

The tables below are the core of the deployment path. They are a subset — the full set also covers multi-tenancy (users, groups, group_members, sessions, invites, oauth_identities, linked_git_accounts, linked_docker_accounts), the queue (deploy_queue, admission_overrides), certificates (acme_challenges, acme_account_keys, acme_certificate_material, acme_delegated_zones, ns_zones), health and observability (health_checks, health_incidents, status_checks, platform_warnings, platform_notifications, oom_events, phantom_running_observations), storage (drive_usage, app_storage_identity, static_app_origins, app_backups, pitr_restore_jobs), email (ses_identities, email_events, email_suppressions, mailer_quota_counters), and cost (usage_records, pricing).

Core Table Overview

#	Table	Purpose	Key Constraint
1	api_keys	API key authentication	Unique key_prefix (active only)
2	apps	Registered applications	Unique (group_id, app_name, branch)
3	builds	Build history	None — identity is the id primary key
4	deployments	Active deployment state	Unique (app_name, branch); deployment_id unique
5	deployment_history	Immutable audit records	None (append-only)
6	service_ports	Host port allocation	Unique (app_name, branch, service_name); unique host_port
7	target_groups	ALB target group mapping	Unique (app_name, service_name, branch)
8	app_env_vars	Environment variables	Unique (app_name, branch)
9	custom_domains	Custom domain certificates	Unique (app_name, domain); global unique domain
10	agents	Agent heartbeats and status	Unique instance_id
11	agent_versions	Published agent version	Singleton row (one per database)
12	deploy_locks	Deployment locks with TTL	Unique (app_name, branch)
13	deploy_queue	Build/deploy/ops actions for the agent	Generated lane column (control | deploy)

Table Details

api_keys

Stores API key hashes for authentication. Keys use the am_k1_ prefix. The full key is hashed with SHA-256 — the raw key is never stored.

Column	Type	Notes
id	UUID	Primary key
key_prefix	VARCHAR(32)	First 13 chars, for identification (e.g. am_k1_abc123d)
key_hash	VARCHAR(128)	SHA-256 hash of full key
username	VARCHAR(256)	Key owner
scope	VARCHAR(32)	admin, deploy, or read
created_at	TIMESTAMPTZ
last_used_at	TIMESTAMPTZ	Updated on each use
revoked_at	TIMESTAMPTZ	NULL = active, set = revoked

Index: idx_api_keys_prefix on key_prefix WHERE revoked_at IS NULL

apps

Registered applications. Each app is uniquely identified by (group_id, app_name, branch) — the group scoping arrived with multi-tenancy so two tenants can register the same repo under the same natural name.

Column	Type	Notes
id	UUID	Primary key
app_name	VARCHAR(128)	Canonical name — group-prefixed for tenant apps
display_name	VARCHAR(256)	Original display name
slug	VARCHAR(128)	URL-safe identifier; branch-invariant (every branch row shares it)
group_id	UUID	Owning group; system-group apps keep their bare app_name
branch	VARCHAR(256)	Default main
is_production	BOOLEAN	Marks the branch that fronts the app's canonical URL
repository	TEXT	Normalized Git URL
description	TEXT
config_path	VARCHAR(512)	Path to appmecca.yaml (default appmecca.yaml)
deploy_config	JSONB	The deploy section from appmecca.yaml
deploy_config_overrides	JSONB	Dashboard-set read-time overlay (e.g. primary services)
services	JSONB	Service definitions as last deployed
containers	JSONB	Container build definitions
visibility	TEXT	public or private (private routes via the auth gateway)
database_enabled	BOOLEAN	Derived from appmecca.yaml — has app database
cache_enabled	BOOLEAN	Derived — has app cache
email_enabled	BOOLEAN	Derived — opt-in only, never inferred
bucket_enabled	BOOLEAN	Derived — has per-app S3 buckets
bucket_names	JSONB	Declared bucket names, stamped for purge
preflight_status	VARCHAR(32)	pending, no_access, needs_config, config_invalid, ready, exempt
preflight_result	JSONB	Structured verdict from the git-preflight RPC
preflight_nonce	BIGINT	Compare-and-set guard so a stale re-check is discarded
git_clone_user_id / git_clone_provider	UUID / VARCHAR(32)	Linked OAuth identity used to clone
seed_from_reference / seeded_at / seeded_from_bundle_id	TEXT / TIMESTAMPTZ / UUID	One-shot seed-from-backup on first deploy
deploy_status	VARCHAR(32)	registered, running, stopped, etc.
latest_build	VARCHAR(256)	Most recent build tag
build_count	INTEGER	Total builds
created_by / created_by_user_id	VARCHAR(256) / UUID	Who registered it
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ	Auto-updated via trigger

Indexes: idx_apps_group_app_name, plus the unique (group_id, app_name, branch) constraint

builds

Build history. One row per build attempt.

Column	Type	Notes
id	UUID	Primary key — the identity of a build
app_name	VARCHAR(128)
display_name	VARCHAR(256)
tag	VARCHAR(256)	Image tag (usually the commit hash)
commit_hash	VARCHAR(64)	Git commit
commit_message / commit_author / commit_committed_at	TEXT / VARCHAR / TIMESTAMPTZ	Captured by CodeBuild at checkout
branch	VARCHAR(256)
status	VARCHAR(32)	pending, building, completed, failed
codebuild_id	VARCHAR(512)	AWS CodeBuild build ID
codebuild_phase	VARCHAR(64)	Current build phase
duration_seconds	INTEGER
images	JSONB	Built image URIs
error_message	TEXT	Failure reason
build_log	TEXT	Captured build output
created_by	VARCHAR(256)
started_at	TIMESTAMPTZ
completed_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ	Auto-updated via trigger

(app_name, tag) is deliberately not unique

Migration 042 dropped builds_app_name_tag_key. A tag is a branch-agnostic commit hash, so every rebuild of an already-built commit (mecca app redeploy with no new push, "Rebuild & Deploy", a sibling branch at the same commit) is a legitimate second row. Upserts key on (id).

deployments

Active deployment state. One row per (app_name, branch) — represents the current or most recent deployment.

Column	Type	Notes
id	UUID	Primary key
deployment_id	VARCHAR(256)	Unique deployment identifier
app_name	VARCHAR(128)
display_name	VARCHAR(256)
branch	VARCHAR(256)
image_tag	VARCHAR(256)	Deployed image tag
status	VARCHAR(64)	queued, setup_in_progress, ready_to_deploy, completed, failed, cancelled
phase	VARCHAR(64)	Deployment progress phase
services	JSONB	Service status map
placement_plan	JSONB	Host assignment plan
instance_status	JSONB	Per-instance deployment state
leader_instance	VARCHAR(64)	Instance that claimed leadership
infrastructure	JSONB	Created TGs, ALB rules
previous_tag	VARCHAR(256)	Tag before this deployment
error_message	TEXT
created_by	VARCHAR(256)
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ	Auto-updated via trigger
completed_at	TIMESTAMPTZ

deployment_history

Immutable audit records. One row per completed deployment, carrying the image tag, the previous tag, per-service status, duration and an env_vars_snapshot.

service_ports

Host port allocation. Ports are in the range 7001-8999, enforced by a CHECK constraint. Unique per (app_name, branch, service_name), and host_port is unique across the database.

The host port is the one that matters

The private-zone A record points at the EC2 host's private IP, so anything pairing a port with that address must use the allocated host_port — never the container port declared in appmecca.yaml.

target_groups

Maps (app_name, service_name, branch) to an ALB target group name and ARN, plus the live active_color for blue/green. The branch column arrived in migration 037: without it a single row was shared across branches, so deploying one branch could deregister its sibling's container.

app_env_vars

Application environment variables stored as a JSONB blob. One row per (app_name, branch).

custom_domains

Customer-owned domains attached to an app's services, with their certificate and DNS-validation state. UNIQUE (app_name, domain) plus a global UNIQUE INDEX on domain alone, so a domain can be claimed by exactly one app platform-wide.

Beyond the identity and certificate columns (certificate_arn, certificate_arn_global, cname_target, validation_cname, validation_target, certificate_source, certificate_expires_at, last_renewal_at, acme_order_url), migration 034 added the uniform-procedure and apex columns:

Column	Purpose
certificate_target	alb or cloudfront — where the certificate is attached
challenge_type	dns-01 for all new domains; legacy rows stay http-01 so they keep renewing without the customer having to add a challenge record
is_apex	Set server-side by multi-part-TLD-aware apex detection
redirect_to	Canonical host for an apex row in redirect mode
distribution_id	Stamped when a CloudFront alias is attached; makes detach and redeploy-reconcile O(1)
listener_attached_at	When the ALB listener certificate was attached
alias_attached_at	When the CloudFront alias was attached
last_attempt_at, attempt_count	Issuance backoff, protecting against Let's Encrypt rate limits
error_message	Migration 049 — the observed-vs-expected diagnosis for a failing delegation, so the reason is not confined to agent logs

The two *_attached_at columns exist to make one specific failure state queryable: a row that reached status = 'active' but was never attached to anything does not serve, while looking healthy everywhere else. appmecca health reports that drift.

ns_zones

One row per domain in the NS-hosting tier (migration 035): domain (unique, CITEXT), group_id, hosted_zone_id, name_servers, imported_records (the pre-cutover scan snapshot), and status (scanning → pending_ns → active, plus releasing). Migration 054 added updated_at plus its trigger so the reaper that reclaims a stranded row has an age gate to key on.

deploy_queue

Every action the agent must perform — builds, deploys, undeploys, database operations, execs, drive queries, purges and the agent-mediated RPCs. The agent claims a row with an atomic UPDATE … WHERE status = 'queued' AND claimed_by IS NULL RETURNING *.

lane is a generated stored column derived from action_type (migration 036, extended by 046): control-plane RPCs resolve to control and everything else to deploy, so a fast request/response RPC is never queued behind the deployment admission gates. Because it is generated, it cannot be mis-set and needs no writer changes.

A CHECK constraint (deploy_queue_status_error_consistency) keeps status and error_message consistent: a row whose status is completed, build_completed or undeployed must carry an empty or NULL error_message.

agents

Agent heartbeats, keyed on a unique instance_id. Agents upsert their status every 30 seconds: containers (a JSONB array whose entries carry app, service, branch and host_port), memory, disk, agent_version, plus the cordon state (schedulable, cordon_reason, cordoned_at, cordoned_by). Stale records (no heartbeat for >5 minutes) indicate a dead agent.

The containers JSONB shape is load-bearing

branch and host_port are probed by JSONB containment predicates elsewhere — the cross-host orphan reconciler and the network-isolation reconciler. Adding a key to one of those predicates means adding it to the heartbeat writer too, or the query silently matches nothing.

agent_versions

A singleton row (one per database, enforced by the agent_versions_singleton unique index) tracking the published agent wheel version, the rollout state machine (update_status, pioneer_instance, pioneer_verified_at, published_at) and the rollback target (previous_version, previous_wheel_key).

deploy_locks

Atomic deployment locks with TTL-based auto-expiry. Prevents concurrent deployments to the same (app_name, branch).

Trigger Function

All tables with updated_at columns use this trigger:

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Applied to: apps, builds, deployments, target_groups, app_env_vars, custom_domains, deploy_queue, cron_jobs (migration 001) and ns_zones (migration 054).

The appmecca_logs Database

Unified log storage and container stats live in a separate appmecca_logs database, outside the alembic chain. It is managed by appmecca.db.logs_schema with direct CREATE TABLE IF NOT EXISTS.

Migration Management

Migrations are managed by Alembic and ship inside the appmecca-common wheel. See Migrations for details.