Identity and deletion

Two conventions apply across core-api, not to one domain: how rows are identified, and what happens when domain data is deleted.

UUID primary keys

Every domain table's primary key is a uuid column named id (the exceptions are the bookkeeping tables search_outbox and rate_limit_hits, which use a bigint, and the plan_tenants join table), defaulting to a fresh UUID both in Python (default=uuid.uuid4) and in the database (server_default=gen_random_uuid()), so raw SQL inserts behave the same as ORM ones.

id: Mapped[uuid.UUID] = mapped_column(
    Uuid, primary_key=True, default=uuid.uuid4, server_default=text("gen_random_uuid()")
)

Why not sequential integers:

• They're handed out over the API. Account, subscriber and balance ids appear in URLs and response bodies. A sequential integer lets a caller enumerate other customers' records by guessing neighbours, and leaks how many exist.
• Uniformity beats a mixed schema. Rather than UUIDs for the customer-facing tables and integers for the catalog ones - which would leave balances.prepaid_include_id pointing at an integer while balances.id is a UUID - every domain table uses UUIDs.

:::caution UUID keys have no sort order - never paginate by id A list endpoint ordered by an integer id was implicitly ordered by insertion. Ordered by a random UUID, rows shuffle arbitrarily between pages. List service functions therefore order by created_at, id - created_at for the meaningful order, id only to break ties deterministically. :::

Descriptive foreign-key names

Foreign keys are named for what they reference, not with opaque abbreviations:

Column	References
accounts.account_id	the logical account
subscribers.subscriber_id	the logical subscriber
subscribers.account_id	the subscriber's account
balances.account_id	the balance's account
balances.subscriber_id	the balance's subscriber (nullable)

accounts.account_id is the logical account, not the row's id: as of 2026-08-27, Accounts are revisioned the same way Subscribers are (see below) - id is a specific revision row, account_id is the logical account every other table's account_id foreign column actually references.

The API surface follows: path params are /accounts/{account_id}, /subscribers/{subscriber_id}, and list filters are ?account_id= / ?subscriber_id=. A non-UUID value in any of them is a 422, not a 404.

Subscribers and Accounts carry two identities

Subscribers and, since 2026-08-27, Accounts are the revisioned domains, so both need two ids:

• id - this revision row. DELETE /subscribers/{subscriber_id}/revisions/{revision_id} (and the Accounts equivalent) addresses a row by it.
• subscriber_id / account_id - the logical entity that every revision of it shares. Every other endpoint (GET/PATCH /subscribers/{subscriber_id}, /move, /close, and the same shape for /accounts/{account_id}) addresses the logical entity, resolving to whichever revision covers the requested moment.

subscriber_id/account_id is minted by the column default only when a genuinely new subscriber/account is created. Code that writes a further revision of an existing one must copy the existing value explicitly - letting the default fire would silently create a second, unrelated subscriber/account. This is why Accounts' own logical id is account_id, not the row's id - see the table above.

balances.subscriber_id is nullable

An account-level balance shared across a customer's subscribers has no subscriber, so subscriber_id is a real NULL (UUIDs have no sentinel value). Two consequences worth knowing:

• Balance.subscriber_id == None never matches in SQL - the account-level lookup needs IS NULL. app/services/balances.py branches on this explicitly.
• Prepaid selection ("this subscriber's buckets plus the account's shared ones") is or_(Balance.subscriber_id.is_(None), Balance.subscriber_id == subscriber_id).

Soft deletion

Domain data is never hard-deleted. The domain tables (accounts, subscribers, plans, rates, balances, customers, tickets, lines, connectors, tenants, ...) carry a nullable deleted_at timestamptz; every DELETE endpoint stamps it, and every read filters deleted_at IS NULL. A soft-deleted row is indistinguishable from a missing one to any caller - GET returns 404, list endpoints exclude it and don't count it in meta.total, and name/key lookups used for cross-domain validation stop resolving it. Users are soft-deleted through status/deleted_at (see Users, invites and lifecycle).

Some tables are deliberately outside this: access and bookkeeping rows are removed for real (memberships, roles, escalation_matrix entries, ticket_members, notifications, MFA recovery codes), and api_keys and invites keep a status instead.

Domain lookups in the service layer avoid db.get(Model, pk): it can't express the deleted_at IS NULL filter, so they are select(...).where(Model.id == pk, Model.deleted_at.is_(None)) instead.

deleted_at is not exposed in any *Read response schema. Since reads never return deleted rows, the field would always be null over the wire.

Accounts and subscribers: archive

Accounts and subscribers also have a separate, reversible archived_at (copied onto every revision of the logical entity). DELETE /accounts/{account_id} and DELETE /subscribers/{subscriber_id} archive the whole entity and POST .../restore reverses it; deleted_at on these tables is only set when a future revision is removed (DELETE .../revisions/{revision_id}).

Uniqueness is scoped to live rows

A soft-deleted row still physically occupies its unique key, which would mean deleting a rate and re-creating it under the same key fails forever. Every uniqueness rule is therefore a partial unique index scoped to live rows rather than a table constraint, and is per tenant (tenant_id is part of the key; platform rows, tenant_id IS NULL, get their own partial index):

Index(
    "uq_rates_tenant_key_active", "tenant_id", "key", unique=True,
    postgresql_where=text("deleted_at IS NULL"),
)

Index	Table	Columns
uq_rates_tenant_key_active	rates	tenant_id, key
uq_plans_tenant_name_active	plans	tenant_id, name
uq_plans_platform_name_active	plans (platform)	name
uq_tariff_categories_tenant_value_active	tariff_categories	tenant_id, value
uq_prepaid_includes_tenant_name_active	prepaid_includes	tenant_id, name
uq_prepaid_includes_tenant_external_id_active	prepaid_includes	tenant_id, external_id
uq_subscriber_groups_tenant_alias_active	subscriber_groups	tenant_id, alias
uq_tenant_configs_tenant_id_active	tenant_configs	tenant_id
uq_tenants_name_active	tenants	name

(Connectors, integrations and mail settings follow the same pattern.)

What soft deletion means per domain

Domain	DELETE does
Accounts	archives the account; does not cascade to its subscribers/balances; POST /accounts/{account_id}/restore reverses it
Subscribers	archives the subscriber (POST /subscribers/{subscriber_id}/restore reverses it); DELETE .../revisions/{revision_id} retires one future revision, extending the previous one over the gap
Plans	retires the catalog entry; the name stops resolving for new references. 409 if a non-deleted subscriber still has it as its plan, if a rate override still references its name, or if a platform plan is still assigned to tenants
Rates	retires the rate card and frees its key for reuse
Prepaid includes	retires the template; balances already granted from it keep working
Balances	retires the balance - it stops being selectable, chargeable and grantable, while its counters and tx charge log survive for audit
Tenant config	retires a tenant's override row; the tenant falls back to the platform/code defaults and can be configured again later
Tenants	retires the tenant and switches off its connectors' scheduled work

:::note Deleting an account does not cascade Most cross-domain references (balances.account_id, subscribers.account_id, ...) are plain columns without a foreign-key constraint; only tenant_id and a few identity/ticket/mail links are real FKs (see Balances). Archiving an account leaves its subscriber and balance rows as they are. If cascading retirement is wanted, it's a deliberate design decision to make, not something to assume. :::

Restore endpoints are added per-domain, not by default

There is no generic restore mechanism: an un-delete on a partial-unique-indexed table has to handle the case where the key was reused in the meantime, which is a real design question, not a one-liner. Accounts, subscribers, users and Plans (POST /plans/{plan_id}/restore, 409 if another live plan has since taken the name) have one; each re-checks what it needs itself. Add the same for another domain when there's a concrete requirement, following that same re-check-don't-assume approach.