25 lines
815 B
PL/PgSQL
25 lines
815 B
PL/PgSQL
BEGIN;
|
|
|
|
-- Replicas start together during a rollout, so serialize schema creation.
|
|
SELECT pg_advisory_xact_lock(hashtext('nereus_schema_migration'));
|
|
|
|
CREATE TABLE IF NOT EXISTS buoys (
|
|
id UUID PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
latitude DOUBLE PRECISION NOT NULL,
|
|
longitude DOUBLE PRECISION NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS readings (
|
|
id UUID PRIMARY KEY,
|
|
buoy_id UUID NOT NULL REFERENCES buoys(id) ON DELETE CASCADE,
|
|
water_temp DOUBLE PRECISION NOT NULL,
|
|
wave_height DOUBLE PRECISION NOT NULL,
|
|
salinity DOUBLE PRECISION,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_readings_buoy_time ON readings (buoy_id, recorded_at DESC);
|
|
|
|
COMMIT;
|