Move Shipping Addresses to Their Own Table: PR Review

Shipping addresses move out of orders and into their own addresses table, which unblocks split shipments and saved addresses next quarter. Migration 0087 creates the table and copies every existing address, the repository now reads and writes the new table, and 0088 drops the old ship_* columns. orders has about 140 million rows. I ran both migrations against last week's staging snapshot and they finished in about 6 minutes. Planning to ship it all in Thursday's release.

Amigrations/0087_create_addresses.sql1 of 4

Files Changed

migrations/

orders/

tests/

migrations/0087_create_addresses.sql

SQL · Click a line number to comment · Tap a line to comment

@@ -0,0 +1,23 @@

BEGIN;

CREATE TABLE addresses (

id bigserial PRIMARY KEY,

order_id bigint NOT NULL REFERENCES orders (id),

kind text NOT NULL,

name text NOT NULL,

line1 text NOT NULL,

line2 text,

city text NOT NULL,

region text,

postal_code text NOT NULL,

country char(2) NOT NULL,

created_at timestamptz NOT NULL DEFAULT now()

);

CREATE UNIQUE INDEX addresses_order_id_kind_key ON addresses (order_id, kind);

INSERT INTO addresses (order_id, kind, name, line1, line2, city, region, postal_code, country)

SELECT id, 'shipping', ship_name, ship_line1, ship_line2, ship_city, ship_region, ship_postal_code, ship_country

FROM orders;

COMMIT;

orders/repository.py

Python · Click a line number to comment · Tap a line to comment

@@ -1,9 +1,9 @@

import logging

from orders.db import Database

from orders.models import Order

from orders.models import Address, Order

log = logging.getLogger(__name__)

class OrderRepository:

@@ -13,22 +13,32 @@ class OrderRepository:

def get(self, order_id: int) -> Order | None:

row = self.db.fetch_one(

"SELECT id, customer_id, status, total_cents, ship_name, ship_line1, ship_line2,"

" ship_city, ship_region, ship_postal_code, ship_country"

" FROM orders WHERE id = %s",

"SELECT o.id, o.customer_id, o.status, o.total_cents,"

" a.name, a.line1, a.line2, a.city, a.region, a.postal_code, a.country"

" FROM orders o JOIN addresses a ON a.order_id = o.id AND a.kind = 'shipping'"

" WHERE o.id = %s",

(order_id,),

)

return Order.from_row(row) if row else None

def create(self, order: Order) -> int:

order_id = self.db.fetch_value(

"INSERT INTO orders (customer_id, status, total_cents, ship_name, ship_line1, ship_line2,"

" ship_city, ship_region, ship_postal_code, ship_country)"

" VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING id",

(order.customer_id, order.status, order.total_cents, *order.shipping.as_tuple()),

"INSERT INTO orders (customer_id, status, total_cents) VALUES (%s, %s, %s) RETURNING id",

(order.customer_id, order.status, order.total_cents),

)

self._insert_shipping_address(order_id, order.shipping)

return order_id

def _insert_shipping_address(self, order_id: int, address: Address) -> None:

try:

self.db.execute(

"INSERT INTO addresses (order_id, kind, name, line1, line2, city, region, postal_code, country)"

" VALUES (%s, 'shipping', %s, %s, %s, %s, %s, %s, %s)",

(order_id, *address.as_tuple()),

)

except Exception:

log.exception("could not write shipping address for order %s", order_id)

def cancel(self, order_id: int) -> None:

with self.db.transaction() as tx:

tx.execute("UPDATE orders SET status = 'cancelled' WHERE id = %s", (order_id,))

tx.execute("INSERT INTO order_events (order_id, kind) VALUES (%s, 'cancelled')", (order_id,))

migrations/0087_create_addresses.sql

migrations/0088_drop_order_ship_columns.sql

orders/repository.py

tests/test_repository.py