ddfds

Tables (38)

account_mapping_rules

  • rule_id — BIGINT — Primary key — NOT NULL
  • debit_subject_id — BIGINT — Foreign key → account_subjects.subject_id — NOT NULL
  • credit_subject_id — BIGINT — Foreign key → account_subjects.subject_id — NOT NULL
  • trigger_condition — VARCHAR(500) — NOT NULL
  • payment_method — VARCHAR(20) — NOT NULL
  • priority — INT — NOT NULL
  • effective_from — DATE — NOT NULL
  • effective_to — DATE
  • version_note — VARCHAR(300)
  • is_active — TINYINT(1) — NOT NULL
  • created_at — TIMESTAMP

account_subjects

  • subject_id — BIGINT — Primary key — NOT NULL
  • subject_code — VARCHAR(20) — NOT NULL
  • subject_name — VARCHAR(100) — NOT NULL
  • subject_type — VARCHAR(20) — NOT NULL
  • category — VARCHAR(50)
  • normal_balance — VARCHAR(10) — NOT NULL
  • is_deductible — TINYINT(1) — NOT NULL
  • tax_category — VARCHAR(20) — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — TIMESTAMP

accounts

  • account_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • account_type — VARCHAR(20) — NOT NULL
  • bank_code — VARCHAR(20) — NOT NULL
  • account_number — VARCHAR(30) — NOT NULL
  • alias — VARCHAR(100)
  • balance — DECIMAL(15,2) — NOT NULL
  • available_balance — DECIMAL(15,2) — NOT NULL
  • pending_amount — DECIMAL(15,2) — NOT NULL
  • settlement_account — TINYINT(1) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP

ai_classifications

  • ai_class_id — BIGINT — Primary key — NOT NULL
  • txn_id — BIGINT — Foreign key → transactions.txn_id — NOT NULL
  • suggested_category_id — VARCHAR(30) — Foreign key → tax_categories.category_id — NOT NULL
  • confidence — DECIMAL(3,2) — NOT NULL
  • method — VARCHAR(20) — NOT NULL
  • model_version — VARCHAR(50)
  • reasoning — VARCHAR(1000)
  • alternatives — JSON
  • tax_saving_tip — VARCHAR(500)
  • classified_at — TIMESTAMP

business_info

  • business_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • business_number — VARCHAR(20) — NOT NULL
  • business_type — VARCHAR(10) — NOT NULL
  • business_type_name — VARCHAR(100)
  • business_name — VARCHAR(200)
  • business_address — VARCHAR(500)
  • establishment_date — DATE
  • is_simplified_bookkeeping — TINYINT(1) — NOT NULL
  • verified — TINYINT(1) — NOT NULL
  • registered_at — TIMESTAMP
  • updated_at — TIMESTAMP

cards

  • card_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • linked_account_id — BIGINT — Foreign key → accounts.account_id — NOT NULL
  • card_type — VARCHAR(20) — NOT NULL
  • card_number_last4 — VARCHAR(4) — NOT NULL
  • card_alias — VARCHAR(100)
  • default_purpose — VARCHAR(20) — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • activated_at — TIMESTAMP
  • created_at — TIMESTAMP

chat_messages

  • message_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → chat_sessions.session_id — NOT NULL
  • role — VARCHAR(10) — NOT NULL
  • content — VARCHAR(2000) — NOT NULL
  • context_txn_id — BIGINT — Foreign key → transactions.txn_id
  • sources — JSON
  • suggested_actions — JSON
  • disclaimer — VARCHAR(300)
  • created_at — TIMESTAMP

chat_sessions

  • session_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • created_at — TIMESTAMP
  • last_message_at — TIMESTAMP

classification_history

  • history_id — BIGINT — Primary key — NOT NULL
  • txn_id — BIGINT — Foreign key → transactions.txn_id — NOT NULL
  • method — VARCHAR(20) — NOT NULL
  • result_category_id — VARCHAR(30) — Foreign key → tax_categories.category_id — NOT NULL
  • confidence — DECIMAL(3,2)
  • processed_at — TIMESTAMP

deduction_limits

  • limit_id — BIGINT — Primary key — NOT NULL
  • category_id — VARCHAR(30) — Foreign key → tax_categories.category_id
  • subject_id — BIGINT — Foreign key → account_subjects.subject_id
  • tax_item — VARCHAR(20) — NOT NULL
  • tax_year — INT — NOT NULL
  • annual_limit — DECIMAL(15,2) — NOT NULL
  • limit_basis — VARCHAR(300) — NOT NULL
  • effective_from — DATE — NOT NULL
  • effective_to — DATE

deduction_usage

  • usage_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • limit_id — BIGINT — Foreign key → deduction_limits.limit_id — NOT NULL
  • tax_year — INT — NOT NULL
  • used_amount — DECIMAL(15,2) — NOT NULL
  • remaining_amount — DECIMAL(15,2) — NOT NULL
  • usage_rate — DECIMAL(5,2) — NOT NULL
  • last_calculated_at — TIMESTAMP

evidences

  • evidence_id — BIGINT — Primary key — NOT NULL
  • txn_id — BIGINT — Foreign key → transactions.txn_id — NOT NULL
  • type — VARCHAR(20) — NOT NULL
  • file_url — VARCHAR(500) — NOT NULL
  • file_size — INT
  • description — VARCHAR(300)
  • created_at — TIMESTAMP

export_logs

  • export_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • export_format — VARCHAR(30) — NOT NULL
  • export_scope — VARCHAR(200) — NOT NULL
  • period_from — DATE — NOT NULL
  • period_to — DATE — NOT NULL
  • include_evidences — TINYINT(1) — NOT NULL
  • record_count — INT
  • file_path — VARCHAR(500) — NOT NULL
  • expires_at — TIMESTAMP
  • exported_at — TIMESTAMP

journal_entries

  • entry_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • txn_id — BIGINT — Foreign key → transactions.txn_id
  • classification_id — BIGINT — Foreign key → user_classifications.classification_id
  • rule_id — BIGINT — Foreign key → account_mapping_rules.rule_id
  • transaction_date — DATE — NOT NULL
  • description — VARCHAR(500) — NOT NULL
  • total_amount — DECIMAL(15,2) — NOT NULL
  • entry_status — VARCHAR(20) — NOT NULL
  • is_editable — TINYINT(1) — NOT NULL
  • modification_note — VARCHAR(500)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

journal_lines

  • line_id — BIGINT — Primary key — NOT NULL
  • entry_id — BIGINT — Foreign key → journal_entries.entry_id — NOT NULL
  • subject_id — BIGINT — Foreign key → account_subjects.subject_id — NOT NULL
  • line_type — VARCHAR(10) — NOT NULL
  • amount — DECIMAL(15,2) — NOT NULL
  • memo — VARCHAR(300)
  • line_order — INT — NOT NULL

merchants

  • merchant_id — BIGINT — Primary key — NOT NULL
  • merchant_name — VARCHAR(200) — NOT NULL
  • merchant_category_code — VARCHAR(10)
  • business_registration_no — VARCHAR(20)
  • business_category — VARCHAR(100)
  • address — VARCHAR(500)
  • phone — VARCHAR(20)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

notification_settings

  • setting_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • transaction_alert — TINYINT(1) — NOT NULL
  • deadline_days_before — JSON
  • batch_reminder — TINYINT(1) — NOT NULL
  • weekly_summary — TINYINT(1) — NOT NULL
  • push_enabled — TINYINT(1) — NOT NULL
  • updated_at — TIMESTAMP

notifications

  • notification_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • type — VARCHAR(30) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • body — VARCHAR(1000) — NOT NULL
  • txn_id — BIGINT — Foreign key → transactions.txn_id
  • action_url — VARCHAR(500)
  • is_read — TINYINT(1) — NOT NULL
  • metadata — JSON
  • created_at — TIMESTAMP

payments

  • payment_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • card_id — BIGINT — Foreign key → cards.card_id — NOT NULL
  • transaction_id — BIGINT
  • amount — DECIMAL(15,2) — NOT NULL
  • currency — VARCHAR(5) — NOT NULL
  • merchant_id — BIGINT — Foreign key → merchants.merchant_id — NOT NULL
  • merchant_name — VARCHAR(200) — NOT NULL
  • merchant_category_code — VARCHAR(10) — NOT NULL
  • payment_method — VARCHAR(20) — NOT NULL
  • purpose — VARCHAR(20) — NOT NULL
  • authorization_code — VARCHAR(30)
  • status — VARCHAR(20) — NOT NULL
  • decline_reason — VARCHAR(100)
  • validation_card_status — VARCHAR(10)
  • validation_balance_check — VARCHAR(10)
  • validation_limit_check — VARCHAR(10)
  • validation_fraud_check — VARCHAR(10)
  • authorized_at — TIMESTAMP
  • captured_at — TIMESTAMP
  • cancelled_at — TIMESTAMP
  • cancel_amount — DECIMAL(15,2)
  • cancel_reason — VARCHAR(300)
  • created_at — TIMESTAMP

pockets

  • pocket_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • account_id — BIGINT — Foreign key → accounts.account_id — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • type — VARCHAR(20) — NOT NULL
  • balance — DECIMAL(15,2) — NOT NULL
  • auto_allocate_percentage — INT
  • color — VARCHAR(10)
  • created_at — TIMESTAMP

settlement_transactions

  • id — BIGINT — Primary key — NOT NULL
  • settlement_id — BIGINT — Foreign key → settlements.settlement_id — NOT NULL
  • txn_id — BIGINT — Foreign key → transactions.txn_id — NOT NULL
  • amount — DECIMAL(15,2) — NOT NULL

settlements

  • settlement_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • settlement_account_id — BIGINT — Foreign key → accounts.account_id — NOT NULL
  • transaction_count — INT — NOT NULL
  • total_amount — DECIMAL(15,2) — NOT NULL
  • fee_amount — DECIMAL(15,2) — NOT NULL
  • net_amount — DECIMAL(15,2) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • settlement_date — DATE — NOT NULL
  • created_at — TIMESTAMP
  • paid_at — TIMESTAMP

tax_agent_contracts

  • contract_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • agent_id — BIGINT — Foreign key → tax_agents.agent_id — NOT NULL
  • plan — VARCHAR(20) — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE
  • status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP

tax_agent_corrections

  • correction_id — BIGINT — Primary key — NOT NULL
  • submission_id — BIGINT — Foreign key → tax_agent_submissions.submission_id — NOT NULL
  • journal_id — BIGINT — Foreign key → journal_entries.entry_id — NOT NULL
  • original_category — VARCHAR(100) — NOT NULL
  • suggested_category — VARCHAR(100) — NOT NULL
  • reason — VARCHAR(500)
  • status — VARCHAR(30) — NOT NULL
  • approved — TINYINT(1)

tax_agent_submissions

  • submission_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • agent_id — BIGINT — Foreign key → tax_agents.agent_id — NOT NULL
  • period_start — DATE — NOT NULL
  • period_end — DATE — NOT NULL
  • message — VARCHAR(500)
  • include_evidences — TINYINT(1) — NOT NULL
  • journal_count — INT — NOT NULL
  • evidence_count — INT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • submitted_at — TIMESTAMP
  • reviewed_at — TIMESTAMP
  • overall_comment — VARCHAR(1000) — ARCHAR(1000

tax_agents

  • agent_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • firm — VARCHAR(200)
  • specialties — JSON
  • rating — DECIMAL(2,1)
  • review_count — INT — NOT NULL
  • monthly_fee — DECIMAL(15,2)
  • is_active — TINYINT(1) — NOT NULL
  • created_at — TIMESTAMP

tax_calendar_events

  • event_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • tax_type — VARCHAR(50) — NOT NULL
  • due_date — DATE — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP

tax_categories

  • category_id — VARCHAR(30) — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • type — VARCHAR(20) — NOT NULL
  • description — VARCHAR(500)
  • tax_deductible — TINYINT(1) — NOT NULL
  • annual_limit — DECIMAL(15,2)
  • related_law — VARCHAR(200)
  • is_active — TINYINT(1) — NOT NULL
  • created_at — TIMESTAMP

tax_reports

  • report_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • report_type — VARCHAR(20) — NOT NULL
  • tax_year — INT — NOT NULL
  • tax_period — INT — NOT NULL
  • total_revenue — DECIMAL(15,2)
  • total_expense — DECIMAL(15,2)
  • net_income — DECIMAL(15,2)
  • vat_payable — DECIMAL(15,2)
  • total_tax_amount — DECIMAL(15,2) — NOT NULL
  • deductible_total — DECIMAL(15,2) — NOT NULL
  • non_deductible_total — DECIMAL(15,2) — NOT NULL
  • estimated_tax_payable — DECIMAL(15,2) — NOT NULL
  • expense_breakdown — JSON
  • insights — JSON
  • detail_breakdown — JSON
  • generated_at — TIMESTAMP

tax_saving_recommendations

  • rec_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • tax_year — INT — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • category — VARCHAR(50) — NOT NULL
  • estimated_saving — DECIMAL(15,2) — NOT NULL
  • description — VARCHAR(500)
  • priority — VARCHAR(10) — NOT NULL
  • related_law — VARCHAR(200)
  • action_required — VARCHAR(500)
  • is_applied — TINYINT(1) — NOT NULL
  • created_at — TIMESTAMP

term_agreements

  • agreement_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • term_id — VARCHAR(50) — Foreign key → terms.term_id — NOT NULL
  • agreed — TINYINT(1) — NOT NULL
  • agreed_at — TIMESTAMP

terms

  • term_id — VARCHAR(50) — Primary key — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • version — VARCHAR(10) — NOT NULL
  • is_required — TINYINT(1) — NOT NULL
  • content_url — VARCHAR(500) — NOT NULL
  • updated_at — TIMESTAMP

transactions

  • txn_id — BIGINT — Primary key — NOT NULL
  • payment_id — BIGINT — Foreign key → payments.payment_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • card_id — BIGINT — Foreign key → cards.card_id — NOT NULL
  • merchant_name — VARCHAR(200) — NOT NULL
  • merchant_category_code — VARCHAR(10)
  • merchant_business_number — VARCHAR(20)
  • amount — DECIMAL(15,2) — NOT NULL
  • vat_amount — DECIMAL(15,2)
  • currency — VARCHAR(5) — NOT NULL
  • purpose — VARCHAR(20) — NOT NULL
  • transacted_at — TIMESTAMP — NOT NULL
  • receipt_url — VARCHAR(500)
  • category_status — VARCHAR(20) — NOT NULL
  • needs_memo — TINYINT(1) — NOT NULL
  • has_evidence — TINYINT(1) — NOT NULL
  • memo — VARCHAR(500)
  • journal_id — BIGINT — Foreign key → journal_entries.entry_id
  • created_at — TIMESTAMP

transfers

  • transfer_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • from_account_id — BIGINT — Foreign key → accounts.account_id — NOT NULL
  • to_account_id — BIGINT — Foreign key → accounts.account_id
  • to_bank_code — VARCHAR(20)
  • to_account_number — VARCHAR(30)
  • to_holder_name — VARCHAR(50)
  • amount — DECIMAL(15,2) — NOT NULL
  • fee — DECIMAL(15,2) — NOT NULL
  • memo — VARCHAR(300)
  • status — VARCHAR(20) — NOT NULL
  • from_balance_after — DECIMAL(15,2)
  • to_balance_after — DECIMAL(15,2)
  • transferred_at — TIMESTAMP
  • created_at — TIMESTAMP

user_classifications

  • classification_id — BIGINT — Primary key — NOT NULL
  • txn_id — BIGINT — Foreign key → transactions.txn_id — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • ai_class_id — BIGINT — Foreign key → ai_classifications.ai_class_id
  • tax_category_id — VARCHAR(30) — Foreign key → tax_categories.category_id — NOT NULL
  • ai_suggestion_accepted — TINYINT(1) — NOT NULL
  • memo — VARCHAR(500)
  • change_reason — VARCHAR(300)
  • classification_status — VARCHAR(20) — NOT NULL
  • confirmed_at — TIMESTAMP
  • created_at — TIMESTAMP

user_surveys

  • survey_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • annual_revenue_range — VARCHAR(20) — NOT NULL
  • main_expense_types — JSON
  • has_employees — TINYINT(1) — NOT NULL
  • is_youth_policy_eligible — TINYINT(1) — NOT NULL
  • home_office — TINYINT(1) — NOT NULL
  • created_at — TIMESTAMP

users

  • user_id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(20) — NOT NULL
  • auth_method — VARCHAR(20) — NOT NULL
  • social_provider — VARCHAR(20)
  • status — VARCHAR(20) — NOT NULL
  • plan — VARCHAR(20) — NOT NULL
  • kyc_verified — TINYINT(1) — NOT NULL
  • ci — VARCHAR(255)
  • business_registered — TINYINT(1) — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • withdrawn_at — TIMESTAMP

verifications

  • verification_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • phone — VARCHAR(20) — NOT NULL
  • carrier — VARCHAR(20) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • birth_date — VARCHAR(8) — NOT NULL
  • gender — VARCHAR(10) — NOT NULL
  • code — VARCHAR(6)
  • verified — TINYINT(1) — NOT NULL
  • ci — VARCHAR(255)
  • expires_at — TIMESTAMP
  • verified_at — TIMESTAMP
  • created_at — TIMESTAMP