> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lokpanchang.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

> Schema, models, connection, migrations, and backups for the Lok Panchang PostgreSQL database

The application uses **PostgreSQL** with **SQLAlchemy** (Flask-SQLAlchemy) for all persistent data: users, cities, message logs, daily metrics, and system health. This page describes the schema, where models live, how the codebase connects to the database, how migrations work, and placeholders for tech stack and DB backup details.

## Schema overview

The database is organized around a few core tables:

* **user\_detail** — Subscribers: phone number, location (lat/lon, timezone), link to city, subscription state, and activity metrics.
* **city\_detail** — Cached geocoding results (city, state, country, timezone, lat/lon) to avoid repeated API calls.
* **message\_log** — Per-message delivery tracking (status, timing, cost, WhatsApp message ID); can reference user (via `message_text` JSON) and city/api.
* **daily\_metrics** — One row per day: message counts, deliveries, new/unsubscribed users, active users, cost, timezones served.
* **system\_health\_log** — Periodic health snapshots (CPU, memory, disk, connections, queue size, response time, service status).
* **api\_detail** — API definitions (name, call string, params); referenced by message logs.
* **api\_usage\_log** — Per-day, per-service API usage (call count, success/error, cost, response time).
* **ritu\_detail**, **ayanam\_detail** — Seasonal/astronomical metadata keyed by city and date ranges.

**user\_detail** links to **city\_detail** via `city_id`. **message\_log** can reference **city\_detail** and **api\_detail**. **ritu\_detail** and **ayanam\_detail** also reference **city\_detail**.

### Schema diagram (placeholder)

*\[Placeholder: A diagram of the database schema (tables and relationships) will be inserted here.]*

## Model files

All SQLAlchemy models live under the **`models/`** package in the repo root:

| File                          | Table               | Purpose                                                                              |
| ----------------------------- | ------------------- | ------------------------------------------------------------------------------------ |
| `models/user_detail.py`       | `user_detail`       | Users: phone, location, timezone, city\_id, state, subscription and activity fields. |
| `models/city_detail.py`       | `city_detail`       | Cities: city, state, country, timezone, latitude, longitude.                         |
| `models/message_log.py`       | `message_log`       | Message delivery logs: status, timing, cost, WhatsApp ID.                            |
| `models/daily_metrics.py`     | `daily_metrics`     | Daily aggregates: sends, deliveries, subscribers, cost, timezones.                   |
| `models/system_health_log.py` | `system_health_log` | Health checks: CPU, memory, disk, connections, queue, response time.                 |
| `models/api_detail.py`        | `api_detail`        | API definitions.                                                                     |
| `models/api_usage_log.py`     | `api_usage_log`     | API usage per day/service.                                                           |
| `models/ritu_detail.py`       | `ritu_detail`       | Ritu (season) data by city/date.                                                     |
| `models/ayanam_detail.py`     | `ayanam_detail`     | Ayanam data by city/date.                                                            |

The package exposes the shared `db` instance and model classes via **`models/__init__.py`**, which imports from **`db`** (see below) and from each model module. The main app and admin portal import from `models` (e.g. `from models import UserDetail, CityDetail`).

## How the codebase connects to the database

1. **`db.py`** (repo root) creates a single **SQLAlchemy** instance:
   ```python theme={null}
   from flask_sqlalchemy import SQLAlchemy
   db = SQLAlchemy()
   ```
2. **`app.py`** (main Flask app) sets the connection string and initializes the app with the DB and migrations:
   * `app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv("DATABASE_URL")`
   * `db.init_app(app)`
   * `migrate = Migrate(app, db)`
3. **`DATABASE_URL`** is the PostgreSQL connection string (e.g. `postgresql+psycopg2://user:password@host:5432/dbname`). In Docker Compose it is set for the `web`, `celery`, `beat`, and `admin-portal` services.

The **admin portal** (`admin_portal/app.py`) uses the same `models` package and its own Flask app that also sets `SQLALCHEMY_DATABASE_URI` from `DATABASE_URL` and calls `db.init_app(app)`. **Celery** tasks run with a Flask app context that provides the same `db` and models (see [Backend](/backend) and [Scheduling](/scheduling)).

## How migrations work

Migrations are managed with **Flask-Migrate** (Alembic under the hood).

* **Config** — `app.py` registers `Migrate(app, db)`. Alembic uses the Flask app’s `db` and `SQLALCHEMY_DATABASE_URI` via **`migrations/env.py`**, which gets the engine/URL from `current_app.extensions['migrate'].db`.
* **Migration scripts** — Stored in **`migrations/versions/`**. Each file is a revision (e.g. `b73282c5dc65_initial_tables.py`, `add_user_profile_fields.py`, `add_notification_channel.py`). They define `upgrade()` and `downgrade()` using Alembic’s `op` API.
* **Commands** (run from repo root, typically inside the `web` container so `DATABASE_URL` points at the DB):
  * Create a new revision after changing models:\
    `flask db revision -m "description"` (then edit the generated file) or\
    `flask db migrate -m "description"` (auto-generate from model diff).
  * Apply all pending migrations:\
    `flask db upgrade`
  * Roll back one revision:\
    `flask db downgrade`

For a fresh clone, after bringing up the stack and ensuring the database is created, run **`docker compose exec web flask db upgrade`** (see [Quickstart](/quickstart)).

## Tech stack (placeholder)

*\[Placeholder: A short description of the database tech stack (PostgreSQL version, driver, connection pooling if any, and any relevant infrastructure) will be added here.]*

## Database backups to S3 (placeholder)

*\[Placeholder: If the project adds PostgreSQL backups (e.g. pg\_dump) and uploads them to S3, the process and schedule will be described here. Currently, only **log** backups (application and Docker logs) are collected and uploaded to S3; see [Logging](/logging) for that flow.]*