91 lines
5.6 KiB
PL/PgSQL
91 lines
5.6 KiB
PL/PgSQL
CREATE TABLE IF NOT EXISTS schema_versions(version INTEGER PRIMARY KEY, applied_at TEXT NOT NULL);
|
|
CREATE TABLE IF NOT EXISTS operators(
|
|
id TEXT PRIMARY KEY, username TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL, created_at TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS auth_sessions(
|
|
token_hash TEXT PRIMARY KEY, operator_id TEXT REFERENCES operators(id), csrf TEXT NOT NULL,
|
|
expires_at BIGINT NOT NULL, created_at TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS auth_attempts(key TEXT PRIMARY KEY, failures INTEGER NOT NULL, window_start BIGINT NOT NULL);
|
|
CREATE TABLE IF NOT EXISTS learning_tasks(
|
|
id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES operators(id), name TEXT NOT NULL,
|
|
platform TEXT NOT NULL, entry_url TEXT NOT NULL, goal TEXT NOT NULL, account_key TEXT NOT NULL,
|
|
parameters_json TEXT NOT NULL, status TEXT NOT NULL, identity_label TEXT, execution_profile TEXT,
|
|
created_at TEXT NOT NULL, updated_at TEXT NOT NULL,
|
|
ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
|
|
);
|
|
CREATE TABLE IF NOT EXISTS observations(
|
|
id TEXT PRIMARY KEY, task_id TEXT NOT NULL REFERENCES learning_tasks(id), data_json TEXT NOT NULL,
|
|
created_at TEXT NOT NULL, ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS observations_task ON observations(task_id,ordinal);
|
|
CREATE TABLE IF NOT EXISTS learning_steps(
|
|
id TEXT PRIMARY KEY, task_id TEXT NOT NULL REFERENCES learning_tasks(id), position INTEGER NOT NULL,
|
|
data_json TEXT NOT NULL, created_at TEXT NOT NULL, UNIQUE(task_id,position)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS candidates(
|
|
id TEXT PRIMARY KEY, task_id TEXT NOT NULL REFERENCES learning_tasks(id), name TEXT NOT NULL,
|
|
version INTEGER NOT NULL, digest TEXT NOT NULL, manifest_json TEXT NOT NULL, created_at TEXT NOT NULL,
|
|
ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE, UNIQUE(task_id,version)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS validations(
|
|
id TEXT PRIMARY KEY, candidate_id TEXT NOT NULL REFERENCES candidates(id), task_id TEXT NOT NULL REFERENCES learning_tasks(id),
|
|
kind TEXT NOT NULL, status TEXT NOT NULL, data_json TEXT NOT NULL, created_at TEXT NOT NULL,
|
|
ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS validations_candidate ON validations(candidate_id,ordinal);
|
|
CREATE TABLE IF NOT EXISTS quality_observations(
|
|
id TEXT PRIMARY KEY, validation_id TEXT NOT NULL REFERENCES validations(id),
|
|
candidate_id TEXT NOT NULL REFERENCES candidates(id), task_id TEXT NOT NULL REFERENCES learning_tasks(id),
|
|
data_json TEXT NOT NULL, created_at TEXT NOT NULL,
|
|
ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS quality_observations_candidate ON quality_observations(candidate_id,ordinal);
|
|
CREATE TABLE IF NOT EXISTS discovery_runs(
|
|
id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES operators(id),
|
|
candidate_id TEXT NOT NULL REFERENCES candidates(id), validation_id TEXT REFERENCES validations(id),
|
|
data_json TEXT NOT NULL, created_at TEXT NOT NULL,
|
|
ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS discovery_runs_owner ON discovery_runs(owner_id,ordinal);
|
|
CREATE TABLE IF NOT EXISTS reading_runs(
|
|
id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES operators(id),
|
|
discovery_run_id TEXT NOT NULL REFERENCES discovery_runs(id), candidate_id TEXT NOT NULL REFERENCES candidates(id),
|
|
validation_id TEXT REFERENCES validations(id), data_json TEXT NOT NULL, created_at TEXT NOT NULL,
|
|
ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS reading_runs_owner ON reading_runs(owner_id,ordinal);
|
|
CREATE TABLE IF NOT EXISTS analysis_runs(
|
|
id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES operators(id),
|
|
reading_run_id TEXT NOT NULL REFERENCES reading_runs(id), data_json TEXT NOT NULL, created_at TEXT NOT NULL,
|
|
ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS analysis_runs_reading ON analysis_runs(reading_run_id,ordinal);
|
|
CREATE TABLE IF NOT EXISTS business_briefs(
|
|
id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES operators(id),
|
|
kind TEXT NOT NULL CHECK(kind IN ('parse','confirmed')), parent_id TEXT REFERENCES business_briefs(id), cache_key TEXT,
|
|
data_json TEXT NOT NULL, created_at TEXT NOT NULL, ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE,
|
|
CHECK((kind='parse' AND parent_id IS NULL) OR (kind='confirmed' AND parent_id IS NOT NULL))
|
|
);
|
|
CREATE INDEX IF NOT EXISTS business_briefs_owner ON business_briefs(owner_id,ordinal);
|
|
CREATE INDEX IF NOT EXISTS business_briefs_cache ON business_briefs(owner_id,cache_key);
|
|
CREATE TABLE IF NOT EXISTS analysis_reviews(
|
|
id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES operators(id),
|
|
analysis_run_id TEXT NOT NULL REFERENCES analysis_runs(id), source_ref TEXT NOT NULL,
|
|
previous_id TEXT REFERENCES analysis_reviews(id), data_json TEXT NOT NULL, created_at TEXT NOT NULL,
|
|
ordinal BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS analysis_reviews_analysis ON analysis_reviews(analysis_run_id,ordinal);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS analysis_reviews_first ON analysis_reviews(owner_id,analysis_run_id,source_ref) WHERE previous_id IS NULL;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS analysis_reviews_successor ON analysis_reviews(previous_id) WHERE previous_id IS NOT NULL;
|
|
CREATE TABLE IF NOT EXISTS artifacts(
|
|
id TEXT PRIMARY KEY, task_id TEXT NOT NULL REFERENCES learning_tasks(id), relative_path TEXT NOT NULL,
|
|
sha256 TEXT NOT NULL, bytes BIGINT NOT NULL, created_at TEXT NOT NULL
|
|
);
|
|
CREATE OR REPLACE FUNCTION reject_learning_fact_mutation() RETURNS TRIGGER
|
|
LANGUAGE plpgsql AS $guard$
|
|
BEGIN
|
|
RAISE EXCEPTION 'learning facts are immutable and append only' USING ERRCODE='23514';
|
|
END;
|
|
$guard$;
|