MyMidas/RECURRING_TRANSACTIONS_PLAN.md

Recurring Transactions — Implementation Plan

Goal

Automatically detect recurring transactions (direct debits, subscriptions, standing orders) from imported and existing transaction history. Tag them, predict next payment dates, and surface them in a dedicated Subscriptions page. All auto-detected values are user-correctable.

---

Scope

• Fixed-amount recurring detection only (variable-amount bills excluded — too unreliable)
• Detection runs automatically after every CSV import and on manual trigger
• Minimum 2 occurrences to flag as recurring
• Supported frequencies: weekly, fortnightly, monthly, quarterly, yearly
• User can manually mark or unmark any transaction as recurring
• New Subscriptions page in nav + recurring indicator in transaction list

---

Detection Algorithm

Description normalisation

UK bank CSV exports embed reference numbers, dates, and prefixes in descriptions that differ per occurrence but refer to the same payee. Normalise before grouping:

1. Lowercase the full string
2. Strip known UK bank prefixes: direct debit, dd , so , standing order, bacs, faster payment
3. Strip trailing reference numbers: sequences of 6+ digits at end of string
4. Strip embedded date patterns (e.g. 15apr, 15/04)
5. Collapse multiple spaces, trim

Examples:

"DIRECT DEBIT NETFLIX 00123456"  →  "netflix"
"DD SPOTIFY AB 987654"           →  "spotify ab"
"NETFLIX.COM"                     →  "netflix.com"
"COUNCIL TAX REF 20240415"       →  "council tax ref"

Grouping and interval analysis

Group all non-deleted transactions for a user by (normalised_description, exact_amount).

For each group with 2+ transactions:

1. Sort by date ascending
2. Calculate day-intervals between consecutive occurrences
3. Compute average interval
4. Classify against frequency table:

Frequency	Avg days range	Per-interval tolerance
Weekly	6–8	±2 days
Fortnightly	13–15	±3 days
Monthly	26–35	±5 days
Quarterly	85–95	±10 days
Yearly	355–375	±15 days

5. Check all individual intervals fall within tolerance of the average
6. If matched: tag all transactions in the group

Confidence score

confidence = 1 - (std_dev_of_intervals / expected_interval)

Capped at 1.0. Stored in recurring_rule for potential future use (e.g. sorting by confidence).

Next expected date

• Weekly/fortnightly: last_date + N days
• Monthly: last_date + 1 month (using dateutil.relativedelta — handles month-end correctly)
• Quarterly: last_date + 3 months
• Yearly: last_date + 1 year

If next_expected is in the past (missed payment or detection ran late), advance by one frequency period until it's in the future.

---

Data Model

No new tables required. Uses existing columns on transactions:

is_recurring: bool        — already exists
recurring_rule: JSONB     — already exists, was never populated

recurring_rule JSONB shape

{
  "frequency": "monthly",
  "typical_amount": -10.99,
  "typical_day": 15,
  "next_expected": "2026-05-15",
  "confidence": 0.94,
  "detected_at": "2026-04-23T10:00:00Z",
  "manually_set": false
}

manually_set: true when the user explicitly toggled it — detection will not overwrite manually-set entries on subsequent re-scans.

---

Backend

Service: backend/app/services/recurring_service.py (new)

def normalise_description(raw: str) -> str
    # Strips prefixes, refs, dates, lowercases

def classify_frequency(avg_days: float) -> str | None
    # Returns "weekly" | "fortnightly" | "monthly" | "quarterly" | "yearly" | None

def next_expected_date(last_date: date, frequency: str) -> date
    # Advances last_date by one frequency period; repeats until future date

async def detect_recurring(db: AsyncSession, user_id: uuid.UUID) -> dict
    # Loads all transactions, runs grouping + interval analysis
    # Updates is_recurring and recurring_rule on matched transactions
    # Skips transactions where recurring_rule.manually_set == true
    # Returns {"newly_tagged": int, "total_recurring": int}

Changes to transaction_service.py

• import_csv(): call detect_recurring(db, user_id) after flush, before return
• _to_response(): ensure recurring_rule is included in the response dict (it currently isn't)

New endpoint: POST /transactions/detect-recurring

Manual trigger. Calls detect_recurring() and returns the counts. Rate-limited — no need to spam it.

Changes to GET /transactions (existing)

No changes needed. is_recurring filter already works.

New endpoint: GET /subscriptions

Returns a grouped summary for the Subscriptions page:

{
  "total_monthly_equivalent": 87.43,
  "currency": "GBP",
  "subscriptions": [
    {
      "name": "Netflix",
      "amount": -10.99,
      "frequency": "monthly",
      "next_expected": "2026-05-03",
      "last_paid": "2026-04-03",
      "account_name": "Monzo",
      "account_id": "...",
      "transaction_ids": ["...", "..."],
      "confidence": 0.98,
      "manually_set": false
    }
  ]
}

Monthly equivalent conversion:

• Weekly × 52 / 12
• Fortnightly × 26 / 12
• Monthly × 1
• Quarterly / 3
• Yearly / 12

Changes to PUT /transactions/{id} (existing)

Already accepts is_recurring. Extend to also accept recurring_rule patch so the frontend can write manually_set: true when the user toggles.

---

Frontend

Transaction list — recurring indicator

In TransactionList.tsx, next to the existing paperclip icon for attachments, add a ↻ (RefreshCw) icon for transactions where is_recurring === true. Small, muted, same style as the paperclip.

Transaction detail drawer — manual toggle

In TransactionDetailDrawer.tsx, add a toggle in the detail panel:

[ ↻ ] Mark as recurring   [toggle]

When toggled on manually: sets is_recurring=true, writes recurring_rule: { manually_set: true, frequency: null, ... } — opens a small inline form to let the user pick the frequency and confirm the amount. When toggled off: sets is_recurring=false, clears recurring_rule.

New page: frontend/src/pages/subscriptions/SubscriptionsPage.tsx

Header

Subscriptions & Standing Orders
Estimated monthly spend: £87.43        [ Re-scan ]

Re-scan button calls POST /transactions/detect-recurring, invalidates the subscriptions query.

Subscription cards / list

Each row:

[ icon ] Netflix                    £10.99 / month
         Monzo · last paid 3 Apr    Next: 3 May  [3 days]   [ ··· ]

Colour coding on the "Next" badge:

• Red: overdue (next_expected in the past)
• Amber: within 7 days
• Muted: more than 7 days away

The ··· menu: "Mark as not recurring" (unsets it with manually_set: true).

Sort options

• Next payment (default)
• Amount (high to low)
• Name (A–Z)

Empty state

If no recurring transactions detected yet: prompt to import a CSV or use Re-scan.

Nav addition

Add Subscriptions to the sidebar and mobile nav, between Transactions and Reports. Icon: RefreshCw or Repeat.

API client: frontend/src/api/subscriptions.ts (new)

export interface Subscription { ... }
export interface SubscriptionsSummary { ... }

export const getSubscriptions = (): Promise<SubscriptionsSummary> => ...
export const triggerDetection = (): Promise<{ newly_tagged: number; total_recurring: number }> => ...

---

Implementation Order

1. recurring_service.py — detection logic, fully unit-testable without DB
2. Wire into import_csv() — auto-detect after every import
3. POST /transactions/detect-recurring — manual trigger endpoint
4. GET /subscriptions — grouped summary endpoint
5. Update _to_response() — include recurring_rule in transaction response
6. SubscriptionsPage.tsx + api/subscriptions.ts — main UI
7. Transaction list ↻ indicator — small icon addition
8. Transaction detail drawer toggle — manual mark/unmark

---

Testing Checkpoints

• Normalisation: verify "DIRECT DEBIT NETFLIX 00123456" → "netflix"
• Monthly detection: 3 transactions, same amount, ~30 days apart → detected
• Below threshold: 1 occurrence → not detected
• Tolerance: transactions on 1st, 31st, 2nd of following months → monthly detected (within ±5 days)
• manually_set: true entries are not overwritten on re-scan
• Price change: Netflix at £10.99 (3 entries) + £11.99 (1 entry) → old entries stay tagged, new one not auto-tagged
• Monthly equivalent: yearly £120 subscription → shows £10.00/month

---

Notes & Decisions

• No new DB migration required — is_recurring and recurring_rule columns already exist from the initial schema.
• Detection is idempotent — running it twice produces the same result. Safe to call on every import.
• manually_set flag protects user corrections — if a user untags something, a subsequent CSV import will not re-tag it.
• recurring_rule not currently returned by _to_response() — this must be added before the frontend can read frequency/next_expected.
• Variable-amount recurrings (energy, credit card payments) are out of scope. User can manually tag these if desired.
• 2-occurrence minimum is configurable as a constant in recurring_service.py (MIN_OCCURRENCES = 2).