Group Expense Tracking & Trip Planning App

A web application designed to manage shared expenses, track group activities and plan optimal travels. Built with FastAPI and MySQL. Handles complex group dynamics, transaction settlements and algorithmic travel route calculations (via Hamiltonian paths!).

Features

• User Management & Authentication: Secure user signup and login.
• Group Expense Tracking: Seamlessly create groups, add members and track shared expenses grouped under specific "Events".
• Transaction Settlement: Record debts between users, upload receipt proofs and settle transactions. The system automatically reconciles balances and uses Gemini to OCR-verify that an uploaded receipt is a genuine payment proof.
• Analytics Dashboard: Visualize your financial history, track debt over time and monitor your participation in groups and events.
• Smart Trip Planning: Plan optimal solo or group trips. Just input your destinations and travel dates and the app will calculate the optimal route using the Hamiltonian path algorithm.
• Interactive Chatbot Assistant: Navigate and manage tasks efficiently through an integrated AI chatbot interface. You could ask it to reverse a linked list as well!

Typical Workflow

1. Onboarding: Create an account and log in to access your personal dashboard.
2. Form a Group: Start a new group and add friends, family, or colleagues as members.
3. Log Expenses: Create an event (e.g., "Weekend Getaway" or "Dinner at Amrita's") within the group and add transactions indicating who paid and who owes whom.
4. Settle Up: The owing member can upload a receipt and mark the transaction as paid. The system automatically adjusts everyone's running debt balance.
5. Plan a Trip: Going somewhere? Use the Trip Planner module to input multiple destinations and get the most efficient travel itinerary automatically calculated.

---

INSTRUCTIONS TO RUN

Database Setup

I have used MySQL for the current project, therefore it is recommended to create and run a docker instance with the same.

docker run -d \
  --name $CONTAINER_NAME \
  -e MYSQL_ROOT_PASSWORD=$ROOT_PASS \
  -p 3306:3306 \
  mysql:8.0

docker exec -i $CONTAINER_NAME mysql -uroot -p$DB_ROOT_PASS <<EOF
CREATE DATABASE IF NOT EXISTS expense_tracker;
CREATE USER IF NOT EXISTS '$USER'@'%' IDENTIFIED BY '$PASSWORD';
GRANT ALL PRIVILEGES ON expense_tracker.* TO '$USER'@'%';
FLUSH PRIVILEGES;
EOF

cat src/schema.sql | docker exec -i $CONTAINER_NAME mysql -u$USER -p$PASSWORD expense_tracker

Environment Variable Configs

The setup process for Google Maps API was daunting and so a workaround using OpenStreetMap + Leaflet has been used in its place. Make a .env file in the root directory with the necessary passkeys as below:

MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_DB=expense_tracker
MYSQL_USER=USER
MYSQL_PASS=PASSWORD
AVIATION_API_KEY=AVIATION_KEY
SECRET_PHRASE=a-long-random-string-used-to-sign-login-sessions
GEMINI_API_KEY=GEMINI_KEY

SECRET_PHRASE signs the JWT session cookie issued on login — required, and should be a long random value (e.g. python -c "import secrets; print(secrets.token_hex(32))"). GEMINI_API_KEY is optional; without it, receipt OCR analysis is disabled but the rest of the app still runs.

General Run

Run the following commands to install dependencies and launch the application:

uv sync
uv run uvicorn src.main:app --reload

Access the website at the link displayed in the terminal or http://127.0.0.1:8000 after running the above commands.

---

Future Work & Known Caveats

The following issues and hardening items were identified during an architecture review of the schema, backend, and integrations. Grouped by area, unchecked items are still open.

Schema Design

• Replace username varchar(100) as User PK with a surrogate INT AUTO_INCREMENT PK; keep username as a unique natural key to avoid expensive ON UPDATE CASCADE and bloated secondary indexes — implemented (User.user_id; every FK that used to store username — Group.created_by, GroupMember, Event.created_by, Transaction.created_by/owed_by/owed_to, Trip.created_by, ChatbotState, ChatMessage, ExtractedInfo — now stores the 4-byte int instead)
• Use an opaque/UUID identifier for user-facing routes instead of username in query params — partially superseded: the IDOR this was meant to close is already fixed (identity now comes from the verified session cookie, not the URL — see Authentication below), so username appearing in a URL is no longer an authorization bypass, just a minor privacy/enumeration nicety. Left open as a cosmetic item.
• Add a surrogate event_id INT AUTO_INCREMENT PK to Event, keeping (group_name, event_name) as a unique constraint, so Transaction stores a 4-byte FK instead of two strings — implemented
• Increase debt DECIMAL(10,2) precision to DECIMAL(15,2) — implemented. Currency-aware debt aggregation (resolving the mismatch when group members use different currencies) is deliberately deferred — it's really the separate multi-currency feature further down this list, not a schema-precision fix.
• Reconsider the Transaction / PaidTransaction split vs. a single table with a status column + partial index — implemented: merged into one Transaction table with status ENUM('pending','paid'), paid_at, and paid_currency (auto-filled by a BEFORE UPDATE trigger on the pending→paid transition). MySQL/InnoDB has no true partial index, so the closest equivalent — a composite index leading with status — is used instead. This does trade away the old design's immutable, append-only audit log; if a strict payment audit trail becomes a requirement later, that's the tradeoff to revisit.

Triggers & Referential Integrity

• Make mark_transaction_paid idempotent at the SQL level (UPDATE ... WHERE is_paid = FALSE) to prevent the trigger double-firing and decrementing debt twice under concurrent requests — implemented
• Review ON DELETE CASCADE on financial tables (e.g. Transaction) — consider RESTRICT or SET NULL to prevent accidental data loss from a direct DELETE FROM Group

FastAPI, ASGI & Async

• Replace the single shared DB connection with a proper connection pool (e.g. aiomysql.create_pool) so concurrent requests don't interleave on the same connection/transaction context
• Add explicit try/except + rollback() around multi-step DB writes instead of relying on implicit rollback on connection close — implemented (upload_receipt, mark_paid, chatbot send/reset routes; all commits/rollbacks now go through a get_connection dependency instead of the private cur._connection)
• Call get_config.cache_clear() in test teardown so @lru_cache'd settings don't leak across tests or survive secret rotation without a restart (moot until a test suite exists)

Authentication & Session Security

• Remove ?userId= query-param-based "auth" — it allows any user to view another user's dashboard (IDOR) — implemented
• Hash passwords with bcrypt/argon2 instead of storing/comparing them in plaintext — implemented (bcrypt)
• Implement JWT-based session auth: sign a token on login, store it in an HttpOnly, Secure, SameSite=Strict cookie, and add a get_current_user dependency used by all protected routes — implemented (src/auth.py; the previously-unused SECRET_PHRASE config value now signs the token)

Transactions & Concurrency

• Stop accessing the private cur._connection attribute; manage transactions via an explicit connection object or context manager — implemented (get_connection dependency in src/db.py)
• Use SELECT ... FOR UPDATE or an atomic conditional UPDATE ... WHERE is_paid = FALSE in mark_transaction_paid to eliminate the race condition described above — implemented
• Replace the Python-side running-balance computation in the analytics endpoint with a SQL window function (SUM(...) OVER (ORDER BY date)) for correctness under equal timestamps and better performance at scale

Trip Planner / Graph Algorithms

• Replace the O(n!) backtracking Hamiltonian cycle search with Held-Karp DP (O(2^n · n²)) for better scaling with city count — not done on purpose: the app has no real per-edge flight-cost data (the adjacency matrix only encodes flight existence), so Held-Karp has no meaningful cost function to optimize yet. Revisit once a cost signal exists. In the meantime, MAX_CITIES = 8 bounds the exponential search.
• Replace blocking requests.get() calls to the flight API with httpx.AsyncClient so the event loop isn't blocked — implemented
• Parallelize adjacency-matrix construction with asyncio.gather() instead of sequential per-pair API calls — implemented
• Normalize TripPathways.path_sequence (JSON-in-TEXT) into a proper TripPathStop(pathway_id, stop_order, destination_id) table with FK constraints

Caching & Rate Limiting

• Move the in-process flight_cache dict to Redis with a TTL (e.g. 1 hour) so caching works across multiple worker processes
• Actually implement the client-side rate limiter (min_request_interval is currently defined but never enforced); prefer a Redis-backed token bucket shared across workers — implemented in-process (asyncio.Lock + asyncio.sleep); still process-local, so the Redis-backed shared version remains open for multi-worker deployments
• Add exponential backoff / retry logic on API 429 responses

AI / LLM Integration (Gemini)

• Add an OCR/verification model to confirm uploaded receipts are genuine — implemented
• Use Gemini's structured output (response_schema / response_mime_type: application/json) instead of relying on prompt instructions for JSON formatting — implemented
• Validate Gemini's response against a Pydantic model and reject malformed structures — implemented (ReceiptAnalysis in src/receipts.py)
• Add a confidence-score threshold below which the analysis is rejected and the user is asked to re-upload — implemented (threshold: 70)
• Harden against prompt injection embedded in receipt image text — partially addressed (the prompt now tells the model to ignore embedded instructions and score confidence low), but this is a prompt-level mitigation only, not a robust defense
• Validate receipt file size before reading fully into memory; cap uploads (e.g. 10MB) and reject oversized files early — implemented
• Validate actual file magic bytes against the declared Content-Type instead of trusting the client-supplied header — implemented
• Stream large uploads directly to object storage rather than buffering the whole file in app memory

Chatbot State Machine

• Move ChatbotState from MySQL to Redis for high-traffic deployments (sub-millisecond reads/writes, shared across instances)
• Add a cron job / TTL cleanup for stale ChatbotState rows (e.g. inactive > 30 days)

System Design & Scalability

• Fix the N+1 query pattern in /groups (nested per-group/per-event queries) by rewriting as 2–3 JOIN queries and assembling in Python — implemented (_load_groups_with_details in src/main.py, shared by /groups and /api/groupsForUser); measured 5 fixed queries total regardless of group/event/transaction count, down from 1+N+N+ΣM
• Add real-time debt notifications (WebSockets or SSE) with Redis Pub/Sub so pushes work across multiple worker processes
• Move receipt storage from local uploads/ to object storage (S3/GCS) with pre-signed, time-limited URLs instead of serving static files
• Use UUID4 (not timestamp-based) filenames for uploaded receipts to avoid collisions — implemented (this also closed a path-traversal hole: the old filename was built from the client-supplied receipt.filename unsanitized)
• Add proper multi-currency support: store currency and a snapshot exchange_rate on each Transaction rather than assuming a single implicit currency
• Add a retention policy (e.g. delete/partition ChatMessage older than 90 days) for privacy/compliance (GDPR / India DPDP Act) and to support account-deletion flows
• Add read replicas for read-heavy analytics/history queries
• Adopt schema migrations (e.g. Alembic) instead of a raw schema.sql file
• Add structured logging (replace print statements) and health-check endpoints for load balancer probes — implemented (src/logging_config.py; unauthenticated GET /health checks DB connectivity)

Bugs Found & Fixed During Hardening

These weren't part of the original review but were caught while implementing the items above:

• /api/groupsForUser referenced nonexistent group_id/user_id columns (the schema uses group_name/username), so the endpoint threw a KeyError on every call — fixed
• The same endpoint didn't convert Decimal transaction amounts to float, so JSON serialization crashed once the previous bug was fixed — fixed
• /api/profile returned the user's bcrypt password hash in the response body — fixed
• The Gemini model name (gemini-1.5-flash) is retired and every OCR call was failing — fixed (now gemini-flash-latest)
• The (blocking, synchronous) Gemini SDK call had no reliable timeout — a slow response would hang the entire single-worker server for all users, and the SDK's own timeout option didn't bound it in testing — fixed (generate_content_async wrapped in asyncio.wait_for)
• /api/transactions never converted Decimal transaction amounts to float, so JSON serialization crashed (pre-existing, unrelated to the schema migration — just never previously exercised by a test) — fixed
• MySQL forbids a statement from reading a table via subquery when a trigger it fires needs to write to that same table — the natural "resolve username→user_id via subquery inside the Transaction INSERT" approach hit this immediately, since after_transaction_insert updates User. Fixed by resolving created_by/owed_by/owed_to to plain ints via a separate SELECT before the insert, in all three call sites (main.py ×2, the chatbot's add-event flow) — fixed