# CPA Firm data dictionary

Sample database `cpafirm` on learnsql.cpetoday.com (SQL Server login: CPE Today course). Each table lists its columns with the type, keys, what the column means, and, for codes and statuses, the values seen in the sample copy (with row counts when the dictionary was written).
18 of 34 tables are described. Undocumented tables are listed at the end.

## clients

The firm's clients: the business or person the firm serves, with office, industry and entity type.

Grain: one row per client

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `client_id` | int | PK | Unique client ID. |  |
| `client_number` | varchar(10) |  | Client number used on files and invoices. |  |
| `client_name` | varchar(120) |  | Legal name of the client. |  |
| `trade_name` | varchar(120) |  |  |  |
| `entity_type_id` | int | FK to entity_types | Legal entity type (entity_types), such as S corporation or partnership. | 1 (380), 2 (160), 3 (160), 4 (120), 5 (60), 6 (60), 7 (60) |
| `industry_id` | int | FK to industries | Industry (industries). |  |
| `referral_source_id` | int | FK to referral_sources |  |  |
| `office_id` | int | FK to offices | Office that serves the client (offices). | 1 (568), 2 (432) |
| `partner_id` | int | FK to staff | Partner responsible for the client (staff). | 2 (484), 3 (265), 1 (251) |
| `manager_id` | int | FK to staff |  |  |
| `tax_id_last4` | char(4) |  |  |  |
| `filing_status` | varchar(30) |  |  |  |
| `fiscal_year_end_month` | tinyint |  | Month number in which the client's fiscal year ends. | 12 (964), 6 (30), 9 (6) |
| `status` | varchar(20) |  | Client status (see the values). | Active (900), Inactive (60), Prospect (40) |
| `onboarded_date` | date |  | Date the client was onboarded. |  |
| `terminated_date` | date |  | Date the relationship ended, if it did. |  |
| `annual_revenue` | decimal |  | Reported annual revenue, when known. |  |
| `employee_count` | int |  |  |  |
| `billing_terms_days` | smallint |  |  |  |
| `address_line1` | varchar(100) |  |  |  |
| `city` | varchar(60) |  |  |  |
| `state` | char(2) |  |  |  |
| `postal_code` | varchar(10) |  |  |  |
| `phone` | varchar(20) |  |  |  |
| `email` | varchar(120) |  |  |  |
| `county` | varchar(60) |  |  |  |
| `country` | char(2) |  |  |  |
| `latitude` | decimal |  |  |  |
| `longitude` | decimal |  |  |  |
| `geocode_quality` | varchar(20) |  |  |  |
| `full_address` | varchar(220) |  |  |  |

## contacts

People at a client, such as owners and bookkeepers.

Grain: one row per contact

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `contact_id` | int | PK | Unique contact ID. |  |
| `client_id` | int | FK to clients | Client (clients). |  |
| `first_name` | varchar(40) |  | First name. |  |
| `last_name` | varchar(40) |  | Last name. |  |
| `job_title` | varchar(60) |  | Job title. |  |
| `email` | varchar(120) |  |  |  |
| `phone` | varchar(20) |  |  |  |
| `is_primary` | bit |  | 1 for the main contact. | True (1000), False (529) |
| `is_billing_contact` | bit |  | 1 if the contact receives invoices. | True (1154), False (375) |

## document_requests

Documents the firm has asked a client to send, and whether they came back.

Grain: one row per request

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `document_request_id` | int | PK | Unique request ID. |  |
| `client_id` | int | FK to clients | Client (clients). |  |
| `engagement_id` | int | FK to engagements | Engagement (engagements). |  |
| `requested_by` | int | FK to staff |  |  |
| `document_name` | varchar(100) |  | Document requested. |  |
| `requested_date` | date |  | Date requested. |  |
| `due_date` | date |  | Date needed by. |  |
| `received_date` | date |  | Date received, if it arrived. |  |
| `status` | varchar(20) |  | Request status (see the values). | Received (20192), Not Applicable (608), Outstanding (83) |
| `reminder_count` | tinyint |  | Reminders sent. | 0 (9650), 1 (9558), 2 (861), 3 (783), 4 (18), 5 (13) |

## engagements

Work the firm agrees to do for a client in a tax year or period, such as a tax return or an audit. Billing and time roll up to engagements.

Grain: one row per engagement

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `engagement_id` | int | PK | Unique engagement ID. |  |
| `client_id` | int | FK to clients | Client (clients). |  |
| `service_id` | int | FK to services | Service being provided (services). |  |
| `tax_year` | smallint |  | Tax year covered. | (blank) (2931), 2025 (924), 2024 (861), 2023 (768), 2022 (722), 2021 (667), 2020 (621) |
| `period_start` | date |  |  |  |
| `period_end` | date |  |  |  |
| `fee_type` | varchar(10) |  | How the fee is set: fixed or hourly, for example. | Fixed (4583), Monthly (2155), Hourly (756) |
| `fee_amount` | decimal |  | Agreed fee. |  |
| `status` | varchar(20) |  | Engagement status (see the values). | Complete (6910), In Progress (489), Cancelled (64), Awaiting Client (15), In Review (15), On Hold (1) |
| `open_date` | date |  | Date the engagement opened. |  |
| `due_date` | date |  | Date the work is due. |  |
| `completed_date` | date |  | Date the work was completed. |  |
| `partner_id` | int | FK to staff | Responsible partner (staff). | 2 (3535), 3 (2014), 1 (1945) |
| `manager_id` | int | FK to staff | Manager on the engagement (staff). | 5 (3581), 4 (2196), 6 (1717) |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## fiscal_calendar

One row per calendar day with its fiscal year, quarter and period, and tax-season flags.

Grain: one row per calendar day

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `fiscal_calendar_id` | int | PK |  |  |
| `calendar_date` | date |  | The calendar day. |  |
| `fiscal_year` | smallint |  | Fiscal year. | 2024 (366), 2021 (365), 2022 (365), 2023 (365), 2025 (365), 2026 (365), 2027 (365) |
| `fiscal_year_label` | varchar(10) |  |  |  |
| `fiscal_quarter` | tinyint |  | Fiscal quarter. | 1 (644), 2 (644), 4 (637), 3 (631) |
| `fiscal_period` | tinyint |  | Fiscal period (month) number. | 1 (217), 2 (217), 4 (217), 6 (217), 7 (217), 9 (217), 11 (217), 3 (210), 5 (210), 10 (210), 12 (210), 8 (197) |
| `fiscal_period_name` | varchar(12) |  |  |  |
| `fiscal_period_start` | date |  |  |  |
| `fiscal_period_end` | date |  |  |  |
| `fiscal_week` | tinyint |  |  |  |
| `fiscal_day_of_year` | smallint |  |  |  |
| `fiscal_year_start` | date |  |  |  |
| `fiscal_year_end` | date |  |  |  |
| `is_period_end` | bit |  | 1 on the last day of the fiscal period. | False (2472), True (84) |
| `is_year_end` | bit |  |  |  |
| `tax_season` | varchar(20) |  | Tax season this date falls in. | Extension season (1281), Busy season (638), Off season (637) |
| `tax_deadline` | varchar(100) |  |  |  |

## gl_accounts

The firm's chart of accounts.

Grain: one row per account

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `gl_account_id` | int | PK | Unique account ID. |  |
| `account_number` | varchar(10) |  | Account number. |  |
| `account_name` | varchar(80) |  | Account name. |  |
| `account_type` | varchar(20) |  | Account type (see the values). | Expense (11), Revenue (8), Asset (3), Equity (1), Liability (1) |
| `parent_account_id` | int | FK to gl_accounts | Parent account, for sub-accounts. | (blank) (8), 6 (7), 17 (7), 14 (2) |

## gl_journal_entries

Journal entries posted by the firm's books, one row per entry.

Grain: one row per entry

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `journal_entry_id` | int | PK | Unique entry ID. |  |
| `entry_date` | date |  | Posting date. |  |
| `source_type` | varchar(20) |  | Where the entry came from (see the values). | Invoice (21282), Payment (20222), Adjustment (850), Trust (125), Operating (69), Opening (1) |
| `source_id` | int |  | ID of the source record. |  |
| `memo` | varchar(200) |  | Entry memo. |  |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## gl_journal_lines

Debit and credit lines of each journal entry. A client can be tagged on a line.

Grain: one row per journal line

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `journal_line_id` | int | PK | Unique line ID. |  |
| `journal_entry_id` | int | FK to gl_journal_entries | Entry (gl_journal_entries). |  |
| `gl_account_id` | int | FK to gl_accounts | Account (gl_accounts). |  |
| `client_id` | int | FK to clients | Client tagged on the line, if any (clients). |  |
| `debit` | decimal |  | Debit amount. |  |
| `credit` | decimal |  | Credit amount. |  |

## invoice_adjustments

Write-downs, write-offs and other changes to an invoice, approved by a partner.

Grain: one row per adjustment

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `adjustment_id` | int | PK | Unique adjustment ID. |  |
| `invoice_id` | int | FK to invoices | Invoice adjusted (invoices). |  |
| `approved_by` | int | FK to staff | Staff member who approved it. | 2 (335), 1 (328), 3 (187) |
| `adjustment_date` | date |  | Date of the adjustment. |  |
| `adjustment_type` | varchar(20) |  | Kind of adjustment (see the values). | Bad Debt (294), Discount (203), Courtesy Credit (187), Write-off (166) |
| `amount` | decimal |  | Amount of the adjustment. |  |
| `reason` | varchar(200) |  | Why it was made. | Uncollectible after repeated follow-up (294), Small balance written off (166), Fee concession after scope change (107), Billing correction (98), Client relationship adjustment (95), Early-pay discount (90) |

## invoice_lines

Lines on each invoice: the service, quantity, rate and amount.

Grain: one row per invoice line

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `invoice_line_id` | int | PK | Unique line ID. |  |
| `invoice_id` | int | FK to invoices | Invoice (invoices). |  |
| `engagement_id` | int | FK to engagements | Engagement the line bills (engagements). |  |
| `line_number` | smallint |  | Line order on the invoice. | 1 (21282), 2 (6174), 3 (880), 4 (52), 5 (1) |
| `description` | varchar(200) |  | What the line bills for. |  |
| `quantity` | decimal |  | Hours or units billed. |  |
| `unit_price` | decimal |  | Rate per unit. |  |
| `amount` | decimal |  | Line amount. |  |

## invoices

Invoices the firm sends to clients for its work.

Grain: one row per invoice

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `invoice_id` | int | PK | Unique invoice ID. |  |
| `invoice_number` | varchar(20) |  | Invoice number shown to the client. |  |
| `client_id` | int | FK to clients | Client billed (clients). |  |
| `billing_contact_id` | int | FK to contacts |  |  |
| `issued_by` | int | FK to staff |  |  |
| `issue_date` | date |  | Date the invoice was issued. |  |
| `due_date` | date |  | Date payment is due. |  |
| `status` | varchar(20) |  | Invoice status (see the values). | Paid (20330), Sent (393), Written Off (294), Partial (136), Overdue (129) |
| `total_amount` | decimal |  | Invoice total. |  |
| `fiscal_year` | smallint |  | Fiscal year of the invoice date. | 2026 (4435), 2025 (4056), 2024 (3640), 2023 (3350), 2022 (3031), 2027 (1550), 2021 (1220) |
| `fiscal_quarter` | tinyint |  | Fiscal quarter of the invoice date. | 4 (6072), 1 (5255), 2 (5078), 3 (4877) |
| `fiscal_period` | tinyint |  | Fiscal month (period) of the invoice date. | 10 (2863), 4 (2256), 3 (1972), 9 (1931), 2 (1679), 11 (1618), 1 (1604), 12 (1591), 8 (1560), 5 (1423), 6 (1399), 7 (1386) |

## offices

The firm's offices.

Grain: one row per office

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `office_id` | int | PK | Unique office ID. | 1 (1), 2 (1) |
| `office_name` | varchar(80) |  | Office name. | Main Office - Bellevue (1), Satellite Office - Portland (1) |
| `city` | varchar(60) |  | City. | Bellevue (1), Portland (1) |
| `state` | char(2) |  | State code. | OR (1), WA (1) |
| `phone` | varchar(20) |  | Phone number. | (425) 555-0100 (1), (503) 555-0140 (1) |
| `address_line1` | varchar(100) |  |  |  |
| `postal_code` | varchar(10) |  |  |  |
| `county` | varchar(60) |  |  |  |
| `country` | char(2) |  |  |  |
| `latitude` | decimal |  |  |  |
| `longitude` | decimal |  |  |  |
| `geocode_quality` | varchar(20) |  |  |  |
| `full_address` | varchar(220) |  |  |  |

## payment_applications

Applies each payment to one or more invoices.

Grain: one row per payment-to-invoice application

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `payment_application_id` | int | PK | Unique row ID. |  |
| `payment_id` | int | FK to payments | Payment applied (payments). |  |
| `invoice_id` | int | FK to invoices | Invoice paid (invoices). |  |
| `amount_applied` | decimal |  | Amount of the payment applied to the invoice. |  |

## payments

Payments clients make, one row per payment received.

Grain: one row per payment

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `payment_id` | int | PK | Unique payment ID. |  |
| `client_id` | int | FK to clients | Client who paid (clients). |  |
| `received_by` | int | FK to staff |  |  |
| `payment_date` | date |  | Date received. |  |
| `amount` | decimal |  | Amount received. |  |
| `payment_method` | varchar(20) |  | How the client paid (see the values). | ACH (8985), Check (5504), Credit Card (5282), Trust Application (442), Wire (9) |
| `reference_number` | varchar(30) |  | Check number or reference. |  |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## staff

Firm staff: partners, managers, seniors, staff and admins, with rate and office.

Grain: one row per staff member

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `staff_id` | int | PK | Unique staff ID. |  |
| `first_name` | varchar(40) |  | First name. |  |
| `last_name` | varchar(40) |  | Last name. |  |
| `email` | varchar(100) |  |  |  |
| `role_id` | int | FK to staff_roles | Role (staff_roles). | 4 (5), 1 (3), 2 (3), 3 (3), 5 (2), 6 (1) |
| `office_id` | int | FK to offices | Home office (offices). | 1 (11), 2 (6) |
| `manager_id` | int | FK to staff | Manager (staff). | (blank) (3), 5 (3), 1 (2), 7 (2), 8 (2), 2 (1), 3 (1), 4 (1), 6 (1), 9 (1) |
| `hire_date` | date |  | Start date. |  |
| `termination_date` | date |  | Leave date, if any. | (blank) (16), 2024-06-30 (1) |
| `is_cpa` | bit |  | 1 if the person is a licensed CPA. | False (9), True (8) |
| `hourly_cost` | decimal |  | Internal cost per hour. |  |

## tasks

To-do items on engagements.

Grain: one row per task

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `task_id` | int | PK | Unique task ID. |  |
| `engagement_id` | int | FK to engagements | Engagement (engagements). |  |
| `assigned_to` | int | FK to staff | Staff member (staff). |  |
| `task_name` | varchar(100) |  | What has to be done. | Manager review (4499), Partner review (4499), Collect client documents (4271), File and deliver to client (4271), Prepare return (4271), Fieldwork (228), Issue report (228), Planning and risk assessment (228) |
| `priority` | varchar(10) |  | Priority (see the values). | Normal (16721), Low (5719), High (55) |
| `status` | varchar(20) |  | Task status (see the values). | Complete (22384), Open (60), In Progress (51) |
| `due_date` | date |  | Due date. |  |
| `completed_date` | date |  | Date finished. |  |

## tax_returns

Tax returns prepared for an engagement, one row per return and jurisdiction.

Grain: one row per return

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `tax_return_id` | int | PK | Unique return ID. |  |
| `engagement_id` | int | FK to engagements | Engagement (engagements). |  |
| `client_id` | int | FK to clients | Client (clients). |  |
| `tax_year` | smallint |  | Tax year. | 2025 (1333), 2024 (1248), 2023 (1108), 2022 (1059), 2021 (972), 2020 (915) |
| `jurisdiction` | varchar(10) |  | Federal or state jurisdiction. | Federal (4271), OR (1037), CA (818), AZ (261), ID (248) |
| `form_type` | varchar(20) |  | Form filed, such as 1120S or 1065. |  |
| `due_date` | date |  | Due date. |  |
| `is_extended` | bit |  | 1 if the return was extended. | False (4055), True (2580) |
| `extended_due_date` | date |  | Due date after the extension. |  |
| `filed_date` | date |  | Date filed. |  |
| `filing_method` | varchar(10) |  |  |  |
| `status` | varchar(20) |  | Return status (see the values). | Accepted (6005), Filed (556), In Review (49), Preparing (25) |
| `preparer_id` | int | FK to staff |  |  |
| `reviewer_id` | int | FK to staff |  |  |
| `total_income` | decimal |  | Total income on the return. |  |
| `refund_or_balance_due` | decimal |  | Refund (negative) or balance due (positive). |  |

## time_entries

Hours staff record against engagements. Billable hours are the ones invoiced.

Grain: one row per time entry

| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
| `time_entry_id` | int | PK | Unique time entry ID. |  |
| `engagement_id` | int | FK to engagements | Engagement the time is for (engagements). |  |
| `staff_id` | int | FK to staff | Person who worked (staff). |  |
| `invoice_id` | int | FK to invoices | Invoice the time was billed on, if billed (invoices). |  |
| `work_date` | date |  | Date the work was done. |  |
| `hours` | decimal |  | Hours worked. |  |
| `is_billable` | bit |  | 1 when the time can be billed to the client. | True (68295), False (1749) |
| `bill_rate` | decimal |  | Rate charged per hour. |  |
| `cost_rate` | decimal |  | Internal cost per hour. |  |
| `description` | varchar(200) |  | What was done. |  |
| `fiscal_year` | smallint |  |  |  |
| `fiscal_quarter` | tinyint |  |  |  |
| `fiscal_period` | tinyint |  |  |  |

## Undocumented tables

`client_relationships`, `client_visits`, `dataset_info`, `engagement_staff`, `entity_types`, `esign_requests`, `geo_places`, `geo_postal_codes`, `geo_states`, `industries`, `referral_sources`, `service_lines`, `services`, `staff_roles`, `tax_organizers`, `trust_transactions`
