feat/sites-v2

Bill insert will crash on folio collision between hero RPUs

packages/database/src/demo/seed-demo-org.ts:357

The seeder dedupes on (utility_contract_id, unique_index) but inserts the real folio verbatim. bills_folio_unique is a non-partial UNIQUE constraint on folio — if any two hero RPU bills share a non-null folio (CFE explicitly does not guarantee folio uniqueness across RPUs), the second INSERT throws a unique_violation and crashes the seeder. Fix: set folio = NULL on insert (folios have no semantic value in a demo preview DB) or add ON CONFLICT (folio) DO UPDATE … on the insert.

GREATEST(ended_at, date) closes open coverage windows on re-run

packages/database/src/demo/seed-demo-org.ts:496

PostgreSQL GREATEST ignores NULLs — GREATEST(NULL, '2024-01-01'::timestamptz) returns '2024-01-01', not NULL. If a coverage row has ended_at = NULL (open window) and the fixture's coverage_ended_at is non-null, the update closes the window. The site-metrics-seam.md rule explicitly calls this a 'silent read-blackout' bug. Fix: use CASE WHEN ended_at IS NULL OR ${r.coverage_ended_at} IS NULL THEN NULL ELSE GREATEST(ended_at, ${r.coverage_ended_at}::timestamptz) END.

Hero contract query in extractor has no deleted_at IS NULL filter

packages/database/src/demo/extract-prod-fixture.ts:107

A soft-deleted and re-created contract with the same contract_number would cause contracts.length != 5, surfacing loudly. Fix: add AND uc.deleted_at IS NULL to the WHERE clause to select only the active row.

Committed doc contains real production RPU numbers and org public IDs

docs/development/sites-devices-demo/execution-prompt.md

The committed markdown contains 5 production RPU contract numbers (e.g. 506220607811), Supabase org public IDs, and billed amounts. RPU numbers in Mexico identify CFE customer accounts under LFPDPPP. This is documented as a deliberate decision, but should have an explicit data-class label and remain access-controlled if the repo is ever made public.

--out flag can write fixture data outside the gitignored path

packages/database/src/demo/extract-prod-fixture.ts:75

The extractor accepts --out=<any path> without validation. If a developer runs with --out=/tmp/fixture or any path outside packages/database/src/demo/fixture/, production customer data (RPUs, billed amounts, CFE line_items JSONB) is written to an unprotected location that could be accidentally committed. The gitignore only covers the default output path. Add a warning when outDir deviates from the default.

assertPreviewDatabase guard is bypassable via non-Supabase or custom-domain URLs

packages/database/src/demo/seed-demo-org.ts:65

The guard uses url.includes(ref) against two Supabase project refs. A pgBouncer/pooler URL, a custom domain, or an IP-based connection string that omits the refs bypasses the check silently. This is a usability speedbump, not a security boundary. Add a doc comment noting the limitation so future maintainers don't over-rely on it.

SQL injection: all dynamic values are properly parameterized

packages/database/src/demo/extract-prod-fixture.ts

All data values use the postgres template literal client's parameterization. sql.array() and sql.json() are used correctly for arrays and JSONB. No dynamic SQL string concatenation. No findings.

Gitignore pattern is path-specific and correctly scoped

.gitignore

packages/database/src/demo/fixture/ with trailing slash correctly scopes the ignore to that exact directory without accidentally matching other fixture/ directories in the repo.

site_locations seeded with wrong PublicIdPrefix (sit_ instead of loc_)

packages/database/src/demo/seed-demo-org.ts:258

generatePublicId(PublicIdPrefix.Site) is used for site_locations.public_id. The correct prefix is PublicIdPrefix.Location (loc_), consistent with seed-demo-sites.ts line 78 and the domain ontology. Using sit_ mints ids that look like Site ids, breaking any prefix-routing logic.

makes seeded with wrong PublicIdPrefix (ast_ instead of mke_)

packages/database/src/demo/seed-demo-org.ts:445

generatePublicId(PublicIdPrefix.Asset) is used for makes.public_id. The correct prefix is PublicIdPrefix.Make (mke_), per MAKE_PREFIX in packages/database/src/schema/makes.ts. Using ast_ makes Make ids indistinguishable from Asset ids.

No top-level transaction wrapping the seeder

packages/database/src/demo/seed-demo-org.ts

The seeder runs 7 sequential phases without atomicity. If it fails mid-way (e.g. bad template row), the DB is left partially seeded. Re-running is safe due to idempotency, but a partial run during a live demo could produce confusing UI state. Wrapping with sql.begin() would make the seed atomic.

assertPreviewDatabase has no unit tests despite matching the pattern tested in preview-db.test.ts

packages/database/src/demo/seed-demo-org.ts:65

The existing preview-db.test.ts and ops-guards.test.ts cover analogous DB-guard functions. assertPreviewDatabase uses a weaker raw-string check than those guards and has no coverage for edge cases: a URL with the ref in query params, a URL-encoded bypass, or a custom-domain URL. Given the established test pattern and the safety-critical role of this guard, it warrants a small unit test suite.

assetlessTaken flag scope is correct but non-obvious — consider a comment

packages/database/src/demo/seed-demo-org.ts:427

assetlessTaken resets correctly for each (site, variable) pair, but its position inside two nested loops is easy to misread. A one-line comment at declaration site linking to the metric_sources_derivation_per_stream_unique constraint would prevent accidental scope changes.

stats.bills counts processed rows, not inserted rows — misleading on re-run

packages/database/src/demo/seed-demo-org.ts

stats.bills is incremented unconditionally after the insert/update branch, so '✓ 87 bills' on a re-run looks identical to a first run. Split into billsInserted/billsUpdated or add a note that this is 'processed'.

Fixture carries unused fields (utility_provider_name, pricing_zone_name, metric_unit, metric_aggregator)

packages/database/src/demo/extract-prod-fixture.ts

The extractor fetches utility_provider_name and pricing_zone_name but the seeder never uses them (provider_id and pricing_zone_id are left null). metric_unit and metric_aggregator in TemplateRow are declared but unused. Either drop these columns or add a comment explaining they are reserved for future seeding.

GREATEST/NULL semantics for coverage windows deserve an explicit comment

packages/database/src/demo/seed-demo-org.ts:496

The coverage window update relies on GREATEST propagating NULL for the open-window case. This is not obvious and the site-metrics-seam.md rule calls the inverted-window bug 'billing-grade'. A comment citing that rule would make the intent explicit for future readers — independently of the correctness finding above.

Silent continue when integration not found — no visibility into skipped sources

packages/database/src/demo/seed-demo-org.ts:439

When an integration name from the template is not found in the preview DB, the source is silently skipped. Add console.warn (matching the metric_type guard pattern) so partial device seeding is visible.