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