refactor: persist quota tracking and key usage in the database

[yjing86]

refactor: persist quota tracking and key usage in the database

While reviewing the current API quota flow, I noticed that request quota tracking is currently stored only in memory.

At the moment:

• quota usage is tracked through the in-memory user_quotas dictionary in app/routes/api.py
• quota state is reset whenever the app restarts
• quota state is not shared across multiple workers or instances
• remaining quota is derived by looking up the API key again from settings.API_KEYS using the user's name

I think this should be moved into the database.

Why the current approach is fragile

There are a few problems with the current implementation:

• quota usage is lost on restart
• quota enforcement becomes inconsistent in multi-instance deployments
• usage state is tied to process memory instead of persistent application state
• remaining quota calculation depends on reverse-matching by name, which is not a stable identifier

That last point is especially risky: if two API keys ever share the same display name, quota accounting can become incorrect.

Proposed improvement

Persist quota tracking and API key usage in the database instead of storing it in process memory.

The database should be the source of truth for:

• total requests used
• per-day quota usage
• request timestamps
• API key activity status

Quota checks should be done using the actual API key or key id, not by reverse-matching the user name.

Suggested design

A possible design would be:

Option 1: store daily usage counters

Create a table keyed by:

• api_key_id
• date

with fields such as:

• request_count
• last_request_at

This is probably the simplest implementation.

Option 2: store request logs and derive counters

Create a request usage table with one row per request, then compute daily totals from it.

This gives more observability, but it is heavier than necessary for a first step.

I think Option 1 is the better starting point.

Recommended behavior

• identify users by API key id, not display name
• read and update quota counters from the database
• keep quota enforcement consistent across restarts and deployments
• return remaining quota based on persisted usage data
• make the quota reset logic date-based in the database instead of process-local memory

Why this is better

This would make quota handling:

• correct across app restarts
• safe for multiple workers / multiple instances
• based on stable identifiers
• easier to audit and extend later

It would also make future features easier, such as:

• usage history
• admin reporting
• per-user analytics
• rate limits beyond daily quota
• different quota levels for different API keys

Acceptance criteria

• quota usage is no longer stored only in memory
• quota tracking is keyed by API key id or key value, not user display name
• quota remains correct after restart
• quota remains correct across multiple app instances
• API responses still include remaining quota information
• migration/documentation is included for the new quota storage model

If maintainers think this is a useful cleanup, I’d be happy to work on it.