twitter-client/NOTIFICATIONS_PLAN.md

Plan: SQLite Notification-Polling System

1. Goal

Add a notification system that polls the X API for followers, mentions, and likes, diffs the results against a local SQLite state, and exposes unread notifications through a real Ktor API + React UI.

2. What Already Exists

• Kotlin/Ktor backend, hand-wired DI in Application.kt.
• Existing XApiClient uses OAuth 1.0a for the v2 API.
• Redis cache for timeline only.
• Frontend NotificationsTab is currently a placeholder.
• schema.sql (already in repo) defines the SQLite tables: users, followers, tracked_tweets, tweet_likers, mentions, notifications, sync_state.

3. Key Design Questions

1. Bearer token vs OAuth 1.0a
   The instructions say the new endpoints use Bearer token auth. The current XApiClient is OAuth-1.0a only. Should we add a separate Bearer-authenticated client, or reuse OAuth 1.0a where possible?

   • Decision: Reuse OAuth 1.0a. Don't build a separate Bearer client. Open: Where does the Bearer token come from? .env/docker-compose.yml? Please confirm.
   • Decision: Doesn't matter. No new tokens will be added, use the existing ones. No need to add a Bearer token. After a reserach I confirm this is not needed.

2. SQLite driver
   Use org.xerial:sqlite-jdbc (JDBC) plus a tiny connection pool like com.zaxxer:HikariCP, or use org.jetbrains.exposed:exposed-core + exposed-jdbc for typed DAOs.
   Proposed: Keep it simple — JDBC + Kotlin data classes. The schema is fixed, and the project already avoids heavy frameworks.

3. tracked_tweets seeding
   Likes polling needs rows in tracked_tweets. The instructions say "populated separately/manually for now".
   Proposed: Seed tracked_tweets automatically with the most recent N tweets from the home timeline each time pollAll() runs, or add an endpoint /api/tracked-tweets to add/remove them. We can start by auto-seeding from timeline tweets and iterate later.

   • Decision: auto-seed from timeline, no need to have a CRUD for it.

4. Triggering the poll
   The user said "when the timeline is loaded, also fetch other endpoints". We can either:

   • trigger pollAll() from the timeline route, or
   • run a background coroutine scheduler on a configurable interval. Proposed: Implement a Ktor ApplicationStarted background coroutine scheduler (configurable interval via env var NOTIFICATION_POLL_INTERVAL_MINUTES, default 5 min). Also allow a manual trigger via an admin/internal endpoint if useful.
     • Decision: do not use a coroutine or scheduler. Pull the data when the user clicks the notifications tab.

5. SQLite file location
   Proposed: NOTIFICATION_DB_PATH env var, default /data/notifications.db for Docker and ./notifications.db for local dev. Mount a volume in docker-compose.yml.

   • Decision: approved.

4. Proposed Files & Changes

Backend

File	Purpose
backend/src/main/kotlin/com/rmcampos/twitterclient/db/DbConfig.kt	Open/close SQLite connection pool, run schema.sql on startup.
backend/src/main/kotlin/com/rmcampos/twitterclient/db/NotificationRepository.kt	All SQL: insert/update users, followers, mentions, tweet_likers, tracked_tweets, sync_state, notifications, mark-read queries.
backend/src/main/kotlin/com/rmcampos/twitterclient/notifications/XApiV2Client.kt	Bearer-token client for /2/users/{id}/followers, /2/users/{id}/mentions, /2/tweets/{id}/liking_users. Handles pagination.
backend/src/main/kotlin/com/rmcampos/twitterclient/notifications/NotificationPoller.kt	pollFollowers(), pollMentions(), pollLikes(), pollAll(). Pure logic, no Ktor dependency.
backend/src/main/kotlin/com/rmcampos/twitterclient/notifications/PollScheduler.kt	Ktor ApplicationStarted/ApplicationStopped hooks launching a CoroutineScope with delay().
backend/src/main/kotlin/com/rmcampos/twitterclient/routes/NotificationRoutes.kt	GET /api/notifications and POST /api/notifications/{id}/read.
backend/src/main/kotlin/com/rmcampos/twitterclient/Application.kt	Wire DbConfig, repository, XApiV2Client, NotificationPoller, scheduler, and route.
backend/build.gradle.kts	Add sqlite-jdbc and HikariCP dependencies.
backend/Dockerfile	No change needed unless we want a default /data volume.
backend/.env.example	Add NOTIFICATION_DB_PATH, NOTIFICATION_POLL_INTERVAL_MINUTES, X_BEARER_TOKEN.

Frontend

File	Purpose
frontend/src/api.ts	Add fetchNotifications() and markNotificationRead(id).
frontend/src/types.ts	Add Notification type.
frontend/src/components/NotificationsTab.tsx	Replace placeholder with list of notifications, unread badge, mark-read button.
frontend/src/components/NotificationsTab.css	Minimal styling.

Project-level

File	Purpose
.env.example	Add X_BEARER_TOKEN, NOTIFICATION_DB_PATH, NOTIFICATION_POLL_INTERVAL_MINUTES.
docker-compose.yml	Pass new env vars; add notifications-data volume mounted at /data.
AGENTS.md	Document new env vars and notification polling behavior.

5. Data Flow

Ktor starts
  -> DbConfig opens SQLite, executes schema.sql
  -> NotificationPoller created (depends on repository + XApiV2Client)
  -> PollScheduler starts CoroutineScope with configurable delay

Every interval:
  pollFollowers()  -> fetch all followers -> diff with DB -> emit new_follower / unfollow notifications
  pollMentions()   -> fetch mentions since since_id -> insert each as mention + notification
  pollLikes()      -> for each tracked_tweet, fetch liking_users -> diff -> emit like notifications
  update sync_state and tracked_tweets.last_checked_at

Frontend:
  GET /api/notifications       -> unread notifications joined with users/mentions/tweet_likers
  POST /api/notifications/{id}/read -> set read_at

6. API Contract

GET /api/notifications

Response:

{
  "notifications": [
    {
      "id": 1,
      "type": "new_follower",
      "created_at": "2026-01-01T12:00:00Z",
      "subject": { "id": "123", "name": "Jane", "username": "jane", "profile_image_url": "..." },
      "ref_tweet_id": null
    }
  ],
  "unread_count": 1
}

POST /api/notifications/{id}/read

Mark single notification as read. Returns 204 No Content.

7. Environment Variables

Variable	Default	Purpose
X_BEARER_TOKEN	required	Bearer token for the new v2 notification endpoints.
NOTIFICATION_DB_PATH	/data/notifications.db	SQLite file path.
NOTIFICATION_POLL_INTERVAL_MINUTES	5	Minutes between pollAll() runs. Set to 0 to disable background polling.

8. Open Decisions

1. Please confirm the source of the X Bearer token: Should X_BEARER_TOKEN be read from env var (.env/docker-compose.yml), or from an app config file?

• Decision: the new token is not needed, use the existing ones.

2. Should the frontend poll the notifications endpoint automatically (e.g. every 30s), or only on tab click?

• Decision: on tab click only.

3. Should tracked tweets be auto-seeded from the home timeline, or do you want a manual /api/tracked-tweets CRUD endpoint first?

• Decision: auto-seeded, no manual CRUD.

9. Next Steps

1. Review this plan.
2. Approve or request changes.
3. Me implement.