# Story 1.4: Core Domain Models & First Database Migration

Status: review

## Story

As a developer,
I want all Phase 1 domain models created and the first database migration applied with all required indexes,
So that the schema is stable, forward-compatible with Phase 2, and won't require live-data migrations later.

## Acceptance Criteria

1. **Given** Stories 1.1–1.3 are complete, **when** this story is complete, **then** model files exist in `app/models/` for all Phase 1 domain entities: Account, Transaction (with Phase 2 nullable columns), Category (is_system BOOL, is_active BOOL soft-delete), Budget, Bill, Debt, PaydownPlan (archived_at nullable, last_recalculated_at nullable), PaydownPlanCard, PaydownBalanceUpdate, ImportBatch, MerchantMapping (user_confirmed BOOL default False — full Phase 2 schema), Settings, StagedTransactionModel (for staging DB, committed_at TIMESTAMP nullable).
2. `flask db init && flask db migrate && flask db upgrade` completes without errors.
3. The migration includes 5 required indexes: `idx_transactions_date`, `idx_transactions_category`, `idx_transactions_account`, `idx_bills_due_date`, `idx_balance_updates_account`.
4. `tests/test_models/test_schema.py` queries `sqlite_master` and asserts all 5 indexes exist (runs via pytest against in-memory DB, no migration required).
5. The migration seeds 14 system categories (`is_system=True`, `is_active=True`): Housing, Groceries, Dining, Transportation, Utilities, Healthcare, Entertainment, Shopping, Subscriptions, Personal Care, Debt Payments, Income, Transfers, Uncategorized.
6. `pytest tests/` passes with all Stories 1.2–1.3 tests still green (no regressions).

## Tasks / Subtasks

- [x] **Task 1: Create missing model stub files** (AC: 1)
  - [x] Create `app/models/paydown_plan_card.py` with `# TODO: implement in Story 1.4` stub
  - [x] Create `app/models/paydown_balance_update.py` with `# TODO: implement in Story 1.4` stub

- [x] **Task 2: Implement all 12 main-DB models** (AC: 1, 3)
  - [x] `app/models/account.py` — Account model (see exact spec below)
  - [x] `app/models/category.py` — Category model with is_system, is_active, and UniqueConstraint on name
  - [x] `app/models/transaction.py` — Transaction model with Phase 2 nullable columns + 3 indexes in `__table_args__`
  - [x] `app/models/budget.py` — Budget model with UniqueConstraint on (category_id, month, year)
  - [x] `app/models/bill.py` — Bill model with due_day (1-31) + due_date DATE + idx_bills_due_date index
  - [x] `app/models/debt.py` — Debt model
  - [x] `app/models/paydown_plan.py` — PaydownPlan model with archived_at, last_recalculated_at nullable
  - [x] `app/models/paydown_plan_card.py` — PaydownPlanCard model
  - [x] `app/models/paydown_balance_update.py` — PaydownBalanceUpdate model with idx_balance_updates_account index
  - [x] `app/models/import_batch.py` — ImportBatch model
  - [x] `app/models/merchant_mapping.py` — MerchantMapping model with full Phase 2 fields (AR-6)
  - [x] `app/models/settings.py` — Settings model (single-row, id=1 convention)

- [x] **Task 3: Implement StagedTransactionModel on separate Base** (AC: 1)
  - [x] `app/models/staged_transaction.py` — uses `declarative_base()` (NOT `db.Model`), separate from main SQLAlchemy metadata
  - [x] Fields: id, date, merchant_raw, merchant_normalized, amount, is_credit, issuer, confidence_score, dedup_hash, raw_text, import_batch_id (nullable, no FK), committed_at (nullable), category_id (nullable, no FK), account_id (nullable, no FK), status DEFAULT 'pending'
  - [x] Include docstring explaining the separate-engine design (Epic 9 wires it)

- [x] **Task 4: Update `app/models/__init__.py` and `app/__init__.py`** (AC: 2)
  - [x] `app/models/__init__.py` — import all 12 main-DB models so SQLAlchemy metadata is populated before Flask-Migrate autogenerate
  - [x] `app/__init__.py` — add `__import__('app.models')` AFTER `db.init_app(app)` (use `__import__` not `import app.models` to avoid rebinding the local `app` Flask variable)

- [x] **Task 5: Run flask db init → migrate → upgrade** (AC: 2, 3, 5)
  - [x] `cd /var/www/html/financials && source .venv/bin/activate`
  - [x] `FLASK_APP=app flask db init`
  - [x] `FLASK_APP=app flask db migrate -m "Initial schema — Phase 1 models and indexes"`
  - [x] Review the generated migration file in `migrations/versions/` — verify all 13 tables are present and all 5 indexes appear
  - [x] Add category seed data to the migration's `upgrade()` function (see exact seed data below)
  - [x] `FLASK_APP=app flask db upgrade`
  - [x] Confirm `instance/financials.db` was created

- [x] **Task 6: Write and run `tests/test_models/test_schema.py`** (AC: 3, 4)
  - [x] Create `tests/test_models/test_schema.py` with 5 index assertion tests (see exact implementation below)
  - [x] `pytest tests/test_models/test_schema.py -v` → all tests pass
  - [x] `pytest tests/ -v` → all prior tests still pass (4 passing + 1 skipped from Story 1.3)

## Dev Notes

### ⚠️ Critical Order Constraint (AR-2 Compliance)

`flask db init` is intentionally run here for the FIRST TIME — not in any prior story. Confirm before proceeding:
```bash
ls migrations/versions/  # must not exist yet (or be empty)
```

### Model Exact Specifications

All models use `from app.extensions import db`. All money amounts use `db.Numeric(10, 2)`. All dates stored as `db.Text` (ISO 8601 per architecture). All models are `db.Model`.

#### `app/models/account.py`

```python
from app.extensions import db


class Account(db.Model):
    __tablename__ = 'accounts'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    type = db.Column(db.String(20), nullable=False)  # checking / savings / credit / loan
    institution_name = db.Column(db.String(200), nullable=True)
    is_active = db.Column(db.Boolean, nullable=False, default=True)

    # Phase 2 / Credit card fields — nullable, required for Epic 6
    current_balance = db.Column(db.Numeric(10, 2), nullable=True)
    apr = db.Column(db.Numeric(6, 4), nullable=True)       # e.g. 19.9900 for 19.99%
    min_payment = db.Column(db.Numeric(10, 2), nullable=True)
    credit_limit = db.Column(db.Numeric(10, 2), nullable=True)

    created_at = db.Column(db.DateTime, server_default=db.func.now(), nullable=False)

    # Relationships
    transactions = db.relationship('Transaction', back_populates='account', lazy='dynamic')
    paydown_plan_cards = db.relationship('PaydownPlanCard', back_populates='account', lazy='dynamic')
    balance_updates = db.relationship('PaydownBalanceUpdate', back_populates='account', lazy='dynamic')
```

#### `app/models/category.py`

```python
from app.extensions import db


class Category(db.Model):
    __tablename__ = 'categories'
    __table_args__ = (
        db.UniqueConstraint('name', name='uq_categories_name'),
    )

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    is_system = db.Column(db.Boolean, nullable=False, default=False)
    is_active = db.Column(db.Boolean, nullable=False, default=True)

    # Relationships
    transactions = db.relationship('Transaction', back_populates='category', lazy='dynamic')
    budgets = db.relationship('Budget', back_populates='category', lazy='dynamic')
    bills = db.relationship('Bill', back_populates='category', lazy='dynamic')
    merchant_mappings = db.relationship('MerchantMapping', back_populates='category', lazy='dynamic')
```

#### `app/models/transaction.py`

```python
from app.extensions import db


class Transaction(db.Model):
    __tablename__ = 'transactions'
    __table_args__ = (
        db.Index('idx_transactions_date', 'date'),
        db.Index('idx_transactions_category', 'category_id'),
        db.Index('idx_transactions_account', 'account_id'),
    )

    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.Text, nullable=False)              # ISO 8601: '2026-05-01'
    merchant_normalized = db.Column(db.String(200), nullable=False)
    amount = db.Column(db.Numeric(10, 2), nullable=False)
    is_credit = db.Column(db.Boolean, nullable=False, default=False)
    is_manual = db.Column(db.Boolean, nullable=False, default=True)
    notes = db.Column(db.Text, nullable=True)
    created_at = db.Column(db.DateTime, server_default=db.func.now(), nullable=False)

    # FKs
    account_id = db.Column(db.Integer, db.ForeignKey('accounts.id', ondelete='RESTRICT'), nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id', ondelete='RESTRICT'), nullable=True)

    # Phase 2 — do not remove these columns
    dedup_hash = db.Column(db.String(64), nullable=True)
    issuer = db.Column(db.String(100), nullable=True)
    merchant_raw = db.Column(db.Text, nullable=True)
    confidence_score = db.Column(db.Float, nullable=True)
    import_batch_id = db.Column(db.Integer, db.ForeignKey('import_batches.id', ondelete='SET NULL'), nullable=True)

    # Relationships
    account = db.relationship('Account', back_populates='transactions')
    category = db.relationship('Category', back_populates='transactions')
    import_batch = db.relationship('ImportBatch', back_populates='transactions')
```

#### `app/models/budget.py`

```python
from app.extensions import db


class Budget(db.Model):
    __tablename__ = 'budgets'
    __table_args__ = (
        db.UniqueConstraint('category_id', 'month', 'year', name='uq_budgets_category_month_year'),
    )

    id = db.Column(db.Integer, primary_key=True)
    month = db.Column(db.Integer, nullable=False)          # 1–12
    year = db.Column(db.Integer, nullable=False)
    amount = db.Column(db.Numeric(10, 2), nullable=False)

    category_id = db.Column(db.Integer, db.ForeignKey('categories.id', ondelete='RESTRICT'), nullable=False)
    category = db.relationship('Category', back_populates='budgets')
```

#### `app/models/bill.py`

```python
from app.extensions import db


class Bill(db.Model):
    __tablename__ = 'bills'
    __table_args__ = (
        db.Index('idx_bills_due_date', 'due_date'),
    )

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    amount = db.Column(db.Numeric(10, 2), nullable=False)
    due_day = db.Column(db.Integer, nullable=False)        # 1–31: day of month for recurrence
    due_date = db.Column(db.Text, nullable=True)           # ISO 8601: computed next occurrence
    payee = db.Column(db.String(200), nullable=True)
    is_active = db.Column(db.Boolean, nullable=False, default=True)

    category_id = db.Column(db.Integer, db.ForeignKey('categories.id', ondelete='RESTRICT'), nullable=True)
    category = db.relationship('Category', back_populates='bills')
```

#### `app/models/debt.py`

```python
from app.extensions import db


class Debt(db.Model):
    __tablename__ = 'debts'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False)
    current_balance = db.Column(db.Numeric(10, 2), nullable=False)
    interest_rate = db.Column(db.Numeric(6, 4), nullable=False)    # e.g. 5.2500 for 5.25%
    min_payment = db.Column(db.Numeric(10, 2), nullable=False)
    due_date = db.Column(db.Text, nullable=True)                   # ISO 8601 specific date
    is_active = db.Column(db.Boolean, nullable=False, default=True)
```

#### `app/models/paydown_plan.py`

```python
from app.extensions import db


class PaydownPlan(db.Model):
    __tablename__ = 'paydown_plans'

    id = db.Column(db.Integer, primary_key=True)
    strategy = db.Column(db.String(30), nullable=False)    # avalanche / snowball / highest_balance / proportional / custom
    extra_monthly = db.Column(db.Numeric(10, 2), nullable=False, default=0)
    status = db.Column(db.String(20), nullable=False, default='active')   # active / archived
    archived_at = db.Column(db.DateTime, nullable=True)
    last_recalculated_at = db.Column(db.DateTime, nullable=True)
    created_at = db.Column(db.DateTime, server_default=db.func.now(), nullable=False)

    cards = db.relationship('PaydownPlanCard', back_populates='plan', lazy='dynamic')
```

#### `app/models/paydown_plan_card.py`

```python
from app.extensions import db


class PaydownPlanCard(db.Model):
    __tablename__ = 'paydown_plan_cards'

    id = db.Column(db.Integer, primary_key=True)
    monthly_allocation = db.Column(db.Numeric(10, 2), nullable=False)
    starting_balance = db.Column(db.Numeric(10, 2), nullable=False)
    starting_apr = db.Column(db.Numeric(6, 4), nullable=False)    # e.g. 19.9900 for 19.99%

    plan_id = db.Column(db.Integer, db.ForeignKey('paydown_plans.id', ondelete='CASCADE'), nullable=False)
    account_id = db.Column(db.Integer, db.ForeignKey('accounts.id', ondelete='RESTRICT'), nullable=False)

    plan = db.relationship('PaydownPlan', back_populates='cards')
    account = db.relationship('Account', back_populates='paydown_plan_cards')
```

#### `app/models/paydown_balance_update.py`

```python
from app.extensions import db


class PaydownBalanceUpdate(db.Model):
    __tablename__ = 'paydown_balance_updates'
    __table_args__ = (
        db.Index('idx_balance_updates_account', 'account_id', 'updated_at'),
    )

    id = db.Column(db.Integer, primary_key=True)
    balance = db.Column(db.Numeric(10, 2), nullable=False)  # positive decimal
    updated_at = db.Column(db.DateTime, nullable=False, server_default=db.func.now())

    account_id = db.Column(db.Integer, db.ForeignKey('accounts.id', ondelete='RESTRICT'), nullable=False)
    account = db.relationship('Account', back_populates='balance_updates')
```

#### `app/models/import_batch.py`

```python
from app.extensions import db


class ImportBatch(db.Model):
    __tablename__ = 'import_batches'

    id = db.Column(db.Integer, primary_key=True)
    filename = db.Column(db.String(255), nullable=False)
    uploaded_at = db.Column(db.DateTime, nullable=False, server_default=db.func.now())
    status = db.Column(db.String(20), nullable=False, default='pending')  # pending / committed / abandoned
    row_count = db.Column(db.Integer, nullable=True)
    committed_at = db.Column(db.DateTime, nullable=True)

    transactions = db.relationship('Transaction', back_populates='import_batch', lazy='dynamic')
```

#### `app/models/merchant_mapping.py`

```python
from app.extensions import db


class MerchantMapping(db.Model):
    """
    Full Phase 2 schema required in Phase 1 (AR-6).
    Prevents a live-data migration when staging_pipeline.py is wired in Epic 9.
    """
    __tablename__ = 'merchant_mappings'

    id = db.Column(db.Integer, primary_key=True)
    raw_pattern = db.Column(db.Text, nullable=False)
    normalized = db.Column(db.Text, nullable=False)
    user_confirmed = db.Column(db.Boolean, nullable=False, default=False)  # AR-6: full Phase 2 schema

    category_id = db.Column(db.Integer, db.ForeignKey('categories.id', ondelete='SET NULL'), nullable=True)
    category = db.relationship('Category', back_populates='merchant_mappings')
```

#### `app/models/settings.py`

```python
from app.extensions import db


class Settings(db.Model):
    """
    Single-row settings table. Always access via Settings.query.first() or upsert with id=1.
    Never create more than one row.
    """
    __tablename__ = 'settings'

    id = db.Column(db.Integer, primary_key=True)   # always 1
    monthly_income = db.Column(db.Numeric(10, 2), nullable=True)
```

#### `app/models/staged_transaction.py`

```python
"""
StagedTransactionModel — operates against a SEPARATE staging database.

Architecture note (Epic 9):
  - staging_pipeline.py opens instance/import_staging.db with a separate SQLAlchemy
    engine bound to this model's Base (not the main db).
  - This model is intentionally NOT in app/models/__init__.py — it must NOT appear
    in the main Flask-Migrate migrations.
  - staged_txn_to_model() in staging_pipeline.py converts StagedTransaction dataclass
    to StagedTransactionModel for persistence in the staging DB.
  - On successful user commit: rows promoted to Transaction (main DB).
  - On abandon or error: staging DB wiped; no main DB debris.
"""
from sqlalchemy import Column, Integer, Text, Numeric, Boolean, Float, DateTime, String
from sqlalchemy.orm import declarative_base

StagingBase = declarative_base()


class StagedTransactionModel(StagingBase):
    __tablename__ = 'staged_transactions'

    id = Column(Integer, primary_key=True)
    date = Column(Text, nullable=False)                  # ISO 8601: '2026-05-01'
    merchant_raw = Column(Text, nullable=False)
    merchant_normalized = Column(Text, nullable=False)
    amount = Column(Numeric(10, 2), nullable=False)
    is_credit = Column(Boolean, nullable=False, default=False)
    issuer = Column(String(100), nullable=True)
    confidence_score = Column(Float, nullable=True)       # 0.0–1.0; < 0.7 flagged for review
    dedup_hash = Column(String(64), nullable=True)
    raw_text = Column(Text, nullable=True)

    # No FK constraints — staging DB has no foreign key relationship to main DB
    import_batch_id = Column(Integer, nullable=True)
    category_id = Column(Integer, nullable=True)          # resolved by user during staged review
    account_id = Column(Integer, nullable=True)           # resolved by user during staged review

    committed_at = Column(DateTime, nullable=True)        # set when promoted to main Transaction
    status = Column(String(20), nullable=False, default='pending')  # pending / accepted / rejected
```

### `app/models/__init__.py` — Import All Main-DB Models

```python
"""
Import all main-DB models so SQLAlchemy metadata is fully populated before
Flask-Migrate's autogenerate runs. StagedTransactionModel is intentionally
excluded — it lives on a separate Base for the staging DB (see staged_transaction.py).
"""
from app.models.account import Account               # noqa: F401
from app.models.category import Category             # noqa: F401
from app.models.transaction import Transaction       # noqa: F401
from app.models.budget import Budget                 # noqa: F401
from app.models.bill import Bill                     # noqa: F401
from app.models.debt import Debt                     # noqa: F401
from app.models.paydown_plan import PaydownPlan      # noqa: F401
from app.models.paydown_plan_card import PaydownPlanCard          # noqa: F401
from app.models.paydown_balance_update import PaydownBalanceUpdate  # noqa: F401
from app.models.import_batch import ImportBatch      # noqa: F401
from app.models.merchant_mapping import MerchantMapping  # noqa: F401
from app.models.settings import Settings             # noqa: F401
```

### `app/__init__.py` — Add Model Import After `db.init_app(app)`

Add this line to `create_app()` AFTER `db.init_app(app)`:
```python
    import app.models  # noqa: F401 — registers ORM models with SQLAlchemy metadata
```

Do NOT add this before `db.init_app(app)` — models import from `app.extensions` which must already be wired.

### Flask DB Migration Commands

```bash
cd /var/www/html/financials
source .venv/bin/activate
FLASK_APP=app flask db init
FLASK_APP=app flask db migrate -m "Initial schema — Phase 1 models and indexes"
# Review migrations/versions/*.py — verify 13 tables + 5 indexes
FLASK_APP=app flask db upgrade
```

### ⚠️ Migration Review Checklist

After `flask db migrate`, open the generated migration file and verify:

1. **13 tables** are created: `accounts`, `categories`, `transactions`, `budgets`, `bills`, `debts`, `paydown_plans`, `paydown_plan_cards`, `paydown_balance_updates`, `import_batches`, `merchant_mappings`, `settings`, `alembic_version`
2. **5 indexes** are created: `idx_transactions_date`, `idx_transactions_category`, `idx_transactions_account`, `idx_bills_due_date`, `idx_balance_updates_account`
3. **`staged_transactions` table is NOT in the migration** (it's on a separate Base)

If any index is missing from the autogenerated migration, add it manually:
```python
op.create_index('idx_transactions_date', 'transactions', ['date'], unique=False)
```

### Category Seed Data (add to migration `upgrade()` function)

Add AFTER the last `op.create_table()` / `op.create_index()` call in `upgrade()`:

```python
    # Seed 14 system categories
    op.bulk_insert(
        sa.table('categories',
            sa.column('name', sa.String),
            sa.column('is_system', sa.Boolean),
            sa.column('is_active', sa.Boolean),
        ),
        [
            {'name': 'Housing',        'is_system': True, 'is_active': True},
            {'name': 'Groceries',      'is_system': True, 'is_active': True},
            {'name': 'Dining',         'is_system': True, 'is_active': True},
            {'name': 'Transportation', 'is_system': True, 'is_active': True},
            {'name': 'Utilities',      'is_system': True, 'is_active': True},
            {'name': 'Healthcare',     'is_system': True, 'is_active': True},
            {'name': 'Entertainment',  'is_system': True, 'is_active': True},
            {'name': 'Shopping',       'is_system': True, 'is_active': True},
            {'name': 'Subscriptions',  'is_system': True, 'is_active': True},
            {'name': 'Personal Care',  'is_system': True, 'is_active': True},
            {'name': 'Debt Payments',  'is_system': True, 'is_active': True},
            {'name': 'Income',         'is_system': True, 'is_active': True},
            {'name': 'Transfers',      'is_system': True, 'is_active': True},
            {'name': 'Uncategorized',  'is_system': True, 'is_active': True},
        ]
    )
```

Also add reverse deletion in `downgrade()`:
```python
    op.execute("DELETE FROM categories WHERE is_system = 1")
```

### `tests/test_models/test_schema.py` — Exact Implementation

```python
"""
Schema contract tests: verify all 5 required indexes exist.

Uses pytest's `db` fixture (creates schema via db.create_all(), which respects
__table_args__ Index definitions — no migration required for tests).

AR-5: idx_transactions_date, idx_transactions_category, idx_transactions_account,
      idx_bills_due_date, idx_balance_updates_account
"""
import pytest
from sqlalchemy import text


REQUIRED_INDEXES = [
    'idx_transactions_date',
    'idx_transactions_category',
    'idx_transactions_account',
    'idx_bills_due_date',
    'idx_balance_updates_account',
]


def _get_index_names(db_session):
    """Query sqlite_master for all user-defined indexes."""
    rows = db_session.execute(
        text("SELECT name FROM sqlite_master WHERE type='index'")
    ).fetchall()
    return {row[0] for row in rows}


def test_idx_transactions_date_exists(db):
    assert 'idx_transactions_date' in _get_index_names(db.session)


def test_idx_transactions_category_exists(db):
    assert 'idx_transactions_category' in _get_index_names(db.session)


def test_idx_transactions_account_exists(db):
    assert 'idx_transactions_account' in _get_index_names(db.session)


def test_idx_bills_due_date_exists(db):
    assert 'idx_bills_due_date' in _get_index_names(db.session)


def test_idx_balance_updates_account_exists(db):
    assert 'idx_balance_updates_account' in _get_index_names(db.session)


def test_all_required_indexes_exist(db):
    """Aggregate: all 5 AR-5 indexes present in schema."""
    found = _get_index_names(db.session)
    missing = [idx for idx in REQUIRED_INDEXES if idx not in found]
    assert missing == [], f"Missing required indexes: {missing}"
```

### Key Design Decisions

**Why `StagedTransactionModel` uses `declarative_base()` instead of `db.Model`?**

The staging DB is a separate SQLite file (`instance/import_staging.db`) opened only during an active import session. Mixing it into the main `db` metadata would cause Flask-Migrate to include it in the main schema migration — which is wrong. By using a separate `StagingBase`, it stays fully isolated. Epic 9 binds this model to a separate engine instance.

**Why 3 indexes on `Transaction` via `__table_args__`?**

Defining indexes on the model class (not manually in migration) means:
1. `db.create_all()` creates them automatically → pytest tests can verify them without migration
2. `flask db migrate` autogenerates the index creation → no manual migration editing needed

**Why `due_day` AND `due_date` on `Bill`?**

`due_day` (1-31) is the user's input (the recurring day of month), per Story 5.1 AC. `due_date` (TEXT/DATE) is the computed next occurrence used for sorting the bills list view (Story 5.2 sorts by next due date). AR-5 specifies the index on `due_date`. Both columns are needed.

**Why `apr` stores as `Numeric(6,4)` not `Numeric(5,2)`?**

`6,4` stores `99.9999` (max APR 99.99% with 4 decimal places of precision). `5,2` would only give `999.99` which is fine for APR but loses precision needed for exact amortization math. The amortization engine uses this value directly in Decimal arithmetic.

**Why `MerchantMapping` has full Phase 2 schema in Phase 1?**

AR-6 explicitly requires this to prevent a live-data migration when Epic 9 starts. The `user_confirmed` field defaults to `False` — all auto-detected mappings start unconfirmed; the staged review UI (Story 9.4) allows user confirmation.

**Why model imports in `app/models/__init__.py`?**

Flask-Migrate's `env.py` inspects `db.metadata.tables` during `flask db migrate`. Tables are only registered in metadata when the model class is *imported*. Without the `__init__.py` imports, `db.migrate` would see an empty schema and generate a no-op migration. The import must happen inside `create_app()` after `db.init_app(app)` to avoid importing models before the app is configured.

**Why is `import_batch_id` on Transaction a nullable FK to `import_batches`?**

Phase 2 — populated when transactions come from PDF import. For all Phase 1 manual entry, it stays `NULL`. The `SET NULL` delete action means deleting an import batch won't cascade-delete the actual transactions.

### Files Modified in This Story

| File | Status | Notes |
|------|--------|-------|
| `app/models/__init__.py` | **MODIFY** | Replace stub with imports of all 12 main-DB models |
| `app/__init__.py` | **MODIFY** | Add `import app.models` after `db.init_app(app)` |
| `app/models/account.py` | **MODIFY** | Replace stub with Account model |
| `app/models/category.py` | **MODIFY** | Replace stub with Category model |
| `app/models/transaction.py` | **MODIFY** | Replace stub with Transaction model + Phase 2 nullable cols + 3 indexes |
| `app/models/budget.py` | **MODIFY** | Replace stub with Budget model |
| `app/models/bill.py` | **MODIFY** | Replace stub with Bill model + idx_bills_due_date |
| `app/models/debt.py` | **MODIFY** | Replace stub with Debt model |
| `app/models/paydown_plan.py` | **MODIFY** | Replace stub with PaydownPlan model |
| `app/models/paydown_plan_card.py` | **NEW** | PaydownPlanCard model |
| `app/models/paydown_balance_update.py` | **NEW** | PaydownBalanceUpdate model + idx_balance_updates_account |
| `app/models/import_batch.py` | **MODIFY** | Replace stub with ImportBatch model |
| `app/models/merchant_mapping.py` | **MODIFY** | Replace stub with MerchantMapping model (full Phase 2 schema) |
| `app/models/settings.py` | **MODIFY** | Replace stub with Settings single-row model |
| `app/models/staged_transaction.py` | **MODIFY** | Replace stub with StagedTransactionModel on separate Base |
| `migrations/` | **NEW** | Created by `flask db init` |
| `migrations/versions/*.py` | **NEW** | First Alembic migration with 13 tables + 5 indexes + category seeds |
| `tests/test_models/test_schema.py` | **NEW** | 6 index assertion tests |

### Files That Must NOT Be Touched

| File | Why |
|------|-----|
| `app/services/pdf_parsers/base.py` | Story 1.3 — complete; do not modify |
| `app/services/duplicate_detector.py` | Story 1.3 — complete; do not modify |
| `tests/conftest.py` | Complete and working; do not modify |
| `tests/test_services/test_duplicate_detector.py` | Story 1.3 tests — must remain green |

### Previous Story Learnings

- **Story 1.3:** `app/services/duplicate_detector.py` and `base.py` are complete. The test suite runs 4 passed + 1 skipped with zero ORM imports anywhere in the test path. Keep it that way.
- **Story 1.2:** `db = SQLAlchemy()` lives in `app/extensions.py`. `create_app()` calls `db.init_app(app)`. The conftest.py `db` fixture calls `db.create_all()` (not a migration) — this is what makes the schema tests work.
- **Story 1.1:** Python 3.12.3, pytest 9.0.3, Flask-SQLAlchemy 3.1.1, Flask-Migrate 4.1.0, SQLAlchemy 2.0.50, Alembic 1.18.4 in `.venv/`.

### Architecture References

- **AR-2** — `test_duplicate_detector.py` written before first migration (satisfied in Story 1.3)
- **AR-4** — Phase 1 `Transaction` model pre-populates nullable Phase 2 columns (`issuer`, `merchant_raw`, `import_batch_id`, `confidence_score`, `dedup_hash`)
- **AR-5** — 5 required indexes in Phase 1 first migration
- **AR-6** — `MerchantMapping` model created in Phase 1 with full Phase 2 schema (`user_confirmed`)
- **Boundary 3 (Parser/DB)** — `StagedTransactionModel` must NOT be in main `db`; uses separate `StagingBase`
- **Data format** — All money: `Numeric(10,2)`; all dates: `Text` (ISO 8601); all booleans: `Boolean`

## Dev Agent Record

### Agent Model Used

claude-sonnet-4-6

### Debug Log References

- **`import app.models` rebind bug**: `import app.models` inside `create_app()` rebinds the local `app` variable (Flask instance) to the `app` MODULE, causing `create_app()` to return the module instead of the Flask app. Fixed by using `__import__('app.models')` which loads the module without rebinding any local names.

### Completion Notes List

- **Task 1**: Created `paydown_plan_card.py` and `paydown_balance_update.py` stubs. These models have no stub files from Story 1.1 scaffold.
- **Task 2**: Implemented all 12 main-DB models. Key design choices: `Transaction.__table_args__` contains all 3 transaction indexes so `db.create_all()` creates them (enabling pytest tests without migration); `Bill` has both `due_day` INTEGER (user input) and `due_date` TEXT (computed next occurrence for sorting); `MerchantMapping` has full Phase 2 `user_confirmed` field (AR-6).
- **Task 3**: `StagedTransactionModel` uses `StagingBase = declarative_base()` (not `db.Model`). NOT imported in `app/models/__init__.py` → correctly absent from main migration. Epic 9 binds it to a separate engine.
- **Task 4**: `app/models/__init__.py` imports all 12 main-DB models in FK-safe order (ImportBatch before Transaction). `app/__init__.py` uses `__import__('app.models')` — avoids the Python `import app.models` local-variable rebind bug.
- **Task 5**: `flask db init && flask db migrate && flask db upgrade` all succeeded. Alembic autogenerated all 12 tables + 5 indexes correctly. Category seed data (14 rows) added to migration `upgrade()` via `op.bulk_insert()`. Confirmed `instance/financials.db` created with correct schema.
- **Task 6**: `tests/test_models/test_schema.py` — 6 tests (5 individual + 1 aggregate). All pass against in-memory SQLite via `db.create_all()`. Full suite: `10 passed, 1 skipped` — zero regressions.

### File List

- `app/models/paydown_plan_card.py` — **NEW**: PaydownPlanCard model
- `app/models/paydown_balance_update.py` — **NEW**: PaydownBalanceUpdate model with idx_balance_updates_account
- `app/models/account.py` — **MODIFIED**: Account model with credit card fields (nullable)
- `app/models/category.py` — **MODIFIED**: Category model with is_system, is_active, UniqueConstraint
- `app/models/transaction.py` — **MODIFIED**: Transaction model with Phase 2 nullable cols + 3 indexes
- `app/models/budget.py` — **MODIFIED**: Budget model with UniqueConstraint on (category_id, month, year)
- `app/models/bill.py` — **MODIFIED**: Bill model with due_day + due_date + idx_bills_due_date
- `app/models/debt.py` — **MODIFIED**: Debt model
- `app/models/paydown_plan.py` — **MODIFIED**: PaydownPlan model with archived_at, last_recalculated_at nullable
- `app/models/import_batch.py` — **MODIFIED**: ImportBatch model
- `app/models/merchant_mapping.py` — **MODIFIED**: MerchantMapping with full Phase 2 schema (user_confirmed)
- `app/models/settings.py` — **MODIFIED**: Settings single-row model
- `app/models/staged_transaction.py` — **MODIFIED**: StagedTransactionModel on separate StagingBase
- `app/models/__init__.py` — **MODIFIED**: imports all 12 main-DB models for metadata registration
- `app/__init__.py` — **MODIFIED**: added `__import__('app.models')` after db.init_app()
- `migrations/` — **NEW**: Flask-Migrate Alembic directory (flask db init)
- `migrations/versions/e46c47c38302_initial_schema_phase_1_models_and_.py` — **NEW**: first migration with 12 tables, 5 indexes, 14 category seeds
- `tests/test_models/test_schema.py` — **NEW**: 6 AR-5 index assertion tests

### Change Log

- 2026-05-27: Story 1.4 implemented — all 12 main-DB models + StagedTransactionModel on separate Base; first Alembic migration (flask db init/migrate/upgrade); 14 system categories seeded; test_schema.py 6 tests pass; full suite 10 passed + 1 skipped. (claude-sonnet-4-6)