I killed an AWS region on purpose: building a disaster check-in app on Aurora DSQL

CREATE TABLE groups (
id UUID PRIMARY KEY,
name TEXT NOT NULL,
slug TEXT UNIQUE NOT NULL
);

CREATE TABLE members (
id UUID PRIMARY KEY,
group_id UUID, -- app-enforced, no FK constraint
name TEXT NOT NULL,
email TEXT,
label TEXT, -- dashboard filter (North block, Eastside, etc.)
home_lat DOUBLE PRECISION,
home_lng DOUBLE PRECISION
);

CREATE TABLE hazards (
id UUID PRIMARY KEY,
source TEXT NOT NULL, -- 'USGS' | 'NWS'
external_id TEXT NOT NULL UNIQUE, -- idempotent ingest key
type TEXT NOT NULL, -- 'earthquake' | 'tornado' | 'flood'
severity TEXT, -- 'moderate' | 'severe' | 'extreme'
title TEXT, -- "M5.8 - 12km NE of San Jose"
lat DOUBLE PRECISION,
lng DOUBLE PRECISION,
radius_km DOUBLE PRECISION, -- quakes: derived from magnitude
geometry JSONB, -- NWS polygons, hit-tested client-side
starts_at TIMESTAMPTZ,
expires_at TIMESTAMPTZ,
ingested_at TIMESTAMPTZ DEFAULT now(),
raw JSONB
);

CREATE TABLE checkins (
id UUID PRIMARY KEY,
member_id UUID NOT NULL,
hazard_id UUID NOT NULL,
status TEXT NOT NULL, -- 'SAFE' | 'NEED_HELP'
note TEXT,
lat DOUBLE PRECISION,
lng DOUBLE PRECISION,
region_written TEXT NOT NULL, -- 'us-east-1' | 'us-east-2'
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

SELECT
m.id, m.name, m.label,
c.status, c.region_written, c.created_at AS checked_in_at
FROM members m
LEFT JOIN LATERAL (
SELECT status, region_written, created_at FROM checkins
WHERE member_id = m.id AND hazard_id = $2
ORDER BY created_at DESC
LIMIT 1
) c ON true
WHERE m.group_id = $1
ORDER BY m.name;

const ENDPOINTS = [
{ region: "us-east-1", host: process.env.DSQL_ENDPOINT_EAST1! },
{ region: "us-east-2", host: process.env.DSQL_ENDPOINT_EAST2! },
]

// Set KILL_REGION=us-east-1 to remove that endpoint from the active list
function activeEndpoints() {
const killed = process.env.KILL_REGION
const list = ENDPOINTS.filter(e =\> e.region !== killed)
return list.length \> 0 ? list : ENDPOINTS
}

=== us-east-1 ===
indexname | tablename
---------------------------------------------+-----------
checkins_hazard_id_created_at_idx | checkins
checkins_member_id_hazard_id_created_at_idx | checkins
checkins_pkey | checkins
groups_pkey | groups
groups_slug_key | groups
hazards_external_id_key | hazards
hazards_ingested_at_idx | hazards
hazards_lat_lng_idx | hazards
hazards_pkey | hazards
idx_hazards_expires | hazards
members_group_id_idx | members
members_pkey | members
(12 rows)

=== us-east-2 ===
indexname | tablename
---------------------------------------------+-----------
checkins_hazard_id_created_at_idx | checkins
checkins_member_id_hazard_id_created_at_idx | checkins
checkins_pkey | checkins
groups_pkey | groups
groups_slug_key | groups
hazards_external_id_key | hazards
hazards_ingested_at_idx | hazards
hazards_lat_lng_idx | hazards
hazards_pkey | hazards
idx_hazards_expires | hazards
members_group_id_idx | members
members_pkey | members
(12 rows)