SkinbaseNova/docs/optimization-m5-metric-snapshot-retention.md

M5 — Artwork Metric Snapshot Retention & Growth

STATUS: COMPLETE (local implementation; not deployed)
PRODUCTION WRITES: none
PRODUCTION DELETES: none
OPTIMIZE TABLE: not run
PARTITIONING: not added
NEW ANALYTICS TABLE: not created

---

Verdict

Pruning needed:              YES
Recommended retention:       30 days
Downsampling needed:         NO
Partitioning needed:         NO
Indexes to add:              none
Indexes to remove:           optional later (redundant idx_artwork_bucket)
Expected storage vs unlimited: large reduction
Expected storage vs current 7d prune: INCREASE (~4.3×) if retention is raised to 30d

Hourly snapshots are already pruned daily with --keep-days=7 (unbounded single DELETE). That window is too short for monthly leaderboards and Studio 30d views. M5 makes prune batched + configurable and sets default retention to 30 days.

---

1. Table schema (production)

CREATE TABLE `artwork_metric_snapshots_hourly` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `artwork_id` bigint unsigned NOT NULL,
  `bucket_hour` datetime NOT NULL,
  `views_count` bigint unsigned NOT NULL DEFAULT '0',
  `downloads_count` bigint unsigned NOT NULL DEFAULT '0',
  `favourites_count` bigint unsigned NOT NULL DEFAULT '0',
  `comments_count` bigint unsigned NOT NULL DEFAULT '0',
  `shares_count` bigint unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_artwork_bucket` (`artwork_id`,`bucket_hour`),
  KEY `idx_bucket_hour` (`bucket_hour`),
  KEY `idx_artwork_bucket` (`artwork_id`,`bucket_hour`),
  KEY `idx_bucket_artwork` (`bucket_hour`,`artwork_id`),
  CONSTRAINT `artwork_metric_snapshots_hourly_artwork_id_foreign`
    FOREIGN KEY (`artwork_id`) REFERENCES `artworks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB

Not partitioned (CREATE_OPTIONS empty; PARTITIONS.PARTITION_NAME NULL).

Duplicates: none (unique (artwork_id, bucket_hour)). has_duplicate_groups=0.

---

2. Production size & range (read-only, 2026-08-23 15:07 UTC)

Metric	Value
Exact COUNT(*)	8,918,000
information_schema est.	8,622,217
Data	740.03 MB
Index	1115.47 MB
Total	1855.50 MB
InnoDB DATA_FREE	121.00 MB
Distinct artworks	49,822
Distinct hours	179
Oldest bucket_hour	2026-08-16 05:00:00
Newest bucket_hour	2026-08-23 15:00:00
Rows per hour (stable)	49,822
Rows / day	1,195,728
AUTO_INCREMENT	153,018,248 (historical insert churn after prune)

Rows older than 7 days at query time: 548,031 (~11 extra hours until the next 04:00 prune). Oldest row is 05:00 on the day after the last 04:00 cutoff — daily prune is running.

---

3. Growth estimates

Assumes current write rate stays ~49,822 rows/hour and size scales linearly (~249 MB/day from 1855.5 MB / 7.45 days).

Horizon	Rows	Approx size
Current (~7.5 d)	8.9M	1.86 GB
24 hours	1.2M	~0.25 GB
7 days	8.4M	~1.75 GB
30 days	35.9M	~7.5 GB
90 days	107.6M	~22.4 GB
365 days	436.4M	~91 GB
Unlimited 1y of this rate	same as 365d	~91 GB

MB/day ≈ 249 MB/day (data+index) at current density.

---

4. Writers (exact)

Writer	When	What
nova:metrics-snapshot-hourly (MetricsSnapshotHourlyCommand)	Scheduler hourlyAt(2)	Upsert one row per eligible artwork for now()->startOfHour()
Eligibility	--days=60 default	artworks.created_at last 60 days OR artwork_stats.ranking_score > 0; approved, not deleted

Chunk 1000. Unique key makes reruns idempotent. No other writers (no jobs, no HTTP, no observers).

That eligibility (ranking_score>0) explains ~50k IDs/hour, not only last-60-day artworks.

---

5. Readers (exact)

Consumer	Lookback	Notes
RecalculateHeatCommand (nova:recalculate-heat every 15 min)	24h	DISTINCT artwork_id + load window; writes artwork_stats.heat_score
HomepageService::risingRecentActivitySubquery	24h	MAX-MIN deltas
DiscoverController rising	24h	same pattern
DiscoverFeedController (RSS)	24h	same pattern
LeaderboardService daily/weekly/monthly	1 / 7 / ~30 days	MAX-MIN cumulative counts grouped by artwork; all-time uses artwork_stats, not this table
StudioMetricsService::getDashboardKpis	30 days	SUM(views_count) — semantically wrong (views_count is cumulative totals, not hourly increments). Falls back to lifetime stats if 0.
CreatorJourneyService::biggestDownloadSpike	all retained hours for the creator’s public artworks	No time filter; bounded only by prune
nova:prune-metric-snapshots	cutoff	writer of deletes

Frontend/API do not query this table directly; they read heat/leaderboard/studio payloads derived from it.

No daily/weekly/monthly artwork metric aggregate table exists (only collection daily stats elsewhere).

---

6. Required historical retention (not assumed)

Need	Depth
Heat / rising homepage / discover / RSS	24 hours
Weekly leaderboards	7 days
Monthly leaderboards	~30 days
Studio “views 30d” query	30 days (even though the SUM is a bad formula)
Journey download spike	prefers longer; not a product requirement for unlimited
Rec similar-art serving	none (M4)

Required: 30 days of hourly rows if monthly leaderboards and Studio 30d stay on this table.

Unlimited / 1 year / 90 days: not required by application code.

Current production --keep-days=7 under-retains monthly leaderboard snapshot deltas.

---

7. Production query plans (EXPLAIN)

Heat DISTINCT 24h:

type=range key=idx_bucket_artwork rows~2.46M
Using where; Using index; Using temporary

Rising GROUP BY 24h:

type=range key=idx_bucket_artwork rows~2.46M
Using index condition; Using temporary

Studio 30d for user_id=1:

artworks: ref artworks_user_id_index (~449 rows)
snapshots: ref uq_artwork_bucket (artwork_id) ~411 rows, Using index condition

Leaderboard monthly GROUP BY (full table, 30d > retained 7d so it scans retained set):

type=index key=uq_artwork_bucket rows~8.87M Using where

Prune count bucket_hour < now()-7d:

type=range key=idx_bucket_artwork rows~1.1M Using where; Using index

Indexes are used. 24h range still estimates millions of rows because ~50k artworks × 24 hours ≈ 1.2M (optimizer overestimate 2.46M). Acceptable for hourly/15-min jobs; leaderboard monthly on 30d would scan ~36M rows if retention grows — still one scheduled hourly job, not request path.

---

8. Pruning / downsample / partition / archive

Pruning: YES

Already scheduled. Replace unbounded DELETE WHERE bucket_hour < cutoff with PK-id batches.

Downsampling: NO

No existing daily artwork snapshot table. Monthly scores only need MIN/MAX of cumulative counters in the window; hourly rows are convenient. A second table is more operational cost than 30d hourly until size is painful (~7.5 GB still fits RAM buffer pool 10 GB). Revisit if table exceeds ~15–20 GB.

Archive: NO

Nothing reads off-box history.

Partitioning: NO

Rolling 7–30 day window + batched DELETE is simpler than PARTITION BY RANGE (TO_DAYS(bucket_hour)) + monthly ALTER/DROP PARTITION. Not justified at 1.86 GB (or 7.5 GB).

---

9. Delete vs InnoDB free space vs filesystem

• DELETE removes rows; InnoDB keeps pages in the tablespace (DATA_FREE today 121 MB).
• Disk file (ibd) typically does not shrink.
• Filesystem reclaim needs OPTIMIZE TABLE / ALTER TABLE ... ENGINE=InnoDB (rebuild) — do not run automatically on production.
• After raising retention, size grows; after later lowering it, expect logical free space, not a smaller .ibd until a rebuild is planned in a maintenance window.

---

10. Implementation (local)

• Config config/metrics.php + env ARTWORK_METRIC_HOURLY_RETENTION_DAYS default 30.
• PruneMetricSnapshotsCommand: count eligible → loop SELECT id … LIMIT chunk → DELETE WHERE id IN (…), sleep, logs per batch, --dry-run, --max-batches.
• Scheduler: nova:prune-metric-snapshots without hardcoded --keep-days=7.
• Tests for keep-days, batches, idempotence, dry-run, config default, max-batches.

Production-safe properties

Requirement	How
Bounded batches	--chunk default 5000
No giant DELETE	one whereIn per batch
Avoid long locks	small PK deletes + --sleep-ms=50
Scheduler-safe	withoutOverlapping; daily 04:00
Idempotent	re-run deletes 0 extra rows
Observable	info logs per batch + completed
Configurable	env + --keep-days override

---

11. Indexes

Keep:

• PRIMARY (id) — batched prune
• uq_artwork_bucket (artwork_id, bucket_hour) — upsert + per-artwork time
• idx_bucket_artwork (bucket_hour, artwork_id) — heat/rising/prune range

Optional later (not in this milestone):

• Drop idx_artwork_bucket — duplicate of unique key
• Drop idx_bucket_hour — prefix of idx_bucket_artwork

Do not add indexes. Do not drop on production in M5 (online DDL on 1.85 GB).

---

12. Expected storage after M5 deploy

If ARTWORK_METRIC_HOURLY_RETENTION_DAYS=30 on production:

• Table grows from ~1.86 GB toward ~7.5 GB over ~3 weeks.
• That is correctness, not reduction.

If production must stay small, set ARTWORK_METRIC_HOURLY_RETENTION_DAYS=7 and accept monthly leaderboard snapshot windows of only 7 days. Do not leave the scheduler at 7 days while believing Studio/monthly boards have 30d of hourly history.

Storage reduction vs never pruning: ~91 GB/year avoided.

---

13. Deployment (later milestone — not M5)

1. Ship code + config/metrics.php.
2. Set env on server: ARTWORK_METRIC_HOURLY_RETENTION_DAYS=30 (or 7 if size is prioritized).
3. php artisan config:cache as the deploy already does.
4. Do not run prune by hand on first deploy unless a dry-run is wanted: php artisan nova:prune-metric-snapshots --dry-run.
5. Do not OPTIMIZE TABLE.
6. Watch next 04:00 run logs for batch counts.

If raising 7 → 30: prune deletes almost nothing until the table ages to 30 days.

---

14. Rollback

• Restore previous command (single DELETE) and scheduler --keep-days=7.
• Or set env back to 7 with the new command (preferred).
• No schema change to roll back.

---

15. Tests

• tests/Feature/PruneMetricSnapshotsCommandTest.php (new)
• Existing tests/Feature/RisingEngineTest.php prune example still valid with --keep-days=7

---

16. Files changed

config/metrics.php                                          (new)
app/Console/Commands/PruneMetricSnapshotsCommand.php        (batched prune)
routes/console.php                                          (no hardcoded 7)
.env.example
docs/cli-reference.md
docs/optimization-m5-metric-snapshot-retention.md           (new)
tests/Feature/PruneMetricSnapshotsCommandTest.php           (new)

---

17. Risks

• Raising retention to 30d increases MySQL size and leaderboard monthly scan cost.
• Studio 30d SUM(views_count) remains an incorrect metric (cumulative summed across hours). Out of M5 scope.
• Journey spike still limited to retained hours.
• Batched prune of ~1.2M rows/day at chunk 5000 ≈ 240 batches; 50ms sleep ≈ 12s extra plus delete time. Fine for 04:00.
• Redundant indexes still cost ~index-heavy 1.1 GB; dropping them is a later DDL decision.

---

18. What was not done

• No production SQL writes/deletes
• No OPTIMIZE TABLE
• No partitioning
• No new aggregate table
• No change to snapshot writer eligibility (--days=60 + ranking_score)
• M1–M4 still undeployed