Skip to content
BranchesTags

Repository files navigation

Ledgerly

Ledgerly is a personal finance transaction management app for turning raw bank transaction text and bank CSV exports into structured, reviewable records. Authenticated users can preview parsed transactions, import and roll back CSV batches, manage category rules, export CSVs, and access only their own tenant-scoped data.

Live Demo

The live demo can be tested safely by registering a fresh email address. New users start with an empty tenant-scoped workspace, so transactions created by one test user should not appear for another user.

Tech Stack

  • Frontend: Next.js 15 App Router, TypeScript, React Server Components
  • Backend: Hono, TypeScript
  • Authentication: Better Auth with email/password sessions, bearer/JWT plugins, and organization/team support
  • Frontend session bridge: Auth.js credentials provider
  • Database: PostgreSQL with Prisma
  • UI: Tailwind CSS and shadcn/ui-style primitives
  • Server state and charts: TanStack Query and Recharts
  • AI insights: OpenAI Responses API through a backend-only provider
  • Testing: Jest, ts-jest, Playwright

Architecture

Next.js frontend
  -> Auth.js credentials session bridge
  -> Hono backend
  -> Better Auth identity and tenant context
  -> Transaction parser, analytics, subscriptions, and AI aggregate services
  -> Prisma
  -> PostgreSQL

Better Auth is the source of truth for registration, login, password hashing, sessions, tokens, and organization/team membership. Auth.js is used only as the Next.js session bridge for pages and client components.

Features

  • Email/password registration and login
  • Raw bank transaction parsing
  • CSV import with column mapping, duplicate detection, import history, and rollback
  • Preview-before-save workflow for parsed drafts
  • Bulk transaction saving
  • Duplicate detection within the authenticated tenant
  • Category rules for merchant-specific categorization
  • Search, filtering, cursor pagination, optimistic edits, and CSV export
  • Production dashboard analytics for monthly trends, category totals, merchant totals, debit/credit totals, review counts, and duplicate counts without mixing currencies
  • Computed recurring subscription detection from tenant-scoped transactions
  • Optional OpenAI spending insights generated from aggregates only
  • Tenant-scoped backend queries and PostgreSQL row-level security exercised through a non-owner runtime role

Local Setup

npm install
cp .env.example .env
docker compose up -d postgres
npm run prisma:generate
npm run prisma:migrate
npm run seed

Run the backend and frontend in separate terminals:

npm run dev:backend
npm run dev:frontend

Open http://localhost:3000.

Environment

.env.example contains the required local variables:

DATABASE_URL="postgresql://ledgerly:ledgerly@localhost:5433/ledgerly?schema=public"
DATABASE_MIGRATION_URL="postgresql://ledgerly_migrator:ledgerly@localhost:5433/ledgerly?schema=public"
BETTER_AUTH_SECRET="replace-with-at-least-32-random-characters"
BETTER_AUTH_URL="http://localhost:4000"
FRONTEND_URL="http://localhost:3000"
AUTH_SECRET="replace-with-at-least-32-random-characters-for-authjs"
AUTH_URL="http://localhost:3000"
NEXT_PUBLIC_BACKEND_URL="http://localhost:4000"
BACKEND_INTERNAL_URL="http://localhost:4000"
REDIS_URL="redis://localhost:6379"
TRUST_PROXY_HEADERS="false"

DATABASE_URL should point at the non-owner runtime role so row-level security is actually enforced. DATABASE_MIGRATION_URL is the owner/migrator connection used for Prisma migrations and grants.

Commands

npm test
npm run test:e2e
npm run typecheck
npm run build
npm run prisma:generate
npm run prisma:migrate
npm run prisma:push
npm run seed

Use DATABASE_URL="$DATABASE_MIGRATION_URL" npm run prisma:migrate for normal local setup when your .env uses the runtime role. prisma:push is available for disposable databases.

Demo Users

After running npm run seed, the following users are available locally:

  • asha@example.com / Password123!
  • rohan@example.com / Password123!

Each seeded user belongs to a separate personal organization and team. Demo transaction records are created only for these explicit demo accounts. Newly registered users start with an empty private workspace.

API

POST /api/auth/register
POST /api/auth/login
POST /api/auth/logout
POST /api/transactions/preview
POST /api/transactions
POST /api/transactions/extract
GET /api/transactions?limit=10&cursor=<opaque_cursor>
PATCH /api/transactions/:id
GET /api/transactions/export
DELETE /api/transactions/:id
POST /api/imports/preview
POST /api/imports
GET /api/imports
DELETE /api/imports/:id
GET /api/analytics/summary
GET /api/analytics/subscriptions
POST /api/insights/generate
GET /api/category-rules
POST /api/category-rules
PATCH /api/category-rules/:id
DELETE /api/category-rules/:id

All transaction, import, and category-rule endpoints are protected. The backend derives userId, organizationId, and teamId from the verified Better Auth session. Client-supplied ownership fields are ignored.

Analytics And Subscriptions

GET /api/analytics/summary accepts the same filters as transaction listing and returns tenant-scoped currency summaries, monthly series, category totals, merchant totals, duplicate count, review count, and transaction count. Totals are grouped by currency code rather than combined across currencies.

GET /api/analytics/subscriptions accepts the same filters and returns computed recurring debit candidates with merchant, amount, cadence, last charge date, confidence, and transaction count. v1 does not persist a subscription table.

AI Insights

POST /api/insights/generate is protected and rate-limited. It accepts optional transaction filters and calls OpenAI only from the backend when AI_INSIGHTS_ENABLED=true and OPENAI_API_KEY is configured. The provider receives aggregate summaries and recurring candidates only; raw SMS text, raw transaction text, user identity, and other tenants' rows are never sent.

Response statuses include:

  • ready
  • empty
  • not_enough_data
  • disabled
  • missing_api_key

Status behavior:

  • empty: the current filters match zero transactions.
  • not_enough_data: fewer than three transactions are available.
  • disabled: AI_INSIGHTS_ENABLED is unset or not true in the backend environment.
  • missing_api_key: AI is enabled but OPENAI_API_KEY is missing.
  • ready: AI is enabled, a key is configured, and enough aggregate data is available.

If the dashboard shows "AI insights are disabled for this environment," the deployed backend is working as configured but has AI disabled. Enable it by setting AI_INSIGHTS_ENABLED=true and OPENAI_API_KEY on the backend deployment, then redeploying or restarting the service.

Preview Transactions

POST /api/transactions/preview accepts raw text and returns editable drafts without saving:

{
  "text": "raw bank transaction text...",
  "accountLabel": "Personal"
}

The parser supports multiple transactions separated by blank lines. Each draft includes nullable required fields, issues: [{field, code, message}], draftId, sourceText, status, accountLabel, and duplicate metadata. Missing or malformed values are never replaced with today, zero, an inferred debit/credit type, or a default currency. The active Import page blocks selected drafts until their issues are corrected; corrected extracted drafts remain NEEDS_REVIEW.

Save Drafts

POST /api/transactions saves one to 100 reviewed drafts:

{
  "drafts": [
    {
      "date": "2025-12-11",
      "description": "STARBUCKS COFFEE MUMBAI",
      "amount": -420,
      "currencyCode": "INR",
      "type": "DEBIT",
      "balanceAfter": 18420.5,
      "category": "Dining",
      "confidence": 1,
      "status": "SAVED",
      "accountLabel": "Personal",
      "sourceText": "raw source text"
    }
  ]
}

Single-Step Extraction

POST /api/transactions/extract parses and saves a single transaction:

{
  "text": "raw bank transaction text...",
  "accountLabel": "Personal"
}

Successful responses include the saved transaction and duplicate metadata.

Listing And Export

Transaction listing returns items, nextCursor, and a temporary transactions alias for frontend compatibility. nextCursor is an opaque cursor based on createdAt + id.

Supported filters:

  • search
  • dateFrom
  • dateTo
  • type=DEBIT|CREDIT
  • category
  • status=SAVED|NEEDS_REVIEW
  • accountLabel
  • minConfidence

GET /api/transactions/export accepts the same filters and returns up to 1,000 tenant-scoped rows as CSV. CSV columns are date, description, amount, currencyCode, type, balanceAfter, category, confidence, status, accountLabel, and createdAt. User-controlled text cells beginning with =, +, -, or @ after whitespace are apostrophe-prefixed before RFC 4180 quoting; numeric and date cells retain spreadsheet-compatible values.

Parser Behavior

The parser is deterministic and does not use an LLM.

Supported date formats:

  • 11 Dec 2025
  • 12/11/2025
  • 2025-12-10

Slash dates are interpreted as MM/DD/YYYY, so 12/11/2025 becomes 2025-12-11.

Supported amount and debit indicators:

  • -420.00
  • ₹1,250.00 debited
  • ₹2,999.00 Dr
  • $42.50
  • €42.50, EUR 42.50, £42.50, and GBP 42.50
  • -> and → balance arrows

Currency behavior:

  • ₹, Rs, and INR entries are stored and displayed as INR.
  • $ and USD entries are stored and displayed as USD.
  • €/EUR and £/GBP entries are stored and displayed as EUR and GBP.
  • Entries without an explicit currency remain unset and carry a review issue.
  • AI spending insights receive tenant-scoped aggregate currency metadata and must format amounts with the stored transaction currency rather than defaulting to dollars.

Confidence is calculated from detected fields:

  • Date found: +0.25
  • Amount found: +0.25
  • Description found: +0.20
  • Debit/credit type found: +0.15
  • Balance found: +0.10
  • Category found: +0.05

Drafts with confidence below 0.85 are marked NEEDS_REVIEW. Drafts corrected in the Import review UI remain NEEDS_REVIEW until reviewed in the ledger.

Security And Data Isolation

Production requires REDIS_URL; development and tests may use the process-local limiter. Login is throttled by hashed normalized email/IP and by hashed IP, and 429 responses include Retry-After. Forwarded IP headers are trusted only when TRUST_PROXY_HEADERS=true behind a proxy that replaces those headers. Logout first asks Better Auth to revoke the bearer-backed database session, then Auth.js clears its local session.

Mutation audit events are written in the same database transaction as transaction, import, and category-rule changes. The audit_event table is tenant-scoped and append-only for the runtime role. Metadata is allowlisted and excludes raw transaction text, credentials, and tokens.

Protected transaction routes verify the incoming cookie or bearer token with Better Auth, resolve the authenticated user's active organization/team membership, and build Prisma filters from server-side auth context.

Transaction reads and writes are scoped by both:

  • authenticated userId
  • authenticated organizationId

The backend does not trust userId, organizationId, teamId, or duplicateOfId from client input unless the referenced data is verified to belong to the authenticated tenant.

PostgreSQL row-level security is enabled and forced on transaction and category_rule. Tenant-scoped Prisma operations run inside a transaction that sets app.current_organization_id before touching those tables.

Seed/demo safety:

  • npm run seed creates demo rows only for asha@example.com and rohan@example.com.
  • Registration and login only ensure a personal tenant exists; they do not copy demo rows into real accounts.
  • CSV export, analytics, subscription detection, category rules, and AI insights all use server-derived tenant scope.

For databases created before the RLS migration, the policy SQL is available at apps/backend/prisma/rls.sql:

psql "$DATABASE_URL" -f apps/backend/prisma/rls.sql

Pagination And Indexes

Transactions are sorted by createdAt desc, id desc. The backend fetches limit + 1 rows, returns the requested page, and returns an opaque composite cursor when another page exists. Listing and export filters are always combined with the authenticated userId and organizationId.

Prisma indexes support tenant-scoped listing and date lookup:

  • userId + createdAt
  • organizationId + createdAt
  • userId + organizationId + createdAt + id
  • organizationId + createdAt + id
  • userId + date
  • organizationId + date
  • organizationId + status
  • organizationId + category
  • organizationId + accountLabel

Deployment Notes

  • Set deployed BETTER_AUTH_URL, FRONTEND_ORIGINS, AUTH_URL, and NEXT_PUBLIC_BACKEND_URL to real HTTPS origins. Production rejects localhost origins.
  • Run Prisma migrations and apply apps/backend/prisma/rls.sql if the target database predates the RLS migration.
  • Keep AI_INSIGHTS_ENABLED=false until OPENAI_API_KEY is set in the backend environment. To test production AI insights, set AI_INSIGHTS_ENABLED=true, set OPENAI_API_KEY, optionally set OPENAI_MODEL, and restart the backend.
  • Demo-video checklist: register a fresh user and show an empty workspace, log into a demo user to show seeded data, apply analytics filters, detect subscriptions, export CSV, and generate AI insights. If production AI is disabled, show the disabled state and explain that raw transaction text is never sent to OpenAI.

Manual Live Testing Checklist

Use the live frontend and create two fresh test users, for example ledgerly-test-a+<date>@example.com and ledgerly-test-b+<date>@example.com, with a password of at least eight characters.

Core flow:

  • Register User A and confirm the dashboard starts empty.
  • Preview and save the sample transactions below.
  • Confirm table rows, analytics, filters, CSV export, category rules, duplicate warnings, and subscription candidates update.
  • Log out, register User B, and confirm User A's transactions, rules, analytics, subscriptions, and CSV rows are not visible.

Sample debit:

Date: 11 Dec 2025
Description: STARBUCKS COFFEE MUMBAI
Amount: -420.00
Balance after transaction: 18,420.50

Sample built-in category:

Uber Ride * Airport Drop
12/11/2025 -> ₹1,250.00 debited
Available Balance -> ₹17,170.50

Sample explicit category:

txn123 2025-12-10 Amazon.in Order #403-1234567-8901234 ₹2,999.00 Dr Bal 14171.50 Shopping

Sample bulk paste:

Date: 14 Dec 2025
Description: BIGBASKET GROCERY BANGALORE
Amount: -1,842.75
Balance after transaction: 32,910.25
Category: Groceries

Swiggy Instamart Order
12/15/2025 -> ₹684.00 debited
Available Balance -> ₹32,226.25 Food

Date: 17 Dec 2025
Description: SALARY CREDIT ACME TECHNOLOGIES
Amount: +85,000.00
Balance after transaction: 116,577.25
Category: Salary

Recurring subscription sample:

Date: 01 Oct 2025
Description: NETFLIX SUBSCRIPTION
Amount: -649.00
Balance after transaction: 50,000.00
Category: Entertainment

Date: 01 Nov 2025
Description: NETFLIX SUBSCRIPTION
Amount: -649.00
Balance after transaction: 49,351.00
Category: Entertainment

Date: 01 Dec 2025
Description: NETFLIX SUBSCRIPTION
Amount: -649.00
Balance after transaction: 48,702.00
Category: Entertainment

Low-confidence review sample:

12 Dec 2025 Local Store -99.00

Expected checks:

  • 12/11/2025 is interpreted as December 11, 2025.
  • Debit amounts are negative and credit amounts are positive.
  • Blank-line-separated text creates multiple editable drafts.
  • Saving the same Starbucks sample twice produces a duplicate warning.
  • A category rule such as starbucks -> Client Meals applies before built-in or explicit categories.
  • Filtering by search, date range, type, category, status, account label, and minimum confidence affects transactions, analytics, subscriptions, export, and insights.

Category Rules

Category rules are tenant-scoped phrase-to-category mappings. They are applied before explicit parsed categories and built-in categories:

{
  "matchText": "starbucks",
  "category": "Client Meals"
}

POST /api/category-rules upserts by organizationId + matchText. PATCH /api/category-rules/:id and DELETE /api/category-rules/:id require the rule to belong to the authenticated tenant.

Error Format

Error responses use a consistent shape:

{
  "error": {
    "code": "UNAUTHORIZED",
    "message": "Authentication required"
  }
}

Known Trade-Offs

  • Auth.js is used only as a Next.js session bridge; Better Auth remains the auth source of truth.
  • Parser support is intentionally focused on common bank transaction text formats and nearby variants.
  • Database-backed auth route tests require DATABASE_URL to be reachable.

About

A personal finance app that converts raw bank text and CSV statements into categorized, searchable transactions and spending insights.

ledgerly-demo.vercel.app/

Resources

Readme
MIT license
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

Packages

Contributors

Languages