Bill Tracker

Bill Tracker is a self-hosted month-to-month bill tracking app for managing recurring bills, payments, monthly bill history, user-owned financial data, imports/exports, backups, and admin operations.

The app is built as a full-stack JavaScript project with a React/Vite frontend, Node/Express backend, and SQLite database.

---

Features

Monthly Bill Tracking

• Track bills by selected month and year
• View active bills for the current month
• Record payments
• Track actual monthly bill amounts
• Add month-specific notes
• Mark bills as skipped for a specific month
• Separate global bill templates from monthly bill state

Bills

• Create and edit recurring bill templates
• Bill due day is stored as a day of the month, such as 1, 15, or 31
• Supports month-end clamping for due days like 31
• Optional credit card interest/APR field
• Active/inactive bill support
• Safe deactivate/reactivate flow
• Destructive delete flow with confirmation
• Historical visibility metadata and range support for inactive bills

Payments

• Add, update, delete, and restore payments
• Payments are scoped to the signed-in user
• Payment data is included in tracker totals and exports
• Payment history remains separate from global bill templates

Categories

• User-owned categories
• Categories can be associated with bills
• Category bill counts are supported
• Categories are scoped to the signed-in user

Monthly Bill State

Each bill can have month-specific state:

• actual_amount
• notes
• is_skipped
• year/month association

This allows one bill template to have separate history for every month.

---

User Accounts and Profile

Bill Tracker supports local username/password accounts and optional authentik/OIDC login.

Profile features include:

• Display name
• Notification preferences
• Password change
• User data exports
• User SQLite import
• Spreadsheet import history

The display name is shown in the top navigation where available. If no display name is set, the app falls back to the username.

---

Authentication

Local Login

Local username/password login is supported and remains available by default.

Local users can:

• log in with username/password
• change their password
• complete first-login password reset flows
• use all normal user-owned bill tracking features

authentik / OIDC Login

The backend is prepared for authentik using OpenID Connect.

Supported OIDC behavior:

• Authorization Code flow
• PKCE
• state validation
• nonce validation
• JWKS-backed ID token signature verification via openid-client
• issuer validation
• audience validation
• expiration validation
• authentik group-to-role mapping
• auto-provisioning of valid authentik users
• local session creation after successful OIDC login

Admin role is never granted by default. A user becomes admin through the configured authentik admin group only.

Admin-Controlled Login Methods

Admins can configure login methods from the Admin panel:

• Enable/disable local username/password login
• Enable/disable authentik/OIDC login
• Configure authentik issuer URL
• Configure client ID
• Configure client secret
• Configure redirect URI
• Configure scopes
• Configure admin group
• Configure auto-provisioning

Lockout protection prevents disabling local login unless authentik is configured, enabled, and has an admin group mapping.

---

Data Ownership

Bill Tracker uses user-owned data separation.

The following data is scoped to the signed-in user:

• Bills
• Categories
• Payments
• Monthly bill state
• Imports
• Exports
• Tracker views

Users cannot select another user_id from the client. Backend routes derive ownership from the authenticated session.

Admin full database backup/restore is separate from user-owned import/export.

---

Import and Export

XLSX Spreadsheet Import

Bill Tracker supports importing historical bill data from Google Sheets .xlsx exports.

The XLSX import flow includes:

• Upload spreadsheet
• Preview before applying
• Multi-sheet parsing
• Month/year detection from sheet names
• Bill match recommendations
• Match existing bill
• Create new bill
• Skip row
• Bulk skip
• Bulk create new bills
• Ambiguous row blocking
• Import history

Supported sheet/tab patterns include examples like:

• Jan 2026
• January 2026
• 2026-01
• 01-2026
• 2026 May
• Bills May 2026
• May with default year

Non-data sheets like Summary, Totals, Dashboard, Notes, Categories, Settings, Overview, and Template are skipped or treated as non-month sheets.

User SQLite Export

Users can export their own data as a SQLite database.

The user export includes only that user’s safe bill-tracker data, such as:

• Bills
• Categories
• Payments
• Monthly bill state
• Notes
• Export metadata

It does not include:

• Password hashes
• Sessions
• Cookies
• Admin settings
• SMTP credentials
• Backup files
• Server paths
• Other users’ data

User SQLite Import

Users can import a SQLite export created by this app.

The user SQLite import flow includes:

• Upload user SQLite export
• Validate SQLite file
• Confirm export format is user_data
• Preview before apply
• Apply only after confirmation
• Create missing records
• Skip conflicts by default
• No overwrite by default
• Import history recording

This is not a full-system restore and does not use the admin backup import path.

Excel Databook Export

Users can export an Excel workbook containing their own bill-tracker data for records and review.

---

Admin Tools

Admin users have access to an Admin area while still keeping access to the normal user app.

Admin tools include:

User/Admin Management

• Admin-only routes
• Admin-only navigation
• Normal app navigation remains available for admins

Authentication Methods

Admins can configure:

• Local login enabled/disabled
• authentik/OIDC login enabled/disabled
• authentik provider settings
• auto-provisioning
• admin group mapping

Database Backups

Admin full database tools are separate from user data tools.

Admin backup features include:

• Manual database backup
• Backup listing
• Backup download
• Backup restore
• Pre-restore backup creation
• SQLite integrity checks
• Path traversal protection
• Managed backup directory

Full database backups are admin-only.

Cleanup and Maintenance

Admin cleanup tools include:

• Expired import session cleanup
• Temporary export file cleanup
• Backup partial cleanup
• Optional import history trimming
• Manual cleanup run
• Cleanup status and last-result display

Cleanup tasks run through the daily worker and are non-fatal.

---

Status Page

The Status page provides safe read-only operational information.

It may show:

• App status
• Runtime status
• Database status
• Daily worker status
• Maintenance cleanup status
• Notification status
• Backup status
• Server clock
• Tracker health
• Recent errors

The Status page does not expose:

• Admin controls
• Cleanup settings controls
• Secrets
• Stack traces
• Absolute filesystem paths

---

Security

Bill Tracker includes several security hardening measures:

• Cookie-based authentication
• Production cookie secure flag support
• sameSite: strict
• Route-level authentication middleware
• Admin-only middleware
• User ownership enforcement
• Rate limiting on sensitive endpoints
• CORS allowlist support through configuration
• Global security headers
• Settings filtering to avoid leaking secrets
• Backup path traversal protection
• SQLite integrity check before restore
• User imports scoped to signed-in user
• User exports exclude auth/admin/system data
• OIDC token verification through openid-client
• authentik admin role mapping is explicit and not granted by default
• Error responses avoid leaking stack traces and internal paths

Known Security Notes

• Downloaded exports and backups contain sensitive financial data and should be protected.
• Admin full database backups are not encrypted by default.
• CSP is deferred because it requires auditing Vite, Tailwind, Radix, and shadcn output.
• authentik live login must be tested against a real authentik instance in the target deployment.
• OIDC single logout is not currently implemented.

---

Tech Stack

Frontend

• React
• Vite
• Tailwind CSS
• shadcn/Radix-style components
• Material Design shadcn theme
• OKLCH theme tokens
• lucide-react icons

Backend

• Node.js
• Express
• CommonJS modules
• better-sqlite3
• bcryptjs
• cookie-parser
• express-rate-limit
• openid-client

Database

• SQLite
• App-managed schema/migrations on startup
• User-owned relational data
• Admin backup support

---

Project Structure

Typical project structure:

client/
  components/
  contexts/
  lib/
  pages/
  api.js

db/
  database.js
  schema.sql

middleware/
  requireAuth.js
  rateLimiter.js
  securityHeaders.js

routes/
  auth.js
  authOidc.js
  admin.js
  bills.js
  categories.js
  payments.js
  tracker.js
  profile.js
  import.js
  export.js
  settings.js
  status.js
  version.js

services/
  authService.js
  backupService.js
  cleanupService.js
  notificationService.js
  oidcService.js
  statusService.js
  userDbImportService.js

workers/
  dailyWorker.js

scripts/
  test-import.js
  test-oidc-smoke.js

setup/
  firstRun.js

---

Installation

Install dependencies:

npm install

Run the app in development:

npm run dev

Run only the API in development:

npm run dev:api

Run only the UI in development:

npm run dev:ui

Build the frontend:

npm run build

Start the production server:

npm start

---

Common Commands

npm run dev
npm run dev:api
npm run dev:ui
npm run build
npm start

Useful test/smoke scripts if present:

node scripts/test-import.js
node scripts/test-oidc-smoke.js

---

Configuration

Common environment variables may include:

PORT=3000
NODE_ENV=production
HTTPS=true
CORS_ORIGIN=https://bills.example.com

authentik / OIDC

authentik can now be configured from the Admin panel. Environment variables may still be used as fallback/bootstrap values when database settings are blank.

Possible OIDC-related variables:

OIDC_ENABLED=true
OIDC_ISSUER_URL=https://auth.example.com/application/o/bill-tracker/
OIDC_CLIENT_ID=your-client-id
OIDC_CLIENT_SECRET=your-client-secret
OIDC_REDIRECT_URI=https://bills.example.com/api/auth/oidc/callback
OIDC_SCOPES="openid email profile groups"
OIDC_ADMIN_GROUP=bill-tracker-admins
OIDC_DEFAULT_ROLE=user
OIDC_AUTO_PROVISION=true
OIDC_PROVIDER_NAME=authentik

Database-backed Admin settings take precedence over environment fallback values once configured.

Never expose OIDC_CLIENT_SECRET to the frontend.

---

authentik Setup

In authentik:

1. Create an OAuth2/OpenID Provider.
2. Use a confidential client.
3. Use Authorization Code flow.
4. Set redirect URI to:

https://your-domain.example/api/auth/oidc/callback

5. Include scopes:

openid email profile groups

6. Ensure authentik sends a groups claim.
7. Create or choose an authentik group for Bill Tracker admins.
8. Configure the same admin group in Bill Tracker Admin settings.
9. Keep local login enabled until authentik login is tested.
10. Only disable local login after confirming an authentik admin can access the Admin panel.

---

Data Model Highlights

Core data areas include:

• users
• bills
• categories
• payments
• monthly_bill_state
• bill_history_ranges
• import_sessions
• import_history
• settings
• oidc_states

Bills are recurring templates. Month-specific values belong in monthly bill state.

---

Backup vs Export

Bill Tracker intentionally separates full-system backups from user exports.

Admin Backup

• Full SQLite database
• Admin-only
• Can be restored
• Includes system data
• Used for disaster recovery

User Export

• Signed-in user’s own data only
• Can be downloaded by the user
• Can be imported back through user SQLite import
• Does not include auth/admin/system data

---

License

License: Not specified.