# Database Design & Schemas

## 1. Relational Groups

### Group A: Identity & Tenancy
- `users`: Identity records, Argon2id password hashes, status (`active`, `suspended`, `pending`), `is_superuser`.
- `tenants`: Workspaces created by users, slug, status, plan ID.
- `tenant_members`: Junction table mapping users to tenants with role (`owner`, `admin`, `manager`, `operator`, `viewer`).
- `sessions`: Refresh token hashes, user agents, IP addresses, expiration, and revocation flags.
- `invitations`: Workspace invite tokens and expiration.

### Group B: Roles & Permissions (RBAC)
- `roles`: Role definitions (system templates or tenant-customized).
- `permissions`: Fine-grained permission codes (`tenant:manage`, `bot:create`, `billing:manage`, etc.).
- `role_permissions`: M2M association between roles and permissions.

### Group C: Telegram Bot Management
- `bots`: Tenant-scoped Telegram bot instances.
- `bot_credentials`: AES-256-GCM encrypted bot tokens with key versioning.
- `bot_webhooks`: Webhook configurations with secret tokens.
- `bot_settings`: JSONB configurations per bot.
- `bot_commands`: Registered commands and handler routing.
- `bot_updates`: Telegram update tracking with deduplication constraint `(bot_id, update_id)`.
- `bot_users`: End-users who interact with client bots on Telegram.

### Group D: Audit & Security
- `audit_logs`: Detailed audit trail capturing tenant, user, action, resource, IP address, and payload.

## 2. Migrations
Database migrations are strictly managed using Alembic async:
```bash
# Generate migration
alembic revision --autogenerate -m "migration_name"

# Apply migration
alembic upgrade head
```
