Mukan Erkin
2498e75594
FastAPI + PostgreSQL 16. KYC, issue sistemi, permission/group yönetimi, session yönetimi, API client auth (kışla kapısı), officials/persons CRUD. Migration 0001–0013 dahil.
38 lines
1.6 KiB
SQL
38 lines
1.6 KiB
SQL
CREATE TABLE user_devices (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
device_name TEXT, -- "iPhone 15", "Chrome/Windows" vb.
|
||
user_agent TEXT,
|
||
last_ip INET,
|
||
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX idx_user_devices_user ON user_devices(user_id);
|
||
|
||
CREATE TABLE access_tokens (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
device_id BIGINT REFERENCES user_devices(id) ON DELETE CASCADE,
|
||
token_hash TEXT NOT NULL UNIQUE, -- SHA-256(token), düz token istemcide
|
||
expires_at TIMESTAMPTZ NOT NULL,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX idx_access_tokens_hash ON access_tokens(token_hash);
|
||
CREATE INDEX idx_access_tokens_user ON access_tokens(user_id);
|
||
CREATE INDEX idx_access_tokens_expires ON access_tokens(expires_at);
|
||
|
||
CREATE TABLE refresh_tokens (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
device_id BIGINT REFERENCES user_devices(id) ON DELETE CASCADE,
|
||
token_hash TEXT NOT NULL UNIQUE,
|
||
used_at TIMESTAMPTZ, -- NULL = henüz kullanılmadı
|
||
revoked BOOLEAN NOT NULL DEFAULT FALSE,
|
||
expires_at TIMESTAMPTZ NOT NULL,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX idx_refresh_tokens_hash ON refresh_tokens(token_hash);
|
||
CREATE INDEX idx_refresh_tokens_user ON refresh_tokens(user_id);
|