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.
65 lines
2.3 KiB
SQL
65 lines
2.3 KiB
SQL
-- Lokasyon hiyerarşisi
|
|
CREATE TYPE location_type AS ENUM (
|
|
'ulke', 'bolge', 'il', 'ilce', 'bucak', 'belde', 'koy', 'mahalle', 'diger'
|
|
);
|
|
|
|
CREATE TABLE locations (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
parent_id BIGINT REFERENCES locations(id) ON DELETE RESTRICT,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
type location_type NOT NULL,
|
|
latitude NUMERIC(10, 7),
|
|
longitude NUMERIC(10, 7),
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_locations_parent ON locations(parent_id);
|
|
CREATE INDEX idx_locations_type ON locations(type);
|
|
|
|
-- Lokasyon değişiklik geçmişi
|
|
CREATE TABLE location_history (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
location_id BIGINT NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
|
|
snapshot JSONB NOT NULL,
|
|
change_reason TEXT,
|
|
changed_by BIGINT, -- users tablosu kurulunca FK eklenecek
|
|
changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_location_history_loc ON location_history(location_id);
|
|
|
|
-- İdari birim türleri
|
|
CREATE TABLE administrative_unit_types (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
description TEXT
|
|
);
|
|
|
|
-- İdari birimler
|
|
CREATE TABLE administrative_units (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
type_id BIGINT NOT NULL REFERENCES administrative_unit_types(id),
|
|
name TEXT NOT NULL,
|
|
established_at DATE,
|
|
abolished_at DATE,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE
|
|
);
|
|
|
|
CREATE INDEX idx_admin_units_type ON administrative_units(type_id);
|
|
|
|
-- Lokasyon ↔ idari birim eşlemesi
|
|
CREATE TABLE location_administrative_units (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
location_id BIGINT NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
|
|
unit_id BIGINT NOT NULL REFERENCES administrative_units(id) ON DELETE CASCADE,
|
|
valid_from DATE NOT NULL,
|
|
valid_until DATE,
|
|
UNIQUE (location_id, unit_id, valid_from)
|
|
);
|
|
|
|
CREATE INDEX idx_loc_admin_units_loc ON location_administrative_units(location_id);
|
|
CREATE INDEX idx_loc_admin_units_unit ON location_administrative_units(unit_id);
|