Roman Chesnokov
90 lines
2.4 KiB
SQL
90 lines
2.4 KiB
SQL
-- 008_raw_schemas.sql — JSONB-снимки источников
|
|
|
|
-- BIT.RA raw
|
|
CREATE TABLE raw_bitra.employees (
|
|
bitra_id text PRIMARY KEY,
|
|
payload jsonb NOT NULL,
|
|
synced_at timestamptz DEFAULT now()
|
|
);
|
|
CREATE TABLE raw_bitra.works (
|
|
bitra_doc_id text PRIMARY KEY,
|
|
payload jsonb NOT NULL,
|
|
synced_at timestamptz DEFAULT now()
|
|
);
|
|
CREATE TABLE raw_bitra.projects (
|
|
bitra_id text PRIMARY KEY,
|
|
payload jsonb NOT NULL,
|
|
synced_at timestamptz DEFAULT now()
|
|
);
|
|
CREATE TABLE raw_bitra.dictionaries (
|
|
kind text NOT NULL,
|
|
bitra_id text NOT NULL,
|
|
payload jsonb NOT NULL,
|
|
synced_at timestamptz DEFAULT now(),
|
|
PRIMARY KEY (kind, bitra_id)
|
|
);
|
|
CREATE TABLE raw_bitra.work_types (
|
|
bitra_id text PRIMARY KEY,
|
|
payload jsonb NOT NULL,
|
|
synced_at timestamptz DEFAULT now()
|
|
);
|
|
CREATE TABLE raw_bitra.dept_history (
|
|
bitra_employee_id text NOT NULL,
|
|
period date NOT NULL,
|
|
payload jsonb NOT NULL,
|
|
synced_at timestamptz DEFAULT now(),
|
|
PRIMARY KEY (bitra_employee_id, period)
|
|
);
|
|
|
|
-- EVA raw
|
|
CREATE TABLE raw_eva.persons (
|
|
eva_id text PRIMARY KEY,
|
|
payload jsonb NOT NULL,
|
|
synced_at timestamptz DEFAULT now()
|
|
);
|
|
CREATE TABLE raw_eva.projects (
|
|
eva_id text PRIMARY KEY,
|
|
payload jsonb NOT NULL,
|
|
synced_at timestamptz DEFAULT now()
|
|
);
|
|
CREATE TABLE raw_eva.tasks (
|
|
eva_id text PRIMARY KEY,
|
|
payload jsonb NOT NULL,
|
|
synced_at timestamptz DEFAULT now()
|
|
);
|
|
CREATE TABLE raw_eva.status_history (
|
|
eva_id text PRIMARY KEY,
|
|
payload jsonb NOT NULL,
|
|
synced_at timestamptz DEFAULT now()
|
|
);
|
|
|
|
-- Bitrix raw
|
|
CREATE TABLE raw_bitrix.deals (
|
|
bitrix_id bigint PRIMARY KEY,
|
|
payload jsonb NOT NULL,
|
|
synced_at timestamptz DEFAULT now()
|
|
);
|
|
CREATE TABLE raw_bitrix.users (
|
|
bitrix_id bigint PRIMARY KEY,
|
|
payload jsonb NOT NULL,
|
|
synced_at timestamptz DEFAULT now()
|
|
);
|
|
CREATE TABLE raw_bitrix.departments (
|
|
bitrix_id bigint PRIMARY KEY,
|
|
payload jsonb NOT NULL,
|
|
synced_at timestamptz DEFAULT now()
|
|
);
|
|
|
|
-- Журнал синхронизаций (общий для всех источников)
|
|
CREATE TABLE public.sync_log (
|
|
id bigserial PRIMARY KEY,
|
|
source text NOT NULL,
|
|
entity text NOT NULL,
|
|
last_sync_ts timestamptz,
|
|
records_count int,
|
|
status text,
|
|
error_message text,
|
|
synced_at timestamptz DEFAULT now()
|
|
);
|
|
CREATE INDEX idx_sync_log_source_entity ON public.sync_log (source, entity, synced_at DESC);
|