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.
102 lines
3.5 KiB
SQL
102 lines
3.5 KiB
SQL
-- KYC durumu
|
||
CREATE TYPE kyc_status AS ENUM ('none', 'pending', 'verified', 'rejected');
|
||
|
||
-- Kullanıcılar
|
||
CREATE TABLE users (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
email TEXT NOT NULL UNIQUE,
|
||
password_hash TEXT NOT NULL,
|
||
kyc_status kyc_status NOT NULL DEFAULT 'none',
|
||
kyc_verified_at TIMESTAMPTZ,
|
||
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
last_login_at TIMESTAMPTZ
|
||
);
|
||
|
||
-- Kişi profilleri (sahipsiz olabilir)
|
||
CREATE TABLE persons (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
first_name TEXT NOT NULL,
|
||
last_name TEXT NOT NULL,
|
||
birth_year SMALLINT,
|
||
user_id BIGINT UNIQUE REFERENCES users(id) ON DELETE SET NULL,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX idx_persons_user ON persons(user_id);
|
||
|
||
-- Organizasyon türleri
|
||
CREATE TYPE organization_type AS ENUM (
|
||
'ngo', 'siyasi_parti', 'medya', 'sirket', 'resmi_kurum', 'diger'
|
||
);
|
||
|
||
-- Organizasyon profilleri
|
||
CREATE TABLE organizations (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
type organization_type NOT NULL DEFAULT 'diger',
|
||
parent_id BIGINT REFERENCES organizations(id) ON DELETE RESTRICT,
|
||
user_id BIGINT UNIQUE REFERENCES users(id) ON DELETE SET NULL,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX idx_organizations_parent ON organizations(parent_id);
|
||
CREATE INDEX idx_organizations_user ON organizations(user_id);
|
||
|
||
-- KYC doğrulama talepleri
|
||
CREATE TYPE verification_status AS ENUM ('pending', 'approved', 'rejected');
|
||
|
||
CREATE TABLE verification_requests (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
id_front_path TEXT NOT NULL,
|
||
id_back_path TEXT NOT NULL,
|
||
selfie_path TEXT NOT NULL,
|
||
status verification_status NOT NULL DEFAULT 'pending',
|
||
reviewed_by BIGINT REFERENCES users(id) ON DELETE SET NULL,
|
||
review_note TEXT,
|
||
submitted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
reviewed_at TIMESTAMPTZ
|
||
);
|
||
|
||
CREATE INDEX idx_verification_user ON verification_requests(user_id);
|
||
CREATE INDEX idx_verification_status ON verification_requests(status);
|
||
|
||
-- İzinler
|
||
CREATE TABLE permissions (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
key TEXT NOT NULL UNIQUE, -- "issue.create", "moderation.assign" vb.
|
||
description TEXT
|
||
);
|
||
|
||
-- Yetki grupları
|
||
CREATE TABLE permission_groups (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
description TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
-- Grup ↔ izin
|
||
CREATE TABLE group_permissions (
|
||
group_id BIGINT NOT NULL REFERENCES permission_groups(id) ON DELETE CASCADE,
|
||
permission_id BIGINT NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (group_id, permission_id)
|
||
);
|
||
|
||
-- Kullanıcı ↔ grup
|
||
CREATE TABLE user_groups (
|
||
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
group_id BIGINT NOT NULL REFERENCES permission_groups(id) ON DELETE CASCADE,
|
||
granted_by BIGINT REFERENCES users(id) ON DELETE SET NULL,
|
||
granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
PRIMARY KEY (user_id, group_id)
|
||
);
|
||
|
||
CREATE INDEX idx_user_groups_user ON user_groups(user_id);
|
||
CREATE INDEX idx_user_groups_group ON user_groups(group_id);
|
||
|
||
-- location_history.changed_by FK'sini şimdi ekle
|
||
ALTER TABLE location_history
|
||
ADD CONSTRAINT fk_location_history_user
|
||
FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL;
|