Database Schema

Generated from backend/db/migrations/*.sql, applied in order.

users

Column	Type	Notes
id	UUID	PK, gen_random_uuid()
name	TEXT
email	VARCHAR(255)	unique
password_hash	TEXT	bcrypt
is_verified	BOOLEAN	default FALSE (unused by current routes)
created_at	TIMESTAMPTZ	default NOW()

projects

Column	Type	Notes
id	UUID	PK
name	VARCHAR(255)
slug	VARCHAR(255)	unique
owner_id	UUID	FK → users.id, ON DELETE CASCADE
description	TEXT	nullable
created_at / updated_at	TIMESTAMPTZ	updated_at auto-touched by trigger

environments

Column	Type	Notes
id	UUID	PK
project_id	UUID	FK → projects.id, ON DELETE CASCADE
name	VARCHAR(255)
slug	VARCHAR(255)	unique per project
sdk_key	UUID	default gen_random_uuid(), rotatable
icon	TEXT	default 'globe'
url	TEXT	drives the CORS allowlist
created_at / updated_at	TIMESTAMPTZ

UNIQUE(project_id, slug).

flags

Column	Type	Notes
id	UUID	PK
project_id	UUID	FK → projects.id, ON DELETE CASCADE
key	VARCHAR(255)	unique per project
name	VARCHAR(255)
type	VARCHAR(50)	CHECK IN ('boolean','string','number','json')
default_value	JSONB
description	TEXT	nullable
created_at / updated_at	TIMESTAMPTZ

UNIQUE(project_id, key).

flag_values

Column	Type	Notes
id	UUID	PK
flag_id	UUID	FK → flags.id, ON DELETE CASCADE
environment_id	UUID	FK → environments.id, ON DELETE CASCADE
is_enabled	BOOLEAN	default FALSE
rollout_percentage	INTEGER	CHECK BETWEEN 0 AND 100, default 0
targeting_attribute	VARCHAR(255)	nullable
targeting_value	TEXT	nullable
targeting_return_value	JSONB	nullable
updated_at	TIMESTAMPTZ	auto-touched by trigger

UNIQUE(flag_id, environment_id) — one value row per flag per environment.

audit_logs

Column	Type	Notes
id	UUID	PK
project_id	UUID	FK → projects.id, ON DELETE CASCADE
flag_id	UUID	FK → flags.id, ON DELETE CASCADE, nullable
environment_id	UUID	FK → environments.id, ON DELETE CASCADE, nullable
user_id	UUID	FK → users.id, ON DELETE CASCADE
change_summary	TEXT
old_value / new_value	TEXT	nullable
reason	TEXT	nullable, currently unused by callers
type	TEXT	e.g. flag_toggle, key_rotation, project_creation
domain	VARCHAR(50)	CHECK IN ('project','environment','flag')
created_at	TIMESTAMPTZ

Indexed by (project_id, created_at DESC) for fast per-project log listing.

Triggers

A shared set_updated_at() trigger function stamps updated_at = NOW() on update, applied to flag_values, flags, environments, and projects.

schema_migrations

Created on demand by backend/db/migrate.js, not a numbered migration itself — tracks which migration files have already been applied so re-running npm run migrate is safe.