Track YouTube API quota usage and minimize API calls

[djbclark]

Objective

Measure every YouTube Data API call and its estimated quota cost, expose useful daily/job-level statistics, and use the resulting evidence to minimize API usage without silently changing playlist semantics or weakening user control.

This is a focused implementation/research task beneath #2 and informs the quota and rollout decisions in #1. The intended upstream end state is being discussed in sidinsearch/superbrain#6; approval-dependent release remains gated accordingly.

Current behavior and motivation

sync_video_category() is already idempotent when a video remains in the same category. It deletes a playlist item only when a locally tracked video moves to a different synced category or loses its synced target, then inserts it into the new playlist. Initial backfill is therefore dominated by playlist-item insertions, not deletions.

At ticket creation, the local deployment had 364 tracked playlist items across seven category playlists and no current category/mapping mismatches. Retained logs did not show removal events, but the application has no durable historical API-usage statistics, so deletion frequency and actual quota consumption cannot currently be measured.

Current published YouTube quota costs relevant to this feature are 1 unit for list calls and 50 units for playlist/playlist-item insert, update, or delete calls. Costs can change, so code and reports must identify the cost-table version/date rather than treating these values as timeless constants.

Instrument every YouTube API call

• Route YouTube Data API requests through a shared instrumentation layer rather than recording only category-playlist calls.
• Record timestamp, endpoint/resource, method, estimated quota units, result class, HTTP status, retry count, duration, operation/job ID, and connection/user identity where applicable.
• Record enough context to distinguish subscription discovery, channel identity, playlist ensure/list, backfill insert, category move, reconciliation, retry, disconnect, and deletion workloads.
• Count failed and invalid requests using the documented quota rules; do not assume errors are free.
• Never record OAuth tokens, authorization codes, request headers, or unnecessary API payloads.
• Persist bounded event or aggregate data so usage survives process restarts while respecting retention/deletion requirements.
• Maintain a versioned, testable quota-cost table derived from current official documentation and make unknown methods visible rather than silently assigning zero.

Reporting and budgeting

• Add a CLI and authenticated status endpoint showing estimated units today, units by endpoint/operation, calls/failures/retries, remaining configured budget, and next Pacific-time reset.
• Report per-job predicted cost before execution and actual estimated cost afterward.
• Add a dry-run planner for backfill/reconciliation that reports planned additions, deletions, retained items, playlist operations, list calls, and estimated total quota.
• Add daily and rolling-window summaries suitable for dashboards and alerts.
• Allow comparison with Google Cloud Console usage so discrepancies between local estimates and provider accounting are visible.
• Support per-user/per-connection and project-wide budgets for a future hosted mode without making the self-hosted design depend on hosted infrastructure.

Optimization work to evaluate

Avoid unnecessary operations

• Preserve the existing same-category no-op and prove it with instrumentation.
• Cache playlist IDs and playlist membership with explicit freshness/reconciliation rules instead of repeatedly listing unchanged resources.
• Batch or debounce rapid recategorizations so only the final category is synchronized when safe.
• Make retry operations idempotent and store real playlist-item IDs; do not rely on synthetic duplicate IDs that cannot later be deleted reliably.
• Prefer adding the new membership before deleting the old one during strict moves, so a failed insertion does not leave the video in neither playlist.

Make deletion semantics configurable and explicit

• Evaluate a strict move mode: add to the new category and remove from the previous category.
• Evaluate an optional add-only mode: add the new category membership but do not automatically remove old membership. Clearly explain that playlists become cumulative/tag-like rather than an exact mirror.
• Do not silently change existing strict synchronization semantics merely to save quota.
• If add-only mode is supported, redesign local mappings to track multiple playlist memberships per video rather than overwriting the single current mapping.

Compare individual reconciliation with playlist rebuild

For a desired playlist state, calculate both strategies before mutation:

• Individual reconciliation: 50 × (additions + deletions) plus cheap list calls.
• Full rebuild: 50 to delete the playlist, 50 to create its replacement, and 50 × desired_items to reinsert the final contents, plus list calls.

With equal 50-unit write costs, additions cancel from the comparison; rebuilding is quota-cheaper only when deletions > retained_items + 2. Verify this formula against the current official quota table rather than hard-coding the assumption indefinitely.

• Implement the calculation first as a dry-run recommendation with exact counts and estimated savings.
• Treat rebuild as an explicit, separately consented maintenance operation—not an automatic optimization.
• Account for the destructive consequences: new playlist ID/URL, lost order or manual metadata, possible user-added items, mapping replacement, partial failures, and external references.
• If implemented, populate a private replacement playlist completely and verify it before deleting the old playlist, with a recoverable operation record.
• Require a meaningful savings margin in addition to the mathematical break-even point.

Tests and acceptance criteria

• Focused tests prove quota attribution for every used endpoint, including errors and retries.
• Re-running an unchanged item produces zero write calls and zero write quota.
• Backfill estimates match observed instrumented calls within documented list/retry overhead.
• Strict moves, add-only behavior, and reconciliation plans have explicit, tested semantics.
• The planner chooses individual deletes below the rebuild threshold and recommends rebuild only above the configured safety margin.
• Rebuild failure tests prove the existing playlist is not deleted before the replacement is complete and verified.
• Reports contain no credentials or sensitive payloads.
• Documentation explains that subscription count is usually cheap; playlist writes, category churn, initial backlog, and shared multi-user traffic drive quota consumption.

Official references

• YouTube Data API quota calculator
• playlistItems.list
• playlistItems.delete
• Playlist implementation guide

[djbclark]

Progress (2026-07-31)

Implemented the instrumentation / reporting core for this issue:

• Versioned cost table in backend/core/youtube_quota.py (COST_TABLE_VERSION=2026-07-31)
• Durable events in youtube_api_usage_events (no tokens/headers/payloads); failed calls still charged when cost is known
• Playlist client + subscription list routed through shared instrumented_request
• CLI: superbrain --youtube-quota-stats (+ --youtube-quota-stats-days N)
• Status/API: --category-playlists-status, GET /api/youtube/quota/stats, enriched category-playlists status
• Backfill planner estimate included in quota-stats (unsynced × 50 units)

Related already shipped on main: quota-aware newest-first sync with new-video reserve, near-reset historic spend, position-0 inserts, and pending queue (b699eb6).

Still open from this ticket: add-only vs strict-move config, rebuild-vs-reconcile dry-run recommendation, debounce/batching, hosted per-user budgets.

Live backfill is sleeping until Pacific midnight (~03:00 ET / 00:00 PT) because Google already returned 403 today; post-reset monitoring is armed.

[djbclark]

More progress

• membership_mode = "move" | "add_only" (default move). Add-only skips auto-delete on category change; local mapping still tracks primary category only.
• --youtube-quota-stats now includes local reconcile-vs-rebuild planner (deletions > retained + 2 break-even). Rebuild remains recommendation-only, never automatic.

Still open: debounce/batching, add-before-delete on strict moves, multi-membership tracking for add-only cleanup, hosted budgets.

[djbclark]

Further progress

• Strict move: add new playlist membership before deleting the old one; if post-add delete hits quota, membership is kept and cleanup is deferred.
• Live maybe_sync_after_category_change debounces per-shortcode (sync_debounce_seconds, default 2).
• Stopped storing synthetic duplicate:… playlist item IDs (not deletable later).
• Rebuild planner requires rebuild_savings_margin_units (default 100) in addition to the break-even rule.

Remaining: multi-membership mapping table for add-only cleanup; hosted budgets; executing rebuilds (kept recommendation-only).

[djbclark]

Multi-membership tracking

Added category_youtube_playlist_memberships (video_id, playlist_id) alongside the primary item row. Add-only keeps prior memberships; move mode removes the old membership after a successful add-before-delete. Existing item rows are backfilled on DB open.

Still intentionally open: hosted per-user budgets; executing playlist rebuilds (planner-only).

[djbclark]

Operator decision (2026-07-31)

Playlist rebuild execution: later / not now — keep the reconcile-vs-rebuild planner as recommendation-only; do not auto-execute rebuilds.

Hosted per-user / project-wide budgets: defer until production/multi-user work (issues #1/#2). Self-hosted stays single-tenant local ledger.

No further implementation expected on those two items until explicitly revisited.