Files
aevolution/migrations/0001_region_bootstrap.sql

28 lines
1.4 KiB
SQL

-- Owner-only bootstrap. No runtime mutation grants until fenced functions exist.
CREATE SCHEMA evolution;
REVOKE ALL ON SCHEMA evolution FROM PUBLIC;
CREATE DOMAIN evolution.u64 AS numeric
CHECK (VALUE >= 0 AND VALUE <= 18446744073709551615 AND VALUE = trunc(VALUE));
CREATE TABLE evolution.schema_migrations (
version integer PRIMARY KEY CHECK (version > 0),
sha256 text NOT NULL CHECK (sha256 ~ '^[0-9a-f]{64}$'),
applied_at timestamptz NOT NULL DEFAULT clock_timestamp()
);
CREATE TABLE evolution.region_leases (
world_id evolution.u64 NOT NULL CHECK (world_id > 0),
region_id evolution.u64 NOT NULL CHECK (region_id > 0),
frame_id evolution.u64 NOT NULL CHECK (frame_id > 0),
epoch evolution.u64 NOT NULL DEFAULT 0,
holder uuid,
expires_at timestamptz,
PRIMARY KEY (world_id, region_id),
CHECK ((holder IS NULL AND expires_at IS NULL) OR
(holder IS NOT NULL AND expires_at IS NOT NULL AND epoch > 0))
);
REVOKE ALL ON ALL TABLES IN SCHEMA evolution FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA evolution REVOKE ALL ON TABLES FROM PUBLIC;
-- PostgreSQL's global PUBLIC execute default cannot be revoked per schema.
-- This is the dedicated migration owner's default, not other owners' defaults.
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
INSERT INTO evolution.schema_migrations(version, sha256) VALUES (1, :'migration_sha');