assessment-model/backlog/drizzle-schema-handoff.md

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):

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()).

// 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:

heatingCylinderSize: text("heating_cylinder_size"),

After:

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)

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):

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:

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):

export const rebaselineReasonEnum = pgEnum("rebaseline_reason", [
  "none",
  "pre_sap10",
  "physical_state_changed",
  "both",
]);

Then add the table to src/app/db/schema/property.ts:

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:

ALTER TABLE "epc_property" ALTER COLUMN "heating_shower_outlet_type" SET DATA TYPE jsonb;

With:

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).