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