288 lines
8.9 KiB
Markdown
288 lines
8.9 KiB
Markdown
# ER-диаграмма схемы `dkb`
|
||
|
||
Диаграмма построена по SQLAlchemy-моделям и Alembic-миграциям. `PK` — первичный ключ, `FK` — внешний ключ, `UK` — уникальное поле. Все таблицы находятся в PostgreSQL-схеме `dkb`.
|
||
|
||
```mermaid
|
||
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/*.py`
|
||
- `api/alembic/versions/1c4e0a7d9612_first_migration.py`
|
||
- `api/alembic/versions/c89d082850bd_add_default_and_nullability.py`
|
||
- `api/scripts/create_osv_entry_view.sql`
|