This file documents notifications and gamification (XP/levels/badges/leaderboard), both implemented in devplacepy/utils.py. Claude Code loads it automatically whenever a file under devplacepy/utils/ is read or edited.

## Notification system

Notifications are created server-side in the route handlers and stored in the `notifications` table. Unread counts are cached per-process in `_unread_cache` (notifications) and `_messages_cache` (messages), both 10s TTL in `templating.py`. Mutating routes call `clear_unread_cache(uid)` / `clear_messages_cache(uid)` to invalidate.

### Live counter badges

The top-nav bell and Messages link carry `data-counter="notifications"` / `data-counter="messages"` with a child `<span data-counter-badge hidden>`. `CounterManager.js` polls `GET /notifications/counts` (returns `{"notifications", "messages"}`, zeros for guests) every 30s (via the shared `Poller`, `pauseHidden`) and on tab focus, then sets each badge's text and toggles its `hidden` attribute. Server-rendered counts seed the initial state, so the badges are correct on first paint and self-heal without a reload. Absolute badges use `.nav-badge`; inline badges (next to text links) add `.nav-badge-inline`.

### Notification types and trigger points

| Type | Trigger | Location | Condition |
|------|---------|----------|-----------|
| `comment` | Top-level comment on post | `comments.py` | `post["user_uid"] != user["uid"]` |
| `reply` | Reply to a comment | `comments.py` | `parent["user_uid"] != user["uid"]` |
| `vote` | Upvote on post or comment | `votes.py` | `value == 1` AND voter != owner |
| `follow` | Follow another user | `follow.py` | Always (self-follow blocked upstream) |
| `message` | Send a message | `messages.py` | Always (different user) |
| `mention` | `@username` in content | `utils.py` `create_mention_notifications` | Mentioned user != actor |
| `level` | Reach a new level | `utils.py` `award_xp` | `new_level > current_level` |
| `badge` | Earn any badge | `utils.py` `notify_badge` | First grant only (`award_badge` returns `True`) |
| `issue` | Issue filed/replied/status | `issues.py`, `services/gitea`, `services/jobs/issue_create_service.py` | Reporter or admins |

`level`/`badge` notifications have `related_uid == user_uid` (self), so the notifications template renders them with the recipient's own avatar; the template is type-agnostic (driven by `message`/`actor`), so no per-type handling is needed.

### Auto-mark-read on content view

Beyond the explicit clears (`/notifications/open/{uid}`, `POST /notifications/mark-read/{uid}`, `POST /notifications/mark-all-read`), a notification is **automatically marked read when the user opens the page where its referenced content is visible**. The single primitive is `database.mark_notifications_read_by_target(user_uid, target_url)`: it stamps `read = 1` on every unread notification of that user whose stored `target_url` equals `target_url` OR begins with `target_url + "#"` (so a post-page key clears its `#comment-{uid}` variants), then `clear_unread_cache(user_uid)`. It returns the count and short-circuits (no write, no cache bump) when nothing matches, so a detail view by a user with no relevant unread notifications costs only one indexed read. There is **no** `target_type`/`target_uid` column - the `target_url` string (produced by `resolve_object_url`) is the only content key, so callers build the page key with that same generator (or the identical literal the create-site used) to guarantee a match with zero drift.

Call sites (each only when a real user is present and the resource resolved, placed after the 404/canonical-redirect guards):

| GET handler | page key | clears |
|-------------|----------|--------|
| `posts.py::view_post` | `resolve_object_url("post", uid)` | comment, reply, vote, mention (post + its comments) |
| `projects/index.py::project_detail` | `resolve_object_url("project", uid)` | vote, mention |
| `gists.py::gist_detail` | `resolve_object_url("gist", uid)` | vote, mention |
| `news.py::news_detail_page` | `resolve_object_url("news", uid)` | comment, vote, mention |
| `messages.py::messages_page` | `/messages?with_uid={with_uid}` | message, DM mention (beside the existing `mark_conversation_read`) |
| `profile/index.py::profile_page` | `/profile/{username}` | follow (follower's profile), badge/level (own profile) |
| `issues/index.py::issue_detail` | `/issues?highlight={number}` | issue (the create-sites store the `?highlight=` URL, not the `/issues/{number}` detail path) |
| `devii.py::devii_page` | `/devii` | reminder |
| `game/index.py::game_home` | `/game` | harvest_stolen |

Deliberately excluded: `GET /notifications` (the list must stay readable) and `/feed` (not a content page). Reuse `mark_notifications_read_by_target` for any future surface; never hand-roll a per-handler `UPDATE notifications`.

### Time-grouped display

Notifications are grouped by time period in `notifications.py` `_group_label()`:
- Same day - **Today**
- Previous day - **Yesterday**
- Within 7 days - **This week**
- Older - **Older**

The template uses `notification_groups` (list of `{label, entries}` dicts). Jinja2 note: avoid `.items` as a dict key - it clashes with Python's `dict.items()` method.

### Vote notification messages

```python
f"{user['username']} ++'d your post"
f"{user['username']} ++'d your comment"
```

### Per-user, per-channel preferences

Every notification type is independently toggleable on three channels - **in-app**, **push** and **telegram** - per user, with admin-set platform defaults. This is enforced at the single funnel, so there is no per-call-site work: a new `create_notification(user_uid, type, ...)` call is automatically subject to preferences.

- **Canonical types** = `database.NOTIFICATION_TYPES` (the nine listed above; `key`/`label`/`description`). This list drives the profile tab and the admin page; it is the single source of truth. The `test` type is intentionally absent, so it always delivers (the resolver defaults unknown types to on).
- **Channels are data-driven.** Adding a channel is a constant change, never scattered edits: `NOTIFICATION_CHANNELS` (the tuple), `_NOTIFICATION_CHANNEL_COLUMNS` (channel -> table column), and `_NOTIFICATION_CHANNEL_DEFAULTS` (channel -> per-type fallback when no admin default row exists). `in_app`/`push` default `1` (on); **`telegram` defaults `0` (off for every type)**. The four helpers (`_notification_overrides`, `get_notification_prefs`, `set_notification_pref`, `_notification_default`) iterate these maps generically, so a new channel needs only the constant entry + the `notification_preferences` column in `init_db`.
- **Resolution** (`database.notification_enabled(user_uid, type, channel)`): a live `notification_preferences` row for `(user_uid, type)` wins; else the global default `get_int_setting("notif_default_{type}_{channel}", _NOTIFICATION_CHANNEL_DEFAULTS[channel])`; else the channel fallback. So a user who never touched a setting inherits the admin default (or the channel fallback when no default row exists); a user who customized keeps their choice.
- **Storage**: table `notification_preferences` (`uid`, `user_uid`, `notification_type`, `in_app_enabled`, `push_enabled`, `telegram_enabled`, timestamps, soft-delete columns), in `SOFT_DELETE_TABLES`, columns+indexes ensured in `init_db`. Cached under the `"notif_prefs"` cache-version name (`sync_local_cache`/`bump_cache_version`), keyed by `user_uid`. Helpers: `get_notification_prefs(user_uid)` (full resolved list for the UI/JSON, one boolean per channel keyed by channel name), `set_notification_pref(user_uid, type, channel, enabled)` (upsert one channel, leaving the others at their resolved value; revives a soft-deleted row), `reset_notification_prefs(user_uid)` (soft-delete the user's overrides -> fall back to defaults), `get_notification_default`/`set_notification_default` (the `notif_default_*` `site_settings` keys; defaults are NOT seeded so an untouched type reads its channel fallback: `1` for in_app/push, `0` for telegram).
- **Enforcement (`create_notification` single funnel) is in `utils.create_notification`**: the in-app `notifications.insert` + `clear_unread_cache` run only when `notification_enabled(..., "in_app")`, `_schedule_push` runs only when `notification_enabled(..., "push")`, and `_schedule_telegram` runs only when `notification_enabled(..., "telegram")`. The `notification.create` audit row is unconditional and records which channels fired (`metadata.in_app`/`push`/`telegram`). **Because the DM path in `messages.py` now routes through `create_notification` (type `message`)** instead of inserting directly, direct messages are gated like everything else and gained a (default-on) push channel. **`create_notification` is a thin wrapper that defers the whole body** (preference reads + insert + push + telegram + audit) to the **background task queue** via `background.submit(_deliver_notification, ...)`, so it never blocks the request (inline under tests); call it directly, never wrap it in `background.submit`. See `devplacepy/services/CLAUDE.md` -> Background task queue.
- **Telegram channel (cross-worker delivery).** `_schedule_telegram(user_uid, message)` calls `services/telegram/store.enqueue_outbox`, which no-ops when the user is not paired (`link_for_user` is None) and otherwise inserts a row into the `telegram_outbox` queue (`uid`, `user_uid`, `chat_id`, `text`, `status` pending|sent|failed, `attempts`, timestamps; transient, NOT in `SOFT_DELETE_TABLES`, columns+index ensured in `init_db`). This is needed because `_deliver_notification` runs on the background queue of **any** worker, but the Telegram bot worker subprocess (and `TelegramService.send_markdown`) lives **only on the service-lock owner**. `services/telegram/outbox_service.py` `TelegramOutboxService` (lock-owner `BaseService`, default-enabled, ~2s interval, registered in `main.py` after `TelegramService`) drains pending rows on the lock owner: if the Telegram service is missing/disabled/`not worker_alive()` it returns (rows wait, nothing is lost), else it sends each via `send_markdown`, `mark_outbox_sent` on success or `mark_outbox_failed` (retries up to `MAX_OUTBOX_ATTEMPTS=3`, then `failed`), and `prune_outbox` clears old sent/failed rows. The DB is the cross-worker bridge here exactly as in `NotificationRelayService`/`MessageRelay`; a dedicated service (not folded into the 1s toast relay) keeps the blocking 30s-per-send `send_markdown` off the toast path. Telegram defaults off, so absent any pairing the platform sends nothing until a paired user opts a type in. The profile Telegram column is disabled until the user pairs Telegram.
- **Live pairing update (pub/sub).** When a user completes pairing from their phone, the bridge (`services/telegram/bridge.py` `_handle_unpaired`) runs on the **service-lock owner** - exactly where every `/pubsub/ws` subscriber converges - so right after the `telegram.pair.success` audit it calls `_publish_pairing(user_uid, True)` -> `services.pubsub.publish(f"user.{uid}.telegram", {"paired": True})` (best-effort, lazy import). No new table, no cross-worker hop. The topic is already authorized for that user by `services/pubsub/policy.py` `_own_namespace` (`user.{uid}.*`). Frontend: `TelegramPairing` and `NotificationPrefs` (both built with `this.pubsub` in `Application.js`, after `PubSubClient`) read `document.body.dataset.userUid` and subscribe to `user.{uid}.telegram` (the `LiveNotifications`/`CounterManager` pattern); on `{paired:true}` the pairing panel flips to Connected (`setPaired`/`hideCode`/`setMessage`) and the notifications Telegram column enables live (drop `disabled`+`notif-switch-disabled`, hide the hint). Cross-device **unpair** live update is intentionally not wired (HTTP unpair runs on any worker; same-page unpair updates locally).
- **UI:** profile **Notifications** tab (`/profile/{username}?tab=notifications`, owner-or-admin, `POST /profile/{username}/notifications` + `/reset`) and admin global defaults at `/admin/notifications`. Devii tools `notification_list`/`notification_set`/`notification_reset` (`handler="notification"`, owner-scoped, `set`/`reset` in `CONFIRM_REQUIRED`). Reuse this canonical-list + resolver + override-table + single-funnel pattern for future per-user per-channel preferences.

### Live delivery (toasts and unread counts)

- **Live toasts ride the `in_app` channel** (no new channel). `services/notification_relay.py` `NotificationRelayService` is a lock-owner `BaseService` (registered in `main.py`, default-enabled, 1s interval) modeled on `MessageRelay`: it primes a watermark to `MAX(notifications.id)` on first tick, then each tick selects `notifications WHERE id > watermark` and publishes `{uid, type, message, target_url}` to the per-recipient pub/sub topic `user.{user_uid}.notifications`. It runs **only on the service lock owner**, which is exactly where every pub/sub WS subscriber converges (non-owner `/pubsub/ws` closes `4013`), so the in-process `services.pubsub.publish` reaches the recipient's browser. Because the `notifications` row exists only when the `in_app` channel is enabled (the insert above), a live toast fires **exactly** for the notifications a user has enabled - the relay needs no preference lookup of its own. The watermark always advances (boot-priming + every-tick) so a newly-connected subscriber never gets a backlog flood. Frontend: `base.html` exposes the viewer uid as `<body data-user-uid>`, `static/js/LiveNotifications.js` (`app.liveNotifications`, constructed with `this.pubsub`/`this.toast`) subscribes to that topic and calls `app.toast.show(message, {type:"info", ms, url})`. **The toast click reproduces a bell-item click exactly:** it targets `GET /notifications/open/{uid}` (which marks the row read and redirects to its `target_url`), not the bare `target_url` - the relay publishes `uid` for this. `AppToast` gained a generic click action via `_action(options)`: `options.onClick` (a function) or `options.url` (navigate); either adds the `.dp-toast-link` pointer cursor, so any caller can attach a click action. DMs toast too (type `message` is not excluded). Reuse this relay-on-the-lock-owner pattern for any future "live mirror of a DB-persisted, per-user event."
- **Unread-count badges ride the same relay.** The same `NotificationRelayService` tick, after publishing the toast rows, also publishes the recipient's fresh unread counts `{notifications, messages}` to `user.{uid}.counts` (computed with a direct DB query on the lock owner, never the per-worker `_unread_cache`, which could be stale there). Because a new DM also inserts a `message`-type notification row through `persist_message -> create_notification`, this one publish point covers both new notifications and new messages, so the header badge bumps instantly. `static/js/CounterManager.js` (`app.counters`, constructed with `this.pubsub`) subscribes to `user.{uid}.counts` and applies the pushed counts; its HTTP poll of `/notifications/counts` stays as a 60s reconciliation fallback (covers decrements on read and any missed frame). Read events still clear the per-worker cache as before.

## Gamification (XP, levels, badges, leaderboard)

The progression engine lives in `utils.py` and is wired into the existing content-creation, vote, and follow hooks. Do NOT scatter XP/badge logic - go through these helpers.

### XP and levels (`utils.py`)

- `award_xp(user_uid, amount)` - adds XP (clamped to `>= 0`), recomputes and stores `level`, invalidates the per-process user cache (`clear_user_cache`), and on level-up fires a `level` notification plus any level-milestone badge. Returns `{"xp", "level", "leveled_up"}`.
- `level_for_xp(xp)` -> `1 + max(0, xp) // LEVEL_XP` (`LEVEL_XP = 100`). `level` is stored on the user (not derived in the template) so `profile.html`'s `xp % 100` progress bar keeps working.
- XP amounts are constants in `utils.py`: `XP_POST=10`, `XP_COMMENT=2`, `XP_PROJECT=15`, `XP_GIST=5`, `XP_UPVOTE=5`, `XP_FOLLOW=5`. Awards for received upvotes/followers go to the content owner / followed user and live **inside** the existing `owner_uid != user["uid"]` guards (`votes.py`, `follow.py`).

### Badges (`utils.py`)

- `award_badge(user_uid, name)` is idempotent (returns `True` only on first grant) - reuse it; never insert into `badges` directly.
- `check_milestone_badges(user_uid)` recomputes **source-derived** count thresholds (counts of existing tables, so always accurate, no tracking needed) and awards + notifies any newly crossed badge. Call it after content creation and after an upvote/follow that changes the recipient's totals. The thresholds are the data-driven `_COUNT_MILESTONES` list `(badge, metric_key, threshold)` over metric keys `posts`/`comments`/`projects`/`gists`/`stars`/`followers`/`following`/`streak`, resolved lazily by `_milestone_metrics(user_uid)` (a memoized resolver that only queries a metric when an unheld badge needs it - badges already held are skipped before any query). To add a source-derived milestone, add a row to `_COUNT_MILESTONES` and the badge to `BADGE_CATALOG`. Streak badges additionally emit a `reward.streak.milestone` audit event.
- `award_rewards(user_uid, amount, first_badge=None)` is the single entry point for the create/upvote/follow reward sequence: it awards the optional first-time badge, calls `award_xp`, then `check_milestone_badges`. Use it instead of calling the three separately (posts/projects/gists/comments/follow/votes all go through it) so milestone checks are never skipped. **`award_rewards` defers its whole body to the background task queue** (`background.submit(_apply_rewards, ...)`), so EVERY XP award on the platform leaves the request path - the badge/XP/milestone writes (and the reward-triggered level/badge notifications nested inside `award_xp`/`check_milestone_badges`) all run on the consumer. In tests the queue runs inline, so XP is awarded synchronously and assertions hold. Never re-wrap an `award_rewards` call in `background.submit` - it self-defers. See `devplacepy/services/CLAUDE.md` -> Background task queue.
- Badge catalog + display metadata is `BADGE_CATALOG` in `utils.py` (55 badges), exposed to templates as the `badge_info(name)` global. Each entry has `icon`, `description`, and a `group` (one of `BADGE_GROUPS`: First steps, Explorer, Engagement, Content, Community, Reputation, Dedication, Levels, Milestones). Unknown names fall back to a default icon and the name as description. `LEVEL_BADGES` maps levels 5/10/25/50/100 to Level badges, awarded inside `award_xp` on level-up.

#### Activity tracking and feature-first-use badges

- For achievements that are **not** derivable from an existing table (first time a user uses a feature, "read N docs", number of DeepSearches, etc.), there is a generic per-user activity counter. `database.record_activity(user_uid, action) -> int` upserts a `user_activity` counter (unique `(user_uid, action)`, raw SQL `ON CONFLICT` inside `with db:`) and returns the new count; `database.record_unique_activity(user_uid, action, target) -> int | None` records a distinct `(user_uid, action, target)` in `user_activity_seen` (returns the new distinct count, or `None` if the target was already seen) for "N distinct things" achievements. Both tables + their unique indexes are ensured in `init_db()`.
- `utils.ACHIEVEMENTS` maps `action -> [(threshold, badge_name), ...]` (threshold 1 = first use). `utils.UNIQUE_ACTIONS` is the set of actions counted distinctly (currently `docs.read`). `utils.track_action(user_uid, action, target=None)` is the single public hook: it returns immediately if the action is unknown, else `background.submit`s `_apply_achievements`, which records the activity (counter or unique), then awards + notifies every crossed threshold via `award_badge`/`notify_badge`. It is fully deferred (off the request path) and idempotent, so call it inline at the success point of any feature.
- Wired actions and their hook sites: `vote`/`bookmark` (`content.py`), `reaction` (`routers/reactions.py`), `follow` (`routers/follow.py`, follower side), `message` (`services/messaging/persist.py`), `docs.read` (`routers/docs/views.py`, unique per slug, authenticated only), `fork`/`zip` (`routers/projects/index.py`), `zip`/`project_file` (`routers/projects/files.py`), `seo` (`routers/tools/seo.py`), `deepsearch` (`routers/tools/deepsearch.py`), `container` (`services/containers/api.create_instance`, user actor), `upload` (`routers/uploads.py`), `issue` (`routers/issues/create.py`), `poll` (`routers/polls.py`), `profile` (`routers/profile/index.py`, when a field is filled), `devii` (`services/devii/session.py` `_run_turn`, user owner on the non-docs channel only). To add a feature badge: add the badge to `BADGE_CATALOG`, an `ACHIEVEMENTS` entry, and a `track_action(...)` call at the feature's success point.
- **Profile showcase:** `utils.build_achievements(held_names)` returns the full catalog grouped by `BADGE_GROUPS` with an `earned` flag per badge and `earned`/`total` per group. The profile route passes it as `achievements` (+ `badge_earned`/`badge_total`) and `profile.html` renders a collapsible **Achievements** showcase (`.profile-achievements`, styles in `profile.css`) above the tabs, so every badge - earned and locked - is discoverable.

### Rank and leaderboard (`database.py`)

- `_ranked_authors()` builds (and 60s-caches in `_authors_cache`) the full list of authors with positive total stars, ordered desc, enriched via `get_users_by_uids`. `get_top_authors(limit)`, `get_leaderboard(limit, offset)` (adds a 1-based `rank`), and `get_user_rank(user_uid)` all slice/scan this one list - keep them consistent.
- `update_target_stars()` clears `_authors_cache` so a vote is reflected on the leaderboard and profile rank immediately (also keeps integration tests deterministic).
- The leaderboard page (`/leaderboard`, `routers/leaderboard.py`) shows the **top 50 only - no pagination** (`TOP_LIMIT = 50`). It is public (`get_current_user`), highlights the current user's row (`.leaderboard-row-self`), and is listed in `sitemap.xml`.

### Backfill

`_backfill_gamification()` runs at the end of `init_db()`. It computes XP from prior activity (same amounts as above) for users still at the default `xp=0`, sets `level`, then runs `check_milestone_badges` per user. Guarded on `xp=0` so it is idempotent across restarts.
