CosmicClash/server/migrations/0004_allocator_registry.sql

-- Durable allocator registry. Agones remains the provider-facing lifecycle
-- authority; these rows are the control-plane's auditable claim projection.
CREATE TABLE game_servers (
    server_id TEXT PRIMARY KEY,
    region TEXT NOT NULL CHECK (region IN ('EU', 'NA')),
    build TEXT NOT NULL,
    protocol_version INTEGER NOT NULL CHECK (protocol_version > 0),
    transport TEXT NOT NULL CHECK (transport IN ('enet', 'steam_sdr')),
    state TEXT NOT NULL CHECK (state IN ('READY', 'ALLOCATED')),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE allocations (
    allocation_id TEXT PRIMARY KEY,
    match_id TEXT NOT NULL UNIQUE,
    server_id TEXT NOT NULL REFERENCES game_servers(server_id),
    region TEXT NOT NULL CHECK (region IN ('EU', 'NA')),
    build TEXT NOT NULL,
    protocol_version INTEGER NOT NULL CHECK (protocol_version > 0),
    transport TEXT NOT NULL CHECK (transport IN ('enet', 'steam_sdr')),
    request_digest BYTEA NOT NULL,
    state TEXT NOT NULL CHECK (state = 'ALLOCATED'),
    allocated_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX game_servers_ready_compatibility
    ON game_servers (region, build, protocol_version, transport, server_id)
    WHERE state = 'READY';