Data Model

Full DDL lives at api/app/db/schema.sql in the repository. This document explains the why behind the shape.

Entity relationship overview

resellers ────┐
                │ (many-to-many, one row per relationship)
                ▼
         customer_resellers  ── cost_key, state, is_primary
                ▲
                │
customers ──────┘
    │
    └──< assets (properties) >── reseller_id, customer_id, cost_key, state
              │
              ├──< usage_records
              ├──< enrollments >── plans >── utilities >──< rate_history
              └──< exchange_transactions >── counterparties

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. This is the core requirement: 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, not on the reseller — on the join row, because the key, the state it applies in, and the effective dates are all 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 a reseller's own billing or settlement code. It's indexed on its own and jointly with reseller_id, so cost records roll up by it directly.

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 rollups and per-property attribution don'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.

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. Modeling them as separate tables means every new asset type is a migration, and anything that needs to query "assets in this polygon" regardless of type has to union across N tables.

Instead:

• asset_type (plain text, not an enum) discriminates the row. Adding transmission_line is an INSERT, not a migration.
• geom is GEOMETRY (not POLYGON or POINT) — the same column holds a point, line, or polygon depending on what the asset actually is. All spatial endpoints work uniformly across every shape in one query (verified: a single polygon query returned both a Polygon property and a Point premise in the same FeatureCollection).
• attributes JSONB holds type-specific fields — sqft for a warehouse, pump_hp for a well pad, meter_type for a premise — without a schema change. A GIN index keeps ad-hoc filtering on those fields fast.
• A premise is just an asset with asset_type='premise', which is what lets usage_records and enrollments reference "a premise" through the ordinary assets.id foreign key with no special-casing.

The trade-off: you lose column-level type constraints per asset type. If a particular type grows enough always-present structured 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 — the generic table is a starting point, not a permanent constraint.

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

The original requirement was "PostgreSQL or Cosmos/Aurora type of NoSQL database due [to] the type of structured and unstructured data." The attributes JSONB pattern is the unstructured-data answer — JSONB 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 and keeping them consistent, while staying directly portable:

• 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 workload — audit logs, raw telemetry — not a wholesale replacement of the transactional/geospatial core.

Table reference

Indexing strategy

• GIST on every geometry column — required for ST_Intersects / ST_Contains / ST_DWithin to use an index scan rather than a full scan.
• GIN 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-trees on every foreign key and common filter column.