FIN-27 · Recurring detection, wasting-money insights, aegis add-service finance

[lbedner]

Milestone: Finance M6 · Depends on: FIN-26 (transfer-clean spending), FIN-22 (nightly job).

Goal

Close the loop: (a) recurring-stream detection, (b) the first three "wasting money" insights, (c) aegis add-service finance works on an EXISTING generated project. Ships the product promise end-to-end.

(a) Recurring detection — app/services/finance/recurring.py

Nightly (append to finance_daily job), per owner:

1. Group candidate outflows (non-transfer, non-deleted, posted) by merchant_id (fall back normalize_payee(original_description)), per account.
2. A group is a stream when ≥3 occurrences with a stable cadence: median interval within ±20% of 7/14/15/30/90/365 days → frequency weekly/biweekly/semi_monthly/monthly/quarterly/annually; amounts within ±20% of median (else amount_is_variable=true).
3. Upsert finance_recurring_stream on uq_finance_recurring_detected (owner_user_id, account_id, direction, normalized_payee): average_amount (median, cents), last_amount, first_date/last_date, next_expected_date = last_date + median_interval, occurrence_count, status='mature' at ≥3 else early_detection, confidence, is_subscription=true when monthly/annual + low variance + not a utility-like variable stream, source='derived'.
4. Back-link members: finance_transaction.recurring_stream_id.
5. Endpoints: GET /finance/recurring (streams + monthly-cost rollup), POST /finance/recurring/{id}/mute.

(b) Insights (rule-based, no AI)

Where they go: {% if include_insights %} emit through the insights service's event machinery (grep the template's insight_event producer pattern) {% else %} write finance_insight rows (FIN-10 table; upsert on uq_finance_insight_dedup (owner_user_id, dedup_key)) — implement the finance-local path FIRST, the insights bridge second.
Three rules, run nightly after recurring detection:

1. price_hike: stream last_amount > average_amount by >10% AND not amount_is_variable AND not muted → dedup_key=f"price_hike:{stream_id}:{last_amount}" (re-alerts only on a NEW price).
2. fee_charged: transaction in a fee category (PFC BANK_FEES subtree via category mapping) or payee regex (FEE|INTEREST CHARGE|FINANCE CHARGE), amount < 0 → dedup_key=f"fee:{transaction_id}".
3. overspend_category: current-month category spend (transfer-excluded, via FIN-26's summary) > 1.5× median of prior 3 full months (needs ≥3 months history; else skip silently) → dedup_key=f"overspend:{category_id}:{yyyymm}".
   Surface: GET /finance/insights?status=new, POST /finance/insights/{id}/dismiss; FIN-18 card shows new-insight count badge; modal gets a simple Insights list tab.

(c) aegis add-service finance

The registry entry (FIN-03) should make this mostly work; this ticket PROVES and patches it:

• template_files covers every finance dir (service, api, cli, frontend card/modal, tests) so ManualUpdater renders all of it into an existing project.
• Migration generation on add: aegis/commands/add_service.py:496-510 calls generate_migration(target, "finance") + bootstrap_alembic() if needed — verify FINANCE_MIGRATION generates with the NEXT free revision number in the target project and applies.
• Prereq flow: adding finance to a project WITHOUT auth/database/scheduler surfaces the requirement (auto-add components path at add_service.py:291-352).

Acceptance criteria

• Synthetic Netflix fixture (6 monthly charges $15.49, 7th at $17.99) → stream detected (monthly, subscription), price_hike insight fires ONCE; re-running nightly does not duplicate; muting stream suppresses future hikes.
• Seeded bank fee → fee insight; 4-month category history with a 2× month → overspend insight; <3 months history → no insight, no crash.
• Insights visible via API + card badge + modal list; dismiss works and survives re-run.
• From a FRESH non-finance generated project: uv run aegis add-service finance (with confirmation) → files rendered, migration generated + applied, /api/v1/finance/health 200, make test green in that project.
• make check + make test-stacks-quick green in aegis-stack.

---

Shared context (read this first — identical in every FIN ticket)

What we're building: a Finance Service for aegis-stack — a personal-finance aggregator (Empower/Quicken class: linked bank/credit/brokerage accounts, Quicken/OFX/CSV import, net-worth-over-time, "wasting money" insights). It is a gated template service like payment/insights: a new include_finance copier flag + a SERVICES["finance"] registry entry. Nothing is bolted into any one generated project.

Authoritative design docs (in this repo):

• docs/plans/finance-service/finance-service-plan.md — the full plan.
• docs/plans/finance-service/finance-schema-canonical.md — THE schema: 33 tables, every column/FK/index/unique/check, dedup contract, ER diagram. Schema tickets inline their slice, but this file is the tiebreaker.
• docs/plans/finance-service/finance-research/ — Plaid/OFX/product research briefs.

The two parallel systems (keep them in sync — this is the #1 thing to understand):

1. Copier template — aegis/templates/copier-aegis-project/{{ project_slug }}/…. Gating = literal {% if include_finance %} blocks in .jinja files + questions in copier.yml (repo root).
2. Python registry — aegis/core/services.py → the SERVICES dict. Single source of truth for: post-gen file pruning (aegis/core/post_gen_tasks.py:139-146 removes every path in a spec's FileManifest.primary when its flag is off), aegis add-service, migrations, aegis update disk-detection (marker_path), and aegis init service listing. Copy the payment spec (services.py:677-763) as the reference.

Migrations are GENERATED, not hand-written. Table definitions live as declarative specs in aegis/core/migration_generator.py: TableSpec / ColumnSpec / IndexSpec(name, columns, unique, where=…) (a where= renders BOTH sqlite_where and postgresql_where — partial uniques work on both engines) / ForeignKeySpec(columns, ref_table, ref_columns, ondelete=…, ref_schema=…) / CheckConstraintSpec(name, sqltext) / AlterTableSpec (for circular FKs; runs in one op.batch_alter_table, SQLite-safe). Revision IDs are auto-assigned at generation time (get_next_revision_id, max+1 zero-padded) — NEVER hardcode a revision number. Which migrations generate is decided by get_services_needing_migrations(context) (migration_generator.py:~1771) — finance gets a block there mirroring payment's (:~1839).

Generated-project conventions (non-negotiable, from the schema doc §conventions):

• int autoincrement PKs (id: int | None = Field(default=None, primary_key=True)) — NO UUIDs.
• Money = int minor units (cents) + a currency code column. NO Decimal/Numeric/float. Fractional share quantities = quantity_e8 (int, ×1e8); prices = int + price_scale; FX = rate_e8.
• Timestamps = naive UTC via a utcnow_naive() helper (datetime.now(UTC).replace(tzinfo=None)).
• Enums we own = String column + CheckConstraint (never native PG enum). Provider taxonomies that grow (Plaid account subtype, PFC categories, security_type) = plain TEXT, no check.
• JSON via sa_column=Column("name", JSON); the attr metadata_ maps to DB column "metadata" (SQLModel reserves metadata).
• Every user-scoped row: owner_user_id FK → user.id (CASCADE, indexed) + nullable organization_id (SET NULL, indexed). These FKs target the auth service's tables — finance declares required_services=["auth"]. Cross-schema FK precedent: payment.payment_customer → auth.user uses ForeignKeySpec(..., ref_schema="auth").
• Every FK indexed. ONE documented exception: currency FKs (low-cardinality) stay unindexed, ON DELETE RESTRICT.
• All finance tables are finance_-prefixed; explicit __tablename__; index names ix_…, uniques uq_…, checks ck_….
• Services: class FinanceService: def __init__(self, db: AsyncSession); queries via sqlmodel.select + await self.db.exec(…); writes self.db.add(…) + await self.db.flush() — services NEVER commit (the request-scoped get_async_db dependency commits).
• Dedup = real UNIQUE constraints + dialect-dispatched idempotent UPSERT (sqlite.insert(...).on_conflict_do_update(...) vs postgresql.insert(...) — pick by db.bind.dialect.name).

Dev workflow / how to validate (all from the aegis-stack repo root):

make check                 # repo's own lint + typecheck + tests — must stay green
make test-template         # generate a project from the template + validate it
make test-stacks-quick     # 3 representative stacks: base, everything, insights
make clean-test-projects   # remove generated test projects

# Generate a throwaway project from your UNCOMMITTED working tree:
uv run aegis init fin-smoke --dev --no-interactive -y \
  -o /tmp/aegis-fin-test \
  -c database,scheduler -s auth,finance
# (…and a second one WITHOUT `finance` in -s to prove pruning.)

Generated-project checks (run inside the generated project): make test, make lint, boot the app, hit endpoints with curl.

Postgres matters: the template supports sqlite AND postgres. FK enforcement, partial-unique behavior, and ON CONFLICT semantics must be validated against postgres too (generate with a postgres database answer, or run the migration against a local postgres). SQLite test sessions don't enforce FKs — don't trust green sqlite tests for constraint behavior.

Ticket codes: FIN-01…FIN-27. Dependencies are cited by code. Do not start a ticket whose dependencies aren't merged.