Core Tables

agencies

Top-level tenant table. Every resource in the system is scoped to an agency.

Column	Type	Default	Nullable	Description
id	uuid	uuid_generate_v4()	no	Primary key
name	varchar(255)	—	no	Agency display name
slug	varchar(100)	—	no	URL-safe identifier (unique)
ultravox_api_key_encrypted	bytea	—	yes	Encrypted Ultravox API key
ultravox_api_key_added_at	timestamptz	—	yes	When the Ultravox key was stored
ultravox_api_key_validated_at	timestamptz	—	yes	When the Ultravox key was last validated
telnyx_api_key_encrypted	bytea	—	yes	Encrypted Telnyx API key
telnyx_public_key_encrypted	bytea	—	yes	Encrypted Telnyx public key
telnyx_account_sid_encrypted	bytea	—	yes	Encrypted Telnyx account SID
telnyx_api_key_added_at	timestamptz	—	yes	When the Telnyx key was stored
telnyx_api_key_validated_at	timestamptz	—	yes	When the Telnyx key was last validated
telnyx_application_id	varchar(255)	—	yes	Telnyx Call Control Application ID
telnyx_outbound_profile_id	varchar(255)	—	yes	Telnyx Outbound Voice Profile ID
telephony_configured	boolean	false	no	Whether Telnyx provisioning is complete
webhook_secret	varchar(64)	—	no	Auto-generated secret for webhook URL auth
branding	jsonb	'{}'	yes	Custom branding settings
settings	jsonb	'{}'	yes	Agency-level configuration
created_at	timestamptz	now()	no	Record creation timestamp
updated_at	timestamptz	now()	no	Last update timestamp (auto-updated)

Constraints:

Constraint	Type	Details
agencies_pkey	Primary key	id
agencies_slug_unique	Unique	slug
agencies_name_not_empty	Check	length(trim(name)) > 0
agencies_slug_format	Check	slug ~ '^[a-z0-9-]+$' (lowercase alphanumeric and hyphens)

Indexes:

Index	Columns	Notes
idx_agencies_slug	slug	Lookup by URL slug
idx_agencies_created_at	created_at	Chronological listing

Triggers:

Trigger	Event	Function
agency_set_webhook_secret	BEFORE INSERT	trigger_agency_set_webhook_secret() — auto-generates webhook_secret
update_agencies_updated_at	BEFORE UPDATE	update_updated_at_column() — keeps updated_at current

Related edge functions: save-ultravox-integration, store-telnyx-credentials, provision-telephony, get-integration-status

---

users

Platform users linked to Supabase Auth. Each user has a role determining access level.

Column	Type	Default	Nullable	Description
id	uuid	—	no	Primary key (matches auth.users.id)
agency_id	uuid	—	yes	FK → agencies.id (required for agency roles)
client_id	uuid	—	yes	FK → clients.id (required for client roles)
role	user_role	—	no	Access level enum
email	varchar(255)	—	no	User email address
full_name	varchar(255)	—	yes	Display name
avatar_url	text	—	yes	Profile image URL
settings	jsonb	'{}'	yes	User preferences
is_active	boolean	true	no	Soft-delete flag
created_at	timestamptz	now()	no	Record creation timestamp
updated_at	timestamptz	now()	no	Last update timestamp (auto-updated)
last_login_at	timestamptz	—	yes	Most recent login timestamp

Constraints:

Constraint	Type	Details
users_pkey	Primary key	id
users_id_fkey	Foreign key	id → auth.users(id) ON DELETE CASCADE
users_agency_id_fkey	Foreign key	agency_id → agencies(id) ON DELETE CASCADE
users_client_id_fkey	Foreign key	client_id → clients(id) ON DELETE CASCADE
users_email_not_empty	Check	length(trim(email)) > 0
users_agency_user_has_agency	Check	Agency roles (agency_owner, agency_member) must have agency_id
users_agency_user_no_client	Check	Agency roles must not have client_id
users_client_user_has_client	Check	Client roles (client_admin, client_viewer) must have client_id
users_client_user_no_agency	Check	Client roles must not have agency_id

Indexes:

Index	Columns
idx_users_agency_id	agency_id
idx_users_client_id	client_id
idx_users_email	email
idx_users_role	role
idx_users_active	is_active

Triggers:

Trigger	Event	Function
update_users_updated_at	BEFORE UPDATE	update_updated_at_column()

---

clients

Client organizations within an agency. Clients can have campaigns and be associated with calls.

Column	Type	Default	Nullable	Description
id	uuid	uuid_generate_v4()	no	Primary key
agency_id	uuid	—	no	FK → agencies.id
name	varchar(255)	—	no	Client display name
settings	jsonb	'{}'	yes	Client-level configuration
is_active	boolean	true	no	Soft-delete flag
created_at	timestamptz	now()	no	Record creation timestamp
updated_at	timestamptz	now()	no	Last update timestamp (auto-updated)

Constraints:

Constraint	Type	Details
clients_pkey	Primary key	id
clients_agency_id_fkey	Foreign key	agency_id → agencies(id) ON DELETE CASCADE
clients_name_not_empty	Check	length(trim(name)) > 0

Indexes:

Index	Columns	Notes
idx_clients_agency_id	agency_id	Agency-scoped queries
idx_clients_agency_active	(agency_id, is_active)	Active clients per agency
idx_clients_created_at	created_at	Chronological listing

Triggers:

Trigger	Event	Function
update_clients_updated_at	BEFORE UPDATE	update_updated_at_column()

---

app_config

Global key-value configuration store. Not agency-scoped.

Column	Type	Default	Nullable	Description
key	text	—	no	Primary key — configuration key name
value	text	—	no	Configuration value
description	text	—	yes	Human-readable description of the setting
created_at	timestamptz	now()	yes	Record creation timestamp
updated_at	timestamptz	now()	yes	Last update timestamp

Constraints:

Constraint	Type	Details
app_config_pkey	Primary key	key