Downloads
Practice management

CPA Firm

A small-business CPA firm: clients, engagements, tax returns, time entries, invoices, payments, retainers, a balanced general ledger and document tracking. About 1,000 clients, six years of history, a client map and a July-June fiscal calendar.

cpafirmSQL ServerMariaDB 34 tables
invoice_linesinvoicesclientsengagementstime_entries

Read the guide

CPA firm practice management (cpafirm): Clients, engagements, tax returns, time, billing, trust and a general ledger for a small-business CPA firm, with realization, WIP and AR aging queries.

Good for

Realization, WIP and AR agingStaff utilizationMapping clients and visitsFiscal-year and tax-season reporting

Questions to try

  • Which service lines earned the most?
  • Who owes the most, and how old is it?
  • What is each partner's realization?
  • How much do we bill in busy season?

Write each as a query in the SQL editor, or ask an AI assistant to walk you through it.

Connect to it

Server learnsql.cpetoday.com,1433, database cpafirm, in Excel or Power BI (Import mode) or DBeaver. These tables are in the dbo schema, so once you have picked the database you can use plain names such as client_relationships.

Download the Excel connection (.odc)

How to use it: download the file and open it. Excel opens it as a connection to cpafirm and asks for the SQL Server username and password from your CPE Today course. The file holds no password. The first table is selected by default, and you can pick others from the Data window. Setup details for other tools are on Connect.

Tables (34)

Click a table to preview it in the Playground.

Data dictionary

What each column means, its type and keys, and for codes and statuses the values seen in this copy. 18 of 34 tables are described so far. Read it as Markdown.

Click a table in the diagram above to jump to it.

clients one row per client 31 columns

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

ColumnTypeKeyMeaningValues 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) Not described yet
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 Not described yet
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 Not described yet
tax_id_last4 char(4) Not described yet
filing_status varchar(30) Not described yet
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 Not described yet
billing_terms_days smallint Not described yet
address_line1 varchar(100) Not described yet
city varchar(60) Not described yet
state char(2) Not described yet
postal_code varchar(10) Not described yet
phone varchar(20) Not described yet
email varchar(120) Not described yet
county varchar(60) Not described yet
country char(2) Not described yet
latitude decimal Not described yet
longitude decimal Not described yet
geocode_quality varchar(20) Not described yet
full_address varchar(220) Not described yet
contacts one row per contact 9 columns

People at a client, such as owners and bookkeepers.

ColumnTypeKeyMeaningValues 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) Not described yet
phone varchar(20) Not described yet
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 one row per request 10 columns

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

ColumnTypeKeyMeaningValues 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 Not described yet
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 one row per engagement 17 columns

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.

ColumnTypeKeyMeaningValues 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 Not described yet
period_end date Not described yet
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 Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
fiscal_calendar one row per calendar day 17 columns

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

ColumnTypeKeyMeaningValues seen
fiscal_calendar_id int PK Not described yet
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) Not described yet
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) Not described yet
fiscal_period_start date Not described yet
fiscal_period_end date Not described yet
fiscal_week tinyint Not described yet
fiscal_day_of_year smallint Not described yet
fiscal_year_start date Not described yet
fiscal_year_end date Not described yet
is_period_end bit 1 on the last day of the fiscal period. False 2472 True 84
is_year_end bit Not described yet
tax_season varchar(20) Tax season this date falls in. Extension season 1281 Busy season 638 Off season 637
tax_deadline varchar(100) Not described yet
gl_accounts one row per account 5 columns

The firm's chart of accounts.

ColumnTypeKeyMeaningValues 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 one row per entry 8 columns

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

ColumnTypeKeyMeaningValues 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 Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
gl_journal_lines one row per journal line 6 columns

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

ColumnTypeKeyMeaningValues 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 one row per adjustment 7 columns

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

ColumnTypeKeyMeaningValues 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 one row per invoice line 8 columns

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

ColumnTypeKeyMeaningValues 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 one row per invoice 12 columns

Invoices the firm sends to clients for its work.

ColumnTypeKeyMeaningValues 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 Not described yet
issued_by int FK to staff Not described yet
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 one row per office 13 columns

The firm's offices.

ColumnTypeKeyMeaningValues 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) Not described yet
postal_code varchar(10) Not described yet
county varchar(60) Not described yet
country char(2) Not described yet
latitude decimal Not described yet
longitude decimal Not described yet
geocode_quality varchar(20) Not described yet
full_address varchar(220) Not described yet
payment_applications one row per payment-to-invoice application 4 columns

Applies each payment to one or more invoices.

ColumnTypeKeyMeaningValues 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 one row per payment 10 columns

Payments clients make, one row per payment received.

ColumnTypeKeyMeaningValues seen
payment_id int PK Unique payment ID.
client_id int FK to clients Client who paid (clients).
received_by int FK to staff Not described yet
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 Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
staff one row per staff member 11 columns

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

ColumnTypeKeyMeaningValues seen
staff_id int PK Unique staff ID.
first_name varchar(40) First name.
last_name varchar(40) Last name.
email varchar(100) Not described yet
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 one row per task 8 columns

To-do items on engagements.

ColumnTypeKeyMeaningValues 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 one row per return 16 columns

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

ColumnTypeKeyMeaningValues 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) Not described yet
status varchar(20) Return status (see the values). Accepted 6005 Filed 556 In Review 49 Preparing 25
preparer_id int FK to staff Not described yet
reviewer_id int FK to staff Not described yet
total_income decimal Total income on the return.
refund_or_balance_due decimal Refund (negative) or balance due (positive).
time_entries one row per time entry 13 columns

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

ColumnTypeKeyMeaningValues 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 Not described yet
fiscal_quarter tinyint Not described yet
fiscal_period tinyint Not described yet
16 more tables (no notes yet)

← All databases