An approval, ask or park can be resolved by an operator, a rule, a classifier or automatically, so the vocabulary stops naming a person: - step type human.approve -> workflow.approve - park class needs_human -> needs_decision (ParkNeedsDecision) - event kinds human_* -> operator_* - alert kind task.needs_human -> task.needs_decision - status grouping "needs you" -> "needs attention" 0061 rewrites the stored values, including the approval step type in the workflow-definitions resource and each task's pinned definition, with the digest recomputed so resumed tasks still match.
192 lines
7.2 KiB
SQL
192 lines
7.2 KiB
SQL
-- State Store schema: tasks, transitions, events, resources, the control-plane
|
|
-- resource documents, channel/apply status, config snapshots and identities.
|
|
--
|
|
-- Translations from the SQLite schema this replaces, and why:
|
|
-- * AUTOINCREMENT becomes GENERATED BY DEFAULT AS IDENTITY. BY DEFAULT
|
|
-- rather than ALWAYS so the one-time import can insert historical ids and
|
|
-- then advance the sequence.
|
|
-- * Times become timestamptz. SQLite stored them as fixed-width text and
|
|
-- ordered them lexicographically; Postgres orders them natively, so the
|
|
-- layout normalisation that step needed does not exist here.
|
|
-- * Blobs become bytea; the 0/1 flag columns become boolean.
|
|
-- * `events.data` stays text even though it holds JSON: nothing queries into
|
|
-- it, and jsonb would reorder keys and reject what the writer accepted.
|
|
-- * identity_aliases is unique on lower(alias) rather than using citext, so
|
|
-- a deployment needs no extension.
|
|
-- * Every column behind a Go int is bigint. SQLite's INTEGER is 64-bit, so
|
|
-- a 32-bit column here would narrow silently: the chat issue-number
|
|
-- allocator seeds at 10^15, which does not fit an int4.
|
|
|
|
-- +goose Up
|
|
|
|
CREATE TABLE tasks (
|
|
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
owner text NOT NULL,
|
|
repo text NOT NULL,
|
|
issue_number bigint NOT NULL,
|
|
title text NOT NULL DEFAULT '',
|
|
body text NOT NULL DEFAULT '',
|
|
labels text NOT NULL DEFAULT '',
|
|
status text NOT NULL DEFAULT 'queued',
|
|
workflow text NOT NULL DEFAULT '',
|
|
stage text NOT NULL DEFAULT '',
|
|
branch text NOT NULL DEFAULT '',
|
|
plan text NOT NULL DEFAULT '',
|
|
notes text NOT NULL DEFAULT '',
|
|
pr_number bigint NOT NULL DEFAULT 0,
|
|
tokens_used bigint NOT NULL DEFAULT 0,
|
|
iterations bigint NOT NULL DEFAULT 0,
|
|
attempt bigint NOT NULL DEFAULT 0,
|
|
park_reason text NOT NULL DEFAULT '',
|
|
watch_comment_id bigint NOT NULL DEFAULT 0,
|
|
park_class text NOT NULL DEFAULT 'needs_decision',
|
|
remediation_rounds bigint NOT NULL DEFAULT 0,
|
|
retry_count bigint NOT NULL DEFAULT 0,
|
|
source text NOT NULL DEFAULT 'forge',
|
|
identity text NOT NULL DEFAULT '',
|
|
binding_id text NOT NULL DEFAULT '',
|
|
binding_version bigint NOT NULL DEFAULT 0,
|
|
review_payload text NOT NULL DEFAULT '',
|
|
workflow_definition_version bigint NOT NULL DEFAULT 0,
|
|
workflow_definition_digest text NOT NULL DEFAULT '',
|
|
workflow_definition_yaml text NOT NULL DEFAULT '',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (owner, repo, issue_number)
|
|
);
|
|
CREATE INDEX idx_tasks_pr ON tasks (owner, repo, pr_number);
|
|
CREATE INDEX idx_tasks_status ON tasks (status);
|
|
|
|
CREATE TABLE transitions (
|
|
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
task_id bigint NOT NULL,
|
|
at timestamptz NOT NULL DEFAULT now(),
|
|
from_status text NOT NULL,
|
|
to_status text NOT NULL,
|
|
detail text NOT NULL DEFAULT ''
|
|
);
|
|
CREATE INDEX idx_transitions_task ON transitions (task_id, id);
|
|
|
|
CREATE TABLE events (
|
|
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
at timestamptz NOT NULL,
|
|
kind text NOT NULL,
|
|
task_id bigint NOT NULL DEFAULT 0,
|
|
repo text NOT NULL DEFAULT '',
|
|
issue bigint NOT NULL DEFAULT 0,
|
|
workflow text NOT NULL DEFAULT '',
|
|
stage text NOT NULL DEFAULT '',
|
|
attempt bigint NOT NULL DEFAULT 0,
|
|
actor_id text NOT NULL DEFAULT '',
|
|
actor_kind text NOT NULL DEFAULT '',
|
|
principal_id text NOT NULL DEFAULT '',
|
|
detail text NOT NULL DEFAULT '',
|
|
data text NOT NULL DEFAULT '{}'
|
|
);
|
|
CREATE INDEX idx_events_task ON events (task_id, id);
|
|
CREATE INDEX idx_events_kind ON events (kind, id);
|
|
CREATE INDEX idx_events_at ON events (at, id);
|
|
|
|
CREATE TABLE resources (
|
|
kind text PRIMARY KEY,
|
|
value bytea NOT NULL,
|
|
version bigint NOT NULL,
|
|
updated_at timestamptz NOT NULL
|
|
);
|
|
|
|
CREATE TABLE resource_history (
|
|
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
kind text NOT NULL,
|
|
value bytea NOT NULL,
|
|
version bigint NOT NULL,
|
|
actor text NOT NULL,
|
|
source text NOT NULL,
|
|
request_id text NOT NULL,
|
|
expected_version bigint NOT NULL,
|
|
current_version bigint NOT NULL,
|
|
at timestamptz NOT NULL
|
|
);
|
|
CREATE INDEX idx_resource_history_kind_version ON resource_history (kind, version);
|
|
-- The request-ID idempotency key is per kind, not global: a resource write
|
|
-- replays only the same kind's prior write (resourceByRequest).
|
|
CREATE UNIQUE INDEX idx_resource_history_kind_request ON resource_history (kind, request_id);
|
|
|
|
CREATE TABLE channel_status (
|
|
id text PRIMARY KEY,
|
|
name text NOT NULL DEFAULT '',
|
|
state text NOT NULL DEFAULT '',
|
|
detail text NOT NULL DEFAULT '',
|
|
configured boolean NOT NULL DEFAULT false,
|
|
reload_supported boolean NOT NULL DEFAULT false,
|
|
observed_at timestamptz NOT NULL
|
|
);
|
|
|
|
CREATE TABLE apply_status (
|
|
process text NOT NULL,
|
|
kind text NOT NULL,
|
|
applied_version bigint NOT NULL DEFAULT 0,
|
|
error text NOT NULL DEFAULT '',
|
|
reported_at timestamptz NOT NULL,
|
|
PRIMARY KEY (process, kind)
|
|
);
|
|
|
|
CREATE TABLE config_snapshot (
|
|
id bigint PRIMARY KEY CHECK (id = 1),
|
|
schema text NOT NULL DEFAULT '',
|
|
document text NOT NULL DEFAULT '',
|
|
published_at timestamptz NOT NULL
|
|
);
|
|
|
|
CREATE TABLE identities (
|
|
id text PRIMARY KEY,
|
|
kind text NOT NULL,
|
|
display_name text NOT NULL,
|
|
lifecycle text NOT NULL,
|
|
version bigint NOT NULL,
|
|
created_at timestamptz NOT NULL,
|
|
updated_at timestamptz NOT NULL
|
|
);
|
|
|
|
CREATE TABLE identity_aliases (
|
|
alias text PRIMARY KEY,
|
|
identity_id text NOT NULL REFERENCES identities (id)
|
|
);
|
|
CREATE UNIQUE INDEX idx_identity_aliases_alias ON identity_aliases (lower(alias));
|
|
|
|
CREATE TABLE identity_events (
|
|
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
identity_id text NOT NULL,
|
|
event_type text NOT NULL,
|
|
from_lifecycle text NOT NULL,
|
|
to_lifecycle text NOT NULL,
|
|
display_name text NOT NULL,
|
|
actor_id text NOT NULL,
|
|
source text NOT NULL,
|
|
request_id text NOT NULL UNIQUE,
|
|
at timestamptz NOT NULL
|
|
);
|
|
CREATE INDEX idx_identity_events_identity ON identity_events (identity_id, id);
|
|
|
|
CREATE TABLE identity_subjects (
|
|
issuer text NOT NULL,
|
|
subject text NOT NULL,
|
|
identity_id text NOT NULL REFERENCES identities (id),
|
|
bound_at timestamptz NOT NULL,
|
|
PRIMARY KEY (issuer, subject)
|
|
);
|
|
|
|
-- +goose Down
|
|
|
|
DROP TABLE identity_subjects;
|
|
DROP TABLE identity_events;
|
|
DROP TABLE identity_aliases;
|
|
DROP TABLE identities;
|
|
DROP TABLE config_snapshot;
|
|
DROP TABLE apply_status;
|
|
DROP TABLE channel_status;
|
|
DROP TABLE resource_history;
|
|
DROP TABLE resources;
|
|
DROP TABLE events;
|
|
DROP TABLE transitions;
|
|
DROP TABLE tasks;
|