FibDash

A modern React Material-UI dashboard for financial reconciliation with Google SSO authentication, CSV import/analysis, DATEV export, and optional MSSQL integration with JTL tables.

Features

• 🚀 React 18 (class components)
• 🎨 Material-UI (MUI) UI with responsive layout
• 🔐 Google SSO (Google Identity Services) + JWT API auth
• 🗄️ MSSQL integration (optional; app runs without DB)
• 📥 CSV import of bank transactions (German MT940-like CSV)
• 🔍 Reconciliation view: CSV vs JTL (if DB available)
• 📤 DATEV export for selected month/quarter/year
• 🧩 Admin data management (Kreditor, Konto, BU)
• ⚡ Webpack dev server (HMR) + nodemon hot-reload
• 🛡️ Email allowlist authorization
• 🧰 Production single-process build (Express serves React)

Architecture

fibdash/
├── client/                 # Frontend React application
│   ├── src/
│   │   ├── components/     # React class components
│   │   ├── services/       # API service classes
│   │   ├── App.js          # Main application component
│   │   └── index.js        # React entry point
│   └── public/
│       └── index.html      # HTML template
├── src/                    # Backend Express API
│   ├── config/             # Database configuration (MSSQL)
│   ├── middleware/         # Auth + email allowlist middleware
│   ├── routes/             # API routes (auth, data, admin, dashboard)
│   ├── database/           # SQL schema and CSV import schema
│   └── index.js            # Express server entry point
├── nginx.*.conf            # Nginx reverse-proxy configs (dev/prod/simple)
├── webpack*.config.js      # Webpack configs
├── docker-compose.dev.yml  # Optional dev docker-compose for proxying
├── data.csv                # Sample CSV for local analysis
└── package.json            # Dependencies and scripts

Functional Overview

Authentication and Authorization

• Login: Frontend uses Google Identity Services. The backend validates the ID token and issues a JWT.
• Email allowlist: Only emails in AUTHORIZED_EMAILS or matching DB rule are allowed.
  • ENV allowlist: fast path check.
  • DB check: optional, queries JTL tables to verify access by attributes 219/220.
• JWT middleware guards all /api routes.

CSV Analysis and Reconciliation

• Upload or place CSV at project root (data.csv) for quick testing.
• CSV parsing: German semicolon-separated format with headers like Buchungstag, Betrag, Verwendungszweck, IBAN, etc.
• Reconciliation:
  • If MSSQL available: fetch JTL transactions (tZahlungsabgleichUmsatz), related PDFs (tUmsatzBeleg, tPdfObjekt), and links; match by date+amount.
  • Kreditor lookup: optional mapping via fibdash.Kreditor using IBAN; also supports banking accounts via is_banking.
• Summary totals: income, expenses, net, match counts.

DATEV Export

• Endpoint returns CSV in DATEV format for the chosen period (month, quarter, year).
• Headers and column mapping created server-side; amounts normalized; encoding served as text/csv.

Admin Management

• Kreditor: CRUD for name, kreditorId, IBAN (optional if is_banking=true).
• Konto: CRUD account numbers/names used for accounting.
• BU (Buchungsschlüssel): CRUD including optional VSt.
• System info endpoint.

Health

• /api/health returns OK and timestamp.

API Surface (key endpoints)

Auth

• POST /api/auth/google — Google token exchange → JWT
• GET /api/auth/verify — Validate JWT, returns user
• POST /api/auth/logout — Stateless logout success

Dashboard

• GET /api/dashboard — Mock dashboard stats
• GET /api/dashboard/user — Returns current JWT user

Data and Reconciliation

• GET /api/data/months — Available months inferred from data.csv
• GET /api/data/transactions/:timeRange — Combined CSV + JTL view with summary
  • timeRange supports YYYY-MM, YYYY, YYYY-Q1..Q4
• GET /api/data/datev/:timeRange — Download DATEV CSV
• GET /api/data/pdf/umsatzbeleg/:kUmsatzBeleg — Stream PDF from tUmsatzBeleg
• GET /api/data/pdf/pdfobject/:kPdfObjekt — Stream PDF from tPdfObjekt

Kreditor, Konto, BU

• GET /api/data/kreditors
• GET /api/data/kreditors/:id
• POST /api/data/kreditors
• PUT /api/data/kreditors/:id
• DELETE /api/data/kreditors/:id
• GET /api/data/assignable-kreditors — only non-banking
• BankingAccountTransactions assignments: POST/PUT/DELETE, and GET /api/data/banking-transactions/:transactionId
• Admin counterparts exist under /api/admin for Kreditor/Konto/BU CRUD.

CSV Import (to DB)

• POST /api/data/import-csv-transactions — Validates rows and inserts into fibdash.CSVTransactions
• GET /api/data/csv-transactions — Paginated list with kreditor joins and assignment info
• GET /api/data/csv-import-batches — Import batch summaries

Frontend UX Summary

App shell

• Top AppBar with title, tabs (Dashboard, Stammdaten), current user, DATEV export button when applicable, and logout.

Login

• Button triggers Google prompt; robust error messaging for SSO, service unavailability, authorization.

Dashboard view

• Month selector, summary header, and a transactions table with reconciliation indicators (CSV-only, JTL-only, matched), PDF links when available.

Stammdaten view

• Management UI for Kreditors, Konten, and Buchungsschlüssel (class-based components under client/src/components/admin).

CSV Import

• Modal dialog with drag-and-drop or file picker, header detection, basic validation, progress, and results summary; uses /api/data/import-csv-transactions.

Prerequisites

• Node.js (v16+)
• Optionally: MSSQL Server for JTL and fibdash schema
• Google Cloud project and OAuth 2.0 Client ID

Setup

1. Clone and install

git clone <your-repo-url>
cd fibdash
npm install

2. Environment

cp .env.example .env
# then edit .env

Required variables

• GOOGLE_CLIENT_ID, GOOGLE_CLIENT_SECRET
• REACT_APP_GOOGLE_CLIENT_ID (must match GOOGLE_CLIENT_ID)
• JWT_SECRET
• Optional authorization: AUTHORIZED_EMAILS=admin@company.com,user@company.com
• MSSQL: DB_SERVER, DB_DATABASE, DB_USERNAME, DB_PASSWORD, DB_PORT=1433
• Server: PORT=5000, NODE_ENV=development

3. Google OAuth

• Create OAuth 2.0 Web Client in Google Cloud Console.
• Authorized origins: http://localhost:5001 (dev), your domain(s).
• Authorized redirects: matching roots (e.g., http://localhost:5001/).
• See detailed guide: docs/GOOGLE_OAUTH_SETUP.md

4. Database (optional but required for JTL features)

• Create DB and run schemas:
  • Core schema: see src/database/schema.sql
  • CSV import table schemas if needed
• The app will run without DB; JTL features and admin CRUD will error if DB not configured.

5. Development

npm run dev            # runs frontend at 5001 and backend at 5000
# or separately:
npm run dev:frontend
npm run dev:backend

6. Optional Nginx for dev

• Automatic:

npm run setup:nginx

• Manual:

sudo cp nginx.simple.conf /etc/nginx/sites-available/fibdash-dev
sudo ln -s /etc/nginx/sites-available/fibdash-dev /etc/nginx/sites-enabled/
sudo nginx -t && sudo systemctl reload nginx

• Hosts entry (optional): 127.0.0.1 fibdash.local

With nginx:

• App: http://localhost/ or http://fibdash.local/
• API: http://localhost/api/
• Direct FE: http://localhost:5001/
• Direct BE: http://localhost:5000/

Production

Single-process model

• Express serves built React app and handles APIs and auth.

Build and run

npm start          # build frontend and start backend
# or:
npm run build
npm run start:prod

Nginx production reverse proxy

sudo cp nginx.prod.conf /etc/nginx/sites-available/fibdash-prod
sudo ln -s /etc/nginx/sites-available/fibdash-prod /etc/nginx/sites-enabled/
sudo nginx -t && sudo systemctl reload nginx

Prod features:

• Static asset caching, gzip, security headers, optional TLS.

Environment variables

• GOOGLE_CLIENT_ID, GOOGLE_CLIENT_SECRET
• REACT_APP_GOOGLE_CLIENT_ID
• JWT_SECRET
• AUTHORIZED_EMAILS (comma-separated; if unset/empty, no users can access)
• DB_SERVER, DB_DATABASE, DB_USERNAME, DB_PASSWORD, DB_PORT
• PORT, NODE_ENV

Data model (MSSQL)

Core tables referenced

• eazybusiness.dbo.tZahlungsabgleichUmsatz (+ tUmsatzKontierung)
• tUmsatzBeleg (PDF storage), tPdfObjekt (PDF objects), tZahlungsabgleichUmsatzLink (links)
• fibdash.Kreditor (id, iban, name, kreditorId, is_banking)
• fibdash.Konto (id, konto, name)
• fibdash.BU (id, bu, name, vst)
• fibdash.BankingAccountTransactions (assignment records)
• fibdash.CSVTransactions (imported CSV rows)

See SQL in src/database/schema.sql and src/database/csv_transactions_schema.sql for exact DDL.

Developer notes

• Backend auto-detects DB availability. If DB env is missing, it logs a warning and continues. Endpoints that require DB will respond with 500/404 as applicable.
• data.csv at repo root is used by /api/data/months and /api/data/transactions/* for local CSV-based analysis.
• Matching logic between CSV and JTL uses exact amount and same-day date to mark matches.
• DATEV export uses well-formed header, consistent number formatting, and limited text field lengths.

Scripts

• npm run dev — run FE+BE
• npm run dev:frontend — FE only (HMR)
• npm run dev:backend — BE only (nodemon)
• npm run build — FE production build
• npm run build:prod — build and start production server
• npm start — build FE and start BE
• npm run start:prod — start BE with existing build
• npm run setup:nginx — dev nginx setup
• npm run nginx:test|reload|start|stop|status — helpers

Troubleshooting

Database

• Ensure MSSQL reachable; set DB_* env; firewall allows 1433; check logs.
• Without DB, JTL and admin features won’t function; CSV-only features still work.

Google OAuth

• Ensure both GOOGLE_CLIENT_ID and REACT_APP_GOOGLE_CLIENT_ID set and equal.
• Add dev/prod origins and redirects in Google Cloud Console.
• Use HTTPS in production and ensure CSP allows Google domains.
• See docs/GOOGLE_OAUTH_SETUP.md.

CORS/Headers

• Dev CORS is open. Tighten in prod behind nginx.

Hot reload

• Make sure both dev servers run; clear cache; check proxy configs.

License

ISC