68 lines
3.1 KiB
SQL
68 lines
3.1 KiB
SQL
-- =====================================================================
|
|
-- iam service — 0001_init.sql
|
|
-- Database-per-service: this is the ONLY schema that owns `users`,
|
|
-- `sessions`, `email_verification_codes` and `password_reset_tokens`.
|
|
-- No other service's migration may create a table with these names or
|
|
-- reach into this database. Transplanted verbatim from the monolith's
|
|
-- schema.sql section of the same name; see ARCHITECTURE.md's
|
|
-- "Database-per-service" note for why the FKs below stay *within* this
|
|
-- file only — every other service refers to a user by a bare
|
|
-- `user_id BIGINT`, not a foreign key, since Postgres can't enforce a
|
|
-- constraint across two separate databases.
|
|
-- =====================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
email_verified BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Email verification codes: a short numeric code (not a link) mailed to
|
|
-- the address at signup. `attempts` caps how many wrong codes a row
|
|
-- will accept before it's dead (see internal/application.VerifyEmail);
|
|
-- code_hash is deliberately NOT unique — two different users' current
|
|
-- codes can coincidentally collide, which is expected and harmless
|
|
-- since every lookup is scoped by user_id, never by code_hash alone.
|
|
CREATE TABLE IF NOT EXISTS email_verification_codes (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
code_hash TEXT NOT NULL,
|
|
attempts INT NOT NULL DEFAULT 0,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
used_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS email_verification_codes_user_id_idx
|
|
ON email_verification_codes (user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS sessions_user_id_idx ON sessions (user_id);
|
|
CREATE INDEX IF NOT EXISTS sessions_expires_at_idx ON sessions (expires_at);
|
|
|
|
-- "Forgot password" tokens — same hash-only-at-rest pattern as
|
|
-- email_verification_codes. A successful reset also deletes every row
|
|
-- in `sessions` for that user, so a stolen session cookie doesn't
|
|
-- survive a reset (see application.PasswordResetRepo.
|
|
-- ConsumeAndResetPassword).
|
|
CREATE TABLE IF NOT EXISTS password_reset_tokens (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
used_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS password_reset_tokens_user_id_idx
|
|
ON password_reset_tokens (user_id);
|