skeleton/internal/database/sql/schema/schema.sql

-- This file is used for generating queries; If you change anything please
-- also add migrations in `assets/migrations`.

-- pgcrypto adds functions for generating UUIDs.
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
-- citext adds indexable case-insensitive text fields.
CREATE EXTENSION IF NOT EXISTS "citext";

-- An Identity is any object that can participate as an actor in the system.
-- It can have groups, permissions, own other objects etc.
CREATE TABLE "identity" (
  "id"          bigserial   NOT NULL,
  "login"       citext      NULL,
  "passphrase"  bytea       NULL,
  "totp_secret" text        NULL,
  "is_admin"    boolean     NOT NULL DEFAULT false,
  "is_disabled" boolean     NOT NULL DEFAULT false,
  "created_at"  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX "identity_login_key" ON "identity" ("login");

-- A person is a human actor within the system, it is linked to exactly one
-- identity.
CREATE TABLE "person" (
  "identity_id"  bigint NOT NULL,
  "display_name" text NULL,
  "first_name"   text NULL,
  "last_name"    text NULL,
  "image_url"    text NULL,
  "zoneinfo"     text NULL,
  "locale"       text NULL,
  FOREIGN KEY ("identity_id")
    REFERENCES "identity" ("id")
    ON DELETE CASCADE
    ON UPDATE RESTRICT,
  PRIMARY KEY ("identity_id")
);

-- Email is an email address for an identity (most likely for a person),
-- that may be verified. Zero or one email address assigned to the identity
-- may be "primary", e.g. used for notifications or login.
CREATE TABLE "email" (
  "address"     citext  NOT NULL,
  "identity_id" bigint  NOT NULL,
  "is_verified" boolean NOT NULL DEFAULT false,
  "is_primary"  boolean NOT NULL DEFAULT false,
  "created_at"  timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY ("identity_id")
    REFERENCES "identity" ("id")
    ON DELETE CASCADE
    ON UPDATE RESTRICT,
  PRIMARY KEY ("address")
);
CREATE INDEX "email_is_verified_idx" ON "email" ("is_verified")
  WHERE "is_verified" = true;
CREATE INDEX "email_identity_id_idx" ON "email" ("identity_id");
CREATE UNIQUE INDEX "email_is_primary_key" ON "email" ("identity_id", "is_primary")
  WHERE "is_primary" = true;

-- Email Confirmation tracks all email confirmations that have been sent out.
CREATE TABLE "email_confirmation" (
  "email_address" citext      NOT NULL,
  "selector"      text        NOT NULL,
  "verifier"      bytea       NOT NULL,
  "valid_until"   timestamptz NOT NULL,
  FOREIGN KEY ("email_address")
    REFERENCES "email" ("address")
    ON DELETE CASCADE
    ON UPDATE RESTRICT,
  PRIMARY KEY ("email_address")
);
CREATE UNIQUE INDEX "email_confirmation_selector_key"
  ON "email_confirmation" ("selector");

-- Password reset keeps track of the password reset tokens.
CREATE TABLE "password_reset" (
  "identity_id" bigserial NOT NULL,
  "selector"    text  NOT NULL,
  "verifier"    bytea NOT NULL,
  "valid_until" timestamptz NOT NULL,
  FOREIGN KEY ("identity_id")
    REFERENCES "person" ("identity_id")
    ON DELETE CASCADE
    ON UPDATE RESTRICT,
  PRIMARY KEY ("identity_id")
);
CREATE UNIQUE INDEX "password_reset_selector_key" ON "password_reset" ("selector");

CREATE TABLE "external_auth" (
  "name"          text NOT NULL,
  "oidc_url"      text NULL,
  "auth_url"      text NOT NULL,
  "token_url"     text NOT NULL,
  "client_key"    text NOT NULL,
  "client_secret" text NOT NULL,
  "created_at"    timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("name")
);
CREATE UNIQUE INDEX "external_auth_name_key" ON "external_auth" ("name");

CREATE TABLE "external_user" (
  "identity_id"        bigint NOT NULL,
  "external_auth_name" text NOT NULL,
  "external_id"        text NOT NULL,
  "auth_token"         text NULL,
  "refresh_token"      text NULL,
  "identity_token"     text NULL,
  FOREIGN KEY ("identity_id")
    REFERENCES "identity" ("id")
    ON UPDATE RESTRICT
    ON DELETE CASCADE,
  FOREIGN KEY ("external_auth_name")
    REFERENCES "external_auth" ("name")
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  PRIMARY KEY ("identity_id")
);
CREATE INDEX "external_user_external_auth_name_idx"
  ON "external_user" ("external_auth_name");
CREATE UNIQUE INDEX "external_user_external_id_key"
  ON "external_user" ("external_auth_name", "external_id");