-- SPDX-License-Identifier: LicenseRef-ConusAI-Template-1.0
create table "outbox" (
  "id" uuid primary key,
  "org_id" uuid not null,
  "name" text not null,
  "payload" jsonb not null,
  "created_at" timestamptz not null,
  "attempts" integer not null,
  "published_at" timestamptz,
  "last_error" text,
  "deferrals" integer not null default 0,
  "deferred_since" timestamptz,
  "deferred_until" timestamptz
);

create index "outbox_pending_idx" on "outbox" ("created_at") where "published_at" is null;

alter table "outbox" enable row level security;

alter table "outbox" force row level security;

create policy "outbox_tenant" on "outbox" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);

create table "idempotency_keys" (
  "org_id" uuid not null,
  -- Not null, with the zero uuid where a scope has no workspace or project: a primary key
  -- ignores a row whose column is NULL, and the key would stop being unique.
  "workspace_id" uuid not null,
  "project_id" uuid not null,
  "procedure" text not null,
  "key" text not null,
  -- The response may itself be the JSON value null, so "finished" is a column of its own
  -- rather than a non-null response. Null while the transaction that claimed this key is
  -- running; a second caller's insert blocks on the key until that transaction ends.
  "response" jsonb,
  "completed_at" timestamptz,
  "created_at" timestamptz not null,
  primary key ("org_id", "workspace_id", "project_id", "procedure", "key")
);

alter table "idempotency_keys" enable row level security;

alter table "idempotency_keys" force row level security;

create policy "idempotency_keys_tenant" on "idempotency_keys" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);

create table "auth_api_keys" (
  "id" uuid primary key,
  "version" integer not null,
  "created_at" timestamptz not null,
  "updated_at" timestamptz not null,
  "org_id" uuid not null,
  "name" text not null,
  "token_hash" text not null,
  "prefix" text not null,
  "user_id" uuid not null,
  "expires_at" text,
  "revoked_at" text
);

alter table "auth_api_keys" add constraint "auth_api_keys_org_id_token_hash_key" unique nulls not distinct ("org_id", "token_hash");

create index "auth_api_keys_org_id_user_id_idx" on "auth_api_keys" ("org_id", "user_id");

create index "auth_api_keys_org_id_prefix_idx" on "auth_api_keys" ("org_id", "prefix");

create table "auth_sessions" (
  "id" uuid primary key,
  "version" integer not null,
  "created_at" timestamptz not null,
  "updated_at" timestamptz not null,
  "user_id" uuid not null,
  "issuer" text not null,
  "subject" text not null,
  "handle_hash" text not null,
  "tokens" text not null,
  "access_token_expires_at" text not null,
  "last_seen_at" text not null,
  "absolute_expires_at" text not null,
  "revoked_at" text
);

alter table "auth_sessions" add constraint "auth_sessions_handle_hash_key" unique nulls not distinct ("handle_hash");

create index "auth_sessions_user_id_idx" on "auth_sessions" ("user_id");

create index "auth_sessions_absolute_expires_at_idx" on "auth_sessions" ("absolute_expires_at");

create table "extensions" (
  "id" uuid primary key,
  "version" integer not null,
  "created_at" timestamptz not null,
  "updated_at" timestamptz not null,
  "org_id" uuid not null,
  "subject_type" text not null,
  "subject_id" uuid not null,
  "namespace" text not null,
  "payload" jsonb not null,
  "payload_version" integer not null
);

alter table "extensions" add constraint "extensions_org_id_subject_type_subject_id_namespace_key" unique nulls not distinct ("org_id", "subject_type", "subject_id", "namespace");

create index "extensions_org_id_namespace_idx" on "extensions" ("org_id", "namespace");

create table "invitations" (
  "id" uuid primary key,
  "version" integer not null,
  "created_at" timestamptz not null,
  "updated_at" timestamptz not null,
  "org_id" uuid not null,
  "email" text not null,
  "role" text not null,
  "workspace_id" uuid,
  "workspace_role" text,
  "token_hash" text not null,
  "expires_at" text not null,
  "accepted_at" text,
  "invited_by" uuid not null
);

alter table "invitations" add constraint "invitations_org_id_token_hash_key" unique nulls not distinct ("org_id", "token_hash");

create index "invitations_org_id_email_idx" on "invitations" ("org_id", "email");

create index "invitations_org_id_accepted_at_idx" on "invitations" ("org_id", "accepted_at");

create table "kit_processed_events" (
  "id" uuid primary key,
  "version" integer not null,
  "created_at" timestamptz not null,
  "updated_at" timestamptz not null,
  "org_id" uuid not null,
  "event_id" uuid not null,
  "subscriber" text not null,
  "event" text not null
);

alter table "kit_processed_events" add constraint "kit_processed_events_org_id_event_id_subscriber_key" unique nulls not distinct ("org_id", "event_id", "subscriber");

create index "kit_processed_events_org_id_event_id_idx" on "kit_processed_events" ("org_id", "event_id");

create table "org_memberships" (
  "id" uuid primary key,
  "version" integer not null,
  "created_at" timestamptz not null,
  "updated_at" timestamptz not null,
  "org_id" uuid not null,
  "user_id" uuid not null,
  "role" text not null
);

alter table "org_memberships" add constraint "org_memberships_org_id_user_id_key" unique nulls not distinct ("org_id", "user_id");

create index "org_memberships_org_id_role_idx" on "org_memberships" ("org_id", "role");

create table "organizations" (
  "id" uuid primary key,
  "version" integer not null,
  "created_at" timestamptz not null,
  "updated_at" timestamptz not null,
  "slug" text not null,
  "name" text not null,
  "status" text not null,
  "settings" jsonb not null,
  "branding" jsonb
);

alter table "organizations" add constraint "organizations_slug_key" unique nulls not distinct ("slug");

create index "organizations_status_idx" on "organizations" ("status");

create table "projects" (
  "id" uuid primary key,
  "version" integer not null,
  "created_at" timestamptz not null,
  "updated_at" timestamptz not null,
  "org_id" uuid not null,
  "workspace_id" uuid not null,
  "slug" text not null,
  "name" text not null,
  "module_key" text not null,
  "archived_at" text
);

alter table "projects" add constraint "projects_org_id_workspace_id_slug_key" unique nulls not distinct ("org_id", "workspace_id", "slug");

create index "projects_org_id_workspace_id_archived_at_idx" on "projects" ("org_id", "workspace_id", "archived_at");

create table "users" (
  "id" uuid primary key,
  "version" integer not null,
  "created_at" timestamptz not null,
  "updated_at" timestamptz not null,
  "issuer" text not null,
  "subject" text not null,
  "email" text not null,
  "display_name" text not null,
  "locale" text not null
);

alter table "users" add constraint "users_issuer_subject_key" unique nulls not distinct ("issuer", "subject");

create index "users_email_idx" on "users" ("email");

create table "workspace_memberships" (
  "id" uuid primary key,
  "version" integer not null,
  "created_at" timestamptz not null,
  "updated_at" timestamptz not null,
  "org_id" uuid not null,
  "workspace_id" uuid not null,
  "user_id" uuid not null,
  "role" text not null
);

alter table "workspace_memberships" add constraint "workspace_memberships_org_id_workspace_id_user_id_key" unique nulls not distinct ("org_id", "workspace_id", "user_id");

create index "workspace_memberships_org_id_user_id_idx" on "workspace_memberships" ("org_id", "user_id");

create table "workspaces" (
  "id" uuid primary key,
  "version" integer not null,
  "created_at" timestamptz not null,
  "updated_at" timestamptz not null,
  "org_id" uuid not null,
  "slug" text not null,
  "name" text not null,
  "is_default" boolean not null,
  "visibility" text not null
);

alter table "workspaces" add constraint "workspaces_org_id_slug_key" unique nulls not distinct ("org_id", "slug");

alter table "auth_api_keys" enable row level security;

alter table "auth_api_keys" force row level security;

create policy "auth_api_keys_tenant" on "auth_api_keys" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);

alter table "extensions" enable row level security;

alter table "extensions" force row level security;

create policy "extensions_tenant" on "extensions" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);

alter table "invitations" enable row level security;

alter table "invitations" force row level security;

create policy "invitations_tenant" on "invitations" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);

alter table "kit_processed_events" enable row level security;

alter table "kit_processed_events" force row level security;

create policy "kit_processed_events_tenant" on "kit_processed_events" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);

alter table "org_memberships" enable row level security;

alter table "org_memberships" force row level security;

create policy "org_memberships_tenant" on "org_memberships" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);

alter table "projects" enable row level security;

alter table "projects" force row level security;

create policy "projects_tenant" on "projects" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);

alter table "workspace_memberships" enable row level security;

alter table "workspace_memberships" force row level security;

create policy "workspace_memberships_tenant" on "workspace_memberships" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);

alter table "workspaces" enable row level security;

alter table "workspaces" force row level security;

create policy "workspaces_tenant" on "workspaces" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);