power-thermal-schema-reorganization

Public task · Field Engineering

← Field Engineering

Move a power-siting app's power and thermal tables out of public and raw into power and thermal Postgres schemas with migrations that keep rows and keys, repoint every query and the pricing importer, and drop two stale schemas.

Version
Category
Engineering
Model configurations
2
Attempts
17
Public trials
1711 passed
Median runtime
6m 49s

Instruction

# Move power and thermal tables into their own schemas and retire stale schemas

We are reorganizing the app's Postgres database. The power and thermal apps keep
their tables in `public`, data center pricing sits alone in a `raw` schema, and
production still carries two schemas the app does not use. Make the change with
new migrations in `db/migrations`, applied by `db/migrate.sh`, and update the
code that uses these tables. Production has already applied every migration
currently in `db/migrations`. The new migrations must also work on an empty
database migrated from scratch, as development setup does.

1. Move these tables, keeping their names, rows, columns, indexes, constraints
   and foreign keys:

   | now | moves to |
   | --- | --- |
   | `public.power_sites` | `power.power_sites` |
   | `public.power_site_fiber_matches` | `power.power_site_fiber_matches` |
   | `raw.data_center_pricing` | `power.data_center_pricing` |
   | `public.thermal_components` | `thermal.thermal_components` |

   Do not leave anything behind under the old names, such as compatibility
   views. `raw` holds only the pricing table, so drop the `raw` schema once it
   is empty.

2. Update every query in `web/` that reads or writes these tables, including the
   pricing Excel importer (`web/scripts/import-data-center-pricing.mjs`), to use
   the new locations. API routes keep their current paths and responses.

3. Drop the obsolete `auth` schema. Better Auth uses the `public.auth_*` tables;
   leave those as they are. In production, `auth` still holds the old Better
   Auth tables `user`, `session`, `account` and `verification` from before the
   app switched to `public.auth_*`. Those tables contain three users who all
   have `public.auth_user` accounts, sessions that expired months ago, and no
   accounts or verifications. None of this needs to be kept.

4. Drop the `geo` schema. Another project's migrations created it in this
   database by mistake. Production's copy holds that project's tables, their
   trigger function, and one seeded organization and workspace. The app does
   not use it.

5. Leave the upstream source schemas (`ercot`, `lantera` and `larkspur`) and
   their data unchanged.

## Environment

PostgreSQL on localhost:5432 and the local database endpoint the app's
driver uses are running when you start, and the dev env files point at the
dev database.

Public trials

Results by model

Model · harnessMean rewardRange± SEAttempts
Claude Opus 5.5Grok Build100.0%100% – 100%—1
Frontier model ACursor CLI62.5%0% – 100%12.5 pp16