Tizian 「ティツィアーン」
e8c4a3d472
Wrong codes for removing an authenticator, new recovery codes or confirming
one counted toward the account's waits (the re-review's third finding): a
stolen session alone, without the password, could keep the owner's codes
waiting for up to a day at a time, renewed without end, holding them out
of signing in and of changing the password, the way to end that session,
and nobody was mailed. Those codes now count per session, where the session
is (the register's own, or a member's, which forwards the code): each try
counted before its code is checked (one statement, so tries sent at once
count each), a code accepted clearing the count, and the tenth wrong in a
row ending the session (sessionEnded; the device says it was signed out).
A try that checked no code (a refusal of something else) is taken back.
The account's waits now count only codes after a right password, and the
per-account ten tries an hour, which a session could fill as well, go.
Migration (module, Learn's and Todo's servers, Learn's device schema):
CREATE TABLE "session_code_failures" ("id" bigserial PRIMARY KEY,
"sessionId" uuid NOT NULL, "authUserId" uuid NOT NULL, "failures"
bigint NOT NULL, "updatedAt" timestamp NOT NULL) with a unique index on
"sessionId": a new table, nothing touched. Its rows go with a code
accepted, with the person, and hourly once their session is gone.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Signed-off-by: Tizian 「ティツィアーン」 <raisondetredev@pm.me>
1008 lines
31 KiB
PL/PgSQL
1008 lines
31 KiB
PL/PgSQL
BEGIN;
|
|
|
|
--
|
|
-- Function: gen_random_uuid_v7()
|
|
-- Source: https://gist.github.com/kjmph/5bd772b2c2df145aa645b837da7eca74
|
|
-- License: MIT (copyright notice included on the generator source code).
|
|
--
|
|
create or replace function gen_random_uuid_v7()
|
|
returns uuid
|
|
as $$
|
|
begin
|
|
-- use random v4 uuid as starting point (which has the same variant we need)
|
|
-- then overlay timestamp
|
|
-- then set version 7 by flipping the 2 and 1 bit in the version 4 string
|
|
return encode(
|
|
set_bit(
|
|
set_bit(
|
|
overlay(uuid_send(gen_random_uuid())
|
|
placing substring(int8send(floor(extract(epoch from clock_timestamp()) * 1000)::bigint) from 3)
|
|
from 1 for 6
|
|
),
|
|
52, 1
|
|
),
|
|
53, 1
|
|
),
|
|
'hex')::uuid;
|
|
end
|
|
$$
|
|
language plpgsql
|
|
volatile;
|
|
|
|
--
|
|
-- Class AccountDeletionNotice as table account_deletion_notice
|
|
--
|
|
CREATE TABLE "account_deletion_notice" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"account" uuid NOT NULL,
|
|
"member" text NOT NULL,
|
|
"createdAt" timestamp without time zone NOT NULL,
|
|
"attempts" bigint NOT NULL,
|
|
"lastAttemptAt" timestamp without time zone
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "account_deletion_notice_idx" ON "account_deletion_notice" USING btree ("account", "member");
|
|
|
|
--
|
|
-- Class AccountPassword as table account_password
|
|
--
|
|
CREATE TABLE "account_password" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"authUserId" uuid NOT NULL,
|
|
"salt" bytea NOT NULL,
|
|
"kdfAlgorithm" text NOT NULL,
|
|
"kdfOpsLimit" bigint NOT NULL,
|
|
"kdfMemLimit" bigint NOT NULL,
|
|
"verifier" bytea NOT NULL,
|
|
"encryptionPublicKey" bytea NOT NULL,
|
|
"signingPublicKey" bytea NOT NULL,
|
|
"passwordKeysNonce" bytea NOT NULL,
|
|
"passwordKeysCipherText" bytea NOT NULL,
|
|
"recoveryKeysNonce" bytea NOT NULL,
|
|
"recoveryKeysCipherText" bytea NOT NULL,
|
|
"recoveryProofKey" bytea,
|
|
"createdAt" timestamp without time zone NOT NULL,
|
|
"updatedAt" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "account_password_person_idx" ON "account_password" USING btree ("authUserId");
|
|
|
|
--
|
|
-- Class AccountReset as table account_reset
|
|
--
|
|
CREATE TABLE "account_reset" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"authUserId" uuid NOT NULL,
|
|
"kind" text NOT NULL,
|
|
"takesEffectAt" timestamp without time zone NOT NULL,
|
|
"createdAt" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "account_reset_person_kind_idx" ON "account_reset" USING btree ("authUserId", "kind");
|
|
|
|
--
|
|
-- Class EmailCodeRequest as table email_code_request
|
|
--
|
|
CREATE TABLE "email_code_request" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"addressHash" text NOT NULL,
|
|
"challengeId" uuid NOT NULL,
|
|
"switchChallengeId" uuid,
|
|
"createdAt" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX "email_code_request_address_idx" ON "email_code_request" USING btree ("addressHash");
|
|
|
|
--
|
|
-- Class EmailIdentity as table email_identity
|
|
--
|
|
CREATE TABLE "email_identity" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"authUserId" uuid NOT NULL,
|
|
"addressHash" text NOT NULL,
|
|
"createdAt" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "email_identity_person_idx" ON "email_identity" USING btree ("authUserId");
|
|
CREATE UNIQUE INDEX "email_identity_address_idx" ON "email_identity" USING btree ("addressHash");
|
|
|
|
--
|
|
-- Class PublicProfile as table public_profile
|
|
--
|
|
CREATE TABLE "public_profile" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"authUserId" uuid NOT NULL,
|
|
"handle" text NOT NULL,
|
|
"handleKey" text NOT NULL,
|
|
"displayName" text NOT NULL,
|
|
"bio" text NOT NULL,
|
|
"links" json NOT NULL,
|
|
"verifiedLinks" json,
|
|
"avatarHash" text,
|
|
"firstPublishedAt" timestamp without time zone,
|
|
"updatedAt" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "public_profile_person_idx" ON "public_profile" USING btree ("authUserId");
|
|
CREATE UNIQUE INDEX "public_profile_handle_idx" ON "public_profile" USING btree ("handleKey");
|
|
|
|
--
|
|
-- Class RecoveryCode as table recovery_code
|
|
--
|
|
CREATE TABLE "recovery_code" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"authUserId" uuid NOT NULL,
|
|
"hash" text NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX "recovery_code_person_idx" ON "recovery_code" USING btree ("authUserId");
|
|
|
|
--
|
|
-- Class SecondFactorFailures as table second_factor_failures
|
|
--
|
|
CREATE TABLE "second_factor_failures" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"authUserId" uuid NOT NULL,
|
|
"failures" bigint NOT NULL,
|
|
"waits" bigint NOT NULL,
|
|
"waitUntil" timestamp without time zone,
|
|
"updatedAt" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "second_factor_failures_person_idx" ON "second_factor_failures" USING btree ("authUserId");
|
|
|
|
--
|
|
-- Class SessionCodeFailures as table session_code_failures
|
|
--
|
|
CREATE TABLE "session_code_failures" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"sessionId" uuid NOT NULL,
|
|
"authUserId" uuid NOT NULL,
|
|
"failures" bigint NOT NULL,
|
|
"updatedAt" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "session_code_failures_session_idx" ON "session_code_failures" USING btree ("sessionId");
|
|
|
|
--
|
|
-- Class SessionsEndedNotice as table sessions_ended_notice
|
|
--
|
|
CREATE TABLE "sessions_ended_notice" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"account" uuid NOT NULL,
|
|
"member" text NOT NULL,
|
|
"endedBefore" timestamp without time zone NOT NULL,
|
|
"keepSession" uuid,
|
|
"attempts" bigint NOT NULL,
|
|
"lastAttemptAt" timestamp without time zone
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "sessions_ended_notice_idx" ON "sessions_ended_notice" USING btree ("account", "member");
|
|
|
|
--
|
|
-- Class SignInChallenge as table sign_in_challenge
|
|
--
|
|
CREATE TABLE "sign_in_challenge" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"authUserId" uuid,
|
|
"purpose" text NOT NULL,
|
|
"addressHash" text NOT NULL,
|
|
"secret" text NOT NULL,
|
|
"serverValue" text,
|
|
"createdAt" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX "sign_in_challenge_created_idx" ON "sign_in_challenge" USING btree ("createdAt");
|
|
|
|
--
|
|
-- Class TotpAuthenticator as table totp_authenticator
|
|
--
|
|
CREATE TABLE "totp_authenticator" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"authUserId" uuid NOT NULL,
|
|
"name" text NOT NULL,
|
|
"secret" bytea NOT NULL,
|
|
"confirmedAt" timestamp without time zone,
|
|
"lastStep" bigint,
|
|
"createdAt" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX "totp_authenticator_person_idx" ON "totp_authenticator" USING btree ("authUserId");
|
|
|
|
--
|
|
-- Class CloudStorageEntry as table serverpod_cloud_storage
|
|
--
|
|
CREATE TABLE "serverpod_cloud_storage" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"storageId" text NOT NULL,
|
|
"path" text NOT NULL,
|
|
"addedTime" timestamp without time zone NOT NULL,
|
|
"expiration" timestamp without time zone,
|
|
"byteData" bytea NOT NULL,
|
|
"verified" boolean NOT NULL,
|
|
"contentType" text,
|
|
"cacheControl" text,
|
|
"contentDisposition" text,
|
|
"contentEncoding" text,
|
|
"customMetadata" text
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_cloud_storage_path_idx" ON "serverpod_cloud_storage" USING btree ("storageId", "path");
|
|
CREATE INDEX "serverpod_cloud_storage_expiration" ON "serverpod_cloud_storage" USING btree ("expiration");
|
|
|
|
--
|
|
-- Class CloudStorageDirectDownloadEntry as table serverpod_cloud_storage_direct_download
|
|
--
|
|
CREATE TABLE "serverpod_cloud_storage_direct_download" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"storageId" text NOT NULL,
|
|
"path" text NOT NULL,
|
|
"expiration" timestamp without time zone NOT NULL,
|
|
"authKey" text NOT NULL,
|
|
"downloadFileName" text,
|
|
"contentType" text
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_cloud_storage_direct_download_auth_key" ON "serverpod_cloud_storage_direct_download" USING btree ("authKey");
|
|
CREATE INDEX "serverpod_cloud_storage_direct_download_expiration" ON "serverpod_cloud_storage_direct_download" USING btree ("expiration");
|
|
|
|
--
|
|
-- Class CloudStorageDirectUploadEntry as table serverpod_cloud_storage_direct_upload
|
|
--
|
|
CREATE TABLE "serverpod_cloud_storage_direct_upload" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"storageId" text NOT NULL,
|
|
"path" text NOT NULL,
|
|
"expiration" timestamp without time zone NOT NULL,
|
|
"authKey" text NOT NULL,
|
|
"maxFileSize" bigint NOT NULL DEFAULT 10485760,
|
|
"contentLength" bigint,
|
|
"preventOverwrite" boolean NOT NULL DEFAULT false,
|
|
"contentType" text,
|
|
"cacheControl" text,
|
|
"contentDisposition" text,
|
|
"contentEncoding" text,
|
|
"customMetadata" text
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_cloud_storage_direct_upload_storage_path" ON "serverpod_cloud_storage_direct_upload" USING btree ("storageId", "path");
|
|
|
|
--
|
|
-- Class FutureCallEntry as table serverpod_future_call
|
|
--
|
|
CREATE TABLE "serverpod_future_call" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"name" text NOT NULL,
|
|
"time" timestamp without time zone NOT NULL,
|
|
"serializedObject" text,
|
|
"serverId" text NOT NULL,
|
|
"identifier" text,
|
|
"scheduling" json
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX "serverpod_future_call_time_idx" ON "serverpod_future_call" USING btree ("time");
|
|
CREATE INDEX "serverpod_future_call_serverId_idx" ON "serverpod_future_call" USING btree ("serverId");
|
|
CREATE INDEX "serverpod_future_call_identifier_idx" ON "serverpod_future_call" USING btree ("identifier");
|
|
|
|
--
|
|
-- Class FutureCallClaimEntry as table serverpod_future_call_claim
|
|
--
|
|
CREATE TABLE "serverpod_future_call_claim" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"futureCallId" bigint,
|
|
"lastHeartbeatTime" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "future_call_unique_idx" ON "serverpod_future_call_claim" USING btree ("futureCallId");
|
|
|
|
--
|
|
-- Class ServerHealthConnectionInfo as table serverpod_health_connection_info
|
|
--
|
|
CREATE TABLE "serverpod_health_connection_info" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"serverId" text NOT NULL,
|
|
"timestamp" timestamp without time zone NOT NULL,
|
|
"active" bigint NOT NULL,
|
|
"closing" bigint NOT NULL,
|
|
"idle" bigint NOT NULL,
|
|
"granularity" bigint NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_health_connection_info_timestamp_idx" ON "serverpod_health_connection_info" USING btree ("timestamp", "serverId", "granularity");
|
|
|
|
--
|
|
-- Class ServerHealthMetric as table serverpod_health_metric
|
|
--
|
|
CREATE TABLE "serverpod_health_metric" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"name" text NOT NULL,
|
|
"serverId" text NOT NULL,
|
|
"timestamp" timestamp without time zone NOT NULL,
|
|
"isHealthy" boolean NOT NULL,
|
|
"value" double precision NOT NULL,
|
|
"granularity" bigint NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_health_metric_timestamp_idx" ON "serverpod_health_metric" USING btree ("timestamp", "serverId", "name", "granularity");
|
|
|
|
--
|
|
-- Class LogEntry as table serverpod_log
|
|
--
|
|
CREATE TABLE "serverpod_log" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"sessionLogId" bigint NOT NULL,
|
|
"messageId" bigint,
|
|
"reference" text,
|
|
"serverId" text NOT NULL,
|
|
"time" timestamp without time zone NOT NULL,
|
|
"logLevel" bigint NOT NULL,
|
|
"message" text NOT NULL,
|
|
"error" text,
|
|
"stackTrace" text,
|
|
"order" bigint NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX "serverpod_log_sessionLogId_idx" ON "serverpod_log" USING btree ("sessionLogId", "order");
|
|
|
|
--
|
|
-- Class MessageLogEntry as table serverpod_message_log
|
|
--
|
|
CREATE TABLE "serverpod_message_log" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"sessionLogId" bigint NOT NULL,
|
|
"serverId" text NOT NULL,
|
|
"messageId" bigint NOT NULL,
|
|
"endpoint" text NOT NULL,
|
|
"messageName" text NOT NULL,
|
|
"duration" double precision NOT NULL,
|
|
"error" text,
|
|
"stackTrace" text,
|
|
"slow" boolean NOT NULL,
|
|
"order" bigint NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX "serverpod_message_log_sessionLogId_idx" ON "serverpod_message_log" USING btree ("sessionLogId", "order");
|
|
|
|
--
|
|
-- Class MethodInfo as table serverpod_method
|
|
--
|
|
CREATE TABLE "serverpod_method" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"endpoint" text NOT NULL,
|
|
"method" text NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_method_endpoint_method_idx" ON "serverpod_method" USING btree ("endpoint", "method");
|
|
|
|
--
|
|
-- Class DatabaseMigrationVersion as table serverpod_migrations
|
|
--
|
|
CREATE TABLE "serverpod_migrations" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"module" text NOT NULL,
|
|
"version" text NOT NULL,
|
|
"timestamp" timestamp without time zone
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_migrations_ids" ON "serverpod_migrations" USING btree ("module");
|
|
|
|
--
|
|
-- Class QueryLogEntry as table serverpod_query_log
|
|
--
|
|
CREATE TABLE "serverpod_query_log" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"serverId" text NOT NULL,
|
|
"sessionLogId" bigint NOT NULL,
|
|
"messageId" bigint,
|
|
"query" text NOT NULL,
|
|
"duration" double precision NOT NULL,
|
|
"numRows" bigint,
|
|
"error" text,
|
|
"stackTrace" text,
|
|
"slow" boolean NOT NULL,
|
|
"order" bigint NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX "serverpod_query_log_sessionLogId_idx" ON "serverpod_query_log" USING btree ("sessionLogId", "order");
|
|
|
|
--
|
|
-- Class ReadWriteTestEntry as table serverpod_readwrite_test
|
|
--
|
|
CREATE TABLE "serverpod_readwrite_test" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"number" bigint NOT NULL
|
|
);
|
|
|
|
--
|
|
-- Class RuntimeSettings as table serverpod_runtime_settings
|
|
--
|
|
CREATE TABLE "serverpod_runtime_settings" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"logSettings" json NOT NULL,
|
|
"logSettingsOverrides" json NOT NULL,
|
|
"logServiceCalls" boolean NOT NULL,
|
|
"logMalformedCalls" boolean NOT NULL
|
|
);
|
|
|
|
--
|
|
-- Class SessionLogEntry as table serverpod_session_log
|
|
--
|
|
CREATE TABLE "serverpod_session_log" (
|
|
"id" bigserial PRIMARY KEY,
|
|
"serverId" text NOT NULL,
|
|
"time" timestamp without time zone NOT NULL,
|
|
"module" text,
|
|
"endpoint" text,
|
|
"method" text,
|
|
"duration" double precision,
|
|
"numQueries" bigint,
|
|
"slow" boolean,
|
|
"error" text,
|
|
"stackTrace" text,
|
|
"authenticatedUserId" bigint,
|
|
"userId" text,
|
|
"isOpen" boolean,
|
|
"touched" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX "serverpod_session_log_serverid_idx" ON "serverpod_session_log" USING btree ("serverId");
|
|
CREATE INDEX "serverpod_session_log_time_idx" ON "serverpod_session_log" USING btree ("time");
|
|
CREATE INDEX "serverpod_session_log_touched_idx" ON "serverpod_session_log" USING btree ("touched");
|
|
CREATE INDEX "serverpod_session_log_isopen_idx" ON "serverpod_session_log" USING btree ("isOpen");
|
|
|
|
--
|
|
-- Class AnonymousAccount as table serverpod_auth_idp_anonymous_account
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_anonymous_account" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"authUserId" uuid NOT NULL,
|
|
"createdAt" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
--
|
|
-- Class AppleAccount as table serverpod_auth_idp_apple_account
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_apple_account" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"userIdentifier" text NOT NULL,
|
|
"refreshToken" text NOT NULL,
|
|
"refreshTokenRequestedWithBundleIdentifier" boolean NOT NULL,
|
|
"lastRefreshedAt" timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"authUserId" uuid NOT NULL,
|
|
"createdAt" timestamp without time zone NOT NULL,
|
|
"email" text,
|
|
"isEmailVerified" boolean,
|
|
"isPrivateEmail" boolean,
|
|
"firstName" text,
|
|
"lastName" text
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_auth_apple_account_identifier" ON "serverpod_auth_idp_apple_account" USING btree ("userIdentifier");
|
|
|
|
--
|
|
-- Class EmailAccount as table serverpod_auth_idp_email_account
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_email_account" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"authUserId" uuid NOT NULL,
|
|
"createdAt" timestamp without time zone NOT NULL,
|
|
"email" text NOT NULL,
|
|
"passwordHash" text NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_auth_idp_email_account_email" ON "serverpod_auth_idp_email_account" USING btree ("email");
|
|
|
|
--
|
|
-- Class EmailAccountPasswordResetRequest as table serverpod_auth_idp_email_account_password_reset_request
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_email_account_password_reset_request" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"emailAccountId" uuid NOT NULL,
|
|
"createdAt" timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"challengeId" uuid NOT NULL,
|
|
"setPasswordChallengeId" uuid
|
|
);
|
|
|
|
--
|
|
-- Class EmailAccountRequest as table serverpod_auth_idp_email_account_request
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_email_account_request" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"createdAt" timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"email" text NOT NULL,
|
|
"challengeId" uuid NOT NULL,
|
|
"createAccountChallengeId" uuid
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_auth_idp_email_account_request_email" ON "serverpod_auth_idp_email_account_request" USING btree ("email");
|
|
|
|
--
|
|
-- Class FacebookAccount as table serverpod_auth_idp_facebook_account
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_facebook_account" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"authUserId" uuid NOT NULL,
|
|
"createdAt" timestamp without time zone NOT NULL,
|
|
"userIdentifier" text NOT NULL,
|
|
"email" text,
|
|
"fullName" text,
|
|
"firstName" text,
|
|
"lastName" text
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_auth_facebook_account_user_identifier" ON "serverpod_auth_idp_facebook_account" USING btree ("userIdentifier");
|
|
|
|
--
|
|
-- Class FirebaseAccount as table serverpod_auth_idp_firebase_account
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_firebase_account" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"authUserId" uuid NOT NULL,
|
|
"created" timestamp without time zone NOT NULL,
|
|
"email" text,
|
|
"phone" text,
|
|
"userIdentifier" text NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_auth_firebase_account_user_identifier" ON "serverpod_auth_idp_firebase_account" USING btree ("userIdentifier");
|
|
|
|
--
|
|
-- Class GitHubAccount as table serverpod_auth_idp_github_account
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_github_account" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"authUserId" uuid NOT NULL,
|
|
"userIdentifier" text NOT NULL,
|
|
"email" text,
|
|
"created" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_auth_github_account_user_identifier" ON "serverpod_auth_idp_github_account" USING btree ("userIdentifier");
|
|
|
|
--
|
|
-- Class GoogleAccount as table serverpod_auth_idp_google_account
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_google_account" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"authUserId" uuid NOT NULL,
|
|
"created" timestamp without time zone NOT NULL,
|
|
"email" text NOT NULL,
|
|
"userIdentifier" text NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_auth_google_account_user_identifier" ON "serverpod_auth_idp_google_account" USING btree ("userIdentifier");
|
|
|
|
--
|
|
-- Class MicrosoftAccount as table serverpod_auth_idp_microsoft_account
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_microsoft_account" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"authUserId" uuid NOT NULL,
|
|
"userIdentifier" text NOT NULL,
|
|
"email" text,
|
|
"created" timestamp without time zone NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_auth_microsoft_account_user_identifier" ON "serverpod_auth_idp_microsoft_account" USING btree ("userIdentifier");
|
|
|
|
--
|
|
-- Class PasskeyAccount as table serverpod_auth_idp_passkey_account
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_passkey_account" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"authUserId" uuid NOT NULL,
|
|
"createdAt" timestamp without time zone NOT NULL,
|
|
"keyId" bytea NOT NULL,
|
|
"keyIdBase64" text NOT NULL,
|
|
"clientDataJSON" bytea NOT NULL,
|
|
"attestationObject" bytea NOT NULL,
|
|
"originalChallenge" bytea NOT NULL
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_auth_idp_passkey_account_key_id_base64" ON "serverpod_auth_idp_passkey_account" USING btree ("keyIdBase64");
|
|
|
|
--
|
|
-- Class PasskeyChallenge as table serverpod_auth_idp_passkey_challenge
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_passkey_challenge" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"createdAt" timestamp without time zone NOT NULL,
|
|
"challenge" bytea NOT NULL
|
|
);
|
|
|
|
--
|
|
-- Class RateLimitedRequestAttempt as table serverpod_auth_idp_rate_limited_request_attempt
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_rate_limited_request_attempt" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"domain" text NOT NULL,
|
|
"source" text NOT NULL,
|
|
"key" text NOT NULL,
|
|
"ipAddress" text,
|
|
"attemptedAt" timestamp without time zone NOT NULL,
|
|
"extraData" json
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX "serverpod_auth_idp_rate_limited_request_attempt_composite" ON "serverpod_auth_idp_rate_limited_request_attempt" USING btree ("domain", "source", "key", "attemptedAt");
|
|
|
|
--
|
|
-- Class SecretChallenge as table serverpod_auth_idp_secret_challenge
|
|
--
|
|
CREATE TABLE "serverpod_auth_idp_secret_challenge" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"challengeCodeHash" text NOT NULL
|
|
);
|
|
|
|
--
|
|
-- Class RefreshToken as table serverpod_auth_core_jwt_refresh_token
|
|
--
|
|
CREATE TABLE "serverpod_auth_core_jwt_refresh_token" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"authUserId" uuid NOT NULL,
|
|
"scopeNames" json NOT NULL,
|
|
"extraClaims" text,
|
|
"method" text NOT NULL,
|
|
"fixedSecret" bytea NOT NULL,
|
|
"rotatingSecretHash" text NOT NULL,
|
|
"lastUpdatedAt" timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"createdAt" timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX "serverpod_auth_core_jwt_refresh_token_last_updated_at" ON "serverpod_auth_core_jwt_refresh_token" USING btree ("lastUpdatedAt");
|
|
|
|
--
|
|
-- Class UserProfile as table serverpod_auth_core_profile
|
|
--
|
|
CREATE TABLE "serverpod_auth_core_profile" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"authUserId" uuid NOT NULL,
|
|
"userName" text,
|
|
"fullName" text,
|
|
"email" text,
|
|
"createdAt" timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"imageId" uuid
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE UNIQUE INDEX "serverpod_auth_profile_user_profile_email_auth_user_id" ON "serverpod_auth_core_profile" USING btree ("authUserId");
|
|
|
|
--
|
|
-- Class UserProfileImage as table serverpod_auth_core_profile_image
|
|
--
|
|
CREATE TABLE "serverpod_auth_core_profile_image" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"userProfileId" uuid NOT NULL,
|
|
"createdAt" timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"storageId" text NOT NULL,
|
|
"path" text NOT NULL,
|
|
"url" text NOT NULL
|
|
);
|
|
|
|
--
|
|
-- Class ServerSideSession as table serverpod_auth_core_session
|
|
--
|
|
CREATE TABLE "serverpod_auth_core_session" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"authUserId" uuid NOT NULL,
|
|
"scopeNames" json NOT NULL,
|
|
"createdAt" timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"lastUsedAt" timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"expiresAt" timestamp without time zone,
|
|
"expireAfterUnusedFor" bigint,
|
|
"sessionKeyHash" bytea NOT NULL,
|
|
"sessionKeySalt" bytea NOT NULL,
|
|
"method" text NOT NULL
|
|
);
|
|
|
|
--
|
|
-- Class AuthUser as table serverpod_auth_core_user
|
|
--
|
|
CREATE TABLE "serverpod_auth_core_user" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid_v7(),
|
|
"createdAt" timestamp without time zone NOT NULL,
|
|
"scopeNames" json NOT NULL,
|
|
"blocked" boolean NOT NULL
|
|
);
|
|
|
|
--
|
|
-- Foreign relations for "email_code_request" table
|
|
--
|
|
ALTER TABLE ONLY "email_code_request"
|
|
ADD CONSTRAINT "email_code_request_fk_0"
|
|
FOREIGN KEY("challengeId")
|
|
REFERENCES "serverpod_auth_idp_secret_challenge"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
ALTER TABLE ONLY "email_code_request"
|
|
ADD CONSTRAINT "email_code_request_fk_1"
|
|
FOREIGN KEY("switchChallengeId")
|
|
REFERENCES "serverpod_auth_idp_secret_challenge"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_future_call_claim" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_future_call_claim"
|
|
ADD CONSTRAINT "serverpod_future_call_claim_fk_0"
|
|
FOREIGN KEY("futureCallId")
|
|
REFERENCES "serverpod_future_call"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_log" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_log"
|
|
ADD CONSTRAINT "serverpod_log_fk_0"
|
|
FOREIGN KEY("sessionLogId")
|
|
REFERENCES "serverpod_session_log"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_message_log" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_message_log"
|
|
ADD CONSTRAINT "serverpod_message_log_fk_0"
|
|
FOREIGN KEY("sessionLogId")
|
|
REFERENCES "serverpod_session_log"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_query_log" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_query_log"
|
|
ADD CONSTRAINT "serverpod_query_log_fk_0"
|
|
FOREIGN KEY("sessionLogId")
|
|
REFERENCES "serverpod_session_log"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_idp_anonymous_account" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_idp_anonymous_account"
|
|
ADD CONSTRAINT "serverpod_auth_idp_anonymous_account_fk_0"
|
|
FOREIGN KEY("authUserId")
|
|
REFERENCES "serverpod_auth_core_user"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_idp_apple_account" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_idp_apple_account"
|
|
ADD CONSTRAINT "serverpod_auth_idp_apple_account_fk_0"
|
|
FOREIGN KEY("authUserId")
|
|
REFERENCES "serverpod_auth_core_user"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_idp_email_account" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_idp_email_account"
|
|
ADD CONSTRAINT "serverpod_auth_idp_email_account_fk_0"
|
|
FOREIGN KEY("authUserId")
|
|
REFERENCES "serverpod_auth_core_user"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_idp_email_account_password_reset_request" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_idp_email_account_password_reset_request"
|
|
ADD CONSTRAINT "serverpod_auth_idp_email_account_password_reset_request_fk_0"
|
|
FOREIGN KEY("emailAccountId")
|
|
REFERENCES "serverpod_auth_idp_email_account"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
ALTER TABLE ONLY "serverpod_auth_idp_email_account_password_reset_request"
|
|
ADD CONSTRAINT "serverpod_auth_idp_email_account_password_reset_request_fk_1"
|
|
FOREIGN KEY("challengeId")
|
|
REFERENCES "serverpod_auth_idp_secret_challenge"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
ALTER TABLE ONLY "serverpod_auth_idp_email_account_password_reset_request"
|
|
ADD CONSTRAINT "serverpod_auth_idp_email_account_password_reset_request_fk_2"
|
|
FOREIGN KEY("setPasswordChallengeId")
|
|
REFERENCES "serverpod_auth_idp_secret_challenge"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_idp_email_account_request" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_idp_email_account_request"
|
|
ADD CONSTRAINT "serverpod_auth_idp_email_account_request_fk_0"
|
|
FOREIGN KEY("challengeId")
|
|
REFERENCES "serverpod_auth_idp_secret_challenge"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
ALTER TABLE ONLY "serverpod_auth_idp_email_account_request"
|
|
ADD CONSTRAINT "serverpod_auth_idp_email_account_request_fk_1"
|
|
FOREIGN KEY("createAccountChallengeId")
|
|
REFERENCES "serverpod_auth_idp_secret_challenge"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_idp_facebook_account" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_idp_facebook_account"
|
|
ADD CONSTRAINT "serverpod_auth_idp_facebook_account_fk_0"
|
|
FOREIGN KEY("authUserId")
|
|
REFERENCES "serverpod_auth_core_user"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_idp_firebase_account" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_idp_firebase_account"
|
|
ADD CONSTRAINT "serverpod_auth_idp_firebase_account_fk_0"
|
|
FOREIGN KEY("authUserId")
|
|
REFERENCES "serverpod_auth_core_user"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_idp_github_account" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_idp_github_account"
|
|
ADD CONSTRAINT "serverpod_auth_idp_github_account_fk_0"
|
|
FOREIGN KEY("authUserId")
|
|
REFERENCES "serverpod_auth_core_user"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_idp_google_account" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_idp_google_account"
|
|
ADD CONSTRAINT "serverpod_auth_idp_google_account_fk_0"
|
|
FOREIGN KEY("authUserId")
|
|
REFERENCES "serverpod_auth_core_user"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_idp_microsoft_account" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_idp_microsoft_account"
|
|
ADD CONSTRAINT "serverpod_auth_idp_microsoft_account_fk_0"
|
|
FOREIGN KEY("authUserId")
|
|
REFERENCES "serverpod_auth_core_user"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_idp_passkey_account" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_idp_passkey_account"
|
|
ADD CONSTRAINT "serverpod_auth_idp_passkey_account_fk_0"
|
|
FOREIGN KEY("authUserId")
|
|
REFERENCES "serverpod_auth_core_user"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_core_jwt_refresh_token" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_core_jwt_refresh_token"
|
|
ADD CONSTRAINT "serverpod_auth_core_jwt_refresh_token_fk_0"
|
|
FOREIGN KEY("authUserId")
|
|
REFERENCES "serverpod_auth_core_user"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_core_profile" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_core_profile"
|
|
ADD CONSTRAINT "serverpod_auth_core_profile_fk_0"
|
|
FOREIGN KEY("authUserId")
|
|
REFERENCES "serverpod_auth_core_user"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
ALTER TABLE ONLY "serverpod_auth_core_profile"
|
|
ADD CONSTRAINT "serverpod_auth_core_profile_fk_1"
|
|
FOREIGN KEY("imageId")
|
|
REFERENCES "serverpod_auth_core_profile_image"("id")
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION
|
|
DEFERRABLE INITIALLY DEFERRED;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_core_profile_image" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_core_profile_image"
|
|
ADD CONSTRAINT "serverpod_auth_core_profile_image_fk_0"
|
|
FOREIGN KEY("userProfileId")
|
|
REFERENCES "serverpod_auth_core_profile"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
--
|
|
-- Foreign relations for "serverpod_auth_core_session" table
|
|
--
|
|
ALTER TABLE ONLY "serverpod_auth_core_session"
|
|
ADD CONSTRAINT "serverpod_auth_core_session_fk_0"
|
|
FOREIGN KEY("authUserId")
|
|
REFERENCES "serverpod_auth_core_user"("id")
|
|
ON DELETE CASCADE
|
|
ON UPDATE NO ACTION;
|
|
|
|
|
|
--
|
|
-- MIGRATION VERSION FOR halkyon_base
|
|
--
|
|
INSERT INTO "serverpod_migrations" ("module", "version", "timestamp")
|
|
VALUES ('halkyon_base', '20261020204618337-session-code-tries', now())
|
|
ON CONFLICT ("module")
|
|
DO UPDATE SET "version" = '20261020204618337-session-code-tries', "timestamp" = now();
|
|
|
|
--
|
|
-- MIGRATION VERSION FOR serverpod
|
|
--
|
|
INSERT INTO "serverpod_migrations" ("module", "version", "timestamp")
|
|
VALUES ('serverpod', '20260824182259319', now())
|
|
ON CONFLICT ("module")
|
|
DO UPDATE SET "version" = '20260824182259319', "timestamp" = now();
|
|
|
|
--
|
|
-- MIGRATION VERSION FOR serverpod_auth_idp
|
|
--
|
|
INSERT INTO "serverpod_migrations" ("module", "version", "timestamp")
|
|
VALUES ('serverpod_auth_idp', '20260924105404509', now())
|
|
ON CONFLICT ("module")
|
|
DO UPDATE SET "version" = '20260924105404509', "timestamp" = now();
|
|
|
|
--
|
|
-- MIGRATION VERSION FOR serverpod_auth_core
|
|
--
|
|
INSERT INTO "serverpod_migrations" ("module", "version", "timestamp")
|
|
VALUES ('serverpod_auth_core', '20260924105232991', now())
|
|
ON CONFLICT ("module")
|
|
DO UPDATE SET "version" = '20260924105232991', "timestamp" = now();
|
|
|
|
|
|
COMMIT;
|