music on atproto
plyr.fm
zzstoatzz.io
1#!/usr/bin/env -S uv run --script --quiet
2"""view per-user upload duration statistics.
3
4## Context
5
6Track how much content each user has uploaded to support future upload caps.
7Currently informational - no enforcement yet.
8
9## What This Script Does
10
111. Queries all tracks grouped by artist
122. Sums duration from extra JSONB column
133. Displays sorted by total upload time
14
15## Usage
16
17```bash
18# show all users with upload stats
19uv run scripts/user_upload_stats.py
20
21# show only users above a threshold (in hours)
22uv run scripts/user_upload_stats.py --min-hours 1
23
24# target specific environment
25DATABASE_URL=postgresql://... uv run scripts/user_upload_stats.py
26```
27"""
28
29import asyncio
30import logging
31import sys
32from pathlib import Path
33
34# add src to path so we can import backend modules
35sys.path.insert(0, str(Path(__file__).parent.parent / "backend" / "src"))
36
37from sqlalchemy import func, select, text
38
39from backend.models import Artist, Track
40from backend.utilities.database import db_session
41
42logging.basicConfig(
43 level=logging.INFO,
44 format="%(asctime)s - %(levelname)s - %(message)s",
45)
46logger = logging.getLogger(__name__)
47
48
49def format_duration(total_seconds: int) -> str:
50 """format seconds into human-readable duration string."""
51 hours = total_seconds // 3600
52 minutes = (total_seconds % 3600) // 60
53
54 if hours == 0:
55 return f"{minutes}m"
56 if minutes == 0:
57 return f"{hours}h"
58 return f"{hours}h {minutes}m"
59
60
61async def get_user_upload_stats(min_hours: float = 0) -> None:
62 """query and display per-user upload statistics."""
63
64 min_seconds = int(min_hours * 3600)
65
66 async with db_session() as db:
67 # aggregate tracks by artist
68 stmt = (
69 select(
70 Track.artist_did,
71 Artist.handle,
72 Artist.display_name,
73 func.count(Track.id).label("track_count"),
74 func.coalesce(
75 func.sum(text("(tracks.extra->>'duration')::int")),
76 0,
77 ).label("total_seconds"),
78 )
79 .join(Artist, Track.artist_did == Artist.did)
80 .group_by(Track.artist_did, Artist.handle, Artist.display_name)
81 .order_by(text("total_seconds DESC"))
82 )
83
84 result = await db.execute(stmt)
85 rows = result.all()
86
87 if not rows:
88 logger.info("no tracks found")
89 return
90
91 # also get totals
92 total_stmt = select(
93 func.count(Track.id),
94 func.coalesce(func.sum(text("(tracks.extra->>'duration')::int")), 0),
95 )
96 total_result = await db.execute(total_stmt)
97 total_row = total_result.one()
98 total_tracks = total_row[0]
99 total_seconds = total_row[1]
100
101 print("\n" + "=" * 80)
102 print("USER UPLOAD STATISTICS")
103 print("=" * 80)
104 print(
105 f"\nPlatform totals: {total_tracks} tracks, {format_duration(total_seconds)}"
106 )
107 print("-" * 80)
108 print(f"{'handle':<30} {'display name':<20} {'tracks':>8} {'duration':>12}")
109 print("-" * 80)
110
111 shown = 0
112 for row in rows:
113 artist_did, handle, display_name, track_count, user_seconds = row
114
115 if user_seconds < min_seconds:
116 continue
117
118 shown += 1
119 display = (display_name or handle)[:20]
120 handle_str = handle[:30] if handle else artist_did[:30]
121
122 print(
123 f"{handle_str:<30} {display:<20} {track_count:>8} {format_duration(user_seconds):>12}"
124 )
125
126 print("-" * 80)
127
128 if min_hours > 0:
129 hidden = len(rows) - shown
130 print(
131 f"showing {shown} users with >= {min_hours}h (hiding {hidden} below threshold)"
132 )
133 else:
134 print(f"total: {len(rows)} users")
135
136 print()
137
138
139async def main() -> None:
140 """main entry point."""
141 min_hours = 0.0
142
143 for i, arg in enumerate(sys.argv):
144 if arg == "--min-hours" and i + 1 < len(sys.argv):
145 min_hours = float(sys.argv[i + 1])
146
147 await get_user_upload_stats(min_hours=min_hours)
148
149
150if __name__ == "__main__":
151 asyncio.run(main())