Files
aevolution/migrations/0065_admin_schedules.sql
dingoandClaude Opus 5.5 018b4b1817
Foundation / checks (ecs) (push) Successful in 5s
Foundation / checks (client) (push) Successful in 5s
Foundation / checks (checks) (push) Successful in 5s
Foundation / checks (network) (push) Successful in 5s
Foundation / checks (deploy) (push) Successful in 6s
Foundation / checks (postgres) (push) Successful in 5s
Foundation / checks (tsan) (push) Successful in 5s
Foundation / postgres-tools (push) Successful in 23s
Foundation / server-image (push) Successful in 4m38s
Foundation / image (push) Successful in 5m35s
feat(admin): resource schedules for bubbles, pool releases and wildlife (schema 65)
ADR-0182 section 12: the game source as recurring admin commands. A schedule
repeats add_bubbles, release_pool or spawn_wildlife every 1 s to 24 h while
enabled; the region's pending-command read queues each due run as an ordinary
command (recorded in the trace, bound once), never two waiting per schedule,
so an idle region builds no backlog. Append-only schedule versions; admin API
GET/POST /api/regions/<n>/schedules and a portal card to add, stop and start.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-27 18:30:37 -04:00

144 lines
8.2 KiB
PL/PgSQL

-- Resource schedules (ADR-0182 section 4, 2026-10-27): the game source as recurring admin
-- commands. A schedule repeats an add_bubbles, release_pool or spawn_wildlife command every
-- `interval_seconds` while it is enabled. When the region reads its pending commands, each
-- enabled schedule whose last command was applied (or that never ran) and whose interval has
-- passed queues its next command, exactly as an operator would; it is then recorded in the
-- trace and bound once like any command. At most one command per schedule waits, so an idle
-- region (no living cells, no ticks) builds no backlog. Schedule versions are append-only;
-- the newest row of a schedule decides.
ALTER TABLE evolution.admin_commands ADD COLUMN schedule_id bigint;
CREATE TABLE evolution.admin_schedules (
sequence bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
schedule_id bigint NOT NULL,
world_id evolution.u64 NOT NULL,
region_id evolution.u64 NOT NULL,
kind smallint NOT NULL CHECK (kind IN (3, 5, 6)),
cell_count integer NOT NULL CHECK (cell_count BETWEEN 1 AND 256),
energy evolution.u64 NOT NULL,
carbon evolution.u64 NOT NULL,
nitrogen evolution.u64 NOT NULL,
phosphorus evolution.u64 NOT NULL,
other evolution.u64 NOT NULL,
interval_seconds integer NOT NULL CHECK (interval_seconds BETWEEN 1 AND 86400),
enabled boolean NOT NULL,
author_subject text NOT NULL CHECK (octet_length(author_subject) BETWEEN 1 AND 255),
reason text NOT NULL DEFAULT '' CHECK (octet_length(reason) <= 500),
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
FOREIGN KEY (world_id, region_id) REFERENCES evolution.region_leases,
CHECK ((kind = 6 AND cell_count <= 64 AND energy + carbon + nitrogen + phosphorus + other = 0)
OR (kind IN (3, 5) AND energy + carbon + nitrogen + phosphorus + other > 0))
);
CREATE INDEX admin_schedules_by_schedule ON evolution.admin_schedules(schedule_id, sequence);
CREATE SEQUENCE evolution.admin_schedule_ids;
CREATE TRIGGER admin_schedules_immutable BEFORE UPDATE OR DELETE ON evolution.admin_schedules
FOR EACH ROW EXECUTE FUNCTION evolution.refuse_history_rewrite();
-- Admin: creates (p_schedule 0) or replaces a schedule's settings; disabling stops it.
CREATE FUNCTION evolution.admin_set_schedule_v1(p_world evolution.u64, p_region evolution.u64,
p_schedule bigint, p_kind integer, p_count integer, p_energy evolution.u64,
p_carbon evolution.u64, p_nitrogen evolution.u64, p_phosphorus evolution.u64,
p_other evolution.u64, p_interval integer, p_enabled boolean, p_subject text, p_reason text)
RETURNS bigint LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
DECLARE id bigint := p_schedule;
BEGIN
IF id IS NULL OR id = 0 THEN
id := nextval('evolution.admin_schedule_ids');
ELSIF NOT EXISTS (SELECT 1 FROM evolution.admin_schedules s WHERE s.schedule_id = id
AND s.world_id = p_world AND s.region_id = p_region) THEN
RAISE EXCEPTION 'unknown schedule' USING ERRCODE = '22023';
END IF;
BEGIN
INSERT INTO evolution.admin_schedules(schedule_id, world_id, region_id, kind, cell_count,
energy, carbon, nitrogen, phosphorus, other, interval_seconds, enabled, author_subject,
reason)
VALUES (id, p_world, p_region, p_kind, p_count, p_energy, p_carbon, p_nitrogen,
p_phosphorus, p_other, p_interval, p_enabled, p_subject, coalesce(p_reason, ''));
EXCEPTION WHEN check_violation OR foreign_key_violation OR not_null_violation THEN
RAISE EXCEPTION 'invalid schedule' USING ERRCODE = '22023';
END;
RETURN id;
END $$;
REVOKE ALL ON FUNCTION evolution.admin_set_schedule_v1(evolution.u64, evolution.u64, bigint,
integer, integer, evolution.u64, evolution.u64, evolution.u64, evolution.u64, evolution.u64,
integer, boolean, text, text) FROM PUBLIC;
-- Admin: a region's schedules as they now stand, with their last queued command.
CREATE FUNCTION evolution.admin_schedules_v1(p_world evolution.u64, p_region evolution.u64)
RETURNS TABLE (schedule_id bigint, kind integer, cell_count integer, energy evolution.u64,
carbon evolution.u64, nitrogen evolution.u64, phosphorus evolution.u64, other evolution.u64,
interval_seconds integer, enabled boolean, author_subject text, reason text,
changed_at timestamptz, last_queued timestamptz, runs bigint)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
SELECT s.schedule_id, s.kind, s.cell_count, s.energy, s.carbon, s.nitrogen, s.phosphorus,
s.other, s.interval_seconds, s.enabled, s.author_subject, s.reason, s.created_at,
(SELECT max(q.created_at) FROM evolution.admin_commands q
WHERE q.schedule_id = s.schedule_id),
(SELECT count(*) FROM evolution.admin_commands q WHERE q.schedule_id = s.schedule_id)
FROM evolution.admin_schedules s
WHERE s.world_id = p_world AND s.region_id = p_region
AND s.sequence = (SELECT max(x.sequence) FROM evolution.admin_schedules x
WHERE x.schedule_id = s.schedule_id)
ORDER BY s.schedule_id
$$;
REVOKE ALL ON FUNCTION evolution.admin_schedules_v1(evolution.u64, evolution.u64) FROM PUBLIC;
-- Queues every due schedule's next command (spawning takes identities from the allocator).
CREATE FUNCTION evolution.queue_due_schedules(p_world evolution.u64, p_region evolution.u64)
RETURNS void LANGUAGE plpgsql SET search_path = pg_catalog, pg_temp AS $$
DECLARE
s record;
first numeric;
BEGIN
FOR s IN SELECT * FROM evolution.admin_schedules x
WHERE x.world_id = p_world AND x.region_id = p_region
AND x.sequence = (SELECT max(y.sequence) FROM evolution.admin_schedules y
WHERE y.schedule_id = x.schedule_id)
AND x.enabled
ORDER BY x.schedule_id LOOP
CONTINUE WHEN EXISTS (SELECT 1 FROM evolution.admin_commands q
WHERE q.schedule_id = s.schedule_id
AND NOT EXISTS (SELECT 1 FROM evolution.admin_command_applications a
WHERE a.command_id = q.command_id));
CONTINUE WHEN coalesce((SELECT max(q.created_at) FROM evolution.admin_commands q
WHERE q.schedule_id = s.schedule_id), '-infinity'::timestamptz)
+ make_interval(secs => s.interval_seconds) > clock_timestamp();
first := 0;
IF s.kind = 6 THEN
SELECT a.next_identity INTO first FROM evolution.life_allocators a
WHERE a.world_id = p_world FOR UPDATE;
CONTINUE WHEN first IS NULL OR first + s.cell_count > 9223372036854775808;
UPDATE evolution.life_allocators SET next_identity = first + s.cell_count
WHERE world_id = p_world;
END IF;
INSERT INTO evolution.admin_commands(world_id, region_id, kind, cell_count, target, energy,
carbon, nitrogen, phosphorus, other, author_subject, reason, schedule_id)
VALUES (p_world, p_region, s.kind, s.cell_count, first, s.energy, s.carbon, s.nitrogen,
s.phosphorus, s.other, 'schedule ' || s.schedule_id, s.reason, s.schedule_id);
END LOOP;
END $$;
REVOKE ALL ON FUNCTION evolution.queue_due_schedules(evolution.u64, evolution.u64) FROM PUBLIC;
-- The region's pending commands, after queuing what its schedules owe (same signature).
ALTER FUNCTION evolution.load_pending_admin_commands_v1(evolution.u64, evolution.u64)
RENAME TO load_pending_admin_commands_v64;
REVOKE ALL ON FUNCTION evolution.load_pending_admin_commands_v64(evolution.u64, evolution.u64)
FROM PUBLIC, :"runtime_role";
CREATE FUNCTION evolution.load_pending_admin_commands_v1(p_world evolution.u64,
p_region evolution.u64)
RETURNS TABLE (command_id bigint, kind integer, cell_count integer, target evolution.u64,
energy evolution.u64, carbon evolution.u64, nitrogen evolution.u64, phosphorus evolution.u64,
other evolution.u64)
LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
BEGIN
PERFORM evolution.queue_due_schedules(p_world, p_region);
RETURN QUERY SELECT * FROM evolution.load_pending_admin_commands_v64(p_world, p_region);
END $$;
REVOKE ALL ON FUNCTION evolution.load_pending_admin_commands_v1(evolution.u64, evolution.u64)
FROM PUBLIC;
GRANT EXECUTE ON FUNCTION evolution.load_pending_admin_commands_v1(evolution.u64, evolution.u64)
TO :"runtime_role";
INSERT INTO evolution.schema_migrations(version,sha256) VALUES(65,:'migration_sha_65');