[2026 API] Replace JSON-file persistence in api/data with a real database and migrations

[Nanle-code]

Objective

Make the API server safe for concurrent, multi-instance production use.

Target area

api/data, api/middleware/stores.js, api/routes

Context

The API keeps state in api/data/access_logs.json and learning.json. File-based JSON storage loses writes under concurrency, can't scale horizontally, and ends up in the repo.

Acceptance criteria

• Add a storage interface with SQLite (dev) and Postgres (prod) implementations using a migration tool.
• Access logs, learning data and idempotency keys move to the DB, with retention policies.
• api/data/*.json is removed from git, and a seed script replaces it.
• docker-compose adds Postgres, and integration tests run against a disposable DB.
• Automated tests cover the primary flow, at least one boundary case, and at least one failure case.
• User-facing documentation or developer guidance is updated, including compatibility, security, or migration notes where applicable.

Pull request requirements

• All continuous integration checks must pass before a pull request may be merged.
• The branch must be free of merge conflicts with the target branch at the time of merge.
• Do not request merge with failing workflows, skipped required checks, or unresolved conflicts.

[drips-wave]

@Nanle-code This issue could not be added to the Stellar Wave Program because the per-repo points budget would be exceeded (25000 / 25000 points already used). The label has been automatically removed.

The budget resets at the end of each Wave. You can review the remaining budget for your repos on the Orgs & Repos page.

ℹ️ Learn more about points budgets