查帳系統 Table Specification

查帳系統 Table Specification

搜尋規則

搜尋規則：

  1. 使用關鍵字搜尋： 關鍵字

    2. 例如：專利年費代繳 (搜尋永輝單項服務)

  2. 結合多個關鍵字：關鍵字1 關鍵字2 關鍵字3

    3. 例如：會計 稅務 薪資 (搜尋永輝多項服務)

  3. 搜尋完全符合關鍵字的結果： "關鍵字"

    4. 例如："跨境稅務" (四個字不拆開)

  4. 排除特定關鍵字：關鍵字1 -關鍵字2

    5. 例如：稅務 -台灣 (排除台灣、只搜尋海外稅務服務)

永輝查帳系統資料庫設計參考資料

文章目錄 點擊收合
3.1 USER 使用者 Table
3.2 Workpaper 底稿 Table
3.3 Engagement Table
3.4 ROLE 角色 Table
3.5 Permission 權限（RBAC） 角色 Table
3.6 Ducumnet 附件Table
3.7 Confirmation 函證
3.8查核發現/議題Finding Table
3.9 Risk 風險項目 Table
3.10 Procedure 查核程序Table
3.11控制點（內部控制） Control Table
3.12查核程序模板 Template Table
3.13 會計科目Account Table
3.14試算表科目tb Table
3.15 對象 counterparty Table
3.16客戶實體（entity）Table
4.1簽核記錄 sign_offs Table
4.2調整分錄 adjustments Table
4.3查核計畫表（Audit Plan）Table
4.4品管審核（EQCR）與二階複核 Table

3.1 USER 使用者 Table

🧱 Core Identification

  • id
  • username
  • email
  • full_name
  • display_name
  • employee_no

🏢 Organization & Role

  • title
  • department
  • role_primary
  • supervisor_id
  • office_location

🔒 Authentication / Security

  • password_hash
  • salt
  • is_active
  • is_locked
  • failed_attempts
  • last_login_at
  • last_ip
  • mfa_enabled
  • mfa_secret

🌍 Localization / Preferences

  • locale
  • timezone
  • theme (optional)

📞 Contact Info

  • phone_mobile
  • phone_office
  • avatar_url
  • signature_image

🕓 Employment Info

  • employment_type
  • joined_date
  • left_date

🧾 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by

These fields cover: identity, organization, login control, localization, contact, employment, and audit trail, which together form a complete and flexible user profile for an audit system.

3.2 Workpaper 底稿 Table

Here’s a concise field list for a workpapers (底稿) table—no SQL, just the fields, grouped for clarity:

🧱 Core Identity & Relations

  • id
  • engagement_id
  • entity_id (可選；亦可由 engagement 推得)
  • index_code (如 C-3、B-2a)
  • title
  • description

🗂 Classification / Structure

  • workpaper_type (permanent / current)
  • area_code (現金/應收/存貨等)
  • account_item (對應科目名稱或代碼)
  • assertion_tags (JSON：existence/valuation/rights 等)
  • confidentiality_level (internal/confidential/restricted)
  • parent_workpaper_id (分層/子底稿)
  • order_no (清單排序用)
  • tags (JSON/字串陣列)

📎 Primary File / Artifact (若未另設 documents 主表)

  • file_url
  • file_type (pdf/xlsx/docx …)
  • doc_version
  • page_count

(若已使用 documents 主表，改用：primary_document_id)

👥 Ownership & Assignment

  • prepared_by
  • prepared_at
  • reviewed_by
  • reviewed_at
  • partner_signed_by
  • partner_signed_at
  • eqcr_required
  • eqcr_by
  • eqcr_at

✅ Workflow / Status

  • status (draft/in_progress/prepared/reviewed/finalized/archived)
  • progress_pct
  • priority
  • due_at
  • is_active
  • archived_at

🔗 Linkage Hints (可去正規化的統計欄位)

  • document_count
  • confirmation_count
  • finding_count
  • risk_count
  • procedure_count

🧾 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by

🔍 Optional UX Helpers

  • default_anchor_id (預設內頁/頁碼/儲存格錨點)
  • human_ref (人類可讀參照，如「見 C-3a 附錄」)

以上欄位組合能支援：索引編碼、檔案掛載/版本、簽核與 EQCR、工作流程進度、與函證/議題/風險/程序的關聯與統計。

3.3 Engagement Table

Excellent — the engagements (查核案件/年度專案) table is the central hub of any audit system.
Below is a clear, structured field list (no SQL) grouped by functional purpose, based on how international systems like CaseWare, TeamMate+, Workiva and Evershine AIS typically design it:

🧱 Core Identity

  • id
  • entity_id (對應客戶主檔)
  • code (年度案件代號，例如 TW-AUD-2025-003)
  • name (案件名稱，例如「XYZ公司2025年度財報查核」)
  • year (查核年度)
  • engagement_type (audit / review / compilation / tax / special)

🗓️ Engagement Period & Scope

  • fiscal_start_date
  • fiscal_end_date
  • audit_period_start
  • audit_period_end
  • start_date (實際查核起始日)
  • end_date (實際結束日)
  • report_issue_date
  • scope_description (查核範圍說明)
  • materiality_amount
  • performance_materiality
  • tolerable_error
  • currency_code

🏢 Organization & Responsibility

  • partner_id (合夥人)
  • manager_id (經理)
  • eqcr_id (品管複核人 EQCR)
  • lead_auditor_id (主查)
  • team_size
  • department (Audit / Tax / Advisory)
  • office_location

(以上欄位多半對應 users.id 或 departments.id)

🔒 Status & Workflow

  • status (planning / in_progress / completed / archived)
  • phase (acceptance / planning / fieldwork / review / reporting)
  • is_accepted (案件是否已正式接受)
  • acceptance_date
  • completion_percentage
  • is_archived
  • archived_at
  • lock_date (封存日期，防止修改)

📋 Engagement Control & Quality

  • risk_level (低/中/高)
  • eqcr_required
  • quality_review_completed
  • independence_confirmed
  • independence_date
  • engagement_letter_id (關聯文件)
  • management_rep_letter_id
  • final_report_id

💼 Financial & Billing (若整合管理模組)

  • billing_code
  • fee_quoted
  • fee_actual
  • billing_status
  • invoice_id
  • profitability_score

🔗 Linkage & Integration

  • client_contact_person
  • client_contact_email
  • client_contact_phone
  • external_auditor_name (若共同查核或交叉查核)
  • parent_engagement_id (母案件，若合併報表查核)
  • child_engagement_count
  • related_entities (JSON：多公司查核)
  • template_version (使用的查核模板版本)

🧾 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by

⚙️ Optional Metadata

  • tags (例如「上市公司」、「首次查核」)
  • notes
  • custom_fields (JSON，可動態擴充)

✅ Summary

類別功能重點
Identity案件唯一代碼與年度資訊
Period & Scope查核期間、重要性、幣別
Team & Roles主要人員（Partner/Manager/Lead）
Status & Quality查核進度、EQCR、品質控制
Finance報酬與開發票管理
Integration與客戶、模板、報告等連結
Audit Trail建立與修改紀錄

3.4 ROLE 角色 Table

the roles (角色) table defines your system’s authority hierarchy and responsibility matrix, forming the backbone of RBAC (Role-Based Access Control) and workflow management in the audit system.
Below is a clean, practical field list (no SQL) — grouped by function and tailored for a professional accounting/audit environment (like Evershine’s structure).

🧱 Core Identification

  • id
  • code (唯一代碼，例如 AUD_PARTNER, TAX_MGR)
  • name (顯示名稱，例如「合夥人 Partner」、「查核經理 Manager」)
  • description (角色職責說明)

🧩 Classification / Context

  • department (Audit / Tax / Payroll / Admin)
  • role_type (system / functional / project)
    • system：平台層角色（如 System Admin）
    • functional：部門職能角色（如 Audit Manager）
    • project：專案內角色（如 Engagement Partner）
  • hierarchy_level (數字表示職級，例如 1=Partner, 2=Manager, 3=Senior, 4=Staff)
  • is_default (是否為預設角色，例如新員工自動套用)

🔒 Access Control

  • can_login (是否可登入系統)
  • is_global_admin (是否擁有全域權限)
  • permission_scope (JSON，例如 {“audit”:”rw”, “tax”:”r”} )
  • approval_limit (可核准金額上限，若整合費用/簽核)

🧾 Workflow & Responsibility

  • signoff_level (簽核層級：preparer / reviewer / partner / eqcr)
  • review_required (布林；此角色執行後是否需上級覆核)
  • assignable (此角色是否可被指派至底稿或任務)
  • notification_rules (JSON：如完成/逾期時寄通知給此角色)

🔗 Integration Fields

  • default_permissions (JSON或外鍵→ role_permissions)
  • default_dashboard_id (預設首頁介面配置)
  • applicable_modules (JSON，如 [“audit”,”tax”,”hr”])

🕓 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by

✅ Summary of Common Roles in an Audit Firm

CodeNameDescriptionHierarchy
AUD_PARTNERPartnerFinal sign-off, report issue1
AUD_MGRAudit ManagerReview & supervise2
AUD_SENIORSenior AuditorPlan & lead fieldwork3
AUD_STAFFAuditorPrepare workpapers4
EQCRQuality ReviewerEngagement Quality Control Review1
TAX_MGRTax ManagerManages tax filings/review2
SYS_ADMINSystem AdministratorMaintains users & permissions0

🧭 Typical Relationships

RelationshipJunction TablePurpose
Users ↔ Rolesuser_roles(user_id, role_id)多角色制
Roles ↔ Permissionsrole_permissions(role_id, permission_id)控管細項權限
Roles ↔ Engagements (optional)engagement_roles(engagement_id, role_id)指派年度專案職責

3.5 Permission 權限（RBAC） 角色 Table

you’re now completing the RBAC (Role-Based Access Control) trio:
users → roles → permissions.
Below is the recommended field list for the permissions (權限) table — designed for an audit-firm management system where every operation (view, edit, approve, sign, delete…) should be traceable and configurable.

🧱 Core Identification

  • id
  • code
    • 唯一識別代碼（系統內使用，例如 audit.view_workpaper、tax.edit_return）
  • name
    • 顯示名稱（例如「查閱底稿」、「編輯稅務申報表」）
  • description
    • 權限功能說明（便於管理員理解用途）

🧩 Classification / Grouping

  • module
    • 所屬模組（如 audit、tax、payroll、admin）
  • category
    • 功能分類（如 workpaper、engagement、confirmation、user_management）
  • action_type
    • 行為類型（view、create、edit、delete、approve、signoff 等）
  • scope_level
    • 權限範圍（global / entity / engagement / workpaper）
  • is_critical
    • 是否為關鍵權限（需額外審批，例如簽發報告、解鎖案件）

🔒 Behavior & Constraints

  • api_endpoint (可選)
    • 對應後端 API 路徑（例如 /api/workpapers/:id/edit）
  • ui_component (可選)
    • 對應前端功能名稱或頁面代碼
  • default_granted (布林)
    • 是否預設賦予基本角色（如普通查帳員擁有「view」）
  • requires_2fa (布林)
    • 是否操作時需二階段驗證（如發報告、刪除案件）

🔗 Integration / Relationships

  • role_permissions
    • 多對多關係表：(role_id, permission_id)
  • user_permissions (可選)
    • 若支援直接指派權限給個別使用者

🧾 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by

🧭 Example Permission Codes (for Audit Module)

ModuleCategoryActionPermission Code說明
auditworkpaperviewaudit.view_workpaper檢視查核底稿
auditworkpapereditaudit.edit_workpaper編輯底稿內容
auditworkpapersignoffaudit.signoff_workpaper底稿簽核
auditengagementapproveaudit.approve_engagement核准案件
auditconfirmationmanageaudit.manage_confirmations管理函證
systemusermanagesystem.manage_users管理使用者與角色
systemrolemanagesystem.manage_roles管理角色權限

✅ Summary Table

類別欄位名稱功能說明
Identityid, code, name, description唯一識別與說明
分類module, category, action_type, scope_level權限分層結構
控制is_critical, default_granted, requires_2fa安全與授權控制
整合api_endpoint, ui_component對應系統功能點
稽核created_at, created_by, updated_at, updated_by追蹤修改紀錄

Would you like me next to show how roles, permissions, and users are linked together in a full RBAC schema (ERD + key junction tables)?

3.6 Ducumnet 附件Table

the documents (附件檔) table is the file metadata hub of an audit system — every uploaded working paper, confirmation letter, evidence, or report should be traceable here.
Below is a complete and structured field list (no SQL) that fits a professional audit / accounting platform like Evershine’s.

🧱 Core Identification

  • id
  • file_name (檔案原始名稱)
  • file_type (副檔名類型：pdf / xlsx / docx / jpg / msg …)
  • mime_type (MIME 類型，可自動偵測)
  • version (版本號，例：1、2、3)
  • description (簡要說明或備註)

☁️ Storage & Location

  • storage_provider (s3 / azure / gcs / local / sharepoint)
  • file_path (在儲存區的相對路徑或 Key)
  • file_url (完整 URL 或預簽名 URL)
  • file_size (檔案大小 bytes)
  • checksum (檔案雜湊 MD5/SHA256，用於驗證)
  • folder_id (若支援虛擬目錄/階層，可對應 folders.id)
  • linked_system (哪個系統模組上傳的：audit / tax / hr)

🔗 Relationships (Foreign Keys or Links)

  • uploaded_by (對應 users.id)
  • engagement_id (對應 engagements.id)
  • entity_id (對應 entities.id，可從 engagement 推得)
  • (不直接放) workpaper_id / confirmation_id —
    → 建議透過 junction tables：
    • workpaper_documents(workpaper_id, document_id)
    • confirmation_documents(confirmation_id, document_id)
    • finding_documents(finding_id, document_id)

📎 File Classification

  • document_category (audit_evidence / working_paper / report / correspondence / legal / tax_return …)
  • document_subtype (函證回函 / 銀行對帳單 / 客戶來信 / 財報附件 …)
  • confidentiality_level (internal / confidential / restricted / public)
  • source_type (manual_upload / auto_generated / imported / scanned)
  • language_code (檔案語言 zh / en / ja …)
  • tags (JSON 或文字陣列，例如 [“函證”,”銀行”])

👥 Ownership & Version Control

  • current_owner_id (目前負責人員)
  • checked_out_by (正在編輯的使用者)
  • checked_out_at (取出時間)
  • is_locked (是否鎖定，避免重覆覆蓋)
  • previous_document_id (前一版本關聯)
  • next_document_id (下一版本關聯)

🧾 Audit Trail

  • uploaded_at
  • approved_at (若需內部審核流程)
  • deleted_at (軟刪除標記)
  • created_at
  • created_by
  • updated_at
  • updated_by

🧩 Optional: Smart Metadata (for AI / OCR integration)

  • ocr_text (全文文字，可供搜尋)
  • metadata_json (解析出的欄位，例如發票號碼、金額、供應商名)
  • ai_extracted_entities (AI 擷取結果)
  • related_documents (JSON：其他關聯附件 ID 列表)

✅ Summary of Key Categories

分類欄位範例說明
Identificationfile_name, file_type, version唯一識別與版本控制
Storagestorage_provider, file_path, checksum儲存位置與安全性
Linkageuploaded_by, engagement_id上傳人與案件對應
Classificationdocument_category, tags檔案類別與標籤
Version Controlchecked_out_by, previous_document_id追蹤修改歷史
Audit Trailcreated_at, updated_at稽核可追蹤性

Would you like me to provide next the folders (虛擬目錄/檔案夾) table field list — for managing hierarchical storage of these documents inside the audit system (e.g., C-3 應收函證底稿/附件)?

3.7  Confirmation 函證

函證Table Design Specification

函證Table Design Specification
the confirmations（函證控制表 / Audit Confirmations） table is one of the most critical components in an audit system, because it tracks requests, replies, follow-ups, discrepancies, and audit evidence for every confirmation process (應收、銀行、投資、負債等).

Below is a professional, standardized field list (no SQL) — modeled after ISA 505 and practical CaseWare / TeamMate+ / Evershine AIS implementations.

🧱 Core Identification

  • id
  • engagement_id (所屬查核案件)
  • entity_id (客戶公司，可由 engagement 推得)
  • confirmation_code (CONF-2025-001)
  • subject_area (bank / receivable / payable / investment / loan / legal / other)
  • title (函證主旨，例如「2025年度應收帳款函證」)
  • description (補充說明)

🧩 Counterparty Information

  • counterparty_id (對象ID；連結 counterparties 表)
  • counterparty_name
  • counterparty_type (customer / bank / vendor / lawyer / investor)
  • contact_person
  • contact_email
  • contact_phone
  • mailing_address (如實體函證)
  • country_code

💰 Financial Details

  • account_item (函證科目：應收帳款、銀行存款、長期借款等)
  • account_code (對應試算表科目代碼)
  • amount_per_books (帳載金額)
  • currency_code
  • is_blank_confirmation (是否空白函證)
  • related_tb_id (連結 TB 行 ID)

📬 Issuance Details

  • prepared_by_user_id
  • reviewed_by_user_id
  • sent_by_user_id
  • is_sent
  • sent_at (寄出日期)
  • delivery_method (postal / email / online_portal / bank_platform)
  • tracking_no (郵件追蹤號 / 掛號號碼)
  • delivery_proof_document_id (寄送證明文件)
  • language_code (zh/en/ja…)
  • batch_id (若批次寄發函證)

📩 Response Details

  • response_received (布林)
  • response_at
  • response_amount (對方回覆金額)
  • response_currency_code
  • response_file_id (回函附件ID)
  • response_channel (郵寄 / email / portal / 手交)
  • response_contact_person
  • response_comments (摘要回覆內容)

⚠️ Discrepancy & Exception Tracking

  • is_discrepancy_found (布林)
  • discrepancy_type (timing / classification / error / unrecorded / other)
  • discrepancy_amount
  • discrepancy_description
  • resolution_action (後續處理方式)
  • resolved_by_user_id
  • resolved_at
  • resolution_status (pending / in_progress / resolved / closed)

🔁 Follow-up & Tracking

  • follow_up_count
  • last_follow_up_at
  • next_follow_up_due_at
  • follow_up_notes
  • follow_up_by_user_id
  • status (pending / sent / no_response / re_sent / confirmed / discrepancy_found / resolved)

🔗 Linkages

  • workpaper_id (函證底稿 C-3 等)
  • finding_id (若衍生查核議題)
  • document_count (相關附件數)
  • related_confirmation_id (若同一對象多次寄函)
  • (正式多對多關聯透過 confirmation_documents、confirmation_logs、confirmation_wp_links)

📊 Metrics & Analytics

  • turnaround_days (回函所需天數)
  • response_delay_days
  • variance_to_books (response_amount − amount_per_books)
  • variance_pct (%差異)
  • risk_weight (系統計算，供Dashboard排序)

🧾 Workflow & Status

  • stage (draft / prepared / reviewed / sent / replied / resolved / finalized)
  • approved_by_user_id
  • approved_at
  • locked_at (封存)
  • is_active
  • archived_at

🕓 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by
  • tags (JSON，如 [“bank”,”AR”,”2025Q1″])
  • notes (自由備註)

✅ Summary Table

分類欄位範例功能重點
Identityconfirmation_code, subject_area, title唯一識別與分類
Counterpartycounterparty_id, contact_email收函對象與聯絡資訊
Financialsaccount_item, amount_per_books, response_amount帳載金額與回覆金額比較
Issuancesent_at, delivery_method, batch_id寄發資訊與批次管理
Responseresponse_received, response_file_id回函狀態與附件
Discrepancydiscrepancy_amount, resolution_status異常追蹤與處理
Follow-upfollow_up_count, status追蹤次數與目前進度
Linkagesworkpaper_id, finding_id, document_count串接底稿、議題、附件
Audit Trailcreated_at, updated_by稽核追蹤性

💡 Best Practice Tips

  • 每筆函證紀錄應唯一對應 counterparty_id + account_item + period。
  • 若同一對象寄多封函，可用 related_confirmation_id 串連歷史。
  • 建立子表：
    • confirmation_documents → 存放寄出/回覆附件。
    • confirmation_logs → 紀錄追蹤歷程（寄發、再發、致電、回覆）。
  • 建議在系統中設定自動狀態轉換（寄出→未回→再發→回覆→完成）。

Would you like me to next outline the confirmation_logs (函證追蹤紀錄 Table) fields — which records every resend, call, or status update for each confirmation?

3.8查核發現/議題Finding Table

Here’s a concise, practical field list for a findings / audit_issues table (no SQL) — tuned for audit workflows, reporting, and traceability:

🧱 Core Identity

  • id
  • engagement_id
  • code (e.g., F-2025-012)
  • title
  • description (issue statement / what was found)

🧩 Classification

  • finding_type (control_deficiency / significant_deficiency / material_weakness / misstatement / compliance / fraud_risk / process_issue)
  • area_code (cash / AR / inventory / revenue …)
  • assertions (JSON: existence, completeness, valuation, rights/obligations, presentation)
  • standards_ref (ISA/PCAOB/GAAP refs; free text or JSON)
  • severity (low / medium / high)
  • likelihood (rare / possible / likely)
  • risk_rating (computed or assigned)
  • is_kam (key audit matter flag)

📊 Impact & Quantification

  • amount_per_books (if applicable)
  • estimated_misstatement (amount/FX)
  • materiality_band (below PM / >PM / clearly trivial)
  • qualitative_impact (disclosure/controls/reputation)

🧠 Root Cause & Evidence

  • root_cause_summary
  • evidence_summary (what supports the finding)
  • evidence_refs (JSON list of document ids / anchors, if you want quick denorm)

🛠 Management Response & Remediation

  • management_response
  • remediation_plan
  • owner_user_id (responsible person)
  • due_date
  • remediation_status (planned / in_progress / implemented / verified)
  • verified_by (user id)
  • verified_at

🔗 Linkage (handled via junction tables, but store hints if needed)

  • primary_workpaper_index (e.g., “C-3”)
  • related_account_codes (JSON array)
  • (formal relations via) workpaper_findings, finding_documents, risk_workpapers

✅ Workflow / Status

  • status (open / under_review / resolved / closed)
  • opened_at
  • closed_at
  • escalation_level (team/manager/partner/EQCR)

👥 Provenance (Who/Where Found)

  • reported_by_user_id
  • source (test_of_controls / substantive_test / analytics / confirmation / walkthrough / external / whistleblower)
  • test_reference (procedure step id / name)

🧾 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by
  • tags (free-form labels)
  • notes

💡 Optional Enhancements

  • recurrence_flag (repeat finding from prior year)
  • prior_year_ref (link/code to last year’s similar finding)
  • disclosure_required (Y/N; drives FS note or Mgm’t Letter)
  • letter_section (which paragraph in management letter)
  • kpi_impact (process metric affected)
  • custom_fields (JSON for firm-specific needs)

This set supports: clear issue statements, risk/impact quantification, traceable evidence, remediation accountability, and reporting (e.g., management letter, KAMs, dashboards).

3.9 Risk 風險項目 Table

the risks (風險項目 / audit risks) table is the cornerstone of your audit planning and risk-response linkage.
Below is a professional and structured field list (no SQL) designed for an audit / assurance system such as Evershine AIS or CaseWare / TeamMate+ equivalents.

🧱 Core Identification

  • id
  • engagement_id
  • code (R-2025-001 or AUTO-GEN)
  • title (risk short name, e.g., “Revenue cut-off risk”)
  • description (詳細描述：what could go wrong / 為何為風險)

🧩 Classification / Typology

  • risk_category (financial_statement / control / operational / compliance / fraud / IT)
  • risk_type (inherent / control / residual)
  • assertions (JSON: existence, completeness, valuation, rights, presentation)
  • financial_area (cash / receivable / inventory / revenue / expense)
  • process_name (sales / procurement / payroll …)
  • related_account_codes (JSON array of TB accounts)
  • related_standards (ISA/PCAOB references, if used)

📊 Risk Assessment

  • inherent_risk (low / medium / high)
  • control_risk (low / medium / high)
  • detection_risk (low / medium / high)
  • residual_risk (auto-computed or assigned)
  • risk_score (numeric 1–9 or 1–100)
  • is_significant_risk (boolean; per ISA 315)
  • is_fraud_risk (boolean)
  • is_key_audit_matter (boolean)
  • materiality_impact (qualitative: above / below PM / clearly trivial)
  • likelihood (rare / possible / likely)
  • impact (minor / moderate / severe)

🧠 Source & Rationale

  • identified_by_user_id
  • identified_at
  • risk_origin (walkthrough / analytical_review / prior_year / management_inquiry / AI_detected)
  • rationale (why assessed this way)
  • related_test_reference (if derived from a procedure)

🔗 Linkages (via junction tables)

(These are implemented elsewhere, but keep summary fields for convenience)

  • primary_workpaper_index (e.g., “C-2”)
  • linked_workpaper_count
  • linked_procedure_count
  • linked_finding_count

(Formal relations come from tables: risk_workpapers, risk_procedures, workpaper_findings)

🛠 Response Planning

  • response_strategy (design_test / increase_sample / rely_on_controls / substantive_only)
  • planned_procedures_summary
  • responsible_user_id
  • due_at (completion target for testing)
  • status (open / in_progress / tested / closed)
  • tested_by_user_id
  • tested_at
  • result_summary (test conclusion)

🧾 Review & Approval

  • reviewed_by_user_id
  • reviewed_at
  • approved_by_user_id
  • approved_at
  • comments

🕓 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by
  • tags (free labels or key words)
  • custom_fields (JSON for firm-specific extensions)

✅ Summary Grouping

類別欄位範例功能重點
Identitycode, title, description唯一識別與說明
Classificationrisk_category, assertions, financial_area定義風險類型與範圍
Assessmentinherent_risk, residual_risk, is_significant_risk量化風險等級
Rationaleidentified_by_user_id, rationale追蹤風險來源與判斷依據
Responseresponse_strategy, status, result_summary規劃與測試結果
Audit Trailcreated_at, updated_at稽核追蹤性

Would you like me to next outline the Audit Procedures / Testing Table field list — so the risk-response linkage (Risk → Procedure → Workpaper) is fully mapped?

3.10 Procedure 查核程序Table

you’re now reaching the core execution layer of the audit system — the procedures (查核程序 / audit_procedures) table.
This table links your risk assessment → workpapers → findings chain and defines what each auditor actually did and concluded.

Here’s a clean, structured field list (no SQL) designed for a professional, digitalized audit system (like Evershine AIS or CaseWare / TeamMate+ equivalents):

🧱 Core Identification

  • id
  • engagement_id
  • workpaper_id (對應底稿主鍵)
  • risk_id (若屬風險導向查核，連到風險項目)
  • code (P-001 或 auto-generated)
  • title (程序名稱，例如「核對銀行對帳單餘額」)
  • description (完整步驟說明：查核目的、方法、來源)

🧩 Classification

  • procedure_type (planning / control_test / substantive / analytical / completion)
  • area_code (例如 cash / AR / inventory / revenue)
  • assertions (JSON：existence, completeness, valuation…)
  • importance_level (low / medium / high)
  • sample_type (judgmental / statistical / 100%)
  • sample_size (numeric)
  • related_account_codes (JSON array of TB accounts)
  • template_id (若由標準查核程序模板產生)

🧠 Objective & Design

  • objective (查核目標)
  • criteria (預期結果/評估準則)
  • source_of_information (資料來源，如 bank statement / GL / confirmation)
  • test_methodology (摘要，例如 sampling / recalculation / observation)

🧍 Execution Details

  • prepared_by_user_id
  • prepared_at
  • reviewed_by_user_id
  • reviewed_at
  • performed_steps (文字或 JSON，紀錄實際執行內容或子步驟)
  • evidence_reference (JSON：對應文件ID或內頁anchor)
  • test_result (pass / fail / exception / not_applicable)
  • result_summary (查核結論摘要)
  • exception_details (若發現異常，簡述內容)

📎 Linkage & Integration

  • finding_id (若此程序導致一個查核發現，可記錄主鍵)
  • document_count (系統自動計算)
  • related_procedure_ids (JSON：若多步驟互相關聯)
  • procedure_sequence (程序順序，用於排序)

🧾 Status & Workflow

  • status (not_started / in_progress / completed / reviewed / archived)
  • start_date
  • end_date
  • due_date
  • progress_pct
  • is_exception_found (布林值)
  • needs_follow_up (布林值)

📊 Analytics / Scoring (optional)

  • risk_weight (連動風險重要性)
  • confidence_level (結果信賴程度)
  • coverage_score (風險覆蓋分數)
  • impact_score (若為分析性程序可量化影響)

🧩 Documentation & Attachments

(Attachments handled via procedure_documents(procedure_id, document_id) junction)

  • primary_document_id
  • attachments_count

🧾 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by
  • tags (自由標籤，如 “函證”, “盤點”)
  • notes

✅ Summary Table

分類欄位範例功能重點
Identityid, workpaper_id, title, description明確識別與連結底稿
Classificationprocedure_type, area_code, assertions程序歸類與範疇
Executionprepared_by_user_id, test_result, result_summary執行與結論
Integrationrisk_id, finding_id, document_count串接風險、發現、附件
Workflowstatus, progress_pct, due_date控制進度與簽核
Audit Trailcreated_at, updated_at, tags稽核可追蹤性

Would you like me next to list the “Audit Planning / Risk-Response Matrix Table” (linking risks, procedures, and workpapers together)? That’s the next layer connecting your audit plan to actual testing.

3.11控制點（內部控制） Control Table

the controls (內部控制 / control points) table is key for internal control evaluation (ICS) and test of controls (TOC) within an audit or SOX environment.

Below is a well-structured field list (no SQL) designed for professional audit systems (like Evershine AIS, CaseWare IDEA, TeamMate+, or Workiva Controls).

🧱 Core Identification

  • id
  • engagement_id
  • code (CTL-2025-001 or auto-generated)
  • title (control name, e.g. “Bank reconciliation review”)
  • description (完整控制描述 — how the control operates)

🧩 Classification

  • control_category (manual / automated / IT-dependent / application / preventive / detective / compensating)
  • process_name (sales / purchasing / payroll / inventory / cash management …)
  • financial_area (cash / receivables / inventory / revenue / expense)
  • control_type (entity-level / process-level / transaction-level)
  • assertions (JSON: existence, completeness, accuracy, cutoff, classification …)
  • control_frequency (daily / weekly / monthly / quarterly / annual / continuous)
  • control_owner_department
  • control_owner_user_id

🧠 Design & Objective

  • control_objective (why the control exists — prevents/detects what)
  • control_activity (how it is performed)
  • key_control (boolean — 是否為關鍵控制 KCA)
  • design_effectiveness (effective / deficient / needs_improvement)
  • implementation_date
  • last_updated_at (if client modified control)

🧩 Risk Linkage

  • risk_id (主要對應風險項目)
  • related_risk_codes (JSON array; 多風險時)
  • residual_risk_level (low / medium / high — after control applied)

🧪 Testing & Evaluation

(usually performed via a separate “control_tests” or “test_of_controls” table, but these summary fields help dashboards)

  • last_test_date
  • last_tested_by_user_id
  • test_result (pass / fail / exception / not_tested)
  • exception_count
  • deficiency_summary (short text)
  • remediation_required (boolean)
  • remediation_status (open / in_progress / closed)

📎 Documentation

  • control_evidence_summary (描述控制證據，如簽核文件、系統紀錄等)
  • related_document_count (系統統計)
  • (formal attachments via) control_documents(control_id, document_id)

🔒 Assessment & Review

  • assessed_by_user_id
  • assessed_at
  • reviewed_by_user_id
  • reviewed_at
  • overall_assessment (effective / partially_effective / ineffective)
  • comments

🧾 Workflow & Status

  • status (draft / in_design / implemented / tested / effective / deficient / retired)
  • priority (low / medium / high)
  • due_at (for remediation follow-up)
  • is_active
  • archived_at

🔗 Integration / Linkage

  • workpaper_id (若控制測試掛在特定底稿)
  • procedure_count
  • finding_count
  • document_count

(formal M↔N via tables: control_tests(control_id, procedure_id)、workpaper_findings 等)

🕓 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by
  • tags (自由標籤，如 “SOX”, “Key Control”, “Walkthrough”)
  • custom_fields (JSON for firm-specific attributes)

✅ Summary Table

類別欄位範例功能重點
Identitycode, title, description定義控制點基本資料
Classificationcontrol_category, process_name, frequency控制類型與頻率
Design & Objectivecontrol_objective, key_control, design_effectiveness確認控制設計目的與有效性
Testing & Reviewlast_test_date, test_result, overall_assessment測試結果與評估狀況
Risk Linkagerisk_id, residual_risk_level對應風險項目
Audit Trailcreated_at, updated_at可追蹤性與歷史記錄

Would you like me to next outline the “Control Testing / Test of Controls Table” field list — the table that records each testing instance and its result for these control points?

3.12查核程序模板 Template Table

you’re now talking about one of the most strategic tables in any audit automation system:
the procedure_templates（查核程序模板表）, which serves as the library of standardized audit steps used to generate engagement-specific procedures (the audit_procedures table).

Here’s a professional and future-proof field list (no SQL), designed for systems like Evershine AIS, CaseWare Smart Templates, or TeamMate+ Methodology Libraries 👇

🧱 Core Identification

  • id
  • code (例如 TMP-AR-001 或 AUTO-GEN)
  • name (模板名稱，例如「應收帳款函證程序」)
  • description (模板說明：使用情境與目的)
  • version (模板版本號，例如 1.0 / 2025.1)
  • is_active (是否啟用)

🧩 Classification / Tagging

  • module (audit / tax / payroll / sox / review)
  • area_code (cash / AR / inventory / revenue …)
  • procedure_type (planning / control_test / substantive / analytical / completion)
  • assertions (JSON：existence, completeness, valuation …)
  • risk_category (financial_statement / control / operational …)
  • industry_code (若模板分行業，例：manufacturing / banking / real_estate)
  • applicable_standards (ISA / PCAOB / IFRS 編號或 JSON 陣列)
  • tags (JSON 或文字陣列，例如 [“函證”, “盤點”, “AR”])

🧠 Content & Design

  • objective (查核目標 / Audit Objective)
  • rationale (風險導向背景：why we perform this step)
  • detailed_steps (JSON 或 TEXT：可包含多個子步驟或問答)
  • expected_result (預期結果或評估準則)
  • sample_guideline (抽樣指引 / 依據)
  • evidence_requirements (應收集的佐證類型)
  • linked_documents (JSON: 模板附件 / 範例文件 / Word 樣板 ID)
  • template_format (word / excel / form / checklist)
  • form_schema_json (若 UI 以表單呈現，定義欄位結構)

🔗 Linkage & Integration

  • linked_risk_ids (JSON；若此模板對應標準風險)
  • linked_control_ids (JSON；對應控制點模板)
  • linked_workpaper_types (JSON；哪類底稿可引用此模板，如 C 類、D 類)
  • related_templates (JSON；前後關聯步驟)

(正式關聯使用 junction tables: procedure_templates_workpapers, procedure_templates_accounts)

⚙️ Usage Tracking / Governance

  • created_by_user_id
  • approved_by_user_id
  • approved_at
  • last_used_at
  • usage_count
  • change_log (JSON；記錄修訂歷程)
  • review_cycle_months (例：12；多久需再審核更新一次)
  • next_review_due (自動計算)

🧾 Audit Trail

  • created_at
  • updated_at
  • updated_by
  • remarks (維護者備註或建議)

🧩 Optional: Advanced Metadata

  • language_code (zh-TW / en / ja …)
  • translation_refs (多語模板對應表)
  • ai_recommendation_prompt (供 AI 自動生成查核步驟使用的提示語)
  • custom_fields (JSON；彈性擴充)

✅ Summary Table

類別欄位範例功能重點
Identitycode, name, version唯一識別與版本管理
Classificationarea_code, procedure_type, assertions程序分類與範疇
Contentobjective, detailed_steps, expected_result標準化查核內容
Integrationlinked_risk_ids, linked_workpaper_types與風險、底稿連結
Governanceapproved_by_user_id, usage_count, review_cycle_months模板維護與品質控制
Audit Trailcreated_at, updated_at修改追蹤與歷史管理

💡 Purpose Summary:
This table is your methodology library — every new engagement pulls procedures from here into the audit_procedures table. It ensures consistency, traceability, and efficiency in risk-based audit execution.

Would you like me to next outline the “Test of Controls / Control Testing Table” (which records each test performed against these control points)?

3.13 會計科目Account Table

Here’s a concise, practical field list for a Chart of Accounts – accounts table (no SQL), tailored for audit/ERP integration and multi-entity use:

🧱 Core Identity

  • id
  • entity_id (所屬公司/客戶)
  • code (會計科目代碼，支援分段如 1101-01-001)
  • name (本地語言名稱)
  • name_en / name_ja (可選，多語)

🧩 Classification & Presentation

  • account_type (asset/liability/equity/revenue/expense/other)
  • subtype (cash, ar, inventory, ap, ppe, cogs, opex…)
  • normal_balance (debit/credit)
  • fs_section (BS/PL/CF; e.g., current_assets, noncurrent_liabilities, operating_income)
  • cash_flow_class (operating/investing/financing/none)
  • tax_category (VAT code, income-tax map, WHT code)

🌳 Hierarchy (COA structure)

  • parent_id (上層科目)
  • level (層級；1=根, 2, 3…)
  • path (如 1.1.01.001 或 materialized path)
  • is_postable (可否直接過帳；否=彙總科目)
  • sort_order

🔗 Standards & Mapping

  • ifrs_map_code (或 gaap_map_code / XBRL tag)
  • group_map_code (集團/合併報表對應碼)
  • external_system_code (客戶ERP代碼，如 SAP/Oracle/QuickBooks)

💼 Policy & Controls

  • currency_code (預設幣別；多幣別時用)
  • requires_subledger (AR/AP/FA/Inventory…)
  • default_counterparty_type (customer/vendor/bank/employee/none)
  • allow_manual_post (是否允許手動分錄)
  • reconciliation_required (需對帳：銀行、往來、代收代付等)
  • materiality_band (可選：high/medium/low，用於稽核重點)

🏷 Analytics & Segments (可選)

  • segment1 … segment4 (部門、成本中心、專案、地區等維度預設/限制)
  • tags (JSON：自訂標籤)

🧾 Lifecycle

  • is_active
  • effective_from
  • effective_to
  • notes

🔍 Audit Link Hints (資料倉儲/報表便利欄位，可去正規化)

  • last_tb_period (最後一次出現在TB的期間)
  • last_activity_at
  • assertion_tags (JSON：existence/valuation/rights…—供風險/程序建議)

🧠 Operational Flags (常用特殊科目)

  • is_cash_account
  • is_bank_account
  • is_intercompany
  • is_related_party
  • is_retained_earnings
  • is_tax_account
  • is_depreciable_asset (若與固定資產模組連動)

🕓 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by

Tips

  • 金額與期間餘額不要放在 accounts，放在 trial_balances / gl_balances。
  • 若需公司群組統一科目，在本表保留 group_map_code，由 ETL 對應到集團COA。
  • 若做全文檢索/建議程序，assertion_tags、tags 很實用。

3.14試算表科目tb Table

the trial_balances（試算表科目 / TB） table sits at the heart of audit data — it’s the bridge between the client’s accounting records and your audit workpapers, risk assessments, and confirmations.

Here’s a complete and structured field list (no SQL), designed for a professional multi-entity, multi-period audit system (like Evershine AIS, CaseWare, or TeamMate+).

🧱 Core Identification

  • id
  • engagement_id (所屬查核案件)
  • entity_id (客戶公司，可由 engagement 推得)
  • account_id (對應 accounts 主表 id)
  • account_code (會計科目代碼，保留快照)
  • account_name (會計科目名稱，保留快照)

🧩 Period & Versioning

  • period (月份/季度/年度，如 FY2025 或 2025Q1)
  • period_start_date
  • period_end_date
  • version (導入版本號：如 original / adjusted / final)
  • import_batch_id (一次匯入批次的識別碼)
  • is_latest (布林值，標示最終版)

💰 Balances (numeric values)

(可依會計方向使用正數或借貸分欄)

  • opening_balance
  • current_dr
  • current_cr
  • adjustment_dr
  • adjustment_cr
  • closing_balance (期末餘額)
  • prior_year_balance (去年同期對比)
  • currency_code (幣別)
  • exchange_rate_to_group (若合併報表需換算)

🧩 Classification

  • account_type (asset/liability/equity/revenue/expense)
  • subtype (cash / ar / inventory / ap / opex …)
  • normal_balance (debit / credit)
  • fs_section (financial statement section，例如 current_assets / long_term_liabilities)
  • cash_flow_class (operating / investing / financing / none)
  • materiality_band (high / medium / low)

🔗 Linkages (for Audit Integration)

  • workpaper_id (若此TB科目掛有底稿)
  • risk_id (若為重大風險對應科目)
  • finding_count
  • confirmation_count
  • (正式關聯走 junctions，如 tb_documents, workpaper_findings, risk_workpapers)

📈 Analysis / Derived Metrics

  • variance_vs_prior (金額變動)
  • variance_pct (百分比變動)
  • ratio_to_total_assets
  • ratio_to_total_revenue
  • is_unusual_fluctuation (是否異常波動)
  • trend_score (AI/統計異常分數)

🧮 Audit Adjustments Snapshot

  • adjustment_count
  • adjusted_by_user_id
  • adjusted_at
  • final_balance_after_adjustments (系統自算)

🧾 Source & Traceability

  • source_system (SAP / QuickBooks / Oracle / Excel Import)
  • source_file_name (上傳檔名)
  • source_row_ref (原檔位置 / Sheet/Row 編號)
  • imported_at
  • imported_by_user_id
  • verified_by_user_id
  • verified_at

🧩 Consolidation (可選)

  • group_entity_code (集團公司代碼)
  • group_account_code (集團會計科目)
  • elimination_flag (是否為消除分錄)
  • consolidation_level (entity / sub-group / group)

🧾 Status & Workflow

  • status (draft / imported / verified / adjusted / finalized)
  • locked_at (封存時間)
  • is_active
  • archived_at

🕓 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by
  • notes
  • tags (JSON, 如 [“cash”,”bank”,”high_risk”])

✅ Summary Table

分類欄位範例功能重點
Identityaccount_code, account_name, engagement_id明確識別 TB 行
Balancesopening_balance, current_dr, current_cr, closing_balance期初/期末金額
Versioningversion, is_latest匯入批次與版本控制
Classificationaccount_type, fs_section, cash_flow_class對應財報結構
Integrationworkpaper_id, risk_id, finding_count串接底稿、風險、議題
Analysisvariance_vs_prior, trend_score稽核分析與異常偵測
Audit Trailimported_at, verified_at, created_at, updated_at追蹤與稽核可追溯性

💡 Best Practice Tip
在正式系統中：

  • accounts 為 靜態主檔（COA）
  • trial_balances 為 動態交易快照（年度 / 期間導入）
  • 所有後續查核模組（底稿、函證、風險、議題）都以 trial_balances.id 為「科目錨點」。

Would you like me next to list the Audit Adjustments（審計調整分錄 Table） fields — since it’s the next key layer tied directly to TB?

3.15 對象　counterparty　Table

對象（counterparty） 資料表的精簡欄位清單（無 SQL），以審計/會計實務為導向，支援客戶、供應商、銀行、投資方等多角色使用。

🧱 Core Identity

  • id
  • entity_id (所屬客戶公司/法人，便於同一客戶多年度共用對象主檔)
  • code (內部代碼，便於搜尋/對應 ERP)
  • name (對象名稱，例：台灣銀行中山分行 / XX 客戶股份有限公司)
  • legal_name (法定全名；有別名時特別重要)
  • alias_names (JSON：常用別名/英文名)

🧩 Classification

  • counterparty_type (customer / vendor / bank / lender / investor / employee / other)
  • relationship_role (AR 客戶 / AP 供應商 / 銀行往來 / 保險商 … 可多選 JSON)
  • industry_code (行業別；選用)
  • risk_category (low/medium/high；KYC or 信用風險分類)
  • is_related_party (是否關係人)

📇 Contact & Organization

  • contact_person
  • contact_title
  • contact_email
  • contact_phone
  • contact_mobile
  • website

🏠 Addresses

  • registered_address (登記地址)
  • mailing_address (通訊地址)
  • country_code
  • city
  • postal_code

🏦 Banking (常用於函證/收付款對應)

  • bank_name
  • branch_name
  • account_holder_name
  • account_number (可拆遮罩儲存)
  • swift_bic
  • iban
  • currency_code (主要往來幣別)

🧾 Tax / Regulatory

  • tax_id (統編/稅籍號碼)
  • vat_id / gst_id (若跨國)
  • registration_no (工商/金融監管註冊編號)
  • lei_code (Legal Entity Identifier；選用)
  • kyc_status (未完成/已完成/需更新)
  • sanction_screened_at (制裁名單比對時間)
  • aml_flags (JSON：高風險地區/PEP 等標記)

💬 Communication Preferences

  • preferred_language (zh-TW/en/ja …)
  • preferred_contact_method (email/phone/portal)
  • notification_opt_in (布林；是否同意通知)

🔗 Linkage Hints / Mapping

  • external_system_code (ERP/CRM 對應碼)
  • ar_customer_code (應收對應碼；選用)
  • ap_vendor_code (應付對應碼；選用)
  • group_counterparty_code (集團層對象代碼；合併用)

🧠 Audit / Confirmation Helpers

  • default_confirmation_channel (postal/email/portal/bank-platform)
  • blank_confirmation_allowed (是否允許空白函證)
  • last_confirmation_at
  • last_response_status (received/no_response/discrepancy)

🔍 Data Quality & Dedup

  • match_keys (JSON：email+phone+country 等組合鍵)
  • dedup_group_id (重複群組標記)
  • verification_score (資料完整度/一致性評分)

🔒 Compliance & Privacy

  • data_protection_level (GDPR/PDPA 等分類)
  • consent_obtained_at
  • retention_policy (保存年限/刪除策略)

🧾 Lifecycle & Audit Trail

  • status (active/inactive/blocked)
  • effective_from / effective_to
  • notes
  • tags (JSON 標籤)
  • created_at / created_by
  • updated_at / updated_by

✅ 小建議

  • 唯一性：同一 entity_id 下，維持 name 或 code 唯一；或用 (entity_id, name, country_code) 做複合唯一。
  • 分拆主檔與往來檔：主檔放固定資料（名稱/稅號/地址），年度往來資訊（交易金額、餘額）放在 AR/AP 或 TB 關聯表。
  • 函證整合：與 confirmations 連結時，直接用 counterparty_id；空白函證、聯絡管道可從本表預設帶入。

需要的話，我也可以列出 Counterparty↔Engagement / TB / Confirmations 的常用關聯欄位與報表查詢示例。

3.16客戶實體（entity）Table

the entities（客戶實體 / 客戶公司主檔） table is the foundation of your entire multi-client audit system — every engagement, trial balance, counterparty, and document links back here.

Below is a professional and future-proof field list (no SQL) suitable for a multi-jurisdictional audit platform such as Evershine AIS.

🧱 Core Identification

  • id
  • code (內部代碼，如 TW001 / CN023，可作為永輝客戶代號)
  • name (中文名稱)
  • name_en (英文名稱)
  • legal_name (法定登記名稱，如有不同)
  • short_name (簡稱，用於報表顯示)

🏢 Organizational Information

  • entity_type (corporation / partnership / branch / subsidiary / fund / trust)
  • business_registration_no (公司登記編號)
  • tax_id (統一編號 / 稅籍號碼)
  • registration_country_code (ISO country code，例如 TW、SG、US)
  • registration_city
  • incorporation_date
  • is_listed_company (布林)
  • stock_code (若為上市公司)
  • industry_code (NAICS / GICS / 中經院代碼)

💼 Group / Structure

  • group_name (母公司 / 集團名稱)
  • parent_entity_id (母公司 ID)
  • ultimate_parent_name
  • related_entity_count (系統自算)
  • is_consolidated_reporting (是否需合併報表)
  • ownership_percentage
  • control_relationship_json (JSON: 關係圖資訊)

📍 Address & Contact

  • registered_address
  • head_office_address
  • country_code
  • city
  • postal_code
  • contact_person
  • contact_title
  • contact_email
  • contact_phone
  • website

💰 Financial & Reporting Info

  • fiscal_year_end
  • currency_code (功能幣)
  • consolidation_currency_code
  • reporting_framework (IFRS / US GAAP / ROC GAAP / J-GAAP)
  • auditor_last_year
  • engagement_fee_currency
  • engagement_fee_estimate

🔒 Compliance / Regulatory

  • aml_kyc_status (not_started / in_progress / completed)
  • aml_screened_at
  • aml_risk_rating (low / medium / high)
  • is_pep_related (政治公職人物關聯)
  • data_privacy_level (PDPA / GDPR category)
  • license_numbers_json (若涉及專業執照：醫療、金管會等)

🌍 Operational Details

  • employee_count
  • annual_revenue
  • business_description
  • main_products_services
  • operation_countries (JSON: 多國營運清單)
  • erp_system_name (SAP / Oracle / QuickBooks / Excel / Others)
  • bookkeeping_provider (內部 / 客戶自行 / 永輝)

🔗 Integration / Linkage

  • primary_contact_user_id (對應 users.id — 永輝負責人)
  • client_portal_account_id (客戶端登入帳號)
  • external_system_code (ERP/CRM 對應代號)
  • default_storage_path (雲端檔案路徑，例如 S3 key prefix)

🧾 Status & Workflow

  • status (active / inactive / prospect / terminated)
  • onboarded_at
  • offboarded_at
  • is_confidential_client (布林)
  • archived_at
  • tags (JSON：可標註「跨國」、「IPO」、「SOX」等)

🕓 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by
  • notes

✅ Summary Table

分類欄位範例功能重點
Identitycode, name, tax_id, registration_country_code客戶唯一識別
Structuregroup_name, parent_entity_id集團與子公司關聯
Financialfiscal_year_end, currency_code, reporting_framework財務與報導架構
Complianceaml_kyc_status, data_privacy_level法遵與隱私要求
Contactcontact_person, contact_email, website聯絡資訊
Statusstatus, onboarded_at, archived_at客戶關係生命週期
Audit Trailcreated_at, updated_by修改與稽核追蹤

💡 Best Practice Tips

  • Keep code globally unique (e.g., TW001)，以利跨模組、跨國系統同步。
  • entity serves as the root anchor for all dependent data tables:
    • Engagements, Trial Balances, Counterparties, Documents, Findings, Risks, etc.
  • Include AML/KYC & GDPR/PDPA fields early — regulators increasingly require evidence of due diligence.

Would you like me next to outline the “Engagement–Entity–Group Structure” relationship design (showing how parent/child entities and multi-company engagements are linked)?

4.1簽核記錄 sign_offs Table

Here’s a clean, practical field list for a sign_offs (簽核記錄) table—no SQL, grouped for real-world audit needs (multi-level sign-off, e-signature, traceability):

🧱 Core Identity

  • id
  • signoff_code (optional human ref, e.g., SO-2025-001)

🔗 Link Targets (what is being signed)

  • engagement_id
  • workpaper_id (most common)
  • document_id (if signing a specific file/report)
  • procedure_id (if step-level sign-off)
  • finding_id (if approving a finding)
  • confirmation_id (if approving a confirmation batch)

(Keep only what you use; others nullable.)

👤 Signer & Role

  • user_id
  • role (preparer / reviewer / manager / partner / eqcr / approver)
  • sequence_no (1,2,3… for ordered workflows)
  • required (bool; is this approval mandatory?)

✅ Status & Outcome

  • status (pending / signed / rejected / revoked / superseded)
  • signed_at
  • due_at (SLA/期限)
  • reject_reason
  • comments

🖊️ E-Signature & Integrity

  • signature_type (drawn / typed / certificate / stamp)
  • signature_image_url (if kept)
  • signature_name (printed name on certificate)
  • signature_cert_issuer (CA / IdP)
  • signature_cert_serial
  • signature_algo (RSA/ECDSA, hash algo)
  • doc_hash_at_sign (SHA-256 of signed artifact / PDF byte range)
  • sign_context_json (JSON: UI version, viewer, clause text, etc.)

🌐 Device / Session Forensics

  • ip_address
  • user_agent
  • geo_hint (city/country)
  • mfa_used (bool)
  • session_id

🔁 Delegation / Escalation

  • delegated_from_user_id (if signer acted for someone)
  • escalated_from_user_id
  • escalated_at
  • reminder_count
  • last_reminder_at

📎 Attachments (if sign-off includes artefacts)

  • attachment_count
  • (formal files via junction: signoff_documents(signoff_id, document_id))

📊 Denormalized Hints (optional for dashboards)

  • workpaper_index_code (e.g., C-3)
  • object_title (cached title of what was signed)
  • turnaround_minutes (signed_at − assigned time)

🧾 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by
  • voided_at (if invalidated)
  • tags (JSON; e.g., [“EQCR”,”urgent”])

🔐 Best-practice notes

  • Store doc_hash_at_sign to ensure non-repudiation (what exactly was approved).
  • Use sequence_no + required to model complex flows (e.g., Preparer→Reviewer→Partner→EQCR).
  • Keep targets flexible: one sign_offs table can serve workpapers, reports, findings—just link the relevant ID(s).

4.2調整分錄 adjustments Table

the adjustments（調整分錄 / audit adjustments） table is the backbone of audit adjustments, reclassifications, and audit journal entries — directly linked to your trial balance (TB) and workpapers.

Below is a professional, structured field list (no SQL) suitable for audit systems like Evershine AIS, CaseWare, or TeamMate+.

🧱 Core Identification

  • id
  • engagement_id (所屬查核案件)
  • entity_id (所屬公司，可由 engagement 推得)
  • adjustment_code (例如 AJ-2025-001)
  • description (分錄摘要 / 調整原因)

🧾 Entry Classification

  • adjustment_type (proposed / final / reclassification / prior_period / consolidation / tax_adjustment / error_correction)
  • source_type (manual / imported / auto_generated)
  • category (audit_adjustment / management_adjustment / consolidation / elimination)
  • is_posted (是否已入帳 TB 最終版)
  • is_reversing (是否期初沖回)
  • reversal_period (沖回期間)

💰 Line-level Breakdown (for each affected account)

(通常另建 adjustment_lines 子表，但主表可留摘要欄)

  • total_dr_amount
  • total_cr_amount
  • net_effect (正負代表增減損益)
  • tb_effect_json (JSON：簡化版科目影響摘要，如 [{account_code:1101, dr:10000}, {account_code:4001, cr:10000}])

🧩 Links & Context

  • workpaper_id (若此分錄源自特定底稿，例如 C-3 應收調整)
  • finding_id (若因查核發現而調整)
  • risk_id (若與特定風險對應)
  • proposed_by_user_id
  • approved_by_user_id
  • approved_at

🧠 Rationale & Impact

  • rationale (調整依據 / 支援分析摘要)
  • supporting_evidence (簡述或文件連結 ID)
  • fs_impact_area (affected FS line item，如 Assets / Liabilities / Revenue)
  • materiality_impact (high / medium / low)
  • adjustment_reason_code (如「cut-off」、「classification」、「error correction」)
  • assertion_affected (existence / valuation / completeness …)

📎 Reconciliation / Audit Linkage

  • linked_tb_ids (JSON array; 對應 TB 行 ID)
  • linked_document_count (系統統計)
  • (正式使用 junction table：adjustment_lines(adjustment_id, tb_id, dr_amount, cr_amount))

🧾 Workflow & Status

  • status (draft / proposed / reviewed / approved / posted / rejected)
  • prepared_at
  • reviewed_at
  • posted_at
  • locked_at (封存防修改)
  • is_active (是否有效)

🔍 Posting & Accounting Metadata

  • journal_number (若匯出至 ERP)
  • posting_date
  • prepared_in_system (audit / ERP / Excel)
  • currency_code
  • exchange_rate (若跨幣別)

📊 Analytical Fields

  • variance_vs_prior (同類調整與去年比較)
  • variance_to_materiality (佔重要性比率)
  • impact_on_profit (淨利影響額)
  • impact_on_equity (權益影響額)

🧾 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by
  • notes (補充說明)
  • tags (JSON，如 [“AR”,”cutoff”,”audit_adjustment”])

✅ Summary Table

類別欄位範例功能重點
Identityadjustment_code, engagement_id唯一識別與案件歸屬
Classificationadjustment_type, category, is_posted分類與狀態控制
Amountstotal_dr_amount, total_cr_amount調整金額概要
Linkagesworkpaper_id, finding_id, risk_id串接底稿、發現與風險
Rationalerationale, fs_impact_area調整依據與報表影響
Workflowstatus, approved_by_user_id審核與簽核流程
Audit Trailcreated_at, updated_by稽核追蹤性

💡 Best Practice Tips

  • 建立子表 adjustment_lines 以支援多科目借貸：
  • adjustment_lines(adjustment_id, tb_id, account_code, dr_amount, cr_amount, memo)
  • 主表保留總覽資訊（摘要、狀態、簽核），子表負責金額明細與 TB 鍊結。
  • 若需匯出 ERP，可儲存 journal_number 與 posting_date。

Would you like me to list next the adjustment_lines (調整分錄明細 Table) field suggestions — to complete the full audit journal entry structure?

4.3查核計畫表（Audit Plan）Table

the audit_plans（查核計畫表 / Audit Plan） table is the strategic layer connecting risks, procedures, materiality, and resource allocation.
It represents the planning phase of the audit life cycle, before actual testing begins.

Below is a professional, structured field list (no SQL) — aligned with ISA 300, ISA 315, and CaseWare / TeamMate+ methodologies, tailored for Evershine AIS.

🧱 Core Identification

  • id
  • engagement_id (所屬查核案件)
  • plan_code (AP-2025-001)
  • title (查核計畫名稱，如「2025年度財報查核計畫」)
  • description (查核計畫總說明)

🧩 Planning Context

  • planning_date (編制日期)
  • prepared_by_user_id
  • reviewed_by_user_id
  • approved_by_user_id
  • approved_at
  • status (draft / reviewed / approved / finalized)
  • version (修訂版本號)
  • is_active

🧾 Scope & Objective

  • scope_description (查核範圍：財報項目、分公司、期間等)
  • audit_objectives (主要查核目標，可 JSON 陣列)
  • framework (準據標準：IFRS / ROC GAAP / US GAAP)
  • financial_statement_areas (JSON：現金、應收、庫存、收入等)
  • in_scope_entities (JSON：合併報表內的子公司清單)
  • excluded_areas (JSON 或文字)

📊 Materiality & Risk Summary

  • overall_materiality
  • performance_materiality
  • clearly_trivial_threshold
  • risk_summary_json (摘要主要重大風險與對應區域)
  • high_risk_area_count
  • significant_risk_count
  • fraud_risk_count

🧩 Audit Strategy & Approach

  • audit_strategy (risk-based / controls-reliant / substantive-heavy / hybrid)
  • planned_approach_summary (整體查核方法摘要)
  • key_audit_matters_summary (主要查核重點 KAM 摘要)
  • use_of_analytics (布林；是否使用資料分析)
  • use_of_specialists (布林；是否依賴專家，如評價/稅務)
  • specialist_details (JSON：專家清單)
  • it_audit_involvement (是否需IT稽核支援)

👥 Team & Resource Planning

  • partner_id
  • manager_id
  • lead_auditor_id
  • team_member_count
  • team_assignment_json (JSON：成員與角色分派)
  • budgeted_hours_total
  • budgeted_hours_fieldwork
  • budgeted_hours_review
  • budgeted_fee
  • timeline_start
  • timeline_end
  • milestone_json (主要里程碑與截止日)

🔗 Linkages (to other audit objects)

  • linked_risk_ids (JSON 或關聯表)
  • linked_workpaper_ids (JSON 或關聯表)
  • linked_control_ids
  • linked_procedure_ids
  • linked_finding_ids
    (正式多對多關聯可透過 audit_plan_links(plan_id, object_type, object_id))

🧮 Analytics & Scoring

  • risk_score_avg
  • coverage_score_avg
  • materiality_utilization_pct (已使用的重要性百分比)
  • progress_pct (整體查核完成率，可動態更新)

🧾 Review & Updates

  • last_reviewed_at
  • last_reviewed_by_user_id
  • revision_reason
  • change_log_json (修訂歷程)
  • next_update_due_at (計畫更新時限)

🕓 Audit Trail

  • created_at
  • created_by
  • updated_at
  • updated_by
  • tags (JSON：如「interim」「final」「SOX」)
  • notes (備註)

✅ Summary Table

分類欄位範例功能重點
Identityplan_code, title明確識別與版本控制
Scopescope_description, framework查核範圍與依據
Materiality & Riskoverall_materiality, risk_summary_json定義重要性與重大風險
Strategyaudit_strategy, key_audit_matters_summary整體查核策略與重點
Team & Resourceteam_assignment_json, budgeted_hours_total人力與時程規劃
Linkageslinked_risk_ids, linked_workpaper_ids串接風險、底稿、程序
Audit Trailstatus, version, approved_by_user_id稽核追蹤與簽核

💡 Best Practice Tips

  • audit_plans 是「主檔」：實際執行的查核程序(audit_procedures)、風險(risks)、控制(controls)都應鏈結回此計畫。
  • 建議在 UI 端提供「版本快照」功能，以保存各階段查核計畫。
  • 若具多國/多子公司案件，可在 in_scope_entities 內保留跨公司結構。

Would you like me to next outline the “Audit Plan – Risk – Procedure linkage structure”, showing how these three tables connect in practice?

4.4品管審核（EQCR）與二階複核 Table

you’re now reaching the quality control layer of the audit workflow — the Engagement Quality Control Review (EQCR) and Second Review table.
This module ensures audit quality, independence, and final reporting integrity, in line with ISA 220, ISQC 1, or IAASB’s ISQM 1 standards.

Below is a structured, professional field list (no SQL) suitable for a firm-level audit management system like Evershine AIS, CaseWare, or TeamMate+.

🧱 Core Identification

  • id
  • engagement_id (所屬查核案件)
  • eqcr_code (EQCR-2025-001)
  • title (案件或審核階段名稱，例如「2025年度財報查核 EQCR」)
  • review_type (eqcr / second_review / cold_review / hot_review)

🧩 Assignment & Roles

  • eqcr_user_id (品管複核人)
  • review_manager_id (二階複核經理，如與 EQCR 不同)
  • assigned_by_user_id (指定人)
  • assigned_at
  • started_at
  • completed_at
  • status (not_started / in_progress / completed / finalized)

📋 Scope & Coverage

  • scope_description (審核範圍說明，例如涵蓋哪些報告、底稿、風險項目)
  • review_objectives (主要審核目標，如獨立性、重要判斷、KAM、品質控制)
  • engagement_materiality (引用主計畫的重要性數值)
  • significant_judgments_json (JSON：重大判斷與核對結果)
  • documents_to_review_json (底稿清單或文件ID陣列)
  • key_areas_reviewed (JSON，如[“收入確認”,”應收帳款減損”])
  • eqcr_checklist_template_id (參考標準模板ID)

🧠 Review Process

  • review_method (desk_review / walkthrough / sampling / discussion)
  • issues_identified_count
  • issues_summary_json (JSON：每項問題與狀態)
  • recommendations_summary
  • follow_up_required (布林)
  • follow_up_due_at
  • follow_up_completed_at
  • follow_up_notes

🧾 Review Results & Conclusions

  • overall_assessment (satisfactory / needs_improvement / unsatisfactory)
  • independence_confirmed (布林)
  • independence_confirmation_date
  • quality_review_comments
  • final_conclusion (例如「已完成EQCR審閱，無進一步異議」)
  • finalized_by_user_id
  • finalized_at

🔗 Linkages

  • linked_workpaper_ids (JSON 或中介表 eqcr_workpapers)
  • linked_finding_ids (如有針對查核發現進行覆核)
  • linked_risk_ids (若針對重大風險做獨立審核)
  • linked_document_ids (審閱過的文件 / 報告)

👥 Communication & Documentation

  • review_meeting_date
  • meeting_minutes_document_id
  • comments_to_engagement_team (文字或 JSON)
  • response_from_engagement_team
  • response_status (pending / responded / resolved)
  • eqcr_signoff_user_id
  • eqcr_signoff_at
  • signoff_document_id

🧮 Scoring / KPI (optional for firm-level quality analytics)

  • review_score (0–100)
  • deficiency_count
  • resolved_deficiency_count
  • avg_resolution_days

🧾 Workflow & Audit Trail

  • status_history_json (自動紀錄狀態流轉)
  • created_at
  • created_by
  • updated_at
  • updated_by
  • tags (JSON：如[“EQCR”,”Hot Review”])
  • notes

✅ Summary Table

類別欄位範例功能重點
Identityeqcr_code, review_type, title審核識別與類型
Assignmenteqcr_user_id, assigned_by_user_id, status指派與進度
Scopescope_description, key_areas_reviewed覆核範圍與重點
Processissues_identified_count, recommendations_summary審核過程與議題追蹤
Conclusionoverall_assessment, final_conclusion, signoff_at最終結論與簽核
Audit Trailcreated_at, updated_by稽核可追溯性

💡 Best Practice Tips

  • 在系統中，EQCR 應為獨立於 Engagement Team 的使用者，不可同時為案件主要審計人員。
  • 連結底稿 / 文件 / 議題以提供完整可追蹤性。
  • 若支援 冷查核 (cold review)，可重用同一表，只增加欄位 review_phase = interim / final / post-issuance。
  • 可搭配「簽核記錄 (sign_offs)」表，自動生成 EQCR 簽核履歷。

Would you like me to next outline the EQCR Checklist Table — i.e., the standard quality review checklist each reviewer fills out (covering independence, documentation, KAM review, and conclusions)?

**Ａbove contents were wrote by Dale Chen 2025/10/21

永輝查帳系統資料庫設計參考資料

接洽我們：
永輝啟佳聯合會計師事務所- EvershineCPA.tpe
臺灣永輝協同網路服務股份有限公司- EvershineBPO.tpe
永輝專利師事務所 – EvershineIPO.tpe
臺北市長春路378號6F 靠近長春路與復興北路交叉口
捷運文湖線與松山線交口 南京復興站,兄弟大飯店附近
接洽人:
陳中成 首席合夥人/會計師/專利師
Mobile: +886-933920199  
Office: +886-2-27170515 E100  
Skype: daleccchen
Wechat ID: evershiinecpa | Line ID:evershinecpa
Linkedin address:Dale Chen Linkedin  
E-mail: dalechen@evershinecpa.com

For your Information:

Evershine在以下城市擁有100%的附屬公司：
永輝總部、臺北永輝、廈門永輝、北京永輝、上海那靈、深圳常新、紐約永輝、加州永輝、德州永輝、鳳凰城永輝、東京永輝、首爾永輝、河內永輝、越南胡志明、曼谷永輝、新加坡永輝、吉隆玻永輝、雅加達永輝、馬尼拉永輝、墨爾本永輝、澳洲雪梨、孟加拉永輝、新德里永輝、印度孟買、杜拜永輝、法蘭克福永輝、巴黎永輝、倫敦永輝、荷蘭永輝、西班牙永輝、義大利永輝、羅馬尼亞永輝、多倫多永輝、墨西哥永輝。
其他已提供中文化服務城市：
邁阿密、亞特蘭大、俄克拉荷馬、密歇根、西雅圖、特拉華；
柏林; 斯圖加特;布拉格；布加勒斯特；班加羅爾；泗水；
高雄、香港、深圳、東關、廣州、清遠、永康、杭州、蘇州、崑山、南京、重慶、許昌、青島、天津。
永輝潛在可服務城市 (2個月籌備期)：
我們為IAPA會員所,總部在倫敦，全球300個會員所，員工約1萬人。
我們為LEA會員所,總部在美國芝加哥，全球600個會員所，員工約2萬8千人。
Evershine is local Partner of ADP Streamline® in Taiwan.
(版本:2025/10)

  • 拓展國外業務－非居民公司

    請點擊

    read more

    世界布點-中文化一站式服務

    read more

    各國短期代僱工商與特許登記

    已在20+國家，50+個主要城市提供外資公司登記服務。

    read more

    各國薪資 財務 稅務合規服務

    read more

    • Top