Data Model

Full DDL: api/app/db/schema.sql. This document explains the why behind the shape.

Entity relationship overview

resellers ──< customer_resellers >── customers
    │           (cost_key, state,          │
    │            is_primary)               │
    │                                      │
    └──────< assets (properties) >─────────┘
              reseller_id, customer_id,
              cost_key, state
                 │
                 ├──< plans ──< enrollments
                 ├──< usage_records
                 └──< exchange_transactions >── counterparties

utilities ──< plans
    └──< rate_history

──< = one-to-many. Every arrow into assets points at the same table regardless of what kind of asset it is. customer_resellers is a many-to-many join carrying its own data (cost_key, state, is_primary) rather than a plain link table — see below.

The saved-query registry (saved_queries) is not in this diagram: it lives in a separate database entirely, with no foreign keys back into anything here. See "The query registry" below.

The tenancy model

A reseller is the tenant boundary. Every scoped query filters on reseller_id, so two resellers never see each other's customers, properties, or transactions.

A customer is not owned by a reseller. It attaches to many, one row per relationship in customer_resellers — the same end customer can be serviced by different resellers in different states without duplicating the customer record.

The cost key lives on the relationship, not on the customer and not on the reseller, because the key — and the state it applies in, and the effective dates — are facts about the relationship. It defaults to the reseller's UUID so it is never empty and always resolves back to a reseller row, and can be overridden with the reseller's own billing or settlement code.

Uniqueness is (customer_id, reseller_id, state) — state is part of the key on purpose, so the same customer/reseller pair can legitimately appear once for TX and again for NM.

At most one primary attachment per customer, enforced twice over: the service layer demotes the incumbent before promoting (in that order — a unique index cannot be deferred), and a partial unique index makes two primaries impossible even under concurrent writes:

CREATE UNIQUE INDEX uq_customer_one_primary_reseller
    ON customer_resellers (customer_id) WHERE is_primary;

Because it is partial, the many non-primary rows per customer are unconstrained — only the primaries collide.

A property is an asset carrying reseller_id, customer_id, state, and cost_key. The cost key is denormalized onto the asset so per-property attribution doesn't need a join on every read, but it is derived at write time from the customer's attachment rather than accepted from the client — a property can never be stamped with a key that doesn't correspond to a real relationship.

The query registry

Saved queries (saved_queries) live in a second, separate database — its own connection string (AXIOM_QUERY_DATABASE_URL), its own DeclarativeBase, no shared migration path with the tables above. This is a deliberate physical separation, not just a different schema:

• Different lifecycle. Saved queries are ephemeral request artifacts with their own retention/TTL; the inventory above is the system of record and is retained indefinitely.
• Different write profile. The registry is append-heavy and read rarely; the inventory is read-heavy. Keeping them apart stops query-journal churn from competing for the inventory database's cache.
• Blast radius. A runaway client generating millions of saved queries cannot fill the disk the parcel inventory depends on.
• No foreign keys cross the boundary. reseller_id and any asset ids referenced by a saved query are stored as opaque strings — the registry never assumes the referenced row in the other database still exists, since either database can be restored or pruned independently of the other.

A saved query is addressable by a short, quotable query_id (e.g. qry_7f3a9c2e4b81) assigned at instantiation, and is either stateful (the response body is frozen alongside the request) or stateless (only the request is stored, and replaying it hits live data).

usage_events lives in the same registry database, for the same reasons — one row per authorized, billable request, written by app/core/metering.py at the moment require_scope authorizes a call. See AUTH_ARCHITECTURE.md for the full metering model.

portal_users also lives here, though its fit is looser — it's platform login identity (for viewing docs/Swagger), not an ephemeral request-shaped artifact like the two tables above. Filed alongside them anyway rather than justifying a third database for a handful of rows; flagged honestly as a stretch of this database's original rationale, not claimed as the ideal long-term home.

Self-provisioning when only one physical Postgres instance exists

On a managed Postgres provider that only grants one free instance per account (Render's free tier, notably — see DEPLOYMENT.md), there's no way to get a second, fully separate server without paying for one. Two databases still exist — genuinely separate CREATE DATABASEs, no shared tables — they just share the same physical server as a cost-driven compromise, not the original two-instance design.

app/core/db_bootstrap.py automates the one manual step that setup would otherwise require: on AXIOM_ENVIRONMENT=local startup, if AXIOM_DATABASE_URL and AXIOM_QUERY_DATABASE_URL resolve to the same host, the app connects with its already-working inventory credentials and issues CREATE DATABASE for the registry database itself, using the inventory user's CREATEDB privilege (granted by default on Render and most managed Postgres providers). Verified locally: dropping the registry database and booting from cold correctly recreates it end to end (portal login, session check, everything downstream all pass on the same boot); a second boot with the database already present is a harmless no-op (DuplicateDatabaseError caught, nothing logged as an error). If the two URLs point at different hosts — the cleaner setup, once a second real instance exists — this function does nothing.

This isn't just a local-sandbox mechanism: it's exactly what runs the live deployment at https://axiom-exchange-api.onrender.com, which really does use a single free-tier Postgres instance for both databases — see "Live deployment verification" in docs/TESTING.md for the real-world confirmation that the first boot against Render's actual server succeeded end to end.

Why one assets table instead of one table per asset type

A well pad, a solar farm, a service territory, a pipeline segment, and a customer premise have almost nothing in common structurally — different attributes, different owners, different lifecycles. Modeling them as separate tables (well_pads, solar_farms, premises, ...) means every new asset type is a migration, and anything that needs to query "assets near this point" or "assets in this polygon" regardless of type has to union across N tables.

Instead:

• asset_type (plain text, not an enum) discriminates what kind of thing a row is. Adding a new kind of asset — say, transmission_line — is an INSERT, not a migration.
• geom is GEOMETRY (not POLYGON or POINT) — the same column holds a point, a line, or a polygon depending on what shape that asset actually has. /geo/query, /geo/bbox, and /geo/query/radius all work uniformly across every shape in one query (verified in testing: a single polygon query returned both a Polygon solar-farm asset and a Point premise asset in the same FeatureCollection).
• attributes JSONB holds whatever fields are specific to that asset type — capacity_mw and commissioned for a solar farm, api_number for a well pad, meter_type for a premise — without a schema change. A GIN index on attributes keeps ad-hoc filtering on those fields reasonably fast if you outgrow "just fetch by type and filter in the app."
• A premise is just an asset with asset_type='premise'. This is what lets usage_records and enrollments reference "a premise" through the ordinary assets.id foreign key, with no special-casing — they'd work identically for a Point asset of any other type too.

The trade-off: you lose column-level type constraints per asset type (nothing stops someone from putting capacity_mw on a well_pad). If a particular asset type grows enough structured, always-present fields that this becomes a real problem, that's the signal to split it into its own table with a foreign key back to a slim assets row for the shared geometry/metadata — the generic table is a starting point, not a permanent constraint.

Why PostgreSQL + PostGIS + JSONB instead of a separate NoSQL store

The original ask was "PostgreSQL or Cosmos/Aurora type of NoSQL database due [to] the type of structured and unstructured data." The attributes JSONB pattern above is the unstructured-data answer — Postgres's JSONB type gives schema-per-document flexibility (index individual keys, query with @>/?/path operators, no migration to add a field) in the same table as strongly-typed structured columns and foreign keys. That avoids running two databases (one relational, one document) and keeping them consistent, while still being directly portable to a managed NoSQL-adjacent-but-relational service:

• Amazon Aurora PostgreSQL-Compatible — same wire protocol, same SQL, same PostGIS/JSONB support, different AXIOM_DATABASE_URL.
• Azure Database for PostgreSQL — Flexible Server — same.
• If a future requirement genuinely needs a schemaless, globally distributed document store (Cosmos DB's actual sweet spot), that's an additive service for one specific workload, not a wholesale replacement — e.g. audit logs or raw telemetry ingestion could live in Cosmos DB while the transactional/geospatial core stays in Postgres.

Table reference

Indexing strategy

• GIST index on every geometry column (ix_assets_geom_gist, ix_utilities_territory_gist) — required for ST_Intersects/ ST_Contains/ST_DWithin to use an index scan instead of a full table scan.
• GIN index on assets.attributes — supports @> containment queries against arbitrary JSONB keys.
• Composite B-trees on the tenancy access paths: (reseller_id, asset_type), (customer_id, state), (cost_key, reseller_id).
• The partial unique index on customer_resellers (customer_id) WHERE is_primary.
• B-tree indexes on every foreign key and every column used as a common filter (asset_type, status, transaction_type, etc.).
• In the query registry: B-trees on query_id (unique), reseller_id, endpoint, is_stateful, and expires_at; on usage_events, subject, organization_id, environment, endpoint, billing_code, and occurred_at — see AUTH_ARCHITECTURE.md for the metering model these support.