ADHDoIt

CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE TYPE todo_status AS ENUM ('active', 'snoozed', 'done');

CREATE TABLE users (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email         TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,      -- empty string for SSO-only accounts
    name          TEXT NOT NULL,
    timezone      TEXT NOT NULL DEFAULT 'UTC',
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE refresh_tokens (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash TEXT NOT NULL UNIQUE,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ON refresh_tokens(user_id);
CREATE INDEX ON refresh_tokens(expires_at);

CREATE TABLE categories (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name       TEXT NOT NULL,
    color      TEXT NOT NULL DEFAULT '#6366f1',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ON categories(user_id);

CREATE TABLE todos (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id      UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    category_id  UUID REFERENCES categories(id) ON DELETE SET NULL,
    title        TEXT NOT NULL,
    description  TEXT,
    deadline     DATE NOT NULL,
    reminder_at  TIMESTAMPTZ,
    priority     SMALLINT NOT NULL DEFAULT 0 CHECK (priority BETWEEN 0 AND 3),
    status       todo_status NOT NULL DEFAULT 'active',
    snooze_until DATE,
    done_at      TIMESTAMPTZ,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ON todos(user_id, status, deadline);
CREATE INDEX ON todos(user_id, category_id);
CREATE INDEX ON todos(reminder_at) WHERE reminder_at IS NOT NULL AND status != 'done';

CREATE TABLE push_subscriptions (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    endpoint   TEXT NOT NULL UNIQUE,
    p256dh     TEXT NOT NULL,
    auth       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ON push_subscriptions(user_id);

ALTER TABLE users ADD COLUMN oidc_subject TEXT;
CREATE UNIQUE INDEX users_oidc_subject_idx ON users(oidc_subject)
    WHERE oidc_subject IS NOT NULL;

POST   /auth/register   → 201 {user, access_token, refresh_token}
POST   /auth/login      → 200 {user, access_token, refresh_token}
POST   /auth/refresh    → 200 {access_token, refresh_token}  (token rotated)
DELETE /auth/logout     → 204

# GitHub OAuth2 SSO (only registered when GITHUB_CLIENT_ID is set)
GET    /auth/github/login     → 302 redirect to GitHub authorization endpoint
GET    /auth/github/callback  → 302 redirect to {FRONTEND_URL}/auth/callback#access_token=...&refresh_token=...

GET    /todos?view=today|upcoming|overdue|done&category_id=<uuid>&sort=deadline|priority|created_at&order=asc|desc
POST   /todos           → 201
GET    /todos/:id       → 200
PATCH  /todos/:id       → 200  (partial update; reminder_at change cancels/re-enqueues River job)
DELETE /todos/:id       → 204
POST   /todos/:id/snooze  body: {snooze_until: "YYYY-MM-DD"} → 200
POST   /todos/:id/done    → 200
POST   /todos/:id/reopen  → 200

GET    /categories      → 200
POST   /categories      → 201
PATCH  /categories/:id  → 200
DELETE /categories/:id  → 204  (todos get category_id = NULL)

POST   /push/subscribe  body: {endpoint, keys: {p256dh, auth}} → 204 (upsert)
DELETE /push/subscribe  → 204

{ "error": "human-readable message", "code": "MACHINE_CODE" }

r.Use(middleware.RealIP)
r.Use(middleware.RequestID)
r.Use(slogMiddleware)
r.Use(cors.Handler(...))
r.Use(middleware.Recoverer)
r.Use(middleware.Timeout(30 * time.Second))
// Auth middleware applied per-route group, not globally

type TodoHandler struct {
    q     *db.Queries
    pool  *pgxpool.Pool
    river *river.Client[pgx.Tx]
    cfg   *config.Config
}
// decode → validate → sqlc query → respondJSON / respondError

['todos', view, categoryId, sort, order]  // list
['todos', id]                             // single
['categories']

self.addEventListener('push', event => {
    let data = {};
    try { data = event.data?.json() ?? {}; } catch (_) {}
    event.waitUntil(self.registration.showNotification(data.title ?? 'ADHDoIt', {
        body: data.body, data: { url: data.url ?? '/' }
    }));
});
self.addEventListener('notificationclick', event => {
    event.notification.close();
    event.waitUntil(clients.openWindow(event.notification.data.url));
});

format(new Date(utcString), "yyyy-MM-dd'T'HH:mm")