8.9 KiB
8.9 KiB
ER-диаграмма схемы dkb
Диаграмма построена по SQLAlchemy-моделям и Alembic-миграциям. PK — первичный ключ, FK — внешний ключ, UK — уникальное поле. Все таблицы находятся в PostgreSQL-схеме dkb.
erDiagram
CHART_OF_ACCOUNT {
uuid id PK
string code UK
uuid parent_id FK "nullable"
string name
enum kind "A | P | AP"
}
REGIONAL_OFFICE {
uuid id PK
string name
boolean is_active
}
COMPANY_GROUP {
uuid id PK
string crm_id UK
string name
uuid base_regional_office_id FK
datetime valid_from
string updated_by
datetime created_at
}
COMPANY_GROUP_HISTORY {
uuid id PK
uuid company_group_id FK
string crm_id UK
string name
uuid base_regional_office_id "snapshot, not FK"
datetime valid_from
datetime valid_to
string updated_by
}
LEGAL_ENTITY {
uuid id PK
string inn UK
enum entity_type "IP | UL"
string name
uuid company_group_id FK
uuid regional_office_id FK
date close_date
string updated_by
datetime created_at
}
LEGAL_ENTITY_HISTORY {
uuid id PK
uuid legal_entity_id FK
string inn UK
enum entity_type "IP | UL"
string name
uuid company_group_id FK
uuid regional_office_id FK
date close_date
datetime valid_from
datetime valid_to
string changed_by
}
LEGAL_ENTITY_ALIAS {
uuid id PK
uuid legal_entity_id FK
string alias
string normalized_alias
string created_by
datetime created_at
}
RELATED_GROUP {
uuid id PK
string code UK
uuid company_group_id FK
enum kind "GSK | GSZ"
string name
date created_date
}
RELATED_GROUP_MEMBER {
uuid id PK
uuid related_group_id FK
uuid legal_entity_id FK
enum kind "GSK | GSZ"
datetime valid_from
datetime valid_to
boolean is_current
string created_by
}
CONSOLIDATION {
uuid id PK
uuid related_group_id FK
smallint year
enum quarter "Q1 | Q2 | Q3 | Q4"
datetime created_at
}
CONSOLIDATION_VERSION {
uuid id PK
uuid consolidation_id FK
string version_no
date consolidation_date
string initiator
string approved_in_rf
string verified_in_go
boolean kfo_confirmation_enabled
boolean is_in_work
datetime created_at
string created_by
}
CONSOLIDATION_MEMBER {
uuid id PK
uuid consolidation_version_id FK
uuid legal_entity_id FK
}
CORRECTION {
uuid id PK
uuid consolidation_version_id FK
uuid correction_id FK "nullable; parent correction"
uuid legal_entity_id FK "nullable"
string debit_line_code
string debit_account_code
numeric debit_adjustment
string credit_line_code
string credit_account_code
numeric credit_adjustment
numeric deviation_amount
numeric deviation_percent
text comment
string created_by
datetime created_at
string uploaded_by
datetime uploaded_at
}
STORED_FILE {
uuid id PK
uuid legal_entity_id FK
enum document_type "nullable"
enum file_type "XLSX | XLS | PDF | PNG | JPG"
string original_name
string s3_key UK "nullable"
string content_hash "nullable"
bigint size_bytes
smallint period_year
enum period_quarter "nullable"
enum status "nullable"
uuid current_recognition_job_id FK "nullable"
string uploaded_by "nullable"
datetime uploaded_at "nullable"
}
RECOGNITION_JOB {
uuid id PK
uuid file_id FK
uuid legal_entity_id FK
enum pipeline "nullable"
string service_version "nullable"
enum status "nullable"
smallint attempt
string queue_msg_id "nullable"
datetime requested_at "nullable"
datetime started_at "nullable"
datetime finished_at "nullable"
text error_message "nullable"
string raw_result_s3_key "nullable"
}
FINANCIAL_STATEMENT_META {
uuid id PK
uuid stored_file_id FK
enum statement_type "BALANCE | INCOME"
string inn
boolean inn_verified
date report_date "nullable"
boolean report_date_verified
string units
boolean units_verified
}
FINANCIAL_STATEMENT_LINE {
uuid id PK
uuid recognition_job_id FK
uuid meta_id FK "nullable"
uuid legal_entity_id FK
string line_code
boolean line_code_recognized
string line_name
boolean line_name_recognized
enum row_kind
bigint ordinal
}
FINANCIAL_STATEMENT_VALUE {
uuid id PK
uuid line_id FK
numeric amount "nullable"
string string_value "nullable"
smallint column_index
boolean is_recognized
}
OSV_ENTRY {
uuid id PK
uuid recognition_job_id FK
uuid legal_entity_id FK
uuid stored_file_id FK
string account_code
uuid account_id FK
string account_root
string counterparty_name
string counterparty_inn
uuid counterparty_id FK "nullable"
numeric opening_debit
numeric opening_credit
numeric turnover_debit
numeric turnover_credit
numeric closing_debit
numeric closing_credit
}
CHART_OF_ACCOUNT o|--o{ CHART_OF_ACCOUNT : "parent_id"
REGIONAL_OFFICE ||--o{ COMPANY_GROUP : "base office"
COMPANY_GROUP ||--o{ COMPANY_GROUP_HISTORY : "history"
COMPANY_GROUP ||--o{ LEGAL_ENTITY : "contains"
REGIONAL_OFFICE ||--o{ LEGAL_ENTITY : "serves"
LEGAL_ENTITY ||--o{ LEGAL_ENTITY_ALIAS : "aliases"
LEGAL_ENTITY ||--o{ LEGAL_ENTITY_HISTORY : "history"
COMPANY_GROUP ||--o{ LEGAL_ENTITY_HISTORY : "group snapshot"
REGIONAL_OFFICE ||--o{ LEGAL_ENTITY_HISTORY : "office snapshot"
COMPANY_GROUP ||--o{ RELATED_GROUP : "owns"
RELATED_GROUP ||--o{ RELATED_GROUP_MEMBER : "memberships"
LEGAL_ENTITY ||--o{ RELATED_GROUP_MEMBER : "participates"
RELATED_GROUP ||--o{ CONSOLIDATION : "periods"
CONSOLIDATION ||--o{ CONSOLIDATION_VERSION : "versions"
CONSOLIDATION_VERSION ||--o{ CONSOLIDATION_MEMBER : "members"
LEGAL_ENTITY ||--o{ CONSOLIDATION_MEMBER : "included"
CONSOLIDATION_VERSION ||--o{ CORRECTION : "corrections"
LEGAL_ENTITY o|--o{ CORRECTION : "entity correction"
CORRECTION o|--o{ CORRECTION : "parent correction"
LEGAL_ENTITY ||--o{ STORED_FILE : "files"
STORED_FILE ||--o{ RECOGNITION_JOB : "recognition attempts"
LEGAL_ENTITY ||--o{ RECOGNITION_JOB : "recognized for"
RECOGNITION_JOB o|--o{ STORED_FILE : "current job"
STORED_FILE ||--o{ FINANCIAL_STATEMENT_META : "metadata"
FINANCIAL_STATEMENT_META o|--o{ FINANCIAL_STATEMENT_LINE : "lines"
RECOGNITION_JOB ||--o{ FINANCIAL_STATEMENT_LINE : "produces"
LEGAL_ENTITY ||--o{ FINANCIAL_STATEMENT_LINE : "owns"
FINANCIAL_STATEMENT_LINE ||--o{ FINANCIAL_STATEMENT_VALUE : "values"
RECOGNITION_JOB ||--o{ OSV_ENTRY : "produces"
STORED_FILE ||--o{ OSV_ENTRY : "contains"
LEGAL_ENTITY ||--o{ OSV_ENTRY : "owner"
LEGAL_ENTITY o|--o{ OSV_ENTRY : "counterparty"
CHART_OF_ACCOUNT ||--o{ OSV_ENTRY : "account"
Важные ограничения
consolidation: уникальна комбинация (related_group_id,year,quarter).consolidation_version: уникальна пара (consolidation_id,version_no).consolidation_member: уникальна пара (consolidation_version_id,legal_entity_id).legal_entity_history: уникальна пара (legal_entity_id,valid_from).related_group_member: действуют частичные уникальные индексы для текущих участников.stored_fileиrecognition_jobсвязаны циклически: файл хранит текущую попытку, а каждая попытка ссылается на файл.
Представление
dkb.v_osv_entry_with_file_flags объединяет osv_entry, stored_file, recognition_job, legal_entity, related_group_member и related_group. Оно добавляет вычисляемый тип контрагента: ГСК, ГК или Внешний.
Источники
api/src/models/*.pyapi/alembic/versions/1c4e0a7d9612_first_migration.pyapi/alembic/versions/c89d082850bd_add_default_and_nullability.pyapi/scripts/create_osv_entry_view.sql