-- identity: squashed from 0300_identity.sql, 1900_retired_account_names.sql.
CREATE TABLE recovery_codes (
user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
code_hash TEXT NOT NULL,
used_at INTEGER,
PRIMARY KEY (user_id, code_hash)
);
CREATE TABLE passkeys (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
name TEXT NOT NULL,
public_key TEXT NOT NULL,
algorithm INTEGER NOT NULL,
sign_count INTEGER NOT NULL DEFAULT 0,
transports TEXT NOT NULL DEFAULT '[]',
last_used_at INTEGER,
created_at INTEGER NOT NULL
);
CREATE INDEX passkeys_user ON passkeys(user_id);
CREATE TABLE access_token_repos (
token_id TEXT NOT NULL REFERENCES access_tokens(id) ON DELETE CASCADE,
repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
PRIMARY KEY (token_id, repo_id)
);
CREATE INDEX access_token_repos_repo ON access_token_repos(repo_id);
CREATE TABLE deploy_tokens (
id TEXT PRIMARY KEY,
repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
name TEXT NOT NULL,
token_hash TEXT NOT NULL UNIQUE,
token_prefix TEXT NOT NULL,
permission TEXT NOT NULL CHECK (permission IN ('read', 'write')),
created_by TEXT REFERENCES accounts(id) ON DELETE SET NULL,
last_used_at INTEGER,