mirror of
https://github.com/Hestia-Homes/assessment-model.git
synced 2026-07-12 13:28:55 +00:00
291 lines
12 KiB
Markdown
291 lines
12 KiB
Markdown
# Drizzle schema handoff — pending EPC migrations
|
|
|
|
**Task:** Update Drizzle table definitions in `src/app/db/schema/property.ts` to match the
|
|
Python SQLModel definitions. Do **not** run `drizzle-kit generate` or any migration
|
|
commands — the developer will run generation manually after your changes.
|
|
|
|
Two sets of changes are covered here:
|
|
|
|
1. EPC property round-trip fidelity gaps (fixes an active production error)
|
|
2. New `property_baseline_performance` table
|
|
|
|
### Before starting: update the import line
|
|
|
|
`jsonb` is not currently imported. Add it (and `pgEnum` is already present):
|
|
|
|
```typescript
|
|
import {
|
|
bigserial,
|
|
text,
|
|
timestamp,
|
|
pgTable,
|
|
real,
|
|
pgEnum,
|
|
integer,
|
|
boolean,
|
|
smallint,
|
|
bigint,
|
|
uniqueIndex,
|
|
jsonb, // ← add this
|
|
} from "drizzle-orm/pg-core";
|
|
```
|
|
|
|
---
|
|
|
|
## 1. `epc_property` — new columns
|
|
|
|
Add these columns to the `epcProperty` table. All are nullable (no `.notNull()`).
|
|
|
|
```typescript
|
|
// Mechanical ventilation
|
|
mechanicalVentDuctInsulationLevel: integer("mechanical_vent_duct_insulation_level"),
|
|
|
|
// Addendum flags
|
|
addendumStoneWalls: boolean("addendum_stone_walls"),
|
|
addendumSystemBuild: boolean("addendum_system_build"),
|
|
addendumNumbers: jsonb("addendum_numbers"),
|
|
|
|
// Heating counts
|
|
heatingNumberBaths: integer("heating_number_baths"),
|
|
heatingNumberBathsWwhrs: integer("heating_number_baths_wwhrs"),
|
|
heatingElectricShowerCount: integer("heating_electric_shower_count"),
|
|
heatingMixerShowerCount: integer("heating_mixer_shower_count"),
|
|
|
|
// Ventilation detail
|
|
ventilationPresent: boolean("ventilation_present").notNull().default(false),
|
|
ventilationShelteredSides: integer("ventilation_sheltered_sides"),
|
|
ventilationHasSuspendedTimberFloor: boolean("ventilation_has_suspended_timber_floor"),
|
|
ventilationSuspendedTimberFloorSealed: boolean("ventilation_suspended_timber_floor_sealed"),
|
|
ventilationHasDraughtLobby: boolean("ventilation_has_draught_lobby"),
|
|
ventilationAirPermeabilityAp4M3HM2: real("ventilation_air_permeability_ap4_m3_h_m2"),
|
|
ventilationMechanicalVentilationKind: text("ventilation_mechanical_ventilation_kind"),
|
|
```
|
|
|
|
---
|
|
|
|
## 2. `epc_property` — type changes: `text` → `jsonb`
|
|
|
|
Change the following existing columns from `text(...)` to `jsonb(...)`. Preserve any
|
|
`.notNull()` that is currently present (none of these have it, but double-check).
|
|
|
|
| Property name | Column name |
|
|
| ------------------------------- | ---------------------------------- |
|
|
| `energyPvConnection` | `energy_pv_connection` |
|
|
| `heatingCylinderSize` | `heating_cylinder_size` |
|
|
| `heatingImmersionHeatingType` | `heating_immersion_heating_type` |
|
|
| `heatingCylinderInsulationType` | `heating_cylinder_insulation_type` |
|
|
| `heatingSecondaryHeatingType` | `heating_secondary_heating_type` |
|
|
| `heatingShowerOutletType` | `heating_shower_outlet_type` |
|
|
|
|
Example — before:
|
|
|
|
```typescript
|
|
heatingCylinderSize: text("heating_cylinder_size"),
|
|
```
|
|
|
|
After:
|
|
|
|
```typescript
|
|
heatingCylinderSize: jsonb("heating_cylinder_size"),
|
|
```
|
|
|
|
---
|
|
|
|
## 3. `epc_main_heating_detail` — type changes: `text` → `jsonb`
|
|
|
|
These four columns are currently `text(...).notNull()`. Change to `jsonb(...).notNull()`.
|
|
|
|
| Property name | Column name |
|
|
| -------------------- | ---------------------- |
|
|
| `mainFuelType` | `main_fuel_type` |
|
|
| `heatEmitterType` | `heat_emitter_type` |
|
|
| `emitterTemperature` | `emitter_temperature` |
|
|
| `mainHeatingControl` | `main_heating_control` |
|
|
|
|
---
|
|
|
|
## 4. `epc_building_part` — type changes and new columns
|
|
|
|
### 4a. Type changes: `text` → `jsonb`
|
|
|
|
| Property name | Column name | Currently nullable? |
|
|
| ----------------------------- | -------------------------------- | ----------------------------------------------------------------------------------------- |
|
|
| `wallConstruction` | `wall_construction` | no (`.notNull()`) |
|
|
| `wallInsulationType` | `wall_insulation_type` | no (`.notNull()`) |
|
|
| `partyWallConstruction` | `party_wall_construction` | **drop `.notNull()`** — Python has this as nullable; the TypeScript `.notNull()` is wrong |
|
|
| `flatRoofInsulationThickness` | `flat_roof_insulation_thickness` | yes |
|
|
| `roofInsulationLocation` | `roof_insulation_location` | yes |
|
|
| `roofInsulationThickness` | `roof_insulation_thickness` | yes |
|
|
|
|
### 4b. New columns (add, nullable)
|
|
|
|
```typescript
|
|
roofConstructionType: text("roof_construction_type"),
|
|
curtainWallAge: text("curtain_wall_age"),
|
|
```
|
|
|
|
---
|
|
|
|
## 5. `epc_window` — type changes: `text` → `jsonb`
|
|
|
|
| Property name | Column name | Currently nullable? |
|
|
| -------------------------- | ---------------------------- | ---------------------------------------------------------- |
|
|
| `glazingGap` | `glazing_gap` | no (`.notNull()`) |
|
|
| `orientation` | `orientation` | no (`.notNull()`) |
|
|
| `windowType` | `window_type` | no (`.notNull()`) |
|
|
| `glazingType` | `glazing_type` | no (`.notNull()`) |
|
|
| `windowLocation` | `window_location` | no (`.notNull()`) |
|
|
| `windowWallType` | `window_wall_type` | no (`.notNull()`) |
|
|
| `draughtProofed` | `draught_proofed` | no (`.notNull()`) — currently `boolean`, change to `jsonb` |
|
|
| `permanentShuttersPresent` | `permanent_shutters_present` | no (`.notNull()`) — currently `boolean`, change to `jsonb` |
|
|
| `transmissionDataSource` | `transmission_data_source` | yes |
|
|
|
|
> **Note on `draughtProofed` and `permanentShuttersPresent`:** these are `boolean` in the
|
|
> current TypeScript schema but `Union[bool, str]` JSONB in the Python model. Change them
|
|
> to `jsonb(...).notNull()` — the TypeScript boolean type was incorrect.
|
|
>
|
|
> These two columns also require **boolean-specific** USING clauses in the generated migration
|
|
> (PostgreSQL will not implicitly cast `boolean` to `jsonb`):
|
|
>
|
|
> ```sql
|
|
> ALTER TABLE "epc_window" ALTER COLUMN "draught_proofed"
|
|
> SET DATA TYPE jsonb
|
|
> USING to_json("draught_proofed")::jsonb;
|
|
>
|
|
> ALTER TABLE "epc_window" ALTER COLUMN "permanent_shutters_present"
|
|
> SET DATA TYPE jsonb
|
|
> USING to_json("permanent_shutters_present")::jsonb;
|
|
> ```
|
|
>
|
|
> `to_json` converts `true`/`false` to JSON booleans (not quoted strings), which is correct
|
|
> for the `Union[bool, str]` Python type.
|
|
|
|
---
|
|
|
|
## 6. New table: `epc_renewable_heat_incentive`
|
|
|
|
Add this table to `src/app/db/schema/property.ts`:
|
|
|
|
```typescript
|
|
export const epcRenewableHeatIncentive = pgTable(
|
|
"epc_renewable_heat_incentive",
|
|
{
|
|
id: bigserial("id", { mode: "bigint" }).primaryKey(),
|
|
epcPropertyId: bigint("epc_property_id", { mode: "bigint" })
|
|
.notNull()
|
|
.unique()
|
|
.references(() => epcProperty.id),
|
|
spaceHeatingKwh: real("space_heating_kwh").notNull(),
|
|
waterHeatingKwh: real("water_heating_kwh").notNull(),
|
|
impactOfLoftInsulationKwh: real("impact_of_loft_insulation_kwh"),
|
|
impactOfCavityInsulationKwh: real("impact_of_cavity_insulation_kwh"),
|
|
impactOfSolidWallInsulationKwh: real("impact_of_solid_wall_insulation_kwh"),
|
|
},
|
|
);
|
|
```
|
|
|
|
---
|
|
|
|
## 7. New table: `property_baseline_performance`
|
|
|
|
First, add the enum (before the table definition):
|
|
|
|
```typescript
|
|
export const rebaselineReasonEnum = pgEnum("rebaseline_reason", [
|
|
"none",
|
|
"pre_sap10",
|
|
"physical_state_changed",
|
|
"both",
|
|
]);
|
|
```
|
|
|
|
Then add the table to `src/app/db/schema/property.ts`:
|
|
|
|
```typescript
|
|
export const propertyBaselinePerformance = pgTable(
|
|
"property_baseline_performance",
|
|
{
|
|
id: bigserial("id", { mode: "bigint" }).primaryKey(),
|
|
propertyId: bigint("property_id", { mode: "bigint" })
|
|
.notNull()
|
|
.unique()
|
|
.references(() => property.id),
|
|
|
|
// Lodged performance (from gov EPC register)
|
|
lodgedSapScore: integer("lodged_sap_score").notNull(),
|
|
lodgedEpcBand: epcEnum("lodged_epc_band").notNull(),
|
|
lodgedCo2EmissionsTPerYr: real("lodged_co2_emissions_t_per_yr").notNull(),
|
|
lodgedPrimaryEnergyIntensityKwhPerM2Yr: integer(
|
|
"lodged_primary_energy_intensity_kwh_per_m2_yr",
|
|
).notNull(),
|
|
|
|
// Effective performance (what modelling scored against)
|
|
effectiveSapScore: integer("effective_sap_score").notNull(),
|
|
effectiveEpcBand: epcEnum("effective_epc_band").notNull(),
|
|
effectiveCo2EmissionsTPerYr: real(
|
|
"effective_co2_emissions_t_per_yr",
|
|
).notNull(),
|
|
effectivePrimaryEnergyIntensityKwhPerM2Yr: integer(
|
|
"effective_primary_energy_intensity_kwh_per_m2_yr",
|
|
).notNull(),
|
|
|
|
rebaselineReason: rebaselineReasonEnum("rebaseline_reason").notNull(),
|
|
|
|
// Interim energy demand (from EPC RHI data; superseded by bill block below once populated)
|
|
spaceHeatingKwh: real("space_heating_kwh").notNull(),
|
|
waterHeatingKwh: real("water_heating_kwh").notNull(),
|
|
|
|
// Bill block — nullable until BillDerivation wiring lands
|
|
fuelRatesPeriod: text("fuel_rates_period"),
|
|
heatingKwh: real("heating_kwh"),
|
|
heatingCostGbp: real("heating_cost_gbp"),
|
|
hotWaterKwh: real("hot_water_kwh"),
|
|
hotWaterCostGbp: real("hot_water_cost_gbp"),
|
|
lightingKwh: real("lighting_kwh"),
|
|
lightingCostGbp: real("lighting_cost_gbp"),
|
|
appliancesKwh: real("appliances_kwh"),
|
|
appliancesCostGbp: real("appliances_cost_gbp"),
|
|
cookingKwh: real("cooking_kwh"),
|
|
cookingCostGbp: real("cooking_cost_gbp"),
|
|
pumpsFansKwh: real("pumps_fans_kwh"),
|
|
pumpsFansCostGbp: real("pumps_fans_cost_gbp"),
|
|
coolingKwh: real("cooling_kwh"),
|
|
coolingCostGbp: real("cooling_cost_gbp"),
|
|
standingChargesGbp: real("standing_charges_gbp"),
|
|
segCreditGbp: real("seg_credit_gbp"),
|
|
totalAnnualBillGbp: real("total_annual_bill_gbp"),
|
|
},
|
|
);
|
|
```
|
|
|
|
---
|
|
|
|
## Post-generation checklist (developer action, not Claude)
|
|
|
|
After running `drizzle-kit generate`, **manually edit the generated `.sql` file** before
|
|
applying it. For every `ALTER COLUMN ... SET DATA TYPE jsonb` statement, add a `USING`
|
|
expression to handle existing rows safely. Without it, any row with a bare unquoted
|
|
string (e.g. `Electric Shower`) will cause the migration to fail.
|
|
|
|
Replace the generated form:
|
|
|
|
```sql
|
|
ALTER TABLE "epc_property" ALTER COLUMN "heating_shower_outlet_type" SET DATA TYPE jsonb;
|
|
```
|
|
|
|
With:
|
|
|
|
```sql
|
|
ALTER TABLE "epc_property" ALTER COLUMN "heating_shower_outlet_type"
|
|
SET DATA TYPE jsonb
|
|
USING (
|
|
CASE
|
|
WHEN "heating_shower_outlet_type" IS NULL THEN NULL
|
|
WHEN "heating_shower_outlet_type" ~ '^-?[0-9]+$' THEN "heating_shower_outlet_type"::jsonb
|
|
ELSE to_json("heating_shower_outlet_type")::jsonb
|
|
END
|
|
);
|
|
```
|
|
|
|
Apply this pattern to **every** `text → jsonb` column across all four tables
|
|
(`epc_property`, `epc_main_heating_detail`, `epc_building_part`, `epc_window`).
|