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.
137 lines
4.7 KiB
SQL
137 lines
4.7 KiB
SQL
-- Seçimler
|
||
CREATE TYPE election_type AS ENUM (
|
||
'genel', 'yerel', 'cumhurbaskanligi', 'referandum'
|
||
);
|
||
|
||
CREATE TABLE elections (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
held_at DATE NOT NULL,
|
||
type election_type NOT NULL
|
||
);
|
||
|
||
-- Görevdeki yetkililer
|
||
CREATE TABLE officials (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
person_id BIGINT NOT NULL REFERENCES persons(id) ON DELETE RESTRICT,
|
||
unit_id BIGINT NOT NULL REFERENCES administrative_units(id),
|
||
title TEXT NOT NULL,
|
||
started_at DATE NOT NULL,
|
||
ended_at DATE,
|
||
election_id BIGINT REFERENCES elections(id) ON DELETE SET NULL
|
||
);
|
||
|
||
CREATE INDEX idx_officials_person ON officials(person_id);
|
||
CREATE INDEX idx_officials_unit ON officials(unit_id);
|
||
|
||
-- Yetkili karnesi (periyodik hesaplanan)
|
||
CREATE TABLE official_stats (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
official_id BIGINT NOT NULL REFERENCES officials(id) ON DELETE CASCADE,
|
||
period_start DATE NOT NULL,
|
||
period_end DATE,
|
||
open_at_start INT NOT NULL DEFAULT 0,
|
||
new_during INT NOT NULL DEFAULT 0,
|
||
resolved INT NOT NULL DEFAULT 0,
|
||
rejected INT NOT NULL DEFAULT 0,
|
||
score NUMERIC(5, 2),
|
||
computed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
UNIQUE (official_id, period_start)
|
||
);
|
||
|
||
CREATE INDEX idx_official_stats_official ON official_stats(official_id);
|
||
|
||
-- Sorumluluk kural tablosu (AI + moderatör için)
|
||
CREATE TABLE issue_category_unit_rules (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
category_id BIGINT NOT NULL REFERENCES issue_categories(id) ON DELETE CASCADE,
|
||
unit_type_id BIGINT NOT NULL REFERENCES administrative_unit_types(id) ON DELETE CASCADE,
|
||
location_type location_type NOT NULL,
|
||
confidence SMALLINT NOT NULL DEFAULT 80 CHECK (confidence BETWEEN 0 AND 100),
|
||
legal_basis TEXT,
|
||
notes TEXT,
|
||
UNIQUE (category_id, unit_type_id, location_type)
|
||
);
|
||
|
||
-- Mevzuat (RAG için)
|
||
CREATE TABLE legislation (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
title TEXT NOT NULL,
|
||
number TEXT,
|
||
published_at DATE,
|
||
body TEXT NOT NULL,
|
||
embedding_done BOOLEAN NOT NULL DEFAULT FALSE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
-- Rapor türleri
|
||
CREATE TABLE report_types (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
slug TEXT NOT NULL UNIQUE,
|
||
description TEXT,
|
||
is_active BOOLEAN NOT NULL DEFAULT TRUE
|
||
);
|
||
|
||
-- Rapor türü ↔ veri kaynağı
|
||
CREATE TABLE report_data_sources (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
name TEXT NOT NULL UNIQUE, -- "issue", "vote", "official_stat"
|
||
unit_price NUMERIC(10, 6) NOT NULL,
|
||
description TEXT
|
||
);
|
||
|
||
CREATE TABLE report_type_sources (
|
||
report_type_id BIGINT NOT NULL REFERENCES report_types(id) ON DELETE CASCADE,
|
||
data_source_id BIGINT NOT NULL REFERENCES report_data_sources(id) ON DELETE CASCADE,
|
||
is_required BOOLEAN NOT NULL DEFAULT TRUE,
|
||
PRIMARY KEY (report_type_id, data_source_id)
|
||
);
|
||
|
||
-- Rapor siparişleri
|
||
CREATE TYPE report_order_status AS ENUM (
|
||
'draft', 'pending_payment', 'processing', 'ready', 'failed', 'expired'
|
||
);
|
||
|
||
CREATE TABLE report_orders (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL REFERENCES users(id),
|
||
report_type_id BIGINT NOT NULL REFERENCES report_types(id),
|
||
parameters JSONB NOT NULL DEFAULT '{}',
|
||
row_counts JSONB NOT NULL DEFAULT '{}',
|
||
calculated_price NUMERIC(10, 2) NOT NULL DEFAULT 0,
|
||
status report_order_status NOT NULL DEFAULT 'draft',
|
||
paid_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX idx_report_orders_user ON report_orders(user_id);
|
||
CREATE INDEX idx_report_orders_status ON report_orders(status);
|
||
|
||
-- Üretilen rapor dosyaları
|
||
CREATE TABLE report_outputs (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
order_id BIGINT NOT NULL UNIQUE REFERENCES report_orders(id) ON DELETE CASCADE,
|
||
file_path TEXT NOT NULL,
|
||
file_size BIGINT NOT NULL DEFAULT 0,
|
||
expires_at TIMESTAMPTZ NOT NULL,
|
||
download_count SMALLINT NOT NULL DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
-- Ödemeler
|
||
CREATE TYPE payment_status AS ENUM ('pending', 'success', 'failed', 'refunded');
|
||
|
||
CREATE TABLE payments (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
order_id BIGINT NOT NULL REFERENCES report_orders(id),
|
||
user_id BIGINT NOT NULL REFERENCES users(id),
|
||
amount NUMERIC(10, 2) NOT NULL,
|
||
provider TEXT NOT NULL,
|
||
provider_ref TEXT,
|
||
status payment_status NOT NULL DEFAULT 'pending',
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX idx_payments_order ON payments(order_id);
|
||
CREATE INDEX idx_payments_user ON payments(user_id);
|