These cookies are required for the website to function properly. They ensure security, enable basic features like page navigation, and store user session data. You cannot disable these cookies.
Data model
Keep expense evidence, approvals and payment identities connected.
18 tables · 33 relationships
Migration order
- Employees, ExpensePolicies, PolicyRules and ExpenseCategories establish ownership and the governing rules.
- Vehicles, MileageRates and PerDiemRates retain reviewed travel references and effective intervals.
- CorporateCards and CardTransactions preserve card assignment and stable issuer transaction identities.
- ExpenseReports, ExpenseLines, Receipts, MileageEntries and MealsAndEntertainment retain the claim and its evidence.
- PolicyViolations and ExpenseApprovalSteps retain findings and revision-specific decisions.
- Reimbursements and CardReconciliations preserve the separate employee and issuer settlement trails.
Financial identity
18 tables
EmployeePayableAmount includes eligible approved OutOfPocket, Mileage and PerDiem lines and excludes company-card costs. A stable SourceKey and external confirmation reference prevent duplicate reimbursement. Preserve approved report revisions and source calculation bases; correction must not silently rewrite earlier decisions.
EmployeePayableAmount includes eligible approved OutOfPocket, Mileage and PerDiem lines and excludes company-card costs. A stable SourceKey and external confirmation reference prevent duplicate reimbursement. Preserve approved report revisions and source calculation bases; correction must not silently rewrite earlier decisions.
ExpenseCategoriesThe category catalog. Drives default GL coding, receipt-requiredmaster
| Field | Type | Required | Details |
|---|---|---|---|
CategoryCode | text | yes | |
Name | text | yes | |
Group | select | yes | TravelAir, TravelHotel, TravelGround, Meals, Entertainment, OfficeSupplies, Equipment, SoftwareSub, Telecom, Mileage, PerDiem, Other |
DefaultGLAccount | text | yes | GL account code in fin-accounting ChartOfAccounts |
ReceiptRequiredAbove | currency | Company policy threshold; validate lodging, category and documentary-evidence exceptions before activation | |
MealsAndEntertainment | toggle | yes | Requires reviewed classification and effective-date treatment; no universal deduction rate |
AllowBillableToProject | toggle | yes | |
Active | toggle | yes |
ExpensePoliciesPolicy header. Scope: Org / Department / Role. One default Orgmaster
| Field | Type | Required | Details |
|---|---|---|---|
PolicyCode | text | yes | |
Name | text | yes | |
Scope | select | yes | Org, Department, Role |
ScopeRef | text | Department code or Role name when Scope != Org | |
JustificationWindowDays | number | yes | Configured card-transaction justification period; default 30 days |
HighValueThreshold | currency | Reports above this add a Controller approval step | |
ReimbursementChannel | select | yes | PayrollAddOn, SeparateACH |
EffectiveFrom | date | yes | |
EffectiveTo | date | ||
Active | toggle | yes |
PolicyRulesThe declarative rules. One row per per-diem cap, alcohol limit, Line item of ExpensePolicies.line item
| Field | Type | Required | Details |
|---|---|---|---|
Policy | reference | yes | → ExpensePolicies |
RuleCode | text | yes | |
RuleType | select | yes | PerDiemCap, SingleMealCap, AlcoholLimit, WeekendApprovalRequired, ReceiptRequired, MileageCap, AttendeeListRequired, JustificationRequired, BlockedCategory, FraudPattern |
Category | reference | → ExpenseCategories (null = all) | |
PerDiemRate | reference | → PerDiemRates (when RuleType = PerDiemCap) | |
CapAmount | currency | ||
CapPercent | number | 0–100 (e.g. AlcoholLimit = 0) | |
AppliesDayOfWeek | select | Any, Mon, Tue, Wed, Thu, Fri, Sat, Sun, Weekend | |
Severity | select | yes | Soft, Hard, Block |
Message | text | yes | Shown to submitter on violation |
Active | toggle | yes |
MileageRatesThe per-tax-year mileage reimbursement rate. Defaults to the IRSmaster
| Field | Type | Required | Details |
|---|---|---|---|
TaxYear | number | yes | |
RateScope | select | yes | Standard, Medical, Charitable, MovingMilitary |
RatePerMile | currency | yes | USD per mile |
Source | select | yes | IRS, CompanyOverride |
EffectiveFrom | date | yes | |
EffectiveTo | date | ||
Active | toggle | yes |
VehiclesThe submitter's registered personal vehicle(s) used for Line item of Employees.line item
| Field | Type | Required | Details |
|---|---|---|---|
Employee | reference | yes | → Employees |
VehicleName | text | yes | e.g. "2022 Honda Civic" |
LicensePlate | text | ||
State | select | US state of registration | |
YearMakeModel | text | ||
InsuranceCarrier | text | ||
InsuranceExpiry | date | ||
Status | select | yes | Active, Inactive |
RegisteredAt | date | yes |
PerDiemRatesGSA-style per-diem rates by US city / locality and effectivemaster
| Field | Type | Required | Details |
|---|---|---|---|
LocalityCode | text | yes | GSA locality code (e.g. "NY-NYC", "DC-WAS", "Standard CONUS") |
LocalityName | text | yes | |
State | select | yes | US state (or "DC") |
FiscalYear | number | yes | |
LodgingPerNight | currency | yes | |
MIEPerDay | currency | yes | Meals & Incidental Expenses |
ConferencePerDay | currency | ||
EffectiveFrom | date | yes | |
EffectiveTo | date | ||
Source | select | yes | GSA, CompanyOverride |
Active | toggle | yes |
CorporateCardsIssued company cards. One Cardholder per card; a Cardholder maymaster
| Field | Type | Required | Details |
|---|---|---|---|
CardNickname | text | yes | |
CardLast4 | text | yes | Masked everywhere except cardholder + Finance Reviewer + Audit |
Cardholder | reference | yes | → Employees |
ProgramType | select | yes | VisaCommercial, MastercardCorporate, AmexCorporate, AmexBusiness |
Issuer | text | e.g. "JPMorgan Chase", "Bank of America", "American Express" | |
GLAccount | text | yes | CorporateCardPayable account in fin-accounting |
CreditLimit | currency | ||
Status | select | yes | Active, Frozen, Closed |
ActivatedAt | date | yes | |
ClosedAt | date |
CardTransactionsThe daily transaction feed from the card program. One row pertransaction
| Field | Type | Required | Details |
|---|---|---|---|
Card | reference | yes | → CorporateCards |
MerchantName | text | yes | |
Amount | currency | yes | Signed: positive = charge, negative = refund |
Currency | select | yes | USD only at this tier |
TransactionDate | date | yes | |
PostedDate | date | yes | |
MCC | text | Merchant category code | |
RawDescription | text | Masked from non-cardholder, non-Finance, non-Audit | |
FeedRefID | text | yes | Card-program unique ID — dedupe key |
MatchedExpenseLine | reference | → ExpenseLines (set when matched) | |
Status | select | yes | Unmatched, Matched, Reconciled, Disputed, Excluded |
DisputedAt | date | ||
ReconciledAt | date | Set on CardReconciliation close |
ExpenseReportsThe submitter's report header. One report aggregates manytransaction
| Field | Type | Required | Details |
|---|---|---|---|
ReportNumber | text | yes | |
Submitter | reference | yes | → Employees |
ReportTitle | text | yes | |
Purpose | text | Trip / project / business reason | |
ReportDate | date | yes | |
TripStart | date | ||
TripEnd | date | ||
Project | text | Optional default BillableTo for all lines | |
Policy | reference | yes | → ExpensePolicies (resolved at submit by submitter scope) |
TotalAmount | formula | yes | fx: SUM(ExpenseLines.Amount) |
ApprovedAmount | formula | yes | fx: SUM(ExpenseLines.ApprovedAmount) |
OOPAmount | formula | yes | fx: SUM(ExpenseLines.ApprovedAmount where PaymentMethod = OutOfPocket) |
CardAmount | formula | yes | fx: SUM(ExpenseLines.ApprovedAmount where PaymentMethod = CorporateCard) |
ViolationCount | formula | yes | fx: COUNT(PolicyViolations across lines) |
Status | select | yes | Draft, Submitted, ApprovedByMgr, ApprovedByFinance, Reimbursed, Rejected, Held |
SubmittedAt | date | ||
RejectedAt | date | ||
RejectionReason | text | ||
HighValue | formula | yes | fx: TotalAmount > Policy.HighValueThreshold |
SubmissionRevision | number | yes | Increment on resubmission after a material change; prior decision evidence retained |
ApprovalComplete | formula | yes | fx: all required Manager, Finance and conditional Controller decisions for this SubmissionRevision are Approved by permitted non-submitter actors |
EmployeePayableAmount | formula | yes | fx: SUM(ExpenseLines.ApprovedAmount where PaymentMethod IN (OutOfPocket, Mileage, PerDiem) AND Status = Approved) |
ExpenseLinesThe line grain. One row per receipt / per per-diem day / per Line item of ExpenseReports.line item
| Field | Type | Required | Details |
|---|---|---|---|
Report | reference | yes | → ExpenseReports |
LineDate | date | yes | |
Category | reference | yes | → ExpenseCategories |
Vendor | text | yes | |
Amount | currency | yes | |
Currency | select | yes | USD |
Description | text | ||
BillableTo | text | Project code (validated against PM Projects on submit) | |
TaxAmount | currency | Sales tax portion of Amount, when broken out | |
Department | text | yes | Department cost code snapshotted for the report revision |
PerDiemRate | reference | → PerDiemRates; reviewed basis for per-diem lines | |
GLAccount | text | yes | Defaults from Category.DefaultGLAccount; any submitter suggestion requires Finance review |
PaymentMethod | select | yes | OutOfPocket, CorporateCard, PerDiem, Mileage |
CardTransaction | reference | → CardTransactions (set on match) | |
ApprovedAmount | currency | Initialized from Amount for review; final value requires the authorized Finance decision | |
Disallowance | currency | fx: Amount − ApprovedAmount when partial | |
ReceiptRequired | formula | yes | fx: Amount > Category.ReceiptRequiredAbove |
ReceiptStatus | select | yes | NotRequired, Missing, Attached, Verified |
Status | select | yes | Draft, Submitted, Approved, Rejected, Held |
ReceiptsThe supporting documentation — phone snap, PDF, emailed Line item of ExpenseLines.line item
| Field | Type | Required | Details |
|---|---|---|---|
ExpenseLine | reference | yes | → ExpenseLines |
FileURL | text | yes | |
FileType | select | yes | Image, PDF, EmailEML |
UploadedAt | date | yes | |
OCRStatus | select | yes | Pending, Parsed, LowConfidence, Failed, Skipped |
OCRVendor | text | ||
OCRDate | date | ||
OCRTotal | currency | ||
OCRMerchantTaxID | text | EIN when present on receipt | |
OCRConfidence | number | 0–100 | |
Verified | toggle | Set true on Finance Reviewer sign-off |
MileageEntriesOne row per business trip drive. Either the user enters Line item of ExpenseLines.line item
| Field | Type | Required | Details |
|---|---|---|---|
ExpenseLine | reference | yes | → ExpenseLines (the auto-created Mileage line) |
Vehicle | reference | yes | → Vehicles |
EntryDate | date | yes | |
FromAddress | text | ||
ToAddress | text | ||
RoundTrip | toggle | yes | |
Miles | number | yes | One-way × 2 if RoundTrip |
MileageRate | reference | yes | → MileageRates (resolved from EntryDate.year) |
CalculatedReimbursement | formula | yes | fx: Miles × MileageRate.RatePerMile |
Purpose | text | yes | |
MapsValidated | toggle | Set true after Google Maps Distance Matrix call | |
Source | select | yes | ManualMiles, GoogleMaps, OdometerCapture |
PolicyViolationsOne row per breached PolicyRules row per ExpenseLine. The Line item of ExpenseLines.line item
| Field | Type | Required | Details |
|---|---|---|---|
ExpenseLine | reference | yes | → ExpenseLines |
Rule | reference | yes | → PolicyRules |
RuleType | select | yes | PerDiemCap, SingleMealCap, AlcoholLimit, WeekendApprovalRequired, ReceiptRequired, MileageCap, AttendeeListRequired, JustificationRequired, BlockedCategory, FraudPattern |
Severity | select | yes | Soft, Hard, Block |
Message | text | yes | |
ExceededBy | currency | Amount over the cap (for cap-type rules) | |
Justification | text | Required when Severity = Hard | |
AcknowledgedBy | reference | → Employees (the approver who passed it through) | |
AcknowledgedAt | date | ||
Status | select | yes | Open, Justified, Waived, Blocking |
ExpenseApprovalStepsThe audit trail of approver actions on the report. One row per Line item of ExpenseReports.line item
| Field | Type | Required | Details |
|---|---|---|---|
Report | reference | yes | → ExpenseReports |
ReportRevision | number | yes | ExpenseReports.SubmissionRevision to which the decision applies |
StepNumber | number | yes | |
StepType | select | yes | Manager, Finance, Controller, Audit |
Approver | reference | yes | → Employees |
Action | select | Approved, Rejected, Held, Delegated, Pending | |
ActionAt | date | ||
Comment | text | ||
ApprovedAmount | currency | This approver's running ApprovedAmount snapshot |
ReimbursementsThe paid-out record. One row per ExpenseReport pertransaction
| Field | Type | Required | Details |
|---|---|---|---|
ReimbursementNumber | text | yes | |
Report | reference | yes | → ExpenseReports |
Employee | reference | yes | → Employees |
Amount | currency | yes | EmployeePayableAmount at final approval; includes eligible OutOfPocket, Mileage and PerDiem lines, never company-card costs |
ReimbursementMethod | select | yes | PayrollAddOn, SeparateACH |
PayrollOneTimePaymentRef | text | Set when ReimbursementMethod = PayrollAddOn | |
PaymentRunRef | text | AP PaymentsMade ID when ReimbursementMethod = SeparateACH | |
SourceKey | text | yes | Stable report revision and reimbursement identity; unique across retries |
PaymentConfirmationRef | text | Confirmed external payment reference; scheduling alone cannot populate this | |
Status | select | yes | Pending, Scheduled, Paid, Returned, Cancelled |
ScheduledFor | date | Next pay date or next AP run date | |
PaidAt | date |
MealsAndEntertainmentThe meals and entertainment classification log for tax review. One rowtransaction
| Field | Type | Required | Details |
|---|---|---|---|
ExpenseLine | reference | yes | → ExpenseLines |
Classification | select | yes | Meal-Travel, Meal-Client, Entertainment-Client, Meal-Office, Meal-Conference |
BusinessPurpose | text | yes | |
AttendeeNames | text | yes | Comma-separated attendee names captured for IRS §274 audit display/export only; never queried as relations. Per-attendee tracking would require an Attendees line-item child table — out of scope for v1. |
AttendeeCount | number | yes | |
ClientCompanyName | text | ||
DeductiblePercent | number | yes | Reviewed percentage for classification and expense date; do not apply a universal default or infer tax eligibility from reimbursement approval |
DeductibleAmount | formula | yes | fx: ExpenseLine.ApprovedAmount × DeductiblePercent / 100 |
CardReconciliationsPeriod-end (typically monthly) reconciliation per CorporateCard.transaction
| Field | Type | Required | Details |
|---|---|---|---|
ReconciliationNumber | text | yes | |
Card | reference | yes | → CorporateCards |
StatementPeriodStart | date | yes | |
StatementPeriodEnd | date | yes | |
OpeningBalance | currency | yes | Issuer statement opening balance |
StatementPayments | currency | yes | Issuer payments not already included as signed CardTransactions; never subtract the same payment twice |
StatementBalance | currency | yes | Closing balance reported by the issuer |
ImportedTransactionsTotal | formula | yes | fx: SUM(CardTransactions.Amount where Card = self.Card AND PostedDate in period) |
MatchedTotal | formula | yes | fx: SUM(CardTransactions.Amount where Card = self.Card AND Status IN (Matched, Reconciled) AND MatchedExpenseLine.Status = Approved AND MatchedExpenseLine.Report.ApprovalComplete = true AND PostedDate in period) |
UnmatchedTotal | formula | yes | fx: ImportedTransactionsTotal − MatchedTotal |
Difference | formula | yes | fx: OpeningBalance + ImportedTransactionsTotal − StatementPayments − StatementBalance |
Status | select | yes | InProgress, Balanced, Unbalanced, Approved |
PreparedBy | reference | yes | → Employees |
ApprovedBy | reference | → Employees (Controller / CFO signs the reconciliation) | |
ApprovedAt | date |
EmployeesA scoped employee identity directory synchronized from the organization identity or HR source. This reference supports ownership and approval attribution; it does not add payroll or HR administration. Role membership alone never grants access to every directory field. External auditors and portal users receive only the identities needed for their granted work.master
| Field | Type | Required | Details |
|---|---|---|---|
EmployeeNumber | text | yes | Stable source identifier; unique within the organization |
Name | text | yes | Display name for an assigned owner or approver |
UserKey | text | yes | Bound to the authenticated platform user; never supplied as authority by a client |
Department | text | Organization-scoped department key | |
Manager | reference | → Employees; synchronized reporting relationship | |
Active | boolean | yes | Inactive identities retained for historical attribution; cannot receive new work |