282 lines
10 KiB
SQL
282 lines
10 KiB
SQL
-- 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);
|