Case Study 2: Data Exchange Between Applications

Overview

In this case study, we build a complete data exchange pipeline between PennyWise and a hypothetical web dashboard. PennyWise exports expense data as JSON, a web dashboard consumes it, and the dashboard can also push budget adjustments back to PennyWise via a JSON file. We also build a CSV import pipeline for bank statements, handling real-world format variations.

This case study demonstrates the full lifecycle of data exchange: export, transform, import, and validation.

Scenario

Rosa uses PennyWise on her desktop to track expenses. She also has a web-based dashboard (built by Tomás in JavaScript) that visualizes her spending trends with interactive charts. The workflow:

1. PennyWise exports monthly data as expenses-2026-03.json
2. Rosa uploads the JSON file to the web dashboard
3. The dashboard displays charts and lets Rosa adjust budget targets
4. Rosa downloads adjusted budgets as budgets-2026-03.json
5. PennyWise imports the budget adjustments

Additionally, Rosa's bank provides monthly transaction exports as CSV files, which PennyWise imports to auto-populate expenses.

The JSON Export Format

We design a schema that both applications agree on:

{
  "meta": {
    "application": "PennyWise",
    "version": "3.4",
    "exportDate": "2026-03-23T14:30:00",
    "period": {
      "start": "2026-03-01",
      "end": "2026-03-31"
    }
  },
  "summary": {
    "totalExpenses": 2847.30,
    "transactionCount": 47,
    "topCategory": "housing",
    "categoryBreakdown": {
      "food": 425.80,
      "transport": 135.00,
      "housing": 1200.00,
      "utilities": 287.50,
      "entertainment": 156.00,
      "health": 245.00,
      "education": 198.00,
      "other": 200.00
    }
  },
  "transactions": [
    {
      "id": 1,
      "date": "2026-03-01",
      "description": "Rent payment",
      "amount": 1200.00,
      "category": "housing",
      "recurring": true
    }
  ]
}

The export includes metadata (for the dashboard to display), a pre-computed summary (so the dashboard can show totals without re-computing), and the full transaction list.

Building the Exporter

procedure ExportMonthlyJSON(const Expenses: TExpenseArray;
  Year, Month: Word; const Filename: string);
var
  Root, Meta, Period, Summary, Breakdown: TJSONObject;
  Transactions: TJSONArray;
  TxnObj: TJSONObject;
  Monthly: TExpenseArray;
  CatTotals: array[TExpenseCategory] of Currency;
  Cat: TExpenseCategory;
  TopCat: TExpenseCategory;
  I: Integer;
  StartDate, EndDate: TDateTime;
  SL: TStringList;
begin
  { Filter to the target month }
  StartDate := EncodeDate(Year, Month, 1);
  if Month = 12 then
    EndDate := EncodeDate(Year + 1, 1, 1) - 1
  else
    EndDate := EncodeDate(Year, Month + 1, 1) - 1;
  Monthly := FilterByDateRange(Expenses, StartDate, EndDate);

  { Calculate category totals }
  for Cat := Low(TExpenseCategory) to High(TExpenseCategory) do
    CatTotals[Cat] := 0;
  for I := 0 to High(Monthly) do
    CatTotals[Monthly[I].Category] :=
      CatTotals[Monthly[I].Category] + Monthly[I].Amount;

  { Find top category }
  TopCat := ecOther;
  for Cat := Low(TExpenseCategory) to High(TExpenseCategory) do
    if CatTotals[Cat] > CatTotals[TopCat] then
      TopCat := Cat;

  { Build JSON }
  Root := TJSONObject.Create;
  try
    { Metadata }
    Meta := TJSONObject.Create;
    Meta.Add('application', 'PennyWise');
    Meta.Add('version', '3.4');
    Meta.Add('exportDate', FormatDateTime('yyyy-mm-dd"T"hh:nn:ss', Now));
    Period := TJSONObject.Create;
    Period.Add('start', FormatDateTime('yyyy-mm-dd', StartDate));
    Period.Add('end', FormatDateTime('yyyy-mm-dd', EndDate));
    Meta.Add('period', Period);
    Root.Add('meta', Meta);

    { Summary }
    Summary := TJSONObject.Create;
    Summary.Add('totalExpenses', Double(TotalExpenses(Monthly)));
    Summary.Add('transactionCount', Length(Monthly));
    Summary.Add('topCategory', CategoryToStr(TopCat));
    Breakdown := TJSONObject.Create;
    for Cat := Low(TExpenseCategory) to High(TExpenseCategory) do
      Breakdown.Add(LowerCase(CategoryToStr(Cat)), Double(CatTotals[Cat]));
    Summary.Add('categoryBreakdown', Breakdown);
    Root.Add('summary', Summary);

    { Transactions }
    Transactions := TJSONArray.Create;
    for I := 0 to High(Monthly) do
    begin
      TxnObj := TJSONObject.Create;
      TxnObj.Add('id', Monthly[I].ID);
      TxnObj.Add('date', FormatDateTime('yyyy-mm-dd', Monthly[I].ExpenseDate));
      TxnObj.Add('description', Monthly[I].Description);
      TxnObj.Add('amount', Double(Monthly[I].Amount));
      TxnObj.Add('category', LowerCase(CategoryToStr(Monthly[I].Category)));
      TxnObj.Add('recurring', Monthly[I].IsRecurring);
      Transactions.Add(TxnObj);
    end;
    Root.Add('transactions', Transactions);

    { Write to file }
    SL := TStringList.Create;
    try
      SL.Text := Root.FormatJSON;
      SL.SaveToFile(Filename);
    finally
      SL.Free;
    end;
  finally
    Root.Free;
  end;
end;

The Bank CSV Import Pipeline

Rosa's bank exports look like this:

Post Date,Description,Debit,Credit,Balance
03/15/2026,WHOLE FOODS MARKET #10847,85.50,,3245.30
03/15/2026,TRIMET MONTHLY PASS,45.00,,3200.30
03/14/2026,PAYROLL DEPOSIT,,2800.00,6000.30
03/14/2026,REGAL CINEMAS,24.00,,5976.30

Challenges: - Date format is MM/DD/YYYY - Debits and credits are in separate columns - Payroll deposits (credits) are not expenses and should be skipped - The balance column is irrelevant for our purposes - Some descriptions include commas (would be quoted)

The Import Pipeline

function ImportBankCSV(const Filename: string): TExpenseArray;
var
  Lines: TStringList;
  Fields: TStringArray;
  I, Count: Integer;
  Amt: Double;
  ExpDate: TDateTime;
  Desc: string;
begin
  Lines := TStringList.Create;
  try
    Lines.LoadFromFile(Filename);
    SetLength(Result, Lines.Count);
    Count := 0;

    for I := 1 to Lines.Count - 1 do  { Skip header }
    begin
      Fields := ParseCSVLine(Lines[I]);
      if Length(Fields) < 5 then Continue;

      { Parse debit amount (skip credits) }
      if (Length(Fields) >= 3) and (Trim(Fields[2]) <> '') then
      begin
        if not TryStrToFloat(Trim(Fields[2]), Amt) then Continue;
        if Amt <= 0 then Continue;

        { Parse date (MM/DD/YYYY) }
        try
          ExpDate := EncodeDate(
            StrToInt(Copy(Fields[0], 7, 4)),
            StrToInt(Copy(Fields[0], 1, 2)),
            StrToInt(Copy(Fields[0], 4, 2))
          );
        except
          Continue;  { Skip malformed dates }
        end;

        { Clean up description }
        Desc := Trim(Fields[1]);

        Result[Count] := CreateExpense(Desc, Amt,
          GuessCategory(Desc), ExpDate);
        Inc(Count);
      end;
    end;

    SetLength(Result, Count);
  finally
    Lines.Free;
  end;
end;

Auto-Categorization

The GuessCategory function uses keyword matching to assign categories:

function GuessCategory(const Desc: string): TExpenseCategory;
var
  Upper: string;
begin
  Upper := UpperCase(Desc);
  if ContainsAny(Upper, ['GROCERY', 'MARKET', 'FOODS', 'RESTAURANT',
                          'CAFE', 'PIZZA', 'BURGER']) then
    Exit(ecFood);
  if ContainsAny(Upper, ['TRIMET', 'UBER', 'LYFT', 'GAS', 'FUEL',
                          'PARKING', 'TRANSIT']) then
    Exit(ecTransport);
  if ContainsAny(Upper, ['RENT', 'MORTGAGE', 'REALTY']) then
    Exit(ecHousing);
  if ContainsAny(Upper, ['ELECTRIC', 'WATER', 'INTERNET', 'PHONE',
                          'UTILITY', 'COMCAST', 'VERIZON']) then
    Exit(ecUtilities);
  if ContainsAny(Upper, ['NETFLIX', 'CINEMA', 'THEATER', 'SPOTIFY',
                          'STEAM', 'GAMING']) then
    Exit(ecEntertainment);
  if ContainsAny(Upper, ['PHARMACY', 'MEDICAL', 'DOCTOR', 'DENTAL',
                          'HOSPITAL', 'HEALTH']) then
    Exit(ecHealth);
  Result := ecOther;
end;

This is a simple heuristic that correctly categorizes about 70% of common transactions. Rosa manually fixes the rest.

Round-Trip Verification

To verify the export-import pipeline, we test round-trip integrity:

1. Start with 100 expenses in memory
2. Export to JSON
3. Import from the JSON file
4. Compare: same count, same IDs, same amounts, same categories

procedure VerifyRoundTrip(const Original: TExpenseArray;
  const Filename: string);
var
  Reimported: TExpenseArray;
  I: Integer;
  Errors: Integer;
begin
  Reimported := ImportFromJSON(Filename);
  Errors := 0;
  if Length(Reimported) <> Length(Original) then
  begin
    WriteLn('ERROR: Count mismatch: ', Length(Original),
            ' vs ', Length(Reimported));
    Exit;
  end;
  for I := 0 to High(Original) do
  begin
    if Original[I].ID <> Reimported[I].ID then
    begin
      WriteLn('ERROR: ID mismatch at index ', I);
      Inc(Errors);
    end;
    if Abs(Double(Original[I].Amount - Reimported[I].Amount)) > 0.005 then
    begin
      WriteLn('ERROR: Amount mismatch at index ', I);
      Inc(Errors);
    end;
  end;
  if Errors = 0 then
    WriteLn('Round-trip verification PASSED: ', Length(Original), ' records OK')
  else
    WriteLn('Round-trip verification FAILED: ', Errors, ' errors');
end;

Lessons Learned

1. Define a schema before coding. Both the exporter and the consumer need to agree on field names, types, and structure. Write the expected JSON/CSV format first, then code to produce and consume it.

2. Include metadata in exports. The version, export date, and period make it possible to diagnose problems ("this file was exported by version 3.2, but I'm running 3.4 — is there a format difference?").

3. Bank CSVs are never standard. Always build configurable importers. Hard-coding column positions for one bank breaks when the user switches banks.

4. Auto-categorization is imperfect. Keyword matching gets you most of the way. The rest requires human review. Design the UI to make manual correction easy.

5. Verify round-trips. Serialization and deserialization are inverses. If deserialize(serialize(data)) != data, there is a bug. Always test this.