shomei-migrations (empty) → 0.2.0.0

raw patch · 44 files changed

+1205/−0 lines, 44 filesdep +aesondep +basedep +bytestring

Dependencies added: aeson, base, bytestring, containers, ephemeral-pg, hasql, optparse-applicative, pg-migrate, pg-migrate-cli, pg-migrate-embed, pg-migrate-test-support, shomei-migrations, tasty, tasty-hunit, text, time

Files

• CHANGELOG.md +43/−0
• LICENSE +21/−0
• app/Main.hs +58/−0
• migrations/shomei/0001-shomei-schema.sql +4/−0
• migrations/shomei/0002-shomei-users.sql +10/−0
• migrations/shomei/0003-shomei-password-credentials.sql +10/−0
• migrations/shomei/0004-shomei-sessions.sql +13/−0
• migrations/shomei/0005-shomei-refresh-tokens.sql +20/−0
• migrations/shomei/0006-shomei-signing-keys.sql +14/−0
• migrations/shomei/0007-shomei-auth-events.sql +15/−0
• migrations/shomei/0008-shomei-users-email-verified.sql +4/−0
• migrations/shomei/0009-shomei-email-verification-tokens.sql +17/−0
• migrations/shomei/0010-shomei-password-reset-tokens.sql +17/−0
• migrations/shomei/0011-shomei-login-attempts.sql +24/−0
• migrations/shomei/0012-shomei-account-lockouts.sql +11/−0
• migrations/shomei/0013-shomei-sessions-actor.sql +7/−0
• migrations/shomei/0014-shomei-webauthn-credentials.sql +19/−0
• migrations/shomei/0015-shomei-webauthn-pending-ceremonies.sql +13/−0
• migrations/shomei/0016-shomei-users-login-id.sql +17/−0
• migrations/shomei/0017-shomei-users-email-optional.sql +14/−0
• migrations/shomei/0018-shomei-password-credentials-login-id.sql +17/−0
• migrations/shomei/0019-shomei-password-credentials-email-optional.sql +14/−0
• migrations/shomei/0020-sweeper-indexes-and-retention.sql +44/−0
• migrations/shomei/0021-shomei-role-grants.sql +27/−0
• migrations/shomei/0022-shomei-service-accounts.sql +38/−0
• migrations/shomei/0023-shomei-oauth-clients.sql +39/−0
• migrations/shomei/0024-shomei-oauth-authorization-codes.sql +47/−0
• migrations/shomei/0025-shomei-sessions-oauth-client.sql +18/−0
• migrations/shomei/0026-shomei-totp-credentials.sql +26/−0
• migrations/shomei/0027-shomei-recovery-codes.sql +25/−0
• migrations/shomei/0028-shomei-role-permissions.sql +27/−0
• migrations/shomei/0029-sessions-kind.sql +19/−0
• migrations/shomei/0030-sessions-granted-scopes.sql +7/−0
• migrations/shomei/0031-oauth-codes-session-id.sql +7/−0
• migrations/shomei/0032-shomei-signing-keys-one-active.sql +24/−0
• migrations/shomei/0033-login-attempts-factor.sql +6/−0
• migrations/shomei/0034-sessions-authenticated-at.sql +8/−0
• migrations/shomei/0035-status-checks-and-case-insensitive-identity.sql +85/−0
• migrations/shomei/0036-unique-password-credential-per-user.sql +8/−0
• migrations/shomei/manifest +36/−0
• shomei-migrations.cabal +108/−0
• src/Shomei/Migrations.hs +59/−0
• test-support/Shomei/Migrations/TestSupport.hs +27/−0
• test/Main.hs +138/−0

@@ -0,0 +1,43 @@+# Changelog for shomei-migrations++All notable changes to `shomei-migrations` are documented here. This package adheres to the+[PVP](https://pvp.haskell.org/) and is versioned independently of the other+Shōmei packages.++## 0.2.0.0 — 2026-08-27++- **Breaking:** every migration in the 36-file history was rewritten to schema-qualify each Shōmei+  relation and to use a transaction-local `SET LOCAL search_path = pg_catalog, pg_temp`, so Shōmei+  no longer leaks session namespace state into a host application that composes other migration+  components. The SQL bytes changed, so **`pg-migrate` checksums differ from `0.1.0.0`**: a database+  that already applied the `0.1.0.0` files will fail checksum verification and needs an operator+  remediation of its ledger rather than a plain upgrade.+- Enforce one password credential per user with+  `shomei_password_credentials_user_id_key`, which also indexes password update lookups.+- Add database `CHECK` constraints for persisted status, outcome, ceremony-kind, and OAuth-client+  vocabulary, plus case-insensitive unique indexes for user and password-credential login ids and+  email addresses.+- Add `shomei_signing_keys.revoked_at` and the partial unique index+  `shomei_signing_keys_one_active`; migration normalizes legacy multi-active rows before enforcing+  the invariant.+- Add the nullable, defaulted `shomei_sessions.kind` column for interactive, machine, and delegated+  session provenance without rewriting existing rows.+- Add `shomei_sessions.granted_scopes` for refresh-stable OAuth grants and nullable+  `shomei_oauth_authorization_codes.session_id` for consumed-code replay revocation.++## 0.1.0.0 — 2026-08-24++Initial release. Owns Shōmei's PostgreSQL schema.++- Embeds Shōmei's ordered SQL manifest at compile time with+  `pg-migrate-embed` and exposes it as a `pg-migrate` `MigrationComponent`,+  so an embedding host composes one migration plan instead of running two.+- Covers the full schema: users and credentials, sessions and refresh+  tokens, lifecycle tokens, login attempts and lockout state, roles and+  role permissions with expiring grants, service accounts, OAuth clients and+  authorization codes, encrypted TOTP secrets and hashed recovery codes,+  passkeys and pending ceremonies, audit events, and signing keys. Includes+  the expiry indexes the sweeper needs.+- Ships the `shomei-migrate` executable for standalone migration runs.+- Ships a public `test-support` sublibrary that provisions a fresh+  ephemeral PostgreSQL with the schema already applied.

@@ -0,0 +1,21 @@+MIT License++Copyright (c) 2026 Nadeem Bitar++Permission is hereby granted, free of charge, to any person obtaining a copy+of this software and associated documentation files (the "Software"), to deal+in the Software without restriction, including without limitation the rights+to use, copy, modify, merge, publish, distribute, sublicense, and/or sell+copies of the Software, and to permit persons to whom the Software is+furnished to do so, subject to the following conditions:++The above copyright notice and this permission notice shall be included in all+copies or substantial portions of the Software.++THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR+IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,+FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE+AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER+LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,+OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE+SOFTWARE.

@@ -0,0 +1,58 @@+-- | @shomei-migrate@: the operator CLI for Shōmei's schema.+--+-- The plan is embedded at compile time, so this binary can only ever migrate the schema+-- it was built with. The application owns configuration (@DATABASE_URL@, overridable per+-- command with @--database-url@), rendering, and the process exit code.+module Main (main) where++import Data.Aeson qualified as Aeson+import Data.ByteString.Lazy.Char8 qualified as LazyByteString+import Data.Text qualified as Text+import Data.Text.IO qualified as Text.IO+import Database.PostgreSQL.Migrate (defaultRunOptions)+import Database.PostgreSQL.Migrate.CLI+import Hasql.Connection.Settings qualified as Settings+import Options.Applicative+import Shomei.Migrations (resolveShomeiMigrationPlan)+import System.Environment (lookupEnv)+import System.Exit qualified as System.Exit++main :: IO ()+main = do+  plan <- resolveShomeiMigrationPlan+  parsedCommand <-+    execParser+      ( info+          (migrationCommandParser plan <**> helper)+          (fullDesc <> progDesc "Manage the Shōmei database schema" <> header "shomei-migrate")+      )+  -- Absent DATABASE_URL is fine for plan/list/check/new, which never connect. The+  -- database-backed commands fail at acquisition with a clear connection error.+  databaseUrl <- maybe "" Text.pack <$> lookupEnv "DATABASE_URL"+  let environment =+        cliEnvironment (Settings.connectionString databaseUrl) plan defaultRunOptions+  outcome <- runMigrationCommand environment parsedCommand+  case commandOutputFormat parsedCommand of+    TextOutput -> Text.IO.putStrLn (renderMigrationCommandText outcome)+    JsonOutput -> LazyByteString.putStrLn (Aeson.encode (renderMigrationCommandJson outcome))+  System.Exit.exitWith (exitCode outcome.exitClass)++-- | Distinct codes so deployment automation can tell a plan/ledger mismatch from a+-- failed apply from a bad invocation.+exitCode :: ExitClass -> System.Exit.ExitCode+exitCode = \case+  ExitSucceeded -> System.Exit.ExitSuccess+  ExitVerificationFailed -> System.Exit.ExitFailure 2+  ExitUsageFailed -> System.Exit.ExitFailure 64+  ExitExecutionFailed -> System.Exit.ExitFailure 1++commandOutputFormat :: MigrationCommand -> OutputFormat+commandOutputFormat = \case+  Plan PlanOptions {output = OutputOptions format} -> format+  List ListOptions {output = OutputOptions format} -> format+  Check CheckOptions {output = OutputOptions format} -> format+  Status StatusOptions {output = OutputOptions format} -> format+  Verify VerifyOptions {output = OutputOptions format} -> format+  Up UpOptions {output = OutputOptions format} -> format+  Repair RepairOptions {output = OutputOptions format} -> format+  New NewOptions {output = OutputOptions format} -> format

@@ -0,0 +1,4 @@+-- Create the dedicated Shōmei namespace. Idempotent.+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE SCHEMA IF NOT EXISTS shomei;

@@ -0,0 +1,10 @@+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE TABLE IF NOT EXISTS shomei.shomei_users (+  user_id      uuid PRIMARY KEY,+  email        text NOT NULL UNIQUE,+  display_name text NULL,+  status       text NOT NULL,+  created_at   timestamptz NOT NULL,+  updated_at   timestamptz NOT NULL+);

@@ -0,0 +1,10 @@+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE TABLE IF NOT EXISTS shomei.shomei_password_credentials (+  credential_id uuid PRIMARY KEY,+  user_id       uuid NOT NULL REFERENCES shomei.shomei_users(user_id),+  email         text NOT NULL UNIQUE,+  password_hash text NOT NULL,+  created_at    timestamptz NOT NULL,+  updated_at    timestamptz NOT NULL+);

@@ -0,0 +1,13 @@+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE TABLE IF NOT EXISTS shomei.shomei_sessions (+  session_id uuid PRIMARY KEY,+  user_id    uuid NOT NULL REFERENCES shomei.shomei_users(user_id),+  status     text NOT NULL,+  created_at timestamptz NOT NULL,+  expires_at timestamptz NOT NULL,+  revoked_at timestamptz NULL+);++CREATE INDEX IF NOT EXISTS shomei_sessions_user_id_idx ON shomei.shomei_sessions (user_id);+CREATE INDEX IF NOT EXISTS shomei_sessions_status_idx  ON shomei.shomei_sessions (status);

@@ -0,0 +1,20 @@+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE TABLE IF NOT EXISTS shomei.shomei_refresh_tokens (+  refresh_token_id uuid PRIMARY KEY,+  session_id       uuid NOT NULL REFERENCES shomei.shomei_sessions(session_id),+  token_hash       text NOT NULL UNIQUE,+  parent_token_id  uuid NULL REFERENCES shomei.shomei_refresh_tokens(refresh_token_id),+  status           text NOT NULL,+  created_at       timestamptz NOT NULL,+  expires_at       timestamptz NOT NULL,+  used_at          timestamptz NULL,+  revoked_at       timestamptz NULL+);++CREATE INDEX IF NOT EXISTS shomei_refresh_tokens_session_id_idx+  ON shomei.shomei_refresh_tokens (session_id);+CREATE INDEX IF NOT EXISTS shomei_refresh_tokens_parent_token_id_idx+  ON shomei.shomei_refresh_tokens (parent_token_id);+CREATE INDEX IF NOT EXISTS shomei_refresh_tokens_status_idx+  ON shomei.shomei_refresh_tokens (status);

@@ -0,0 +1,14 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- public_key_jwk / private_key_jwk are opaque JWK-JSON text (IP-4: the core and+-- postgres packages never import jose; only shomei-jwt interprets the material).+CREATE TABLE IF NOT EXISTS shomei.shomei_signing_keys (+  key_id          text PRIMARY KEY,+  algorithm       text NOT NULL,+  public_key_jwk  text NOT NULL,+  private_key_jwk text NOT NULL,+  status          text NOT NULL,+  created_at      timestamptz NOT NULL,+  activated_at    timestamptz NULL,+  retired_at      timestamptz NULL+);

@@ -0,0 +1,15 @@+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE TABLE IF NOT EXISTS shomei.shomei_auth_events (+  event_id   uuid PRIMARY KEY,+  user_id    uuid NULL,+  session_id uuid NULL,+  event_type text NOT NULL,+  payload    jsonb NOT NULL,+  created_at timestamptz NOT NULL+);++CREATE INDEX IF NOT EXISTS shomei_auth_events_user_id_idx    ON shomei.shomei_auth_events (user_id);+CREATE INDEX IF NOT EXISTS shomei_auth_events_session_id_idx ON shomei.shomei_auth_events (session_id);+CREATE INDEX IF NOT EXISTS shomei_auth_events_event_type_idx ON shomei.shomei_auth_events (event_type);+CREATE INDEX IF NOT EXISTS shomei_auth_events_created_at_idx ON shomei.shomei_auth_events (created_at);

@@ -0,0 +1,4 @@+SET LOCAL search_path = pg_catalog, pg_temp;++ALTER TABLE shomei.shomei_users+  ADD COLUMN IF NOT EXISTS email_verified_at timestamptz NULL;

@@ -0,0 +1,17 @@+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE TABLE IF NOT EXISTS shomei.shomei_email_verification_tokens (+  verification_token_id uuid PRIMARY KEY,+  user_id               uuid NOT NULL REFERENCES shomei.shomei_users(user_id),+  token_hash            text NOT NULL UNIQUE,+  status                text NOT NULL,+  created_at            timestamptz NOT NULL,+  expires_at            timestamptz NOT NULL,+  consumed_at           timestamptz NULL,+  revoked_at            timestamptz NULL+);++CREATE INDEX IF NOT EXISTS shomei_email_verification_tokens_user_id_idx+  ON shomei.shomei_email_verification_tokens (user_id);+CREATE INDEX IF NOT EXISTS shomei_email_verification_tokens_status_idx+  ON shomei.shomei_email_verification_tokens (status);

@@ -0,0 +1,17 @@+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE TABLE IF NOT EXISTS shomei.shomei_password_reset_tokens (+  password_reset_token_id uuid PRIMARY KEY,+  user_id                 uuid NOT NULL REFERENCES shomei.shomei_users(user_id),+  token_hash              text NOT NULL UNIQUE,+  status                  text NOT NULL,+  created_at              timestamptz NOT NULL,+  expires_at              timestamptz NOT NULL,+  consumed_at             timestamptz NULL,+  revoked_at              timestamptz NULL+);++CREATE INDEX IF NOT EXISTS shomei_password_reset_tokens_user_id_idx+  ON shomei.shomei_password_reset_tokens (user_id);+CREATE INDEX IF NOT EXISTS shomei_password_reset_tokens_status_idx+  ON shomei.shomei_password_reset_tokens (status);

@@ -0,0 +1,24 @@+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE TABLE IF NOT EXISTS shomei.shomei_login_attempts (+  attempt_id  uuid PRIMARY KEY,+  account_key text NOT NULL,+  client_ip   text NOT NULL,+  outcome     text NOT NULL,+  occurred_at timestamptz NOT NULL+);++-- Windowed counting reads "failures since cutoff" by account and by IP, so index both+-- (key, occurred_at) pairs; partial on failures keeps the index small and hot.+CREATE INDEX IF NOT EXISTS shomei_login_attempts_account_failures_idx+  ON shomei.shomei_login_attempts (account_key, occurred_at)+  WHERE outcome = 'failure';++CREATE INDEX IF NOT EXISTS shomei_login_attempts_ip_failures_idx+  ON shomei.shomei_login_attempts (client_ip, occurred_at)+  WHERE outcome = 'failure';++-- Counter-reset-on-success reads "most recent success for an account", so index successes too.+CREATE INDEX IF NOT EXISTS shomei_login_attempts_account_successes_idx+  ON shomei.shomei_login_attempts (account_key, occurred_at)+  WHERE outcome = 'success';

@@ -0,0 +1,11 @@+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE TABLE IF NOT EXISTS shomei.shomei_account_lockouts (+  account_key  text PRIMARY KEY,+  failed_count int NOT NULL,+  locked_until timestamptz NULL,+  updated_at   timestamptz NOT NULL+);++CREATE INDEX IF NOT EXISTS shomei_account_lockouts_locked_until_idx+  ON shomei.shomei_account_lockouts (locked_until);

@@ -0,0 +1,7 @@+SET LOCAL search_path = pg_catalog, pg_temp;++ALTER TABLE shomei.shomei_sessions+  ADD COLUMN IF NOT EXISTS actor_user_id uuid NULL REFERENCES shomei.shomei_users(user_id);++CREATE INDEX IF NOT EXISTS shomei_sessions_actor_user_id_idx+  ON shomei.shomei_sessions (actor_user_id);

@@ -0,0 +1,19 @@+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE TABLE IF NOT EXISTS shomei.shomei_webauthn_credentials (+  passkey_id    uuid PRIMARY KEY,+  user_id       uuid NOT NULL REFERENCES shomei.shomei_users(user_id),+  credential_id bytea NOT NULL UNIQUE,+  user_handle   bytea NOT NULL,+  public_key    bytea NOT NULL,+  sign_counter  bigint NOT NULL,+  transports    jsonb NOT NULL,+  label         text NULL,+  created_at    timestamptz NOT NULL,+  last_used_at  timestamptz NULL+);++CREATE INDEX IF NOT EXISTS shomei_webauthn_credentials_user_id_idx+  ON shomei.shomei_webauthn_credentials (user_id);+CREATE INDEX IF NOT EXISTS shomei_webauthn_credentials_user_handle_idx+  ON shomei.shomei_webauthn_credentials (user_handle);

@@ -0,0 +1,13 @@+SET LOCAL search_path = pg_catalog, pg_temp;++CREATE TABLE IF NOT EXISTS shomei.shomei_webauthn_pending_ceremonies (+  ceremony_id  uuid PRIMARY KEY,+  user_id      uuid NULL REFERENCES shomei.shomei_users(user_id),+  kind         text NOT NULL,+  options_blob bytea NOT NULL,+  created_at   timestamptz NOT NULL,+  expires_at   timestamptz NOT NULL+);++CREATE INDEX IF NOT EXISTS shomei_webauthn_pending_ceremonies_expires_at_idx+  ON shomei.shomei_webauthn_pending_ceremonies (expires_at);

@@ -0,0 +1,17 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- Expand: add the new principal column, nullable for now so old and new code coexist.+ALTER TABLE shomei.shomei_users+  ADD COLUMN IF NOT EXISTS login_id text NULL;++-- Backfill: existing rows had email as the principal; identifier defaults to email.+UPDATE shomei.shomei_users+  SET login_id = email+  WHERE login_id IS NULL;++-- Constrain: every user must now have a login id, and it must be unique.+ALTER TABLE shomei.shomei_users+  ALTER COLUMN login_id SET NOT NULL;++CREATE UNIQUE INDEX IF NOT EXISTS shomei_users_login_id_key+  ON shomei.shomei_users (login_id);

@@ -0,0 +1,14 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- Contract: email is now an optional attribute, not the principal.+ALTER TABLE shomei.shomei_users+  ALTER COLUMN email DROP NOT NULL;++-- The old UNIQUE on email was created inline by the CREATE TABLE; drop it and replace+-- with a partial unique index so NULL emails don't collide while real emails stay unique.+ALTER TABLE shomei.shomei_users+  DROP CONSTRAINT IF EXISTS shomei_users_email_key;++CREATE UNIQUE INDEX IF NOT EXISTS shomei_users_email_key+  ON shomei.shomei_users (email)+  WHERE email IS NOT NULL;

@@ -0,0 +1,17 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- Expand: add the new principal column, nullable for now so old and new code coexist.+ALTER TABLE shomei.shomei_password_credentials+  ADD COLUMN IF NOT EXISTS login_id text NULL;++-- Backfill: existing rows had email as the principal; identifier defaults to email.+UPDATE shomei.shomei_password_credentials+  SET login_id = email+  WHERE login_id IS NULL;++-- Constrain: every credential must now have a login id, and it must be unique.+ALTER TABLE shomei.shomei_password_credentials+  ALTER COLUMN login_id SET NOT NULL;++CREATE UNIQUE INDEX IF NOT EXISTS shomei_password_credentials_login_id_key+  ON shomei.shomei_password_credentials (login_id);

@@ -0,0 +1,14 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- Contract: email is now an optional attribute, not the principal.+ALTER TABLE shomei.shomei_password_credentials+  ALTER COLUMN email DROP NOT NULL;++-- The old UNIQUE on email was created inline by the CREATE TABLE; drop it and replace+-- with a partial unique index so NULL emails don't collide while real emails stay unique.+ALTER TABLE shomei.shomei_password_credentials+  DROP CONSTRAINT IF EXISTS shomei_password_credentials_email_key;++CREATE UNIQUE INDEX IF NOT EXISTS shomei_password_credentials_email_key+  ON shomei.shomei_password_credentials (email)+  WHERE email IS NOT NULL;

@@ -0,0 +1,44 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- Sweep-supporting indexes (EP-2 of the operational-hardening MasterPlan,+-- docs/plans/34-expired-data-sweeper-retention-windows-and-supporting-indexes.md).+-- The background sweeper deletes by expiry cutoffs; without these it would seq-scan the+-- very tables it exists to keep small.++-- Sessions are swept on "dead past a grace period", which is+--   expires_at <= cutoff OR (status = 'revoked' AND revoked_at <= cutoff)+-- A single index cannot serve an OR, so index each branch and let the planner BitmapOr+-- them. The revoked branch is partial because only revoked rows carry a revoked_at.+CREATE INDEX IF NOT EXISTS shomei_sessions_expires_at_idx+  ON shomei.shomei_sessions (expires_at);+CREATE INDEX IF NOT EXISTS shomei_sessions_revoked_at_idx+  ON shomei.shomei_sessions (revoked_at)+  WHERE status = 'revoked';++CREATE INDEX IF NOT EXISTS shomei_email_verification_tokens_expires_at_idx+  ON shomei.shomei_email_verification_tokens (expires_at);+CREATE INDEX IF NOT EXISTS shomei_password_reset_tokens_expires_at_idx+  ON shomei.shomei_password_reset_tokens (expires_at);++-- The login-attempt sweep predicate is age-only (occurred_at <= cutoff), which the existing+-- partial indexes cannot serve: they lead on account_key / client_ip.+CREATE INDEX IF NOT EXISTS shomei_login_attempts_occurred_at_idx+  ON shomei.shomei_login_attempts (occurred_at);++-- Audit keyset pagination: ORDER BY created_at DESC, event_id DESC with a+-- (created_at, event_id) < ($cursor) row-comparison predicate wants exactly this composite.+-- It also serves the auth-event retention sweep's created_at <= cutoff range predicate.+CREATE INDEX IF NOT EXISTS shomei_auth_events_created_event_idx+  ON shomei.shomei_auth_events (created_at DESC, event_id DESC);++-- Dead single-column status indexes: each status column holds 3-4 distinct values and no+-- query filters by status alone (every status predicate in shomei-postgres/src is paired+-- with an id equality that a primary-key, unique, or foreign-key index already serves).+-- They are pure write amplification on the hottest write paths.+DROP INDEX IF EXISTS shomei.shomei_sessions_status_idx;+DROP INDEX IF EXISTS shomei.shomei_refresh_tokens_status_idx;+DROP INDEX IF EXISTS shomei.shomei_email_verification_tokens_status_idx;+DROP INDEX IF EXISTS shomei.shomei_password_reset_tokens_status_idx;++-- Superseded by shomei_auth_events_created_event_idx, whose leading column is created_at.+DROP INDEX IF EXISTS shomei.shomei_auth_events_created_at_idx;

@@ -0,0 +1,27 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- The role registry: the catalog of roles an operator has declared grantable. Seeded with+-- 'admin' so the bootstrap grant works on a fresh database with no prior `roles define`.+CREATE TABLE IF NOT EXISTS shomei.shomei_roles (+  role        text        PRIMARY KEY,+  description text        NULL,+  created_at  timestamptz NOT NULL+);++INSERT INTO shomei.shomei_roles (role, description, created_at)+VALUES ('admin', 'Full access to the shomei /admin surface and admin CLI-equivalent HTTP routes', now())+ON CONFLICT (role) DO NOTHING;++-- Durable "user U has role R" facts. The FK into shomei_roles makes "grants reference defined+-- roles" a database invariant rather than workflow discipline. granted_by is nullable: CLI+-- bootstrap grants and config-driven default-role grants have no authenticated actor. There is+-- deliberately no CASCADE on the role FK — the registry is append-only, so the case never arises.+CREATE TABLE IF NOT EXISTS shomei.shomei_role_grants (+  user_id    uuid        NOT NULL REFERENCES shomei.shomei_users(user_id) ON DELETE CASCADE,+  role       text        NOT NULL REFERENCES shomei.shomei_roles(role),+  granted_by uuid        NULL REFERENCES shomei.shomei_users(user_id),+  granted_at timestamptz NOT NULL,+  PRIMARY KEY (user_id, role)+);++CREATE INDEX IF NOT EXISTS shomei_role_grants_role_idx ON shomei.shomei_role_grants (role);

@@ -0,0 +1,38 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- Database-backed service accounts: machine credentials an operator creates, rotates, and+-- revokes at runtime, replacing the static config-defined accounts that required a redeploy.+--+-- client_id is the TypeID text rendering of service_account_id (prefix 'svcacct'), so it is+-- unique and copy-pasteable; secrecy lives entirely in the secret, never in the identifier.+--+-- secret_hash is a lowercase 64-char SHA-256 hex digest of a server-generated 256-bit random+-- secret, compared in constant time. Deliberately NOT Argon2id: these secrets are never+-- human-chosen, so there is no low-entropy preimage to slow down, and an Argon2 verify on every+-- token request would be a self-inflicted DoS vector.+--+-- user_id backs the account with a row in shomei_users, because AuthClaims.subject is a UserId+-- and shomei_sessions.user_id has an FK into shomei_users: a token cannot be minted without a+-- user row behind its session. No CASCADE — the user row is provisioned for this account and+-- the audit trail references both.+--+-- allowed_scopes rides as a jsonb array of scope texts, matching how shomei_webauthn_credentials+-- stores `transports`. No query here needs SQL-level array operators.+--+-- status is 'active' or 'revoked'. A revoked account keeps its row so audit events that name it+-- still resolve.+CREATE TABLE IF NOT EXISTS shomei.shomei_service_accounts (+  service_account_id uuid        PRIMARY KEY,+  client_id          text        NOT NULL UNIQUE,+  user_id            uuid        NOT NULL REFERENCES shomei.shomei_users(user_id),+  secret_hash        text        NOT NULL,+  display_name       text        NOT NULL,+  allowed_scopes     jsonb       NOT NULL,+  status             text        NOT NULL,+  created_at         timestamptz NOT NULL,+  rotated_at         timestamptz NULL,+  revoked_at         timestamptz NULL+);++CREATE INDEX IF NOT EXISTS shomei_service_accounts_user_id_idx+  ON shomei.shomei_service_accounts (user_id);

@@ -0,0 +1,39 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- OAuth2 / OIDC clients: the relying parties that drive the authorization-code flow.+--+-- client_id is the TypeID text rendering of oauth_client_id (prefix 'oauthclient'), matching how+-- shomei_service_accounts derives its client_id. A client_id is public and copy-pasteable;+-- secrecy lives entirely in the secret.+--+-- secret_hash is a lowercase 64-char SHA-256 hex digest, the same format the service accounts use,+-- so Shomei.Workflow.ServiceToken.verifyServiceSecret verifies both. It is NULL for exactly the+-- public clients (SPAs, native apps), which hold no secret and whose only binding between the+-- authorize and token requests is PKCE.+--+-- client_type is 'confidential' or 'public'.+--+-- redirect_uris is a jsonb array of absolute URI texts, compared by EXACT STRING EQUALITY at+-- authorize time. No prefix matching, no wildcards: a redirect_uri that is not registered must+-- never receive a redirect, or the endpoint becomes an open redirector.+--+-- allowed_scopes is a jsonb array of scope texts (matching shomei_service_accounts.allowed_scopes),+-- the ceiling on what an authorize request may ask for.+--+-- status is 'active' or 'revoked'. A revoked client keeps its row so audit events naming it still+-- resolve, and so a revoked client_id is never recycled.+--+-- Unlike a service account, an oauth client has NO backing shomei_users row: it is never a token+-- subject. The user it acts for is the one who authenticated at /oauth/authorize.+CREATE TABLE IF NOT EXISTS shomei.shomei_oauth_clients (+  oauth_client_id uuid        PRIMARY KEY,+  client_id       text        NOT NULL UNIQUE,+  secret_hash     text        NULL,+  client_type     text        NOT NULL,+  display_name    text        NOT NULL,+  redirect_uris   jsonb       NOT NULL,+  allowed_scopes  jsonb       NOT NULL,+  status          text        NOT NULL,+  created_at      timestamptz NOT NULL,+  revoked_at      timestamptz NULL+);

@@ -0,0 +1,47 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- Single-use OAuth2 authorization codes (RFC 6749 §4.1), issued by GET /oauth/authorize and+-- exchanged at POST /oauth/token.+--+-- The primary key is the code's SHA-256 hex digest, never the code: the code itself is a+-- high-entropy opaque string that exists only in the redirect URL and the exchanging request, so a+-- database leak leaks no usable codes. This mirrors how refresh tokens and the one-time+-- verification/reset tokens are stored.+--+-- Every column is a binding the exchange must re-check, which is the point of the table:+--+--   client_id      the code may be exchanged only by the client it was issued to+--   redirect_uri   the exchange must present the same URI the authorize request did+--   code_challenge the PKCE S256 challenge (RFC 7636), NULL when the confidential client sent+--                  none. The method is not stored because only S256 is accepted.+--   user_id        the subject whose session the exchange will mint+--   scopes         a jsonb array of scope texts, as shomei_oauth_clients.allowed_scopes is+--   nonce          echoed verbatim into the ID token when present, so the client can bind the+--                  token to its own session+--   auth_time      when the user actually authenticated (the authorizing token's iat), for the+--                  ID token's auth_time claim+--+-- consumed_at is what makes a code single-use. The exchange is one atomic statement --+-- UPDATE ... SET consumed_at = now WHERE code_hash = $1 AND consumed_at IS NULL AND+-- expires_at > now RETURNING ... -- so two racing exchanges of the same code cannot both win.+-- The row is kept rather than deleted, so a replay is distinguishable from an unknown code by+-- anyone reading the table (both answer invalid_grant on the wire), and so the sweeper is the+-- single deleter.+CREATE TABLE IF NOT EXISTS shomei.shomei_oauth_authorization_codes (+  code_hash       text        PRIMARY KEY,+  client_id       text        NOT NULL,+  redirect_uri    text        NOT NULL,+  user_id         uuid        NOT NULL REFERENCES shomei.shomei_users(user_id),+  scopes          jsonb       NOT NULL,+  nonce           text        NULL,+  code_challenge  text        NULL,+  auth_time       timestamptz NOT NULL,+  created_at      timestamptz NOT NULL,+  expires_at      timestamptz NOT NULL,+  consumed_at     timestamptz NULL+);++-- The sweeper deletes by expiry; codes live 60 seconds by default, so this table is small and+-- churns fast.+CREATE INDEX IF NOT EXISTS shomei_oauth_authorization_codes_expires_at_idx+  ON shomei.shomei_oauth_authorization_codes (expires_at);

@@ -0,0 +1,18 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- The OAuth client that minted this session, for sessions created by the authorization-code grant.+--+-- NULL for every session that already exists and for every one minted by password login, passkey+-- login, MFA completion, impersonation, or a service-account grant. Those flows are unchanged and+-- the bespoke POST /v1/auth/refresh ignores this column entirely.+--+-- Its purpose is client binding on the OAuth refresh_token grant: a refresh token issued through+-- client A must not be rotatable by client B, and Shomei's refresh tokens are already+-- session-scoped, so binding the session is enough. A session with a NULL here cannot be refreshed+-- through /oauth/token at all -- only through the endpoint that created it.+--+-- Deliberately a plain text client_id rather than a foreign key into shomei_oauth_clients: a+-- revoked and re-registered client must never inherit the sessions of its namesake, and the+-- sessions of a deleted client row must not block its deletion.+ALTER TABLE shomei.shomei_sessions+  ADD COLUMN IF NOT EXISTS oauth_client_id text NULL;

@@ -0,0 +1,26 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- A user's TOTP (RFC 6238) second-factor credential (EP-7). One per user (UNIQUE (user_id)):+-- re-enrolling while an unconfirmed row exists replaces it; enrolling over a confirmed row is+-- refused by the workflow (removal is a separate, audited, impersonation-blocked step).+--+-- secret_enc is the AES-256-GCM ciphertext of the raw 20-byte shared secret, laid out as+-- `nonce (12 bytes) || ciphertext || GCM tag (16 bytes)` in one bytea. It is encrypted, never+-- hashed: a verifier must recompute HMAC(secret, counter) on every login, so it needs the+-- secret back. The key lives outside the database (SHOMEI_TOTP_ENCRYPTION_KEY), so a database+-- dump alone never yields a usable secret.+--+-- last_used_counter is the RFC 6238 §5.2 replay-defense high-water mark: a code is accepted only+-- when its time-step counter is strictly greater than this value, which is then updated. NULL+-- until the first acceptance (the confirming code sets it too).+--+-- confirmed_at NULL marks an enrollment that has not yet been activated with a first valid code;+-- rows older than the enrollment TTL with NULL confirmed_at are treated as absent and replaced.+CREATE TABLE IF NOT EXISTS shomei.shomei_totp_credentials (+  totp_credential_id uuid        PRIMARY KEY,+  user_id            uuid        NOT NULL UNIQUE REFERENCES shomei.shomei_users(user_id),+  secret_enc         bytea       NOT NULL,+  last_used_counter  bigint      NULL,+  confirmed_at       timestamptz NULL,+  created_at         timestamptz NOT NULL+);

@@ -0,0 +1,25 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- Single-use MFA recovery codes (EP-7): the lockout escape hatch when a user loses their TOTP+-- authenticator or passkey. Ten per set; regeneration replaces the whole set.+--+-- code_hash is a lowercase SHA-256 hex digest of the normalized code (dash stripped, casefolded),+-- the same defensible pattern machine-credential secrets use. The plaintext is shown to the user once+-- and never stored, so a database dump never yields a spendable code.+--+-- used_at NULL marks a code still spendable. Consumption is a compare-and-set+-- (UPDATE ... WHERE used_at IS NULL RETURNING), which makes a double-spend impossible even under+-- concurrent requests. A spent row is kept (not deleted) so a replay finds a used row.+--+-- No CASCADE on the user FK: a user is never hard-deleted while codes exist, and the row set is+-- replaced wholesale by regeneration.+CREATE TABLE IF NOT EXISTS shomei.shomei_recovery_codes (+  recovery_code_id uuid        PRIMARY KEY,+  user_id          uuid        NOT NULL REFERENCES shomei.shomei_users(user_id),+  code_hash        text        NOT NULL,+  created_at       timestamptz NOT NULL,+  used_at          timestamptz NULL+);++CREATE INDEX IF NOT EXISTS shomei_recovery_codes_user_id_idx+  ON shomei.shomei_recovery_codes (user_id);

@@ -0,0 +1,27 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- Role→permission definitions (EP-9): a role implies a set of flat verb-noun capability+-- strings (e.g. 'projects:write'), resolved to the union across a subject's roles at token+-- mint and carried in the 'permissions' claim. The FK into shomei_roles makes "a permission+-- can only attach to a defined role" a database invariant (typo protection), mirroring the+-- shomei_role_grants.role FK. There is deliberately no CASCADE on the role FK — the registry+-- is append-only, so a role is never deleted.+CREATE TABLE IF NOT EXISTS shomei.shomei_role_permissions (+  role       text        NOT NULL REFERENCES shomei.shomei_roles(role),+  permission text        NOT NULL,+  created_at timestamptz NOT NULL,+  PRIMARY KEY (role, permission)+);++-- Time-bound grants (EP-9): a nullable expiry on each grant. NULL means "forever", so every+-- existing row keeps its meaning — a safe additive migration. Expiry is passive: the mint path+-- filters (expires_at IS NULL OR expires_at > $now); nothing fires at the instant a grant+-- expires, and the (already-inert) row is swept later as hygiene.+ALTER TABLE shomei.shomei_role_grants+  ADD COLUMN IF NOT EXISTS expires_at timestamptz NULL;++-- Serves the sweeper's `DELETE … WHERE expires_at < $1` in bounded batches without taxing the+-- common forever-NULL case; the mint-path list query stays on the (user_id) leading key.+CREATE INDEX IF NOT EXISTS shomei_role_grants_expires_at_idx+  ON shomei.shomei_role_grants (expires_at)+  WHERE expires_at IS NOT NULL;

@@ -0,0 +1,19 @@+-- sessions-kind++SET LOCAL search_path = pg_catalog, pg_temp;++-- How the session was established: 'interactive' (a human proved a credential, or exchanged an+-- authorization code that an interactive session authorized), 'machine' (client_credentials), or+-- 'delegated' (impersonation or RFC 8693 on-behalf-of; the session's access token carries `act`).+--+-- Nullable with a default so that every row predating the column reads as 'interactive' -- the+-- only kind that existed before machine and delegated sessions were distinguishable -- and so a+-- binary built before this column keeps inserting after it is applied (its INSERT names no `kind`+-- and the default fills it). The interpreter always writes an explicit value and refuses an unknown+-- one on read, as it does for `status`.+--+-- The column exists so GET /oauth/authorize can refuse to mint an authorization code for anything+-- but an interactive session. A code becomes a brand-new, refreshable, fully privileged session;+-- a machine or delegated credential must not be able to obtain one (plan 51).+ALTER TABLE shomei.shomei_sessions+  ADD COLUMN IF NOT EXISTS kind text NULL DEFAULT 'interactive';

@@ -0,0 +1,7 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- The scopes the authorization-code grant granted this session, re-applied to every access+-- token refresh mints for it. Empty for every session no OAuth client minted and for every+-- row that predates the column: those sessions never had a granted set to lose.+ALTER TABLE shomei.shomei_sessions+  ADD COLUMN IF NOT EXISTS granted_scopes text[] NOT NULL DEFAULT '{}';

@@ -0,0 +1,7 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- The session the exchange of this code minted, stamped after consumption, so a second+-- presentation of a consumed code (RFC 6749 §4.1.2) can revoke what the first produced.+-- No foreign key: the sweeper deletes codes and sessions on independent schedules.+ALTER TABLE shomei.shomei_oauth_authorization_codes+  ADD COLUMN IF NOT EXISTS session_id uuid NULL;

@@ -0,0 +1,24 @@+-- shomei-signing-keys-one-active++SET LOCAL search_path = pg_catalog, pg_temp;++ALTER TABLE shomei.shomei_signing_keys+  ADD COLUMN IF NOT EXISTS revoked_at timestamptz NULL;++-- Establish the invariant before declaring it. Keep the row the pre-migration loader would+-- have selected (latest activation, then creation, then key id) and retire every other active+-- row. Retired rows remain published so outstanding tokens continue to verify.+UPDATE shomei.shomei_signing_keys+SET status = 'retired', retired_at = now()+WHERE status = 'active'+  AND key_id <> (+    SELECT key_id+    FROM shomei.shomei_signing_keys+    WHERE status = 'active'+    ORDER BY activated_at DESC NULLS LAST, created_at DESC, key_id DESC+    LIMIT 1+  );++CREATE UNIQUE INDEX IF NOT EXISTS shomei_signing_keys_one_active+  ON shomei.shomei_signing_keys ((1))+  WHERE status = 'active';

@@ -0,0 +1,6 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- Which credential the attempt proved or failed to prove; every pre-existing row was a password+-- attempt. Counting stays by outcome (all factors share one lockout), so 0011's indexes stay right.+ALTER TABLE shomei.shomei_login_attempts+  ADD COLUMN IF NOT EXISTS factor text NOT NULL DEFAULT 'password';

@@ -0,0 +1,8 @@+-- sessions-authenticated-at++SET LOCAL search_path = pg_catalog, pg_temp;++-- When the session's last credential was proven (the auth_time claim, which a refresh must not+-- renew). NULL for rows that predate the column; readers fall back to created_at.+ALTER TABLE shomei.shomei_sessions+  ADD COLUMN IF NOT EXISTS authenticated_at timestamptz NULL;

@@ -0,0 +1,85 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- Persisted text is part of the domain boundary. Replacing named constraints keeps this+-- migration deterministic for development databases that may have received an earlier draft.+ALTER TABLE shomei.shomei_users+  DROP CONSTRAINT IF EXISTS shomei_users_status_check;+ALTER TABLE shomei.shomei_users+  ADD CONSTRAINT shomei_users_status_check+  CHECK (status IN ('active', 'suspended', 'deleted'));++ALTER TABLE shomei.shomei_sessions+  DROP CONSTRAINT IF EXISTS shomei_sessions_status_check;+ALTER TABLE shomei.shomei_sessions+  ADD CONSTRAINT shomei_sessions_status_check+  CHECK (status IN ('active', 'revoked', 'expired'));++ALTER TABLE shomei.shomei_refresh_tokens+  DROP CONSTRAINT IF EXISTS shomei_refresh_tokens_status_check;+ALTER TABLE shomei.shomei_refresh_tokens+  ADD CONSTRAINT shomei_refresh_tokens_status_check+  CHECK (status IN ('active', 'used', 'revoked', 'expired'));++ALTER TABLE shomei.shomei_signing_keys+  DROP CONSTRAINT IF EXISTS shomei_signing_keys_status_check;+ALTER TABLE shomei.shomei_signing_keys+  ADD CONSTRAINT shomei_signing_keys_status_check+  CHECK (status IN ('pending', 'active', 'retired', 'revoked'));++ALTER TABLE shomei.shomei_email_verification_tokens+  DROP CONSTRAINT IF EXISTS shomei_email_verification_tokens_status_check;+ALTER TABLE shomei.shomei_email_verification_tokens+  ADD CONSTRAINT shomei_email_verification_tokens_status_check+  CHECK (status IN ('active', 'consumed', 'revoked', 'expired'));++ALTER TABLE shomei.shomei_password_reset_tokens+  DROP CONSTRAINT IF EXISTS shomei_password_reset_tokens_status_check;+ALTER TABLE shomei.shomei_password_reset_tokens+  ADD CONSTRAINT shomei_password_reset_tokens_status_check+  CHECK (status IN ('active', 'consumed', 'revoked', 'expired'));++ALTER TABLE shomei.shomei_login_attempts+  DROP CONSTRAINT IF EXISTS shomei_login_attempts_outcome_check;+ALTER TABLE shomei.shomei_login_attempts+  ADD CONSTRAINT shomei_login_attempts_outcome_check+  CHECK (outcome IN ('success', 'failure'));++ALTER TABLE shomei.shomei_webauthn_pending_ceremonies+  DROP CONSTRAINT IF EXISTS shomei_webauthn_pending_ceremonies_kind_check;+ALTER TABLE shomei.shomei_webauthn_pending_ceremonies+  ADD CONSTRAINT shomei_webauthn_pending_ceremonies_kind_check+  CHECK (kind IN ('registration', 'authentication'));++ALTER TABLE shomei.shomei_service_accounts+  DROP CONSTRAINT IF EXISTS shomei_service_accounts_status_check;+ALTER TABLE shomei.shomei_service_accounts+  ADD CONSTRAINT shomei_service_accounts_status_check+  CHECK (status IN ('active', 'revoked'));++ALTER TABLE shomei.shomei_oauth_clients+  DROP CONSTRAINT IF EXISTS shomei_oauth_clients_status_check;+ALTER TABLE shomei.shomei_oauth_clients+  ADD CONSTRAINT shomei_oauth_clients_status_check+  CHECK (status IN ('active', 'revoked'));++ALTER TABLE shomei.shomei_oauth_clients+  DROP CONSTRAINT IF EXISTS shomei_oauth_clients_client_type_check;+ALTER TABLE shomei.shomei_oauth_clients+  ADD CONSTRAINT shomei_oauth_clients_client_type_check+  CHECK (client_type IN ('confidential', 'public'));++-- The application normalizes new identifiers, but uniqueness must still hold when historical+-- imports, administrative SQL, or a future writer reaches the database directly.+CREATE UNIQUE INDEX IF NOT EXISTS shomei_users_login_id_lower_key+  ON shomei.shomei_users (lower(login_id));++CREATE UNIQUE INDEX IF NOT EXISTS shomei_users_email_lower_key+  ON shomei.shomei_users (lower(email))+  WHERE email IS NOT NULL;++CREATE UNIQUE INDEX IF NOT EXISTS shomei_password_credentials_login_id_lower_key+  ON shomei.shomei_password_credentials (lower(login_id));++CREATE UNIQUE INDEX IF NOT EXISTS shomei_password_credentials_email_lower_key+  ON shomei.shomei_password_credentials (lower(email))+  WHERE email IS NOT NULL;

@@ -0,0 +1,8 @@+SET LOCAL search_path = pg_catalog, pg_temp;++-- One password credential per user is the invariant every workflow assumes: signup creates+-- exactly one, and password reset and change update by user_id expecting one row. Stating it as a+-- UNIQUE index also gives those updates the index they lacked. A duplicate can only have arrived+-- out of band; this migration deliberately refuses to apply until the operator resolves it.+CREATE UNIQUE INDEX IF NOT EXISTS shomei_password_credentials_user_id_key+  ON shomei.shomei_password_credentials (user_id);

@@ -0,0 +1,36 @@+0001-shomei-schema.sql+0002-shomei-users.sql+0003-shomei-password-credentials.sql+0004-shomei-sessions.sql+0005-shomei-refresh-tokens.sql+0006-shomei-signing-keys.sql+0007-shomei-auth-events.sql+0008-shomei-users-email-verified.sql+0009-shomei-email-verification-tokens.sql+0010-shomei-password-reset-tokens.sql+0011-shomei-login-attempts.sql+0012-shomei-account-lockouts.sql+0013-shomei-sessions-actor.sql+0014-shomei-webauthn-credentials.sql+0015-shomei-webauthn-pending-ceremonies.sql+0016-shomei-users-login-id.sql+0017-shomei-users-email-optional.sql+0018-shomei-password-credentials-login-id.sql+0019-shomei-password-credentials-email-optional.sql+0020-sweeper-indexes-and-retention.sql+0021-shomei-role-grants.sql+0022-shomei-service-accounts.sql+0023-shomei-oauth-clients.sql+0024-shomei-oauth-authorization-codes.sql+0025-shomei-sessions-oauth-client.sql+0026-shomei-totp-credentials.sql+0027-shomei-recovery-codes.sql+0028-shomei-role-permissions.sql+0029-sessions-kind.sql+0030-sessions-granted-scopes.sql+0031-oauth-codes-session-id.sql+0032-shomei-signing-keys-one-active.sql+0033-login-attempts-factor.sql+0034-sessions-authenticated-at.sql+0035-status-checks-and-case-insensitive-identity.sql+0036-unique-password-credential-per-user.sql

@@ -0,0 +1,108 @@+cabal-version:      3.0+name:               shomei-migrations+version:            0.2.0.0+synopsis:+  Schema migrations for Shōmei (pg-migrate component, embedded SQL)++description:+  Owns Shōmei's PostgreSQL schema. Embeds Shōmei's ordered SQL manifest at+  compile time with pg-migrate-embed and exposes it as a pg-migrate+  MigrationComponent that a host application can compose with its own+  migrations, so an embedding service applies one migration plan rather than+  two. Ships the shomei-migrate CLI for standalone use, and a public+  test-support sublibrary that provisions a fresh ephemeral PostgreSQL with+  the schema already applied.++homepage:           https://github.com/shinzui/shomei+bug-reports:        https://github.com/shinzui/shomei/issues+license:            MIT+license-file:       LICENSE+author:             Nadeem Bitar+maintainer:         nadeem@gmail.com+copyright:          2026 Nadeem Bitar+category:           Database, Security+build-type:         Simple+tested-with:        GHC ==9.12.4+extra-doc-files:    CHANGELOG.md+extra-source-files:+  migrations/shomei/*.sql+  migrations/shomei/manifest++source-repository head+  type:     git+  location: https://github.com/shinzui/shomei.git++common warnings+  ghc-options:+    -Wall -Wcompat -Widentities -Wincomplete-record-updates+    -Wincomplete-uni-patterns -Wpartial-fields -Wredundant-constraints++common shared+  default-language:   GHC2024+  default-extensions:+    BlockArguments+    DeriveAnyClass+    DuplicateRecordFields+    MultilineStrings+    OverloadedLabels+    OverloadedRecordDot+    OverloadedStrings+    QualifiedDo+    TemplateHaskell++library+  import:          warnings, shared+  hs-source-dirs:  src+  exposed-modules: Shomei.Migrations+  build-depends:+    , base              >=4.18 && <5+    , containers        >=0.7  && <0.8+    , hasql             >=1.10 && <1.11+    , pg-migrate        >=1.1  && <1.2+    , pg-migrate-embed  >=1.1  && <1.2+    , text              >=2.0  && <2.2++executable shomei-migrate+  import:         warnings, shared+  main-is:        Main.hs+  hs-source-dirs: app+  ghc-options:    -threaded -rtsopts -with-rtsopts=-N+  build-depends:+    , aeson                 >=2.1      && <2.3+    , base                  >=4.18     && <5+    , bytestring            >=0.11     && <0.13+    , hasql                 >=1.10     && <1.11+    , optparse-applicative  >=0.19     && <0.20+    , pg-migrate            >=1.1      && <1.2+    , pg-migrate-cli        >=1.1      && <1.2+    , shomei-migrations     ^>=0.2.0.0+    , text                  >=2.0      && <2.2++library test-support+  import:          warnings, shared+  visibility:      public+  hs-source-dirs:  test-support+  exposed-modules: Shomei.Migrations.TestSupport+  build-depends:+    , base               >=4.18     && <5+    , ephemeral-pg       >=0.2.2    && <0.3+    , pg-migrate         >=1.1      && <1.2+    , shomei-migrations  ^>=0.2.0.0+    , text               >=2.0      && <2.2+    , time               >=1.12     && <1.15++test-suite shomei-migrations-test+  import:         warnings, shared+  type:           exitcode-stdio-1.0+  hs-source-dirs: test+  main-is:        Main.hs+  ghc-options:    -threaded -rtsopts -with-rtsopts=-N+  build-depends:+    , base                     >=4.18     && <5+    , containers               >=0.7      && <0.8+    , hasql                    >=1.10     && <1.11+    , pg-migrate               >=1.1      && <1.2+    , pg-migrate-test-support  >=1.1      && <1.2+    , shomei-migrations        ^>=0.2.0.0+    , tasty                    >=1.4      && <1.6+    , tasty-hunit              >=0.10     && <0.11

@@ -0,0 +1,59 @@+{-# LANGUAGE TemplateHaskell #-}+{-# OPTIONS_GHC -fplugin=Database.PostgreSQL.Migrate.Embed.RecompilePlugin #-}++-- | Shōmei's PostgreSQL schema as a @pg-migrate@ component.+--+-- The SQL under @migrations\/shomei\/@ is embedded at compile time from the ordered+-- manifest beside it, so a built binary never reads the migration directory at runtime.+--+-- Host applications that also own migrations should compose 'shomeiMigrationComponent'+-- with their own components into a single 'MigrationPlan', so the whole database is+-- described by one plan and tracked by one ledger. Applications that only need Shōmei's+-- schema can use 'shomeiMigrationPlan' or 'applyShomeiMigrations' directly.+module Shomei.Migrations+  ( shomeiMigrationComponent,+    shomeiMigrationPlan,+    resolveShomeiMigrationPlan,+    applyShomeiMigrations,+  )+where++import Data.List.NonEmpty (NonEmpty (..))+import Data.Set qualified as Set+import Data.Text (Text)+import Database.PostgreSQL.Migrate+import Database.PostgreSQL.Migrate.Embed (embedMigrationManifest)+import Hasql.Connection.Settings qualified as Settings++-- | Shōmei's migration component. Its durable identities are @shomei\/0001-shomei-schema@+-- and so on, in manifest order. It depends on no other component.+shomeiMigrationComponent :: Either DefinitionError MigrationComponent+shomeiMigrationComponent =+  migrationComponentFromEmbeddedSql+    "shomei"+    Set.empty+    $(embedMigrationManifest "migrations/shomei/manifest")++-- | A single-component plan containing only Shōmei's schema.+shomeiMigrationPlan :: Either DefinitionError (Either PlanError MigrationPlan)+shomeiMigrationPlan = do+  component <- shomeiMigrationComponent+  pure (migrationPlan (component :| []))++-- | Resolve 'shomeiMigrationPlan', failing loudly. Both failure modes are programmer+-- errors in a compiled binary: the SQL is embedded and validated at compile time.+resolveShomeiMigrationPlan :: IO MigrationPlan+resolveShomeiMigrationPlan =+  case shomeiMigrationPlan of+    Left definitionError -> fail ("Invalid Shōmei migration component: " <> show definitionError)+    Right (Left planError) -> fail ("Invalid Shōmei migration plan: " <> show planError)+    Right (Right plan) -> pure plan++-- | Apply Shōmei's schema to the database named by a libpq connection string, using+-- @pg-migrate@'s default ledger (schema @pgmigrate@), indefinite advisory-lock waiting,+-- and no statement timeout. Idempotent: already-applied migrations are reported as such+-- and not re-run.+applyShomeiMigrations :: Text -> IO (Either MigrationError MigrationReport)+applyShomeiMigrations connStr = do+  plan <- resolveShomeiMigrationPlan+  runMigrationPlan defaultRunOptions (Settings.connectionString connStr) plan

@@ -0,0 +1,27 @@+-- | Provision a fresh, isolated ephemeral PostgreSQL with the complete Shōmei schema+-- applied in-process through @pg-migrate@. Each call gets a brand-new database+-- (@ephemeral-pg@ caches only the @initdb@ cluster and hands back a fresh server plus+-- database per call), so tests stay isolated.+module Shomei.Migrations.TestSupport+  ( withShomeiMigratedDatabase,+  )+where++import Data.Text (Text)+import EphemeralPg qualified as Pg+import Shomei.Migrations (applyShomeiMigrations)++-- | Run @action@ against a fresh ephemeral PostgreSQL connection string whose database+-- already has the full Shōmei schema applied.+withShomeiMigratedDatabase :: (Text -> IO a) -> IO a+withShomeiMigratedDatabase action = do+  result <- Pg.withCached \db -> do+    let connStr = Pg.connectionString db+    applied <- applyShomeiMigrations connStr+    case applied of+      Left migrationError ->+        error ("Failed to migrate ephemeral Shōmei database: " <> show migrationError)+      Right _ -> action connStr+  case result of+    Left err -> error ("Failed to start ephemeral PostgreSQL: " <> show err)+    Right value -> pure value

@@ -0,0 +1,138 @@+module Main (main) where++import Data.Int (Int64)+import Data.List.NonEmpty (NonEmpty (..))+import Data.Set qualified as Set+import Database.PostgreSQL.Migrate+import Database.PostgreSQL.Migrate.Test+import Hasql.Connection qualified as Connection+import Hasql.Decoders qualified as Decoders+import Hasql.Encoders qualified as Encoders+import Hasql.Session qualified as Session+import Hasql.Statement (Statement)+import Hasql.Statement qualified as Statement+import Shomei.Migrations (shomeiMigrationComponent)+import Test.Tasty+import Test.Tasty.HUnit++main :: IO ()+main =+  defaultMain+    ( testGroup+        "Shomei migrations"+        [ testCase+            "composition preserves a host component's namespace state"+            testCompositionPreservesHostNamespace+        ]+    )++testCompositionPreservesHostNamespace :: Assertion+testCompositionPreservesHostNamespace = do+  result <-+    withMigratedDatabase composedPlan $ \connection ->+      Connection.use connection (Session.statement () compositionSnapshotStatement)+  case result of+    Right (Right snapshot) ->+      assertEqual+        "the host probe and collision table remain in the host schema"+        (CompositionSnapshot 1 True True True True)+        snapshot+    other -> assertFailure ("unexpected composed migration result: " <> show other)++composedPlan :: MigrationPlan+composedPlan =+  expectRight+    ( migrationPlan+        ( hostBeforeComponent+            :| [expectRight shomeiMigrationComponent, hostAfterComponent]+        )+    )++hostBeforeComponent :: MigrationComponent+hostBeforeComponent =+  expectRight+    ( migrationComponent+        "host-before"+        Set.empty+        ( expectRight+            ( sqlMigration+                "0001-host-fixture"+                """+                CREATE SCHEMA host;+                SET search_path TO host, pg_catalog;++                CREATE TABLE host_probe (+                  value text NOT NULL+                );++                CREATE TABLE shomei_users (+                  host_marker text NOT NULL+                );+                """+            )+            :| []+        )+    )++hostAfterComponent :: MigrationComponent+hostAfterComponent =+  expectRight+    ( migrationComponent+        "host-after"+        (Set.fromList ["host-before", "shomei"])+        ( expectRight+            ( sqlMigration+                "0001-use-host-namespace"+                "INSERT INTO host_probe (value) VALUES ('after-shomei')"+            )+            :| []+        )+    )++data CompositionSnapshot = CompositionSnapshot+  { probeRows :: !Int64,+    hostCollisionExists :: !Bool,+    shomeiUsersExists :: !Bool,+    hostMarkerExists :: !Bool,+    hostUserIdAbsent :: !Bool+  }+  deriving stock (Eq, Show)++compositionSnapshotStatement :: Statement () CompositionSnapshot+compositionSnapshotStatement =+  Statement.preparable+    """+    SELECT+      (SELECT count(*)::int8 FROM host.host_probe),+      to_regclass('host.shomei_users') IS NOT NULL,+      to_regclass('shomei.shomei_users') IS NOT NULL,+      EXISTS (+        SELECT 1+        FROM information_schema.columns+        WHERE table_schema = 'host'+          AND table_name = 'shomei_users'+          AND column_name = 'host_marker'+      ),+      NOT EXISTS (+        SELECT 1+        FROM information_schema.columns+        WHERE table_schema = 'host'+          AND table_name = 'shomei_users'+          AND column_name = 'user_id'+      )+    """+    Encoders.noParams+    ( Decoders.singleRow+        ( CompositionSnapshot+            <$> required Decoders.int8+            <*> required Decoders.bool+            <*> required Decoders.bool+            <*> required Decoders.bool+            <*> required Decoders.bool+        )+    )+  where+    required = Decoders.column . Decoders.nonNullable++expectRight :: (Show error) => Either error value -> value+expectRight = either (error . show) id