Database Abstraction Core

This service owns the storage abstraction layer for the platform.

Responsibilities

• Define and enforce storage adapter contracts.
• Provide pluggable backend adapters (legacy compatibility first, modern backend next).
• Publish a stable adapter API for core services.
• Offer health/readiness endpoints for dependency orchestration.
• Keep migration and compatibility concerns isolated from business service code.

Planned adapters

• mysql50 — compatibility adapter for the existing legacy MySQL 5.0 schema.
• postgres — migration-ready target backend (planned).
• sqlite — local test/dev adapter (planned).

Development

cd services/database-abstraction-core
uv sync
Copy

Start this service only through the automation stack:

uv run --with fastapi==0.115.14 --with uvicorn==0.37.0 --with pydantic==2.11.7 --with starlette==0.46.0 python scripts/automation.py up --start-stack
Copy

For local secrets such as STATS_DB_PASSWORD_CAPETOWN, a repo-root .env file is loaded automatically by the automation launcher and by the local database_abstraction_core.main entrypoint. Existing exported environment variables are preserved and take precedence.

Health check:

• GET /health

Readiness:

• GET /ready

Backend selection and diagnostics:

• GET /backends
• GET /statistics/configuration?company=<company>
• GET /cashier/configuration?company=<company>
• GET /automatedmail/configuration?company=<company>

Statistics adapter endpoints:

• POST /statistics/authenticate (company optional in payload)
• GET /statistics/userreports?limit=500&company=<company>
• POST /statistics/reports/daily-comm-sales
• POST /statistics/reports/daily-mass-sales
• POST /statistics/reports/commodity-summary
• GET /statistics/company-map
• POST /statistics/company-map
• POST /statistics/company-map/test-connection (validates host/user/password and lists visible databases)

Statistics report behavior:

• Implemented Statistics report endpoints do not return mock rows.
• If live adapter/database readiness is missing, report endpoints fail instead of returning synthetic data.

Cashier adapter endpoints:

• POST /cashier/authenticate (company optional in payload)
• POST /cashier/session-bootstrap
• GET /cashier/session-summary?company=<company>&session_id=<session_id>
• GET /cashier/session-read-model?company=<company>&session_id=<session_id>
• POST /cashier/day-end-preview
• POST /cashier/day-end-execute
• GET /cashier/company-map
• POST /cashier/company-map
• POST /cashier/company-map/test-connection (validates host/user/password and lists visible databases)

Automated mail adapter endpoints:

• GET /automatedmail/configuration?company=<company>
• GET /automatedmail/company-map
• POST /automatedmail/company-map
• POST /automatedmail/company-map/test-connection
• GET /automatedmail/queue?company=<company>&filter_text=<text>&limit=100
• GET /automatedmail/queue/<mail_id>?company=<company>
• POST /automatedmail/submit
• POST /automatedmail/queue/<mail_id>/requeue?company=<company>
• DELETE /automatedmail/queue/<mail_id>?company=<company>

Company-map durability:

• The repo now carries deploy-owned baseline company maps at:
  • services/database-abstraction-core/config/company-maps/stats-company-map.json
  • services/database-abstraction-core/config/company-maps/cashier-company-map.json
  • services/database-abstraction-core/config/company-maps/creditors-company-map.json
  • services/database-abstraction-core/config/company-maps/icapv-company-map.json
  • services/database-abstraction-core/config/company-maps/automatedmail-company-map.json
• Server deploys sync those tracked files into .faas-state, so the initial live configuration does not depend on the admin workspace.
• Company mappings are persisted to disk by default at:
  • services/database-abstraction-core/.faas-state/stats-company-map.json
• Override location with:
  • STATS_DB_COMPANY_MAP_FILE=<absolute-or-relative-path>
• STATS_DB_COMPANY_MAP (env JSON) is still supported and overlays persisted values at runtime.
• Statistics company mappings can now carry:
  • database for the main statistics auth/report path, usually iimsstats
  • aux_database for levy and debtors-linked reporting, usually cfidebtors
  • mail_database for the legacy automated-mail queue path, usually automatedmail
• Cashier company mappings are persisted to disk by default at:
  • services/database-abstraction-core/.faas-state/cashier-company-map.json
• Override location with:
  • CASHIER_DB_COMPANY_MAP_FILE=<absolute-or-relative-path>
• CASHIER_DB_COMPANY_MAP (env JSON) is still supported and overlays persisted values at runtime.
• Creditors company mappings are persisted to disk by default at:
  • services/database-abstraction-core/.faas-state/creditors-company-map.json
• Override location with:
  • CREDITORS_DB_COMPANY_MAP_FILE=<absolute-or-relative-path>
• CREDITORS_DB_COMPANY_MAP (env JSON) is still supported and overlays persisted values at runtime.
• ICAPV company mappings are persisted to disk by default at:
  • services/database-abstraction-core/.faas-state/icapv-company-map.json
• Override location with:
  • ICAPV_DB_COMPANY_MAP_FILE=<absolute-or-relative-path>
• ICAPV_DB_COMPANY_MAP (env JSON) is still supported and overlays persisted values at runtime.
• Automated mail company mappings are persisted to disk by default at:
  • services/database-abstraction-core/.faas-state/automatedmail-company-map.json
• Override location with:
  • AUTOMATEDMAIL_DB_COMPANY_MAP_FILE=<absolute-or-relative-path>
• AUTOMATEDMAIL_DB_COMPANY_MAP (env JSON) is still supported and overlays persisted values at runtime.
• Debtors does not currently have a separate company-map file. It uses the Statistics aux DB configuration path for core data and the Statistics mail DB configuration path for automated mail workflows.
• The committed deploy baseline currently points all live MySQL-backed apps at the shared remote account and uses STATS_DB_PASSWORD_CAPETOWN as the baseline secret reference. Runtime overrides can still move any app onto a different env var later.

Migration plan

See documentation/operations/database-migration.md for staged migration strategy from legacy MySQL 5.0 to pluggable backends.