# sales_service Migrations Inventory

## Migration Files

Sales-specific migrations:

| File | Purpose |
|---|---|
| `migrations/sql/0023_sales_sqlite.sql` | Creates main sales tables for SQLite. |
| `migrations/sql/0023_sales_postgres.sql` | Creates main sales tables for Postgres. |
| `migrations/sql/0024_sales_external_links_sqlite.sql` | Adds `sales_external_links` for SQLite. |
| `migrations/sql/0024_sales_external_links_postgres.sql` | Adds `sales_external_links` for Postgres. |

## Migration Runner

Runner: `scripts/migrate_core_db.py`.

Behavior:

- Detects dialect from `DATABASE_URL`.
- Selects all `*_{dialect}.sql` files in `migrations/sql`.
- Applies them in sorted filename order.
- Tracks applied files in `schema_migrations`.

Runtime schema mode:

- SQLite defaults to `SCHEMA_MANAGEMENT_MODE=legacy`, which calls `Base.metadata.create_all`.
- Non-SQLite requires `SCHEMA_MANAGEMENT_MODE=migrations`.
- `SCHEMA_MANAGEMENT_MODE=migrations` validates that all checked-in migration files were applied.

## Smoke Check

Command used:

```bash
DATABASE_URL="sqlite:///.../migration_check.db" \
SCHEMA_MANAGEMENT_MODE=migrations \
.venv/bin/python scripts/migrate_core_db.py
```

Result:

- Exit status: 0.
- Applied migrations include `0023_sales_sqlite.sql` and `0024_sales_external_links_sqlite.sql`.
- `missing_migration_versions()` returned `[]`.
- `services.sales_service.app` imports successfully after migrations.

Full output: `sales_service_migration_check.txt`.

## Observations

- `migrations/README.md` is stale: it still lists only `0001` and `0002`, while the repo now has migrations through `0024`.
- Sales migrations create no physical foreign keys between sales tables.
- Several external ids are indexed but not unique: messages, calls, payments.
- There is no migration for `Tenant`, configurable `Pipeline`, or configurable `Stage`.
- ORM metadata has more `index=True` declarations than the explicit SQL migrations create. Use `sales_service_er_db_sqlite.md` for the migrated DB baseline and `sales_service_er_map.md` for ORM intent.
