52 lines
2.0 KiB
SQL
52 lines
2.0 KiB
SQL
CREATE TABLE IF NOT EXISTS app.auth_user (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR NOT NULL,
|
|
email VARCHAR NOT NULL UNIQUE,
|
|
email_verified BOOLEAN NOT NULL DEFAULT FALSE,
|
|
image VARCHAR,
|
|
role VARCHAR NOT NULL DEFAULT 'owner',
|
|
type VARCHAR NOT NULL DEFAULT 'candidate',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS app.auth_session (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
token VARCHAR NOT NULL UNIQUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
ip_address VARCHAR,
|
|
user_agent VARCHAR,
|
|
user_id UUID NOT NULL REFERENCES app.auth_user(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS app.auth_account (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
account_id VARCHAR NOT NULL,
|
|
provider_id VARCHAR NOT NULL,
|
|
user_id UUID NOT NULL REFERENCES app.auth_user(id) ON DELETE CASCADE,
|
|
access_token VARCHAR,
|
|
refresh_token VARCHAR,
|
|
id_token VARCHAR,
|
|
access_token_expires_at TIMESTAMPTZ,
|
|
refresh_token_expires_at TIMESTAMPTZ,
|
|
scope VARCHAR,
|
|
password VARCHAR,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS app.auth_verification (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
identifier VARCHAR NOT NULL,
|
|
value VARCHAR NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_auth_session_user_id ON app.auth_session(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_auth_account_user_id ON app.auth_account(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_auth_verification_identifier ON app.auth_verification(identifier);
|