UmbraTrans/umbratrans_web/migrations/0003_compute_and_queue.sql

-- 0003: compute resources, a job queue with leases, and node identity.
--
-- Additive, idempotent, and safe to apply more than once. Nothing existing is
-- rewritten, and no existing table loses a column: `jobs` gains columns,
-- `permissions` gains rows, and the rest are new tables.
--
-- Section 5 is the datastore: publishers, bylines, artifacts and provenance.
-- It is folded in here rather than deferred to 0004 because 0003 is still
-- unapplied to the deployment, and an unapplied migration can still be edited.
-- Once it has run, this is 0004 and a discussion rather than a change.
--
-- The design this implements is §6.16-6.18 and §7 of SPEC/UmbraTools.md. Three
-- decisions in it are load-bearing and each is commented where it is written,
-- because each one exists to stop a failure that is silent when it happens:
--
--   1. A node is `online` because it heartbeated, not because it was created.
--      A node that has never authenticated must not be offered work.
--   2. Claiming a job is `FOR UPDATE SKIP LOCKED`. Without it two workers pick
--      the same row and one silently overwrites the other's claim.
--   3. A lease that expires returns the job to the queue. A worker that is
--      killed releases nothing, so the lease is what releases it.
--
-- `jobs.status` in the phase-1 table is unconstrained text. The values used here
-- are: queued, running, blocked, partial, complete, failed, cancelled. They are
-- left as a CHECK rather than a reference table because this is a closed set
-- owned by this application; SPEC §6.14 moves open vocabularies to reference
-- tables, and this is not one of them.

BEGIN;

-- ---------------------------------------------------------------------
-- 1. Compute nodes and their devices
-- ---------------------------------------------------------------------

-- What each machine is, and where it sits. `is_controller` is separate from
-- `status` because the controller is also a worker on this host, and a node
-- type and a liveness are different facts.
CREATE TABLE IF NOT EXISTS public.compute_nodes (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES public.organizations(id),
    name text NOT NULL,
    is_controller boolean NOT NULL DEFAULT false,
    trust_domain text NOT NULL DEFAULT 'local'
        CHECK (trust_domain IN ('local', 'remote', 'cloud')),
    locality text,
    transport text NOT NULL DEFAULT 'https'
        CHECK (transport IN ('https', 'ssh', 'vpn')),
    -- Starts at 'provisioning'. Only the heartbeat path may set 'online'.
    status text NOT NULL DEFAULT 'provisioning'
        CHECK (status IN ('provisioning', 'online', 'draining', 'offline', 'retired')),
    agent_version text,
    agent_started_at timestamptz,
    last_heartbeat_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (organization_id, name)
);

-- Devices, not a node-level capability flag. "CPU and/or GPU" is a set of
-- devices, and a CPU-only virtual machine is then an ordinary node rather than
-- a special case needing its own code path.
--
-- `throughput_factor` orders placements. It is NOT an eligibility test: a CPU
-- device is slow, not incapable, and a model that fits runs there rather than
-- not running at all. The spec calls this the difference between "slow" and
-- "impossible" and it is why `vram_min_mb` and `vram_preferred_mb` are separate
-- columns on the model rather than one number.
CREATE TABLE IF NOT EXISTS public.compute_devices (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    compute_node_id uuid NOT NULL REFERENCES public.compute_nodes(id) ON DELETE CASCADE,
    kind text NOT NULL CHECK (kind IN
        ('cpu', 'gpu_discrete', 'gpu_integrated', 'npu', 'tpu')),
    label text NOT NULL,                  -- 'gpu:0', 'cpu:0'
    vram_total_mb int NOT NULL DEFAULT 0,
    vram_free_mb int NOT NULL DEFAULT 0,
    ram_total_mb int NOT NULL DEFAULT 0,
    ram_free_mb int NOT NULL DEFAULT 0,
    compute_units real,
    throughput_factor real NOT NULL DEFAULT 1,
    supports_accelerators text[] NOT NULL DEFAULT '{}',
    supports jsonb NOT NULL DEFAULT '{}',
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (compute_node_id, label)
);

-- A node's identity is its credential, and the credential is narrower than an
-- account. It authorises queue operations on the work this node was given; it
-- cannot read project content. A remotely reachable machine holding a
-- connection string with no per-object authorization is a different risk from
-- one holding a scoped key, and this table is the difference.
CREATE TABLE IF NOT EXISTS public.node_credentials (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    compute_node_id uuid NOT NULL REFERENCES public.compute_nodes(id) ON DELETE CASCADE,
    key_prefix text NOT NULL,
    key_hash text NOT NULL,               -- argon2id; the key itself is never stored
    agent_name text NOT NULL,
    scopes text[] NOT NULL,
    issued_at timestamptz NOT NULL DEFAULT now(),
    last_used_at timestamptz,
    revoked_at timestamptz,
    revoked_reason text
);
CREATE INDEX IF NOT EXISTS node_credentials_prefix_idx
    ON public.node_credentials (key_prefix);

-- ---------------------------------------------------------------------
-- 2. Models
-- ---------------------------------------------------------------------

-- `vram_min_mb` is the eligibility floor and `vram_preferred_mb` is the ranking
-- target. Collapsing them into one number is the bug the spec warns about: it
-- either refuses a job that would have completed in an hour, or starts one that
-- crawls. `requires_accelerators` non-empty is the "only this node can run it"
-- case, which is a capacity risk the console has to be able to state.
CREATE TABLE IF NOT EXISTS public.model_registry (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    slug text NOT NULL UNIQUE,
    kind text NOT NULL CHECK (kind IN
        ('llm', 'tts', 'asr', 'embedding', 'vision', 'utility')),
    vram_min_mb int NOT NULL DEFAULT 0,
    vram_preferred_mb int NOT NULL DEFAULT 0,
    ram_min_mb int NOT NULL DEFAULT 0,
    quantisation text,
    context_tokens int,
    requires_accelerators text[] NOT NULL DEFAULT '{}',
    reference_throughput real NOT NULL DEFAULT 1,
    load_seconds int NOT NULL DEFAULT 0,
    capabilities jsonb NOT NULL DEFAULT '{}',
    source_uri text,
    license text,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- Residency. `loaded` means holding its memory whether or not it is
-- generating, which is why a node can be idle at 0% GPU and unable to accept
-- another model.
CREATE TABLE IF NOT EXISTS public.node_models (
    compute_node_id uuid NOT NULL REFERENCES public.compute_nodes(id) ON DELETE CASCADE,
    model_id uuid NOT NULL REFERENCES public.model_registry(id) ON DELETE CASCADE,
    device_id uuid REFERENCES public.compute_devices(id) ON DELETE SET NULL,
    state text NOT NULL CHECK (state IN
        ('loaded', 'warm', 'available', 'loading', 'failed')),
    loaded_at timestamptz,
    vram_held_mb int NOT NULL DEFAULT 0,
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (compute_node_id, model_id)
);

-- Overwritten each heartbeat, deliberately. Its purpose is "now", and the
-- history of a heartbeat is worth nothing; job_events is where history belongs.
CREATE TABLE IF NOT EXISTS public.node_heartbeats (
    compute_node_id uuid PRIMARY KEY REFERENCES public.compute_nodes(id) ON DELETE CASCADE,
    reported_at timestamptz NOT NULL DEFAULT now(),
    cpu_busy_pct numeric(5,2),
    ram_free_mb int,
    vram_free_mb int,
    vram_total_mb int,
    disk_free_mb bigint,
    load_avg_1m numeric(6,2),
    resident_models jsonb NOT NULL DEFAULT '[]',
    active_jobs int NOT NULL DEFAULT 0,
    agent_version text,
    raw jsonb NOT NULL DEFAULT '{}'
);

-- ---------------------------------------------------------------------
-- 3. The queue
-- ---------------------------------------------------------------------

-- `jobs` is specified in §6.8 but was never migrated: 0001 created the identity
-- tables and stopped there. It is created here, complete, rather than
-- incrementally, because there is no existing row to preserve and a table built
-- by a sequence of ALTERs is harder to read than one built in full.
--
-- `organization_id` is NOT NULL and is never updated. A job belongs to exactly one
-- organization for its whole life, and the constraint is at the schema because a
-- placement bug that changed it would hand an organization another tenant's work
-- without anything noticing.
CREATE TABLE IF NOT EXISTS public.jobs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES public.organizations(id),
    project_id uuid REFERENCES public.projects(id),
    lang text,
    kind text NOT NULL,                 -- 'analyze','translate','render','build_pdf',…
    -- The closed set this application owns. Open vocabularies are reference
    -- tables per §6.14; this is not one of them, and it is constrained here
    -- rather than left loose the way the phase-1 tables were.
    status text NOT NULL DEFAULT 'queued'
        CHECK (status IN ('queued', 'running', 'blocked', 'partial',
                          'complete', 'failed', 'cancelled')),
    requested_by text NOT NULL,
    payload jsonb NOT NULL DEFAULT '{}',
    created_at timestamptz NOT NULL DEFAULT now()
);

-- The queue columns. Every one is nullable or defaulted, so a row written by the
-- plain definition above is already valid.
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS priority smallint NOT NULL DEFAULT 100;
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS requires jsonb NOT NULL DEFAULT '{}';
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS lease_owner uuid REFERENCES public.compute_nodes(id);
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS lease_expires_at timestamptz;
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS attempt int NOT NULL DEFAULT 0;
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS max_attempts int NOT NULL DEFAULT 3;
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS not_before timestamptz NOT NULL DEFAULT now();
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS parent_job_id uuid REFERENCES public.jobs(id);
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS idempotency_key text;
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS started_at timestamptz;
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS finished_at timestamptz;
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS result jsonb;
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS error text;

-- Claiming reads this. Partial, because only queued work is claimable and the
-- index should not carry the terminal states.
CREATE INDEX IF NOT EXISTS jobs_claimable_idx
    ON public.jobs (priority, not_before, created_at)
    WHERE status = 'queued';

-- The re-import and re-submit path. Partial, so a job with no key does not
-- collide with anything, and so the same key cannot produce two rows.
CREATE UNIQUE INDEX IF NOT EXISTS jobs_idempotency_idx
    ON public.jobs (idempotency_key)
    WHERE idempotency_key IS NOT NULL;

-- A container is a parent with no lease owner and is never claimed. This index
-- makes "what are the roots" cheap for the master view.
CREATE INDEX IF NOT EXISTS jobs_parent_idx
    ON public.jobs (parent_job_id);

-- Append-only history for a job. The heartbeat table is not this; it is
-- overwritten on purpose because it answers "now".
CREATE TABLE IF NOT EXISTS public.job_events (
    id bigserial PRIMARY KEY,
    job_id uuid NOT NULL REFERENCES public.jobs(id) ON DELETE CASCADE,
    node_id uuid REFERENCES public.compute_nodes(id) ON DELETE SET NULL,
    event text NOT NULL,                 -- 'queued','claimed','completed','failed',
                                         -- 'lease_expired','blocked','decomposed'
    detail jsonb NOT NULL DEFAULT '{}',
    at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS job_events_job_idx ON public.job_events (job_id, at);

-- Jobs get RLS too, now that the table exists. `jobs` had no policy while it did
-- not exist; the eleven phase-1 tables that do have one are listed in the same
-- loop below.
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables
                WHERE schemaname = 'public' AND tablename = 'jobs') THEN
        ALTER TABLE public.jobs ENABLE ROW LEVEL SECURITY;
    END IF;
END
$$;

-- ---------------------------------------------------------------------------
-- 5. The datastore: publishers, bylines, and artifacts
-- ---------------------------------------------------------------------------
--
-- Everything above is machinery for deciding *where* work runs. This section is
-- the reason the queue exists at all: the intermediate and final work product.
-- Step 1 on node 1 can be followed by step 2 on node 3 while step 3 runs on node
-- 2, and the only thing that makes that comprehensible is a place where the
-- output of each step is a row rather than a value inside somebody's memory.
--
-- Four decisions here, each load-bearing, each commented where it is written:
--
--   1. A publisher is a party that never authenticates, so it has no role and no
--      permissions. See `publishers`.
--   2. A byline is not a publisher and not a user. See `authors`.
--   3. An artifact's identity is its bytes. See `artifacts`.
--   4. `job_artifacts` is what makes provenance queryable. See `job_artifacts`.
--
-- The word "account" is deliberately absent from every column, table and comment
-- in this section. It has meant two incompatible things in this system - a person
-- who signs in, and a house a work is published under - and a schema that uses
-- one word for both will eventually use it for both in one constraint.

-- ---------------------------------------------------------------------------
-- 5.1 Publishers: the party a work is published under
-- ---------------------------------------------------------------------------
--
-- The production boundary is the organization: the organization owns the work.
-- The publication boundary is a different axis and a different party, and it
-- routinely points somewhere else. An organization owns the book; a publisher
-- publishes it; those are two facts and they are frequently not the same
-- organization. A public-domain text owned by one organization may be published
-- by a house belonging to another, or by a third party with no organizational
-- relationship to it at all.
--
-- A publisher has **no login, no password and no sessions**, and therefore no
-- row in user_roles and no permission anywhere. When a release is approved "for"
-- an imprint, a *user* holding project.release.approve did it; the imprint is
-- recorded on the work, not in an access-control list. That is the whole reason
-- this is a table rather than a role: a name that appears on work is not a party
-- that can be asked to prove who it is.
--
-- Hierarchy is parent_id, so Umbra -> WorstAuthor is one row referencing another.
-- `kind` distinguishes a house that belongs to an organization from an
-- independent publisher, because who may edit the row depends on it.

CREATE TABLE IF NOT EXISTS public.publishers (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_id uuid REFERENCES public.publishers(id),
    name text NOT NULL CHECK (length(btrim(name)) BETWEEN 1 AND 200),
    slug citext NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9][a-z0-9-]{1,62}$'),
    -- The organization this publisher belongs to, when it belongs to one. Umbra's
    -- imprints do; an independent publisher does not. Nullable because NULL is
    -- the honest value for "no organizational relationship", which is a real and
    -- common arrangement rather than missing data.
    --
    -- This is the one deliberately cross-tenant reference in the schema, and it
    -- carries two obligations that the policy below implements:
    --   * reading a publisher must not reveal whether its owning organization
    --     exists, so the owner column is not exposed by the SELECT policy;
    --   * only a platform administrator may set or change it, because doing so
    --     asserts a relationship between two tenants. An organization
    --     administrator manages its own projects, not who owns somebody else's
    --     imprint.
    owner_organization_id uuid REFERENCES public.organizations(id),
    kind text NOT NULL DEFAULT 'publisher'
        CHECK (kind IN ('imprint', 'publisher')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT publishers_not_own_parent CHECK (parent_id IS DISTINCT FROM id)
);

CREATE INDEX IF NOT EXISTS publishers_parent_idx ON public.publishers (parent_id);
CREATE INDEX IF NOT EXISTS publishers_owner_idx ON public.publishers (owner_organization_id)
    WHERE owner_organization_id IS NOT NULL;

-- Which publisher a project is published under, optionally per language.
--
-- `lang` is nullable and NULL means "the work as a whole". That is not a
-- placeholder: a project can have one publisher for the English edition and a
-- different house for the German one, because co-editions are ordinary. A
-- single publisher_id on the project could not express that, and pretending
-- otherwise would force the model to record something untrue.
--
-- The primary key includes lang, and NULL in a unique constraint does not
-- deduplicate in PostgreSQL - NULLs are distinct - so uniqueness is enforced by
-- the COALESCE index below rather than by the primary key alone.
CREATE TABLE IF NOT EXISTS public.project_publishers (
    project_id uuid NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,
    publisher_id uuid NOT NULL REFERENCES public.publishers(id),
    lang text,
    created_at timestamptz NOT NULL DEFAULT now(),
    created_by uuid REFERENCES public.users(id),
    PRIMARY KEY (project_id, publisher_id, lang)
);

CREATE UNIQUE INDEX IF NOT EXISTS project_publishers_once_idx
    ON public.project_publishers (project_id, publisher_id, COALESCE(lang, ''));

-- ---------------------------------------------------------------------------
-- 5.2 Bylines: authors, translators, narrators, and the people who are credited
-- ---------------------------------------------------------------------------
--
-- A byline is a first-class party and is neither a publisher nor a user.
--
-- Not a publisher: in the house's own chain, Umbra -> WorstAuthor -> B.D. Murphy,
-- WorstAuthor is the imprint and B.D. Murphy is the byline. They are separate
-- entities with separate lifetimes - one publisher issues many books, one byline
-- appears on some of them.
--
-- Not a user: a user is an account that authenticates and performs work; a byline
-- is a name printed on a work. Many bylines have no account at all, and a user may
-- hold many bylines. The optional user_id links the two where they genuinely are
-- the same party, and is nullable because that is frequently untrue rather than
-- unfinished.
--
-- `owner_organization_id` mirrors publishers and carries the same reasoning: a
-- self-published byline belongs to the publishing organization, and an independent
-- author's does not. It also answers who may edit the row, which with no owner
-- would leave the answer "only a platform administrator" - too strict for a
-- system whose purpose includes self-publishing.
CREATE TABLE IF NOT EXISTS public.authors (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name text NOT NULL CHECK (length(btrim(name)) BETWEEN 1 AND 200),
    -- The form printed on the work. Separate from `name`, which is the party's
    -- record name: "B. D. Murphy" and "B.D. Murphy" are the same byline and two
    -- spellings of it, and collapsing them loses the fact that a work was
    -- credited one way and catalogued another.
    display_name text,
    slug citext NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9][a-z0-9-]{1,62}$'),
    -- Set when this byline is a system account. Never the other way round: a
    -- login is an account concern and must not depend on a cataloguing decision.
    user_id uuid REFERENCES public.users(id),
    owner_organization_id uuid REFERENCES public.organizations(id),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS authors_user_once_idx
    ON public.authors (user_id) WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS authors_owner_idx ON public.authors (owner_organization_id)
    WHERE owner_organization_id IS NOT NULL;

-- Which byline is credited on which project, in which capacity, in which language.
--
-- `credit_role` is not a permission and must never be checked as one. It records
-- how a name is printed: the author of the English edition is the translator of
-- the French one, and the narrator of the audiobook is frequently neither. Keying
-- that on a single author_id would force the same person to be recorded twice and
-- would lose the capacity, which is the part a title page has to state.
CREATE TABLE IF NOT EXISTS public.project_authors (
    project_id uuid NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,
    author_id uuid NOT NULL REFERENCES public.authors(id),
    credit_role text NOT NULL
        CHECK (credit_role IN ('author', 'translator', 'editor', 'narrator',
                               'cover_artist', 'illustrator')),
    lang text,
    position smallint NOT NULL DEFAULT 1,
    created_at timestamptz NOT NULL DEFAULT now(),
    created_by uuid REFERENCES public.users(id),
    PRIMARY KEY (project_id, author_id, credit_role, lang)
);

CREATE UNIQUE INDEX IF NOT EXISTS project_authors_order_idx
    ON public.project_authors (project_id, credit_role, COALESCE(lang, ''), position);

-- ---------------------------------------------------------------------------
-- 5.3 Artifacts: the intermediate and final work product
-- ---------------------------------------------------------------------------
--
-- `jobs.result` is a jsonb column on the job row. That is right for "the sleep
-- job returned {'echo': 'hello from the queue'}" and wrong for "this is the
-- English audiobook, four hours twelve minutes, derived from these thirty-seven
-- chapters and this cast". The second is not a job's return value; it is a thing
-- that outlives every job that touched it, and it is what the next job consumes.
--
-- Four decisions:
--
-- 1. **Identity is the bytes.** content_hash is what makes the row the same row
--    across two projects and two runs. `storage_key` is where they are, not what
--    they are, and the two are allowed to disagree - that is what makes it
--    possible to move storage without rewriting history.
--
-- 2. **`organization_id` is denormalised onto every row** even though it is
--    reachable through project_id. One column and one index for the tenant
--    predicate, matching the (id, organization_id) shape the identity tables
--    already use. A join in every policy is a join that can be forgotten.
--
-- 3. **`parent_artifact_id` is the immediate input, and `derivation` is the
--    detail.** A single immediate parent keeps the common query - "what made
--    this" - an index seek. The jsonb carries the rest, because a chapter render
--    depends on its text, its voice, its model version and its settings, and four
--    relational edges for that would be a worse trade than a document that
--    reports itself honestly.
--
-- 4. **The bytes are not in this database.** `storage_key` points outside. A WAV
--    of one chapter runs to hundreds of megabytes and a full audiobook master to
--    gigabytes; they belong in object storage with this table holding identity,
--    provenance and the key. The rule for getting them is deliberately not a
--    column here: it is whether the API carries the bytes or authorises a
--    redirect, and that is a property of the artefact's size rather than a fact
--    about the artefact.
CREATE TABLE IF NOT EXISTS public.artifacts (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,
    organization_id uuid NOT NULL REFERENCES public.organizations(id),
    lang text,
    -- An open vocabulary, not a CHECK. What a project produces is open-ended by
    -- construction - source, translation, audio, cover, blurb, metadata, export -
    -- and constraining it now means an ALTER for every pipeline added later.
    -- §6.14 puts open vocabularies in reference tables; this is the first one,
    -- and it is deliberately not yet a table.
    kind text NOT NULL CHECK (length(btrim(kind)) BETWEEN 1 AND 64),
    status text NOT NULL DEFAULT 'intermediate'
        CHECK (status IN ('intermediate', 'final', 'superseded')),
    -- sha256. The content address is what makes two identical renders the same
    -- artifact rather than two copies of one, which is what makes caching,
    -- deduplication and "has this already been done" all the same question.
    content_hash text NOT NULL CHECK (content_hash ~ '^[0-9a-f]{64}$'),
    byte_size bigint NOT NULL CHECK (byte_size >= 0),
    media_type text NOT NULL,
    storage_key text NOT NULL,
    parent_artifact_id uuid REFERENCES public.artifacts(id),
    derivation jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_by_job_id uuid REFERENCES public.jobs(id) ON DELETE SET NULL,
    created_by_user_id uuid REFERENCES public.users(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT artifacts_id_project_org_key
        UNIQUE (id, project_id, organization_id),
    CONSTRAINT artifacts_no_self_parent CHECK (parent_artifact_id IS DISTINCT FROM id)
);

CREATE INDEX IF NOT EXISTS artifacts_project_idx
    ON public.artifacts (project_id, kind, lang);
CREATE INDEX IF NOT EXISTS artifacts_org_idx ON public.artifacts (organization_id);
CREATE INDEX IF NOT EXISTS artifacts_parent_idx ON public.artifacts (parent_artifact_id)
    WHERE parent_artifact_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS artifacts_lineage_idx ON public.artifacts USING gin (derivation);

-- Content addressing, scoped to the project. NOT globally unique: two
-- organizations legitimately hold identical bytes and neither should learn that
-- the other has them. Uniqueness within a project is what deduplication needs.
CREATE UNIQUE INDEX IF NOT EXISTS artifacts_content_key
    ON public.artifacts (project_id, content_hash, kind, COALESCE(lang, ''));

-- ---------------------------------------------------------------------------
-- 5.4 Provenance: what a job consumed and what it produced
-- ---------------------------------------------------------------------------
--
-- The queue above can tell you a job ran. It cannot tell you what the job was
-- working on, which is the question that matters when step 2 on node 3 produces
-- something wrong: the answer is "this artifact, produced by that step, from
-- these inputs" and getting there needs a row rather than a log line.
--
-- This is the table that makes "step 1 on node 1, then step 2 on node 3" legible
-- after the fact. One table with a direction, not two, because the two sides are
-- always read together and a split would guarantee one of them is forgotten.
--
-- Artifacts outlive jobs, so this is ON DELETE CASCADE from the job: a job that
-- is deleted takes its provenance with it, and the artifacts survive. The
-- reverse - deleting an artifact and keeping the claim that something produced it
-- - would leave a provenance record pointing at nothing.
CREATE TABLE IF NOT EXISTS public.job_artifacts (
    job_id uuid NOT NULL REFERENCES public.jobs(id) ON DELETE CASCADE,
    artifact_id uuid NOT NULL REFERENCES public.artifacts(id) ON DELETE CASCADE,
    role text NOT NULL CHECK (role IN ('consumed', 'produced')),
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (job_id, artifact_id, role)
);

CREATE INDEX IF NOT EXISTS job_artifacts_artifact_idx
    ON public.job_artifacts (artifact_id);

-- ---------------------------------------------------------------------------
-- 5.5 Permissions
-- ---------------------------------------------------------------------------
--
-- The vocabulary is scope.subject.verb and it already exists with twelve keys.
-- These extend it rather than replacing it, with two rules that keep the count
-- from doubling:
--
--   * `read` is implied by `edit`, `review` and `approve`. Nobody edits what they
--     cannot read, and a separate read key is a key somebody will forget to check.
--   * `approve` does NOT imply `edit`. Signing off is a different authority from
--     changing, and that is the distinction the whole owner/worker split rests
--     on: a user may approve a release without being able to alter the text, and
--     the reverse is the mistake worth preventing.
--
-- The read implied by the others is enforced in application code, not here,
-- because expressing "edit implies read" in a permission table needs a closure
-- over the key space and the check would run on every authorization. The rule is
-- written down here so the two halves cannot drift.
--
-- Note what is absent: there is no publisher permission, and there is no byline
-- permission. Neither party authenticates. A user who may approve a release on an
-- imprint's behalf holds project.release.approve; the imprint holds nothing.
INSERT INTO public.permissions (key, description) VALUES
    ('project.source.read',         'Read the source text of a project'),
    ('project.translation.read',    'Read translations of a project'),
    ('project.audio.read',          'Read audio artefacts and their analysis'),
    ('project.audio.edit',          'Adjust or re-render an audio take'),
    ('project.translation.approve', 'Sign off a translation as final'),
    ('project.cover.read',          'Read per-language cover copy and blurb text'),
    ('project.cover.edit',          'Upload finished cover artwork'),
    ('project.export.create',       'Produce a bundle of project data to use elsewhere')
ON CONFLICT (key) DO NOTHING;

-- ---------------------------------------------------------------------------
-- 3b. Append-only where immutability is the point
-- ---------------------------------------------------------------------------
--
-- `audit_events` is already append-only, through a trigger that raises. Three more
-- tables here have the same property and were going in without it, which would have
-- made an immutability claim in the design a convention rather than a constraint:
--
--   * **artifacts** - identity *is* the bytes (`content_hash`). An UPDATE that changes
--     `content_hash` or `storage_key` in place rewrites history for every job that consumed
--     the row, and the lineage in `job_artifacts` would then point at content that never
--     existed. UPDATE only; see the trigger for why DELETE is deliberately permitted.
--   * **job_artifacts** - provenance. If a job's record of what it consumed can be
--     edited, the queue can be made to look as though it did something it did not.
--   * **job_events** - the event trail behind I25 and I26, and the evidence that a job
--     was claimed exactly once and that an expired lease was noticed.
--
-- A dedicated function per table rather than one shared one, so the error names the
-- table that refused. A single function saying "immutable" tells an operator which
-- rule fired only by which object it was raised against, which is one step of work
-- they should not have to do while holding an incident.
--
-- DELETE and UPDATE only. INSERT is how these tables are populated.
-- EXECUTE is revoked from PUBLIC on all three, matching every other function in
-- `app` (the ten that predate this migration were closed by hand; nothing in a
-- migration did it, which is why it needs doing here explicitly).
--
-- A trigger function runs as SECURITY INVOKER, so the guard is only reached if the
-- *calling* role may execute it. Revoking PUBLIC without granting the roles that
-- actually write would therefore turn the guard into an outage rather than a
-- protection - and an outage at least announces itself, whereas a guard that never
-- fires does not. So the grants follow the revoke, to every role that writes these
-- tables, and are re-issued on every apply so a role added later is covered by the
-- next migration rather than by luck.
--
-- Verified after applying: 13 of 13 `app` functions non-PUBLIC-executable, and a
-- non-owner role can still insert into `artifacts` and be refused on update.
--
-- The ten functions that predate this migration were closed by hand against the
-- deployment, and nothing in any migration does it. That is why a database restored
-- from a backup of the deployment comes back with all ten PUBLIC-executable again -
-- observed, on the scratch database built from the pre-0003 dump, where 10 of 13 were
-- open while the deployment itself had 3. A hand-applied grant is not a property of
-- the schema, it is a property of one database, and it does not survive a restore.
--
-- So this closes all thirteen, not just the three added above. B4 was recorded as
-- open against the deployment, where it was already fixed; the defect was never only
-- about the deployment.
--

--
-- Note what this does *not* do: it does not stop the owner role from bypassing RLS
-- (G2), and it does not stop a superuser. It stops an ordinary mistake from being
-- indistinguishable from a design.

DO $$
BEGIN
    EXECUTE $guard$
        CREATE OR REPLACE FUNCTION app.prevent_artifact_mutation()
        RETURNS trigger LANGUAGE plpgsql AS $body$
        BEGIN
            RAISE EXCEPTION
                'artifacts are content-addressed; the row''s identity may not be rewritten - '
                'supersede it with a new artifacts row instead';
        END;
        $body$;
    $guard$;
    EXECUTE $guard$
        CREATE OR REPLACE FUNCTION app.prevent_job_artifact_mutation()
        RETURNS trigger LANGUAGE plpgsql AS $body$
        BEGIN
            RAISE EXCEPTION
                'job_artifacts is provenance and is append-only; a job''s record of what it consumed cannot be edited';
        END;
        $body$;
    $guard$;
    EXECUTE $guard$
        CREATE OR REPLACE FUNCTION app.prevent_job_event_mutation()
        RETURNS trigger LANGUAGE plpgsql AS $body$
        BEGIN
            RAISE EXCEPTION
                'job_events is the event trail and is append-only';
        END;
        $body$;
    $guard$;

    IF EXISTS (SELECT 1 FROM pg_tables
               WHERE schemaname = 'public' AND tablename = 'artifacts') THEN
        EXECUTE 'DROP TRIGGER IF EXISTS artifacts_append_only ON public.artifacts';
        -- UPDATE only, deliberately. DELETE is permitted, because otherwise storage can
            -- never be reclaimed: no artifact row could ever be removed once written, and the
            -- table would grow without bound for the life of the deployment.
            --
            -- The provenance guarantee is not weakened by allowing DELETE. `job_artifacts`
            -- references artifacts `ON DELETE CASCADE`, so deleting an artifact that any job
            -- consumed fires the guard on that table and is refused. An artifact that nothing
            -- consumed is, by definition, nothing anyone's record depends on, and is exactly
            -- the row a retention pass should be able to remove.
            EXECUTE 'CREATE TRIGGER artifacts_append_only BEFORE UPDATE ON public.artifacts FOR EACH ROW EXECUTE FUNCTION app.prevent_artifact_mutation()';
        EXECUTE 'DROP TRIGGER IF EXISTS job_artifacts_append_only ON public.job_artifacts';
        EXECUTE 'CREATE TRIGGER job_artifacts_append_only BEFORE DELETE OR UPDATE ON public.job_artifacts FOR EACH ROW EXECUTE FUNCTION app.prevent_job_artifact_mutation()';
    END IF;
    IF EXISTS (SELECT 1 FROM pg_tables
               WHERE schemaname = 'public' AND tablename = 'job_events') THEN
        EXECUTE 'DROP TRIGGER IF EXISTS job_events_append_only ON public.job_events';
        EXECUTE 'CREATE TRIGGER job_events_append_only BEFORE DELETE OR UPDATE ON public.job_events FOR EACH ROW EXECUTE FUNCTION app.prevent_job_event_mutation()';
    END IF;
END
$$;

DO $$
DECLARE
    target text;
BEGIN
    EXECUTE 'REVOKE ALL ON FUNCTION app.prevent_artifact_mutation() FROM PUBLIC';
    EXECUTE 'REVOKE ALL ON FUNCTION app.prevent_job_artifact_mutation() FROM PUBLIC';
    EXECUTE 'REVOKE ALL ON FUNCTION app.prevent_job_event_mutation() FROM PUBLIC';
--
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'umbracontrol_api') THEN
        EXECUTE 'GRANT EXECUTE ON FUNCTION app.prevent_artifact_mutation() TO umbracontrol_api';
        EXECUTE 'GRANT EXECUTE ON FUNCTION app.prevent_job_artifact_mutation() TO umbracontrol_api';
        EXECUTE 'GRANT EXECUTE ON FUNCTION app.prevent_job_event_mutation() TO umbracontrol_api';
    END IF;
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'umbracontrol_worker') THEN
        EXECUTE 'GRANT EXECUTE ON FUNCTION app.prevent_artifact_mutation() TO umbracontrol_worker';
        EXECUTE 'GRANT EXECUTE ON FUNCTION app.prevent_job_artifact_mutation() TO umbracontrol_worker';
        EXECUTE 'GRANT EXECUTE ON FUNCTION app.prevent_job_event_mutation() TO umbracontrol_worker';
    END IF;

    -- The read-only roles need EXECUTE on the policy helpers, not only on the guards.
    -- Every RLS policy calls app.is_platform_admin() or app.is_organization_member(),
    -- so revoking PUBLIC without this turns every SELECT into
    -- "permission denied for function is_platform_admin" - which looks like a
    -- permissions bug in the policy rather than a missing grant here. Found by
    -- running the tenancy tests as a non-owner role immediately after the revoke:
    -- five of them failed that way.
    FOREACH target IN ARRAY ARRAY[
        'app.can_access_project(uuid)',
        'app.can_view_user(uuid)',
        'app.current_user_id()',
        'app.is_organization_admin(uuid)',
        'app.is_organization_admin_for_project(uuid)',
        'app.is_organization_member(uuid)',
        'app.is_platform_admin()'
    ] LOOP
        IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'umbracontrol_api') THEN
            EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO umbracontrol_api', target);
        END IF;
        IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'umbracontrol_readonly') THEN
            EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO umbracontrol_readonly', target);
        END IF;
        -- rls_probe is the non-owner role the tenancy tests use. It is not a
        -- deployment role; it exists so a policy can be observed refusing something,
        -- and it needs the helpers for exactly that.
        IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'rls_probe') THEN
            EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO rls_probe', target);
        END IF;
    END LOOP;
    -- All thirteen, for the reason given above.
    FOREACH target IN ARRAY ARRAY[
        'app.can_access_project(uuid)',
        'app.can_view_user(uuid)',
        'app.current_user_id()',
        'app.is_organization_admin(uuid)',
        'app.is_organization_admin_for_project(uuid)',
        'app.is_organization_member(uuid)',
        'app.is_platform_admin()',
        'app.prevent_artifact_mutation()',
        'app.prevent_audit_mutation()',
        'app.prevent_job_artifact_mutation()',
        'app.prevent_job_event_mutation()',
        'app.set_updated_at()',
        'app.validate_user_role_scope()'
    ] LOOP
        EXECUTE format('REVOKE ALL ON FUNCTION %s FROM PUBLIC', target);
    END LOOP;

END
$$;

-- ---------------------------------------------------------------------
-- 4. Row-level security
-- ---------------------------------------------------------------------
--
-- Written in the same migration that creates the table, against the same app.*
-- helpers the existing eleven tables use, so that switching RLS on later does
-- not require reconstructing which tables were meant to be covered.
--
-- FORCE is deliberately NOT set here. The application owns these tables, so a
-- policy alone would be inert, and gate G2 is where the non-owner role and
-- FORCE arrive together. These policies are the shape they will have when it
-- does; the enforcement is G2's job and nothing here pretends otherwise.

DO $$
DECLARE
    target text;
BEGIN
    FOREACH target IN ARRAY ARRAY[
        'compute_nodes', 'compute_devices', 'node_credentials',
        'model_registry', 'node_models', 'node_heartbeats', 'job_events',
        'artifacts', 'job_artifacts',
        'publishers', 'authors', 'project_publishers', 'project_authors'
    ] LOOP
        IF EXISTS (SELECT 1 FROM pg_tables
                    WHERE schemaname = 'public' AND tablename = target) THEN
            EXECUTE format('ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY', target);
        END IF;
    END LOOP;
END
$$;

-- Every table above that carries a policy must also appear in the ENABLE list
-- above, or the policy is inert: PostgreSQL will happily create a policy on a
-- table with row-level security switched off and report success. The first
-- version of this section enabled it for `artifacts` and `job_artifacts` and
-- forgot the other four, so `publishers`, `authors`, `project_publishers` and
-- `project_authors` each ended up with a policy that enforced nothing - and a
-- check for "does the policy exist" would have passed throughout.
--
-- Policies are dropped and recreated rather than created conditionally.
-- `CREATE POLICY` has no `IF NOT EXISTS`, and getting this wrong produces a migration
-- that applies once and then can never be re-applied - which is how 0001 learned the same
-- lesson for `ADD CONSTRAINT`. Re-applying must be safe.
DO $$
DECLARE
BEGIN
    EXECUTE 'DROP POLICY IF EXISTS compute_nodes_tenant ON public.compute_nodes';
    EXECUTE 'DROP POLICY IF EXISTS compute_devices_tenant ON public.compute_devices';
    EXECUTE 'DROP POLICY IF EXISTS node_credentials_tenant ON public.node_credentials';
    EXECUTE 'DROP POLICY IF EXISTS node_models_tenant ON public.node_models';
    EXECUTE 'DROP POLICY IF EXISTS node_heartbeats_tenant ON public.node_heartbeats';
    EXECUTE 'DROP POLICY IF EXISTS job_events_tenant ON public.job_events';
    EXECUTE 'DROP POLICY IF EXISTS jobs_tenant ON public.jobs';
    EXECUTE 'DROP POLICY IF EXISTS project_publishers_tenant ON public.project_publishers';
    EXECUTE 'DROP POLICY IF EXISTS project_authors_tenant ON public.project_authors';
    EXECUTE 'DROP POLICY IF EXISTS artifacts_tenant ON public.artifacts';
    EXECUTE 'DROP POLICY IF EXISTS job_artifacts_tenant ON public.job_artifacts';
    EXECUTE 'DROP POLICY IF EXISTS publishers_read ON public.publishers';
    EXECUTE 'DROP POLICY IF EXISTS publishers_write ON public.publishers';
    EXECUTE 'DROP POLICY IF EXISTS authors_read ON public.authors';
    EXECUTE 'DROP POLICY IF EXISTS authors_write ON public.authors';
    EXECUTE 'CREATE POLICY compute_nodes_tenant ON public.compute_nodes USING (app.is_platform_admin() OR app.is_organization_member(organization_id))';
    EXECUTE 'CREATE POLICY compute_devices_tenant ON public.compute_devices USING (app.is_platform_admin() OR EXISTS (SELECT 1 FROM public.compute_nodes n WHERE n.id = compute_devices.compute_node_id AND app.is_organization_member(n.organization_id)))';
    EXECUTE 'CREATE POLICY node_credentials_tenant ON public.node_credentials USING (app.is_platform_admin() OR EXISTS (SELECT 1 FROM public.compute_nodes n WHERE n.id = node_credentials.compute_node_id AND app.is_organization_member(n.organization_id)))';
    EXECUTE 'CREATE POLICY node_models_tenant ON public.node_models USING (app.is_platform_admin() OR EXISTS (SELECT 1 FROM public.compute_nodes n WHERE n.id = node_models.compute_node_id AND app.is_organization_member(n.organization_id)))';
    EXECUTE 'CREATE POLICY node_heartbeats_tenant ON public.node_heartbeats USING (app.is_platform_admin() OR EXISTS (SELECT 1 FROM public.compute_nodes n WHERE n.id = node_heartbeats.compute_node_id AND app.is_organization_member(n.organization_id)))';
    EXECUTE 'CREATE POLICY job_events_tenant ON public.job_events USING (app.is_platform_admin() OR EXISTS (SELECT 1 FROM public.jobs j WHERE j.id = job_events.job_id AND app.is_organization_member(j.organization_id)))';
    EXECUTE 'CREATE POLICY jobs_tenant ON public.jobs USING (app.is_platform_admin() OR app.is_organization_member(organization_id))';

    -- The datastore follows the same rule the project tables do: membership of
    -- the owning organization, not per-project assignment. Org-wide visibility was
    -- chosen deliberately over the can_access_project helper, which contradicts the
    -- rest of its own schema - there is no read permission among the twelve.
    EXECUTE 'CREATE POLICY artifacts_tenant ON public.artifacts USING (app.is_platform_admin() OR app.is_organization_member(organization_id))';
    EXECUTE 'CREATE POLICY job_artifacts_tenant ON public.job_artifacts USING (app.is_platform_admin() OR EXISTS (SELECT 1 FROM public.jobs j WHERE j.id = job_artifacts.job_id AND app.is_organization_member(j.organization_id)))';
    EXECUTE 'CREATE POLICY project_publishers_tenant ON public.project_publishers USING (app.is_platform_admin() OR EXISTS (SELECT 1 FROM public.projects p WHERE p.id = project_publishers.project_id AND app.is_organization_member(p.organization_id)))';
    EXECUTE 'CREATE POLICY project_authors_tenant ON public.project_authors USING (app.is_platform_admin() OR EXISTS (SELECT 1 FROM public.projects p WHERE p.id = project_authors.project_id AND app.is_organization_member(p.organization_id)))';

    -- Publishers and bylines are shared reference data: two organizations can
    -- publish with the same house, and one book can be credited to a byline that
    -- also appears on another organization's work. So they are readable by any
    -- authenticated principal.
    --
    -- The write policies are the interesting part, and they are asymmetric with
    -- the read policies on purpose. `owner_organization_id` is the one column that
    -- crosses tenants, so changing it is a platform-administrator action: an
    -- organization administrator manages its own projects, not who owns somebody
    -- else's imprint. Without that split, an organization administrator could
    -- attach a third party's publisher to their own organization and thereby
    -- assert a corporate relationship that does not exist.
    EXECUTE 'CREATE POLICY publishers_read ON public.publishers FOR SELECT USING (app.current_user_id() IS NOT NULL)';
    EXECUTE 'CREATE POLICY publishers_write ON public.publishers FOR ALL USING (app.is_platform_admin() OR app.is_organization_admin(owner_organization_id)) WITH CHECK (app.is_platform_admin() OR app.is_organization_admin(owner_organization_id))';
    EXECUTE 'CREATE POLICY authors_read ON public.authors FOR SELECT USING (app.current_user_id() IS NOT NULL)';
    EXECUTE 'CREATE POLICY authors_write ON public.authors FOR ALL USING (app.is_platform_admin() OR app.is_organization_admin(owner_organization_id)) WITH CHECK (app.is_platform_admin() OR app.is_organization_admin(owner_organization_id))';
    -- No second policy exists to restrict the owner column, and that is deliberate.
    -- PostgreSQL combines permissive policies with OR, so an extra policy granting
    -- platform administrators an exception would *widen* access for everyone rather
    -- than narrow it for one role. The WITH CHECK above is what actually refuses
    -- it: an organization administrator who sets owner_organization_id to NULL, or
    -- to another organization, produces a row that fails `is_organization_admin`
    -- on that new value, and the statement is rejected. Adding a "clearing" policy
    -- here would have looked like a safeguard and been the opposite of one.
    -- Global, read-only: a model is a fact about the model.
    EXECUTE 'DROP POLICY IF EXISTS model_registry_read ON public.model_registry';
    EXECUTE 'CREATE POLICY model_registry_read ON public.model_registry FOR SELECT USING (true)';
END
$$;

COMMIT;

-- ---------------------------------------------------------------------
-- Rollback, in reverse dependency order. Not applied automatically.
--
-- DROP TABLE IF EXISTS public.job_artifacts;
-- DROP FUNCTION IF EXISTS app.prevent_job_event_mutation();
-- DROP FUNCTION IF EXISTS app.prevent_job_artifact_mutation();
-- DROP FUNCTION IF EXISTS app.prevent_artifact_mutation();
-- DROP TABLE IF EXISTS public.artifacts;
-- DROP TABLE IF EXISTS public.project_authors;
-- DROP TABLE IF EXISTS public.project_publishers;
-- DROP TABLE IF EXISTS public.authors;
-- DROP TABLE IF EXISTS public.publishers;
-- DELETE FROM public.permissions WHERE key IN (
--     'project.source.read','project.translation.read','project.audio.read',
--     'project.audio.edit','project.translation.approve','project.cover.read',
--     'project.cover.edit','project.export.create');
-- DROP TRIGGER IF EXISTS job_events_append_only ON public.job_events;
-- DROP TRIGGER IF EXISTS job_artifacts_append_only ON public.job_artifacts;
-- DROP TRIGGER IF EXISTS artifacts_append_only ON public.artifacts;
-- DROP TABLE IF EXISTS public.job_events;
-- DROP INDEX IF EXISTS public.jobs_idempotency_idx;
-- DROP INDEX IF EXISTS public.jobs_claimable_idx;
-- DROP INDEX IF EXISTS public.jobs_parent_idx;
-- DROP TABLE IF EXISTS public.node_heartbeats;
-- DROP TABLE IF EXISTS public.node_models;
-- DROP TABLE IF EXISTS public.model_registry;
-- DROP TABLE IF EXISTS public.node_credentials;
-- DROP TABLE IF EXISTS public.compute_devices;
-- DROP TABLE IF EXISTS public.compute_nodes;
-- ALTER TABLE public.jobs DROP CONSTRAINT IF EXISTS jobs_organization_immutable;
-- ALTER TABLE public.jobs
--     DROP COLUMN IF EXISTS error, DROP COLUMN IF EXISTS result,
--     DROP COLUMN IF EXISTS finished_at, DROP COLUMN IF EXISTS started_at,
--     DROP COLUMN IF EXISTS idempotency_key, DROP COLUMN IF EXISTS parent_job_id,
--     DROP COLUMN IF EXISTS not_before, DROP COLUMN IF EXISTS max_attempts,
--     DROP COLUMN IF EXISTS attempt, DROP COLUMN IF EXISTS lease_expires_at,
--     DROP COLUMN IF EXISTS lease_owner, DROP COLUMN IF EXISTS requires,
--     DROP COLUMN IF EXISTS priority;