skala-5-final
Tables (22)
ai_validation_results
- id — BIGSERIAL
- usage_statement_id — BIGINT — Foreign key → usage_statements.id — NOT NULL
- category_id — BIGINT — Foreign key → usage_categories.id — NOT NULL
- validation_status_id — BIGINT — Foreign key → validation_statuses.id — NOT NULL
- total_statement_amount — NUMERIC(20,2)
- total_receipt_amount — NUMERIC(20,2)
- amount_difference — NUMERIC(20,2)
- missing_receipts_count — INTEGER — NOT NULL
- missing_photos_count — INTEGER — NOT NULL
- agent_reasoning — TEXT
- agent_version — VARCHAR(50)
- validated_at — TIMESTAMPTZ
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
file_types
- id — BIGSERIAL
- code — VARCHAR(50) — NOT NULL
- name — VARCHAR(100) — NOT NULL
files
- id — BIGSERIAL
- uploaded_by_user_id — BIGINT — Foreign key → users.id — NOT NULL
- file_type_id — BIGINT — Foreign key → file_types.id — NOT NULL
- ocr_status_id — BIGINT — Foreign key → ocr_statuses.id
- original_filename — VARCHAR(255) — NOT NULL
- stored_filename — VARCHAR(255) — NOT NULL
- file_path — TEXT — NOT NULL
- mime_type — VARCHAR(100)
- file_size — BIGINT
- storage_provider — VARCHAR(50)
- ocr_raw_text — TEXT
- uploaded_at — TIMESTAMPTZ — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
line_item_photo_links
- id — BIGSERIAL
- statement_line_item_id — BIGINT — Foreign key → statement_line_items.id — NOT NULL
- photo_id — BIGINT — Foreign key → photos.id — NOT NULL
- category_id — BIGINT — Foreign key → usage_categories.id — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
match_statuses
- id — BIGSERIAL
- code — VARCHAR(50) — NOT NULL
- name — VARCHAR(100) — NOT NULL
ocr_statuses
- id — BIGSERIAL
- code — VARCHAR(50) — NOT NULL
- name — VARCHAR(100) — NOT NULL
photos
- id — BIGSERIAL
- project_id — BIGINT — Foreign key → projects.id — NOT NULL
- file_id — BIGINT — Foreign key → files.id — NOT NULL
- taken_at — TIMESTAMPTZ — NOT NULL
- description — TEXT — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
projects
- id — BIGSERIAL
- construction_company — VARCHAR(200) — NOT NULL
- construction_name — VARCHAR(300) — NOT NULL
- location — VARCHAR(300) — NOT NULL
- representative — VARCHAR(100) — NOT NULL
- contract_amount — NUMERIC(20,2) — NOT NULL
- construction_start — DATE — NOT NULL
- construction_end — DATE — NOT NULL
- orderer — VARCHAR(200) — NOT NULL
- cumulative_progress — NUMERIC(5,2)
- allocated_amount — NUMERIC(20,2)
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
receipt_line_item_classifications
- id — BIGSERIAL
- receipt_line_item_id — BIGINT — Foreign key → receipt_line_items.id — NOT NULL
- category_id — BIGINT — Foreign key → usage_categories.id — NOT NULL
- confidence — NUMERIC(5,4)
- agent_version — VARCHAR(50)
- classified_at — TIMESTAMPTZ — NOT NULL
receipt_line_items
- id — BIGSERIAL
- receipt_id — BIGINT — Foreign key → receipts.id — NOT NULL
- usage_date — DATE
- description — TEXT — NOT NULL
- quantity — NUMERIC(15,4)
- unit_price — NUMERIC(20,2)
- total_amount — NUMERIC(20,2)
- note — TEXT
- created_at — TIMESTAMPTZ — NOT NULL
receipts
- id — BIGSERIAL
- usage_statement_id — BIGINT — Foreign key → usage_statements.id — NOT NULL
- file_id — BIGINT — Foreign key → files.id — NOT NULL
- merchant_name — VARCHAR(200)
- receipt_date — DATE
- total_amount — NUMERIC(20,2)
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
statement_category_summaries
- id — BIGSERIAL
- usage_statement_id — BIGINT — Foreign key → usage_statements.id — NOT NULL
- category_id — BIGINT — Foreign key → usage_categories.id — NOT NULL
- previous_amount — NUMERIC(20,2) — NOT NULL
- current_amount — NUMERIC(20,2) — NOT NULL
- cumulative_amount — NUMERIC(20,2) — NOT NULL
statement_line_items
- id — BIGSERIAL
- usage_statement_id — BIGINT — Foreign key → usage_statements.id — NOT NULL
- category_id — BIGINT — Foreign key → usage_categories.id — NOT NULL
- usage_date — DATE
- description — TEXT — NOT NULL
- unit — VARCHAR(50)
- quantity — NUMERIC(15,4)
- unit_price — NUMERIC(20,2)
- total_amount — NUMERIC(20,2) — NOT NULL
- note — TEXT
- created_at — TIMESTAMPTZ — NOT NULL
statement_photo_links
- id — BIGSERIAL
- usage_statement_id — BIGINT — Foreign key → usage_statements.id — NOT NULL
- photo_id — BIGINT — Foreign key → photos.id — NOT NULL
- category_id — BIGINT — Foreign key → usage_categories.id — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
statement_receipt_matches
- id — BIGSERIAL
- statement_line_item_id — BIGINT — Foreign key → statement_line_items.id — NOT NULL
- receipt_line_item_id — BIGINT — Foreign key → receipt_line_items.id
- match_status_id — BIGINT — Foreign key → match_statuses.id — NOT NULL
- amount_difference — NUMERIC(20,2)
- quantity_difference — NUMERIC(15,4)
- confidence — NUMERIC(5,4)
- agent_reasoning — TEXT
- agent_version — VARCHAR(50)
- matched_at — TIMESTAMPTZ — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
usage_categories
- id — BIGSERIAL
- name — VARCHAR(200) — NOT NULL
- display_order — INTEGER — NOT NULL
usage_statements
- id — BIGSERIAL
- project_id — BIGINT — Foreign key → projects.id — NOT NULL
- file_id — BIGINT — Foreign key → files.id — NOT NULL
- statement_month — DATE — NOT NULL
- document_date — DATE — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
user_projects
- id — BIGSERIAL
- user_id — BIGINT — Foreign key → users.id — NOT NULL
- project_id — BIGINT — Foreign key → projects.id — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
user_roles
- id — BIGSERIAL
- code — VARCHAR(50) — NOT NULL
- name — VARCHAR(100) — NOT NULL
users
- id — BIGSERIAL
- user_role_id — BIGINT — Foreign key → user_roles.id — NOT NULL
- username — VARCHAR(100) — NOT NULL
- password_hash — TEXT — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
validation_issues
- id — BIGSERIAL
- ai_validation_result_id — BIGINT — Foreign key → ai_validation_results.id — NOT NULL
- issue_text — TEXT — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
validation_statuses
- id — BIGSERIAL
- code — VARCHAR(50) — NOT NULL
- name — VARCHAR(100) — NOT NULL