max / makenotwork

612 B · 16 lines History Blame Raw

1	-- Passkey / WebAuthn credentials for passwordless login.
2	-- Each row stores one registered passkey per user.
3
4	CREATE TABLE user_passkeys (
5	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
6	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
7	name VARCHAR(100) NOT NULL DEFAULT 'Passkey',
8	credential_json JSONB NOT NULL,
9	credential_id BYTEA NOT NULL,
10	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
11	last_used_at TIMESTAMPTZ
12	);
13
14	CREATE INDEX idx_user_passkeys_user_id ON user_passkeys(user_id);
15	CREATE UNIQUE INDEX idx_user_passkeys_credential_id ON user_passkeys(credential_id);
16