LedgerPilot

• 

  Dashboard

• 

  Transactions

• 

  Invoices

• 

  Audit Logs

• 

  Clients Directory

• 

  Approvals

Inspiration

Freelancers who get paid on PayPal often end up reconciling by hand. A payment comes in without a clear invoice number. Sometimes it is only a partial payment. Sometimes one transfer is meant to cover several invoices. Fees look normal until they are not. Duplicates are easy to miss. Overdue invoices sit until someone remembers to chase them.

Spreadsheets and the PayPal activity list can show the raw data, but they do not explain which payment belongs to which invoice, or what should happen next. Fully automatic tools that message clients or refund money without a person checking first are hard to trust with real finances.

LedgerPilot started from that gap: keep PayPal as the source of truth, make matching and anomalies visible, and require a human decision before any reminder or refund goes out.

What it does

LedgerPilot is a web app for a single freelancer (one merchant account) that:

• Ingests PayPal-shaped events from sandbox webhooks or from an in-app Demo Controls simulator (controlled amounts and emails for reliable demos).
• Stores transactions, invoices, clients, matches, annotations, pending actions, webhook events, and an audit log in Supabase.
• Matches payments to invoices first with simple rules (exact amount + email; fee-adjusted net amount).
• Runs a background-style agent for fee spikes, duplicates, unknown payers, simple partials, and—only when still unclear—Google Gemini for structured match/category suggestions.
• Never sends reminders or refunds on its own. Those become rows in Approvals. The user approves or rejects; only then does the app call PayPal (or record a local result for simulated ids).
• Shows a dashboard (including AG Charts), dense AG Grid views for transactions and invoices, detail sheets, and the approvals queue.

Problem → product mapping

Sandbox webhooks can be configured on a public URL. Dashboard “test webhooks” use fixed dummy data, so Demo mode is used to simulate payments when showing matching and approvals.

How we built it

• Frontend: React Router (framework mode), TypeScript, Tailwind, shadcn/ui, React Hook Form + Zod for login.
• Tables & charts: AG Grid on Transactions and Invoices; AG Charts on the Dashboard.
• Backend data: Supabase Auth and Postgres; service classes on the server (Ingest, Agent, Approvals, PayPal, Gemini, etc.) with a shared result shape and errors.
• PayPal: REST sandbox (captures, invoicing-related events, reminders/refunds when real ids exist). Events are normalized, then upserted.
• Pipeline: webhook or demo simulate → normalize → save → rule engine → agent checks → optional Gemini JSON → tools write matches / annotations / pending_actions → Approvals execute or reject.
• Tooling: pnpm; Supabase CLI via pnpm dlx; migrations for schema and optional seed; pnpm gen-types after migrations.

Challenges we ran into

• PayPal test webhooks always send the same dummy payer and amounts, so they rarely demonstrate real matching. We relied on Demo Controls for controlled scenarios and kept webhooks for the live pipe.
• Event types differ (e.g. payment created vs capture completed). Treating “created” as money received polluted the ledger until the normalizer ignored non-settlement events.
• Status and field mismatches (uppercase vs schema lowercase, annotation entity_type, linking client_id on transactions) broke matches and UI until aligned with the database.
• Auth on webhooks: there is no logged-in user on a PayPal POST, so profile resolution had to work without a normal session.
• Human-in-the-loop: auto-approving fuzzy matches or calling PayPal from the agent would break the trust model; Approvals and demo-safe handling for simulated capture ids had to be explicit.
• Scope: a full LLM agent loop on every payment is unnecessary; limiting Gemini to ambiguous cases kept cost and behavior simpler.

Accomplishments that we're proud of

• An end-to-end path from ingest → rules/agent → Approvals → audit, with grids that show matches, clients, and anomalies.
• A clear split: deterministic rules first, light agent next, Gemini only when needed.
• Demo Controls that make the product explainable in a short video without depending on PayPal’s dummy test payloads.
• Approvals that can hit real sandbox APIs when ids exist, and still complete cleanly for simulator data.
• One-merchant, freelancer-focused data model instead of a multi-tenant product we could not finish well in time.

What we learned

• Payment products need a boring, reliable layer (normalize, rules, status conventions) before AI helps.
• Human approval for outreach and refunds is easier to defend than fully automatic side effects.
• Official webhook simulators are not the same as production-shaped data; product demos need fixtures you control.
• Small schema details (status enums, polymorphic annotations, profile id on every row) decide whether features appear to “work.”
• A fast, constrained model with JSON-only output is enough for match suggestions if inputs and ids are validated in code.

What's next for LedgerPilot

• Stronger webhook signature verification and clearer multi-merchant onboarding if the product grows beyond one freelancer profile.
• Richer partial and bulk allocation UI on top of proposed matches.
• Optional streaming of agent steps in the UI for transparency.
• Tighter real sandbox invoice → pay → webhook demos alongside Demo mode.
• Deeper AG Grid / AG Charts reporting (aging, fee trends) and export for bookkeeping.
• More complete audit filters and retention settings for real use.

Built With

• ag-charts
• ag-grid
• paypal
• react
• shadcn
• supabase
• tailwind
• typescript