Periods

-- better-supabase module: periods
-- @bsb-block periods@1 managed
-- @bsb-hash sha256:29fa4a572566e150b9060f2d8501d6f5d9952cbf9a83a942b27bb9d3cf0ce646
-- Dated periods per kind with open, locked and closed states, a lookup by date and a close protocol where blocks contribute close blockers and an on-close step.
-- Managed by `better-supabase add`; re-running it rewrites this file unless it was edited by hand.
-- Change it through `blocks` in better-supabase.config.ts and the module's SQL hooks.

create or replace function better_supabase.actor_may_write()
returns boolean
language sql
stable
security invoker
set search_path = ''
as $$
  select case
    when auth.jwt() is null then true
    when auth.jwt() -> 'act' ->> 'kind' = 'support'
      then coalesce(auth.jwt() -> 'act' -> 'read_only' = 'false'::jsonb, false)
    when auth.jwt() -> 'act' ->> 'kind' = 'agent'
      then coalesce(auth.jwt() -> 'act' -> 'read_only', 'false'::jsonb) = 'false'::jsonb
    when auth.jwt() -> 'act' ->> 'kind' is not null then false
    when auth.jwt() -> 'act' ->> 'session_id' is not null
      and auth.jwt() -> 'act' ->> 'kind' is null then false
    else true
  end
$$;
revoke execute on function better_supabase.actor_may_write() from public;
grant execute on function better_supabase.actor_may_write() to anon, authenticated, service_role;

create or replace function better_supabase.raise_actor_readonly()
returns void
language plpgsql
volatile
set search_path = ''
as $$
begin
  raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
end;
$$;
revoke execute on function better_supabase.raise_actor_readonly() from public;
grant execute on function better_supabase.raise_actor_readonly() to anon, authenticated, service_role;
-- The tenant scope policies call the module's functions, so they go before
-- the module drops or replaces one and come back at the end.
do $$
begin
  begin
    drop policy if exists bsb_tenant_scope on "better_supabase"."periods";
    drop policy if exists bsb_tenant_scope_select on "better_supabase"."periods";
    drop policy if exists bsb_tenant_scope_insert on "better_supabase"."periods";
    drop policy if exists bsb_tenant_scope_update on "better_supabase"."periods";
    drop policy if exists bsb_tenant_scope_delete on "better_supabase"."periods";
  exception when undefined_table or invalid_schema_name then null;
  end;
end $$;

create schema if not exists better_supabase;
grant usage on schema better_supabase to anon, authenticated, service_role;
-- Periods: one table of dated ranges per kind with a status, a lookup by date
-- and a close protocol. Blocks add kinds through contributions; closing asks
-- each kind's blockers first and runs its on-close step in the same transaction.
-- Kinds installed now: none.
create table if not exists "better_supabase"."periods" (
  "id" uuid primary key default gen_random_uuid(),
  "tenant_id" uuid not null,
  "kind" text not null check ("kind" ~ '^[a-z][a-z0-9_]{0,62}$'),
  "starts_on" date not null,
  "ends_on" date not null,
  "status" text not null default 'open' check ("status" in ('open', 'locked', 'closed')),
  "closed_at" timestamptz,
  "closed_by" uuid,
  "created_at" timestamptz not null default now(),
  check ("ends_on" >= "starts_on"),
  unique ("tenant_id", "kind", "starts_on")
);
create index if not exists "periods_range_idx" on "better_supabase"."periods" ("tenant_id", "kind", "starts_on", "ends_on");
alter table "better_supabase"."periods" enable row level security;
revoke all on "better_supabase"."periods" from public, anon, authenticated;
grant select on "better_supabase"."periods" to authenticated;
grant all on "better_supabase"."periods" to service_role;
drop policy if exists periods_read on "better_supabase"."periods";
create policy periods_read on "better_supabase"."periods"
  for select to authenticated
  using ("tenant_id" in (select better_supabase.tenant_ids_with('periods.read')));

-- Creates a period of a kind. The range may not overlap another period of the
-- same kind in the organization. Needs periods.write.
create or replace function "better_supabase"."create_period"(tenant uuid, kind text, starts_on date, ends_on date)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_variable
declare
  v_row "better_supabase"."periods";
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (create_period.tenant is not null and coalesce(better_supabase.can('tenant', create_period.tenant, 'periods.write'), false))) then
    raise exception 'not allowed to create periods' using errcode = '42501', hint = 'PERIOD_FORBIDDEN';
  end if;
  if create_period.kind is null or create_period.kind !~ '^[a-z][a-z0-9_]{0,62}$' or create_period.starts_on is null
    or create_period.ends_on is null or create_period.ends_on < create_period.starts_on then
    raise exception 'a period needs a lower case kind and a range that ends on or after it starts' using errcode = '22023', hint = 'PERIOD_INVALID';
  end if;
  if exists (
    select 1 from "better_supabase"."periods" x
    where x."tenant_id" = create_period.tenant and x."kind" = create_period.kind
      and daterange(x."starts_on", x."ends_on", '[]') && daterange(create_period.starts_on, create_period.ends_on, '[]')
  ) then
    raise exception 'the range overlaps a period of this kind' using errcode = '23P01', hint = 'PERIOD_OVERLAP';
  end if;
  insert into "better_supabase"."periods" ("tenant_id", "kind", "starts_on", "ends_on")
  values (create_period.tenant, create_period.kind, create_period.starts_on, create_period.ends_on)
  returning * into v_row;
  null;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'kind', v_row."kind", 'startsOn', v_row."starts_on", 'endsOn', v_row."ends_on", 'status', v_row."status", 'closedAt', v_row."closed_at", 'closedBy', v_row."closed_by", 'createdAt', v_row."created_at");
end;
$$;
revoke execute on function "better_supabase"."create_period"(uuid, text, date, date) from public, anon;
grant execute on function "better_supabase"."create_period"(uuid, text, date, date) to authenticated, service_role;

-- The period of a kind that holds a date, or null. A kind a block gave a
-- cadence gets its calendar period created the first time
-- a date in it is asked for. Needs periods.read.
create or replace function "better_supabase"."period_for"(tenant uuid, kind text, on_date date)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_variable
declare
  v_row "better_supabase"."periods";
begin
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (period_for.tenant is not null and coalesce(better_supabase.can('tenant', period_for.tenant, 'periods.read'), false))) then
    raise exception 'not allowed to read periods' using errcode = '42501', hint = 'PERIOD_FORBIDDEN';
  end if;
  select * into v_row from "better_supabase"."periods" x
  where x."tenant_id" = period_for.tenant and x."kind" = period_for.kind
    and period_for.on_date between x."starts_on" and x."ends_on";
  if found then
    return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'kind', v_row."kind", 'startsOn', v_row."starts_on", 'endsOn', v_row."ends_on", 'status', v_row."status", 'closedAt', v_row."closed_at", 'closedBy', v_row."closed_by", 'createdAt', v_row."created_at");
  end if;
  if v_row."id" is null then
    return null;
  end if;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'kind', v_row."kind", 'startsOn', v_row."starts_on", 'endsOn', v_row."ends_on", 'status', v_row."status", 'closedAt', v_row."closed_at", 'closedBy', v_row."closed_by", 'createdAt', v_row."created_at");
end;
$$;
revoke execute on function "better_supabase"."period_for"(uuid, text, date) from public, anon;
grant execute on function "better_supabase"."period_for"(uuid, text, date) to authenticated, service_role;

-- Whether writes dated on_date may still land in the kind's period: true when
-- there is no period or it is open. Blocks call it before they write.
create or replace function "better_supabase"."period_is_open"(tenant uuid, kind text, on_date date)
returns boolean
language sql
stable
security invoker
set search_path = ''
as $$
  select not exists (
    select 1 from "better_supabase"."periods" x
    where x."tenant_id" = period_is_open.tenant and x."kind" = period_is_open.kind
      and period_is_open.on_date between x."starts_on" and x."ends_on"
      and x."status" <> 'open'
  )
$$;
revoke execute on function "better_supabase"."period_is_open"(uuid, text, date) from public, anon;
grant execute on function "better_supabase"."period_is_open"(uuid, text, date) to authenticated, service_role;

-- What keeps a period from closing, from every block that contributes its
-- kind. An empty array means it can close. Needs periods.read.
create or replace function "better_supabase"."period_close_blockers"(period_id uuid)
returns text[]
language plpgsql
stable
security definer
set search_path = ''
as $$
#variable_conflict use_variable
declare
  v_row "better_supabase"."periods";
  v_blockers text[] := '{}';
begin
  select * into v_row from "better_supabase"."periods" x where x."id" = period_close_blockers.period_id;
  if not found or not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (v_row."tenant_id" is not null and coalesce(better_supabase.can('tenant', v_row."tenant_id", 'periods.read'), false))) then
    raise exception 'no such period' using errcode = 'P0002', hint = 'PERIOD_NOT_FOUND';
  end if;
  -- No installed block adds blockers.
  return v_blockers;
end;
$$;
revoke execute on function "better_supabase"."period_close_blockers"(uuid) from public, anon;
grant execute on function "better_supabase"."period_close_blockers"(uuid) to authenticated, service_role;

-- Stops writes into an open period without closing it. Needs periods.manage.
create or replace function "better_supabase"."lock_period"(period_id uuid)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_variable
declare
  v_row "better_supabase"."periods";
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  select * into v_row from "better_supabase"."periods" x where x."id" = lock_period.period_id for update;
  if not found or not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (v_row."tenant_id" is not null and coalesce(better_supabase.can('tenant', v_row."tenant_id", 'periods.read'), false))) then
    raise exception 'no such period' using errcode = 'P0002', hint = 'PERIOD_NOT_FOUND';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (v_row."tenant_id" is not null and coalesce(better_supabase.can('tenant', v_row."tenant_id", 'periods.manage'), false))) then
    raise exception 'not allowed to lock periods' using errcode = '42501', hint = 'PERIOD_FORBIDDEN';
  end if;
  if v_row."status" <> 'open' then
    return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'kind', v_row."kind", 'startsOn', v_row."starts_on", 'endsOn', v_row."ends_on", 'status', v_row."status", 'closedAt', v_row."closed_at", 'closedBy', v_row."closed_by", 'createdAt', v_row."created_at");
  end if;
  update "better_supabase"."periods" x set "status" = 'locked' where x."id" = v_row."id" returning * into v_row;
  null;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'kind', v_row."kind", 'startsOn', v_row."starts_on", 'endsOn', v_row."ends_on", 'status', v_row."status", 'closedAt', v_row."closed_at", 'closedBy', v_row."closed_by", 'createdAt', v_row."created_at");
end;
$$;
revoke execute on function "better_supabase"."lock_period"(uuid) from public, anon;
grant execute on function "better_supabase"."lock_period"(uuid) to authenticated, service_role;

-- Closes a period. It fails with PERIOD_CLOSE_BLOCKED while any contributing
-- block names a blocker; otherwise it runs each block's on-close step and marks
-- the period closed in one transaction. Closing a closed period returns it
-- unchanged. Needs periods.manage.
create or replace function "better_supabase"."close_period"(period_id uuid)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_variable
declare
  v_row "better_supabase"."periods";
  v_blockers text[];
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  select * into v_row from "better_supabase"."periods" x where x."id" = close_period.period_id for update;
  if not found or not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (v_row."tenant_id" is not null and coalesce(better_supabase.can('tenant', v_row."tenant_id", 'periods.read'), false))) then
    raise exception 'no such period' using errcode = 'P0002', hint = 'PERIOD_NOT_FOUND';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (v_row."tenant_id" is not null and coalesce(better_supabase.can('tenant', v_row."tenant_id", 'periods.manage'), false))) then
    raise exception 'not allowed to close periods' using errcode = '42501', hint = 'PERIOD_FORBIDDEN';
  end if;
  if v_row."status" = 'closed' then
    return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'kind', v_row."kind", 'startsOn', v_row."starts_on", 'endsOn', v_row."ends_on", 'status', v_row."status", 'closedAt', v_row."closed_at", 'closedBy', v_row."closed_by", 'createdAt', v_row."created_at");
  end if;
  v_blockers := "better_supabase"."period_close_blockers"(v_row."id");
  if cardinality(v_blockers) > 0 then
    raise exception 'the period can''t close yet: %', array_to_string(v_blockers, ', ') using errcode = '55000', hint = 'PERIOD_CLOSE_BLOCKED', detail = jsonb_build_object('blockers', to_jsonb(v_blockers))::text;
  end if;
  -- No installed block adds an on-close step.
  update "better_supabase"."periods" x set "status" = 'closed', "closed_at" = now(), "closed_by" = auth.uid()
  where x."id" = v_row."id" returning * into v_row;
  null;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'kind', v_row."kind", 'startsOn', v_row."starts_on", 'endsOn', v_row."ends_on", 'status', v_row."status", 'closedAt', v_row."closed_at", 'closedBy', v_row."closed_by", 'createdAt', v_row."created_at");
end;
$$;
revoke execute on function "better_supabase"."close_period"(uuid) from public, anon;
grant execute on function "better_supabase"."close_period"(uuid) to authenticated, service_role;

-- Reopens a locked or closed period. The on-close steps of the blocks are not
-- undone. Needs periods.manage.
create or replace function "better_supabase"."reopen_period"(period_id uuid, reason text default null)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
#variable_conflict use_variable
declare
  v_row "better_supabase"."periods";
begin
  if not (better_supabase.actor_may_write()) then
    raise exception 'This session is read-only' using errcode = '42501', hint = 'ACTOR_READ_ONLY';
  end if;
  select * into v_row from "better_supabase"."periods" x where x."id" = reopen_period.period_id for update;
  if not found or not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (v_row."tenant_id" is not null and coalesce(better_supabase.can('tenant', v_row."tenant_id", 'periods.read'), false))) then
    raise exception 'no such period' using errcode = 'P0002', hint = 'PERIOD_NOT_FOUND';
  end if;
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (v_row."tenant_id" is not null and coalesce(better_supabase.can('tenant', v_row."tenant_id", 'periods.manage'), false))) then
    raise exception 'not allowed to reopen periods' using errcode = '42501', hint = 'PERIOD_FORBIDDEN';
  end if;
  if v_row."status" = 'open' then
    return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'kind', v_row."kind", 'startsOn', v_row."starts_on", 'endsOn', v_row."ends_on", 'status', v_row."status", 'closedAt', v_row."closed_at", 'closedBy', v_row."closed_by", 'createdAt', v_row."created_at");
  end if;
  update "better_supabase"."periods" x set "status" = 'open', "closed_at" = null, "closed_by" = null
  where x."id" = v_row."id" returning * into v_row;
  null;
  return jsonb_build_object('id', v_row."id", 'organizationId', v_row."tenant_id", 'kind', v_row."kind", 'startsOn', v_row."starts_on", 'endsOn', v_row."ends_on", 'status', v_row."status", 'closedAt', v_row."closed_at", 'closedBy', v_row."closed_by", 'createdAt', v_row."created_at");
end;
$$;
revoke execute on function "better_supabase"."reopen_period"(uuid, text) from public, anon;
grant execute on function "better_supabase"."reopen_period"(uuid, text) to authenticated, service_role;

-- An organization's periods, newest first. filters is { kind, status, limit }.
create or replace function "better_supabase"."list_periods"(tenant uuid, filters jsonb default '{}')
returns jsonb
language plpgsql
stable
security definer
set search_path = ''
as $$
#variable_conflict use_variable
declare
  v_filters jsonb := coalesce(list_periods.filters, '{}'::jsonb);
begin
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin') or (list_periods.tenant is not null and coalesce(better_supabase.can('tenant', list_periods.tenant, 'periods.read'), false))) then
    raise exception 'not allowed to read periods' using errcode = '42501', hint = 'PERIOD_FORBIDDEN';
  end if;
  return coalesce((
    select jsonb_agg(jsonb_build_object('id', y."id", 'organizationId', y."tenant_id", 'kind', y."kind", 'startsOn', y."starts_on", 'endsOn', y."ends_on", 'status', y."status", 'closedAt', y."closed_at", 'closedBy', y."closed_by", 'createdAt', y."created_at") order by y."starts_on" desc, y."kind")
    from (
      select * from "better_supabase"."periods" z
      where z."tenant_id" = list_periods.tenant
        and (not v_filters ? 'kind' or z."kind" = v_filters ->> 'kind')
        and (not v_filters ? 'status' or z."status" = v_filters ->> 'status')
      order by z."starts_on" desc, z."kind"
      limit least(greatest(coalesce((v_filters ->> 'limit')::integer, 100), 1), 500)
    ) y
  ), '[]'::jsonb);
end;
$$;
revoke execute on function "better_supabase"."list_periods"(uuid, jsonb) from public, anon;
grant execute on function "better_supabase"."list_periods"(uuid, jsonb) to authenticated, service_role;

-- Tenant isolation (ADR 0099): permissive policies combine with or, so one
-- the app adds would widen the block's. These restrictive policies keep each
-- table to the caller's organizations and what the block's policies grant.
-- Until memberships installs, the caller is a member of no organization.
do $$
begin
  if to_regprocedure('better_supabase.member_organization_ids(text[])') is null then
    create function better_supabase.member_organization_ids(roles text[] default null)
    returns setof uuid
    language sql
    stable
    set search_path = ''
    as $members$ select null::uuid where false $members$;
  end if;
end $$;
drop policy if exists bsb_tenant_scope on "better_supabase"."periods";
drop policy if exists bsb_tenant_scope_select on "better_supabase"."periods";
create policy bsb_tenant_scope_select on "better_supabase"."periods" as restrictive
  for select to public
  using (
    "tenant_id" in (select better_supabase.member_organization_ids())
    or ("tenant_id" in (select better_supabase.tenant_ids_with('periods.read')))
  );
drop policy if exists bsb_tenant_scope_insert on "better_supabase"."periods";
create policy bsb_tenant_scope_insert on "better_supabase"."periods" as restrictive
  for insert to public
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_update on "better_supabase"."periods";
create policy bsb_tenant_scope_update on "better_supabase"."periods" as restrictive
  for update to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()))
  with check ("tenant_id" in (select better_supabase.member_organization_ids()));
drop policy if exists bsb_tenant_scope_delete on "better_supabase"."periods";
create policy bsb_tenant_scope_delete on "better_supabase"."periods" as restrictive
  for delete to public
  using ("tenant_id" in (select better_supabase.member_organization_ids()));

create schema if not exists better_supabase;
create table if not exists better_supabase.blocks (
  name text primary key,
  block text,
  version integer not null,
  mode text not null,
  installed_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
alter table better_supabase.blocks enable row level security;
revoke all on better_supabase.blocks from public, anon, authenticated;
grant select on better_supabase.blocks to service_role;