deploy_requests.sql

deploy_requests.sql

-- enums
create type deploy_request_status as enum ('pending','approved','rejected');

-- table
create table deploy_requests (
  id BIGSERIAL PRIMARY KEY,

  -- who/what asked for approval (exactly one)
  created_by_user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
  created_by_token_id BIGINT REFERENCES api_tokens(id) ON DELETE SET NULL,
  check (
    (created_by_user_id is not null) <> (created_by_token_id is not null)
  ),

  -- lifecycle/decision
  status deploy_request_status not null default 'pending',
  status_changed_by BIGINT REFERENCES users(id) ON DELETE SET NULL,   -- approver/rejector
  status_changed_at timestamptz,
  -- you can compute “still valid” as (status = 'approved' and status_changed_at > now() - interval '15 minutes')

  -- step 1: build
  build_id varchar(16),
  build_created_at timestamptz,

  -- step 2: object
  object_id varchar(16),
  object_created_at timestamptz,

  -- step 3: release promotion
  release_id varchar(16),
  release_promoted_at timestamptz,

  -- audit
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index on deploy_requests (status);
create index on deploy_requests (status_changed_at);
create index on deploy_requests (created_at);

-- quick lookup for “recent approved and unused for build”
create index deploy_requests_recent_approved_idx
  on deploy_requests (status_changed_at)
  where status = 'approved' and build_id is null;

-- ensure each step is used at most once across all requests
create unique index on deploy_requests (build_id)  where build_id  is not null;
create unique index on deploy_requests (object_id) where object_id is not null;
create unique index on deploy_requests (release_id) where release_id is not null;

-- keep decision fields in sync with status
create or replace function trg_deploy_requests_status_guard()
returns trigger language plpgsql as $$
begin
  -- pending must have no decision fields
  if new.status = 'pending' then
    new.status_changed_by := null;
    new.status_changed_at := null;

  elsif new.status = 'approved' then
    if new.status_changed_by is null then
      raise exception 'approved requires status_changed_by';
    end if;
    if new.status_changed_at is null then
      new.status_changed_at := now();
    end if;

  elsif new.status = 'rejected' then
    if new.status_changed_by is null then
      raise exception 'rejected requires status_changed_by';
    end if;
    if new.status_changed_at is null then
      new.status_changed_at := now();
    end if;
  end if;

  new.updated_at := now();
  return new;
end $$;

create trigger deploy_requests_status_guard
before insert or update of status, status_changed_by, status_changed_at
on deploy_requests
for each row execute function trg_deploy_requests_status_guard();

-- enforce sequencing: build -> object -> release, and immutability per stage
create or replace function trg_deploy_requests_sequence_guard()
returns trigger language plpgsql as $$
begin
  -- must be approved to attach any step
  if (coalesce(new.build_id, new.object_id, new.release_id) is not null)
     and new.status <> 'approved' then
    raise exception 'cannot attach build/object/release unless status=approved';
  end if;

  -- build is first: object/release require build
  if new.object_id is not null and new.build_id is null then
    raise exception 'object_id requires build_id';
  end if;
  if new.release_id is not null and new.object_id is null then
    raise exception 'release_id requires object_id';
  end if;

  -- first time we set each id, stamp its timestamp; prevent changing once set
  if tg_op = 'UPDATE' then
    if old.build_id is not null and new.build_id <> old.build_id then
      raise exception 'build_id is immutable once set';
    end if;
    if old.object_id is not null and new.object_id <> old.object_id then
      raise exception 'object_id is immutable once set';
    end if;
    if old.release_id is not null and new.release_id <> old.release_id then
      raise exception 'release_id is immutable once set';
    end if;
  end if;

  if new.build_id is not null and new.build_created_at is null then
    new.build_created_at := now();
  end if;
  if new.object_id is not null and new.object_created_at is null then
    new.object_created_at := now();
  end if;
  if new.release_id is not null and new.release_promoted_at is null then
    new.release_promoted_at := now();
  end if;

  return new;
end $$;

create trigger deploy_requests_sequence_guard
before insert or update of build_id, object_id, release_id
on deploy_requests
for each row execute function trg_deploy_requests_sequence_guard();

-- optional: forbid attaching steps after the 15-minute window
create or replace function trg_deploy_requests_window_guard()
returns trigger language plpgsql as $$
begin
  if new.status = 'approved'
     and new.approved_at is not null
     and (new.build_id is not null or new.object_id is not null or new.release_id is not null)
     and now() > new.approved_at + interval '15 minutes'
  then
    raise exception 'approval window expired';
  end if;
  return new;
end $$;

create trigger deploy_requests_window_guard
before insert or update of build_id, object_id, release_id
on deploy_requests
for each row execute function trg_deploy_requests_window_guard();