Mobile support to come.
..20260117042027_create_users.down.sql20260117042027_create_users.up.sql20260117043157_create_organizations.down.sql20260117043157_create_organizations.up.sql20260118222626_create_repositories.down.sql20260118222626_create_repositories.up.sql20260125182548_create_questions.down.sql20260125182548_create_questions.up.sql20260126215900_create_votes.down.sql20260126215900_create_votes.up.sql20260201050312_create_oauth.down.sql20260201050312_create_oauth.up.sql20260206190315_create_commits.down.sql20260206190315_create_commits.up.sql20260207120000_disable_auth_user_trigger.down.sql20260207120000_disable_auth_user_trigger.up.sql
-- Device authorization status enum
CREATE TYPE device_authorization_status AS ENUM ('pending', 'authorized', 'expired');
-- Device authorization requests (short-lived, for OAuth device flow)
CREATE TABLE device_authorizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
device_code VARCHAR(64) NOT NULL UNIQUE,
user_code VARCHAR(16) NOT NULL UNIQUE,
client_id VARCHAR(64) NOT NULL,
user_id UUID REFERENCES users(id),
status device_authorization_status NOT NULL DEFAULT 'pending',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
expires_at TIMESTAMPTZ NOT NULL
);
CREATE INDEX idx_device_authorizations_device_code ON device_authorizations(device_code);
CREATE INDEX idx_device_authorizations_user_code ON device_authorizations(user_code);
-- Long-lived access tokens for CLI/git
CREATE TABLE access_tokens (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id),
client_id VARCHAR(64) NOT NULL,
token_hash VARCHAR(64) NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
last_used_at TIMESTAMPTZ
);
CREATE INDEX idx_access_tokens_user_id ON access_tokens(user_id);
CREATE INDEX idx_access_tokens_token_hash ON access_tokens(token_hash);