rob thijssen
5ce52fff4d
A self-hosted feed where the user owns the ranking: per-source and signed per-interest weights, decayed by recency, via a transparent deterministic scorer. Content is sourced algorithmically (worker RSS/Atom polling) and agentically (per-user API tokens POSTing candidates to the ingest endpoint). Single-user today, multi-user by construction (every row keyed on user_id). Rust cargo workspace + Vite/React/SWC/TS SPA: - newsfeed-entities: DTOs (ts-rs bindings -> web/src/api/bindings) - newsfeed-core: ranking, auth primitives, ingest, data-access ports - newsfeed-data: SQLite adapters (sqlx, runtime queries) - newsfeed-api: axum REST/JSON daemon - newsfeed-worker: RSS polling + rescoring loop - web: responsive, mobile-first SPA (React Query, generated types) Deploy (Gitea Actions, build static musl + SPA, rsync as gitea_ci): api+worker -> slartibartfast, SPA -> oolon (nginx serves + proxies /v1). Deliberate deviations from house conventions (documented in CLAUDE.md/readme): - SQLite instead of Postgres; api+worker co-locate sharing one DB file. - Runtime sqlx queries instead of query! macros (SQLite dynamic typing; keeps CI database-free). Verified end-to-end: auth, token ingest, interest-weighted ranking, signals, pagination (curl + browser); cargo fmt/clippy -D/test and pnpm build/lint pass. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_016fKZzDpvjiJ9eYbPGgJvUP
93 lines
3.2 KiB
SQL
93 lines
3.2 KiB
SQL
-- newsfeed initial schema (SQLite).
|
|
--
|
|
-- Conventions for this datastore:
|
|
-- * ids TEXT — hyphenated UUIDv4
|
|
-- * timestamps TEXT — RFC3339 / ISO8601 (UTC)
|
|
-- * booleans INTEGER — 0/1
|
|
-- * json blobs TEXT — serde_json arrays/objects
|
|
--
|
|
-- Every user-owned row carries user_id and cascades on user deletion, because each
|
|
-- user is wholly in control of — and isolated within — their own feed.
|
|
|
|
CREATE TABLE users (
|
|
id TEXT PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE sessions (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
created_at TEXT NOT NULL,
|
|
expires_at TEXT NOT NULL
|
|
);
|
|
CREATE INDEX idx_sessions_user ON sessions(user_id);
|
|
|
|
CREATE TABLE api_tokens (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
prefix TEXT NOT NULL,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
created_at TEXT NOT NULL,
|
|
last_used_at TEXT,
|
|
revoked_at TEXT
|
|
);
|
|
CREATE INDEX idx_api_tokens_user ON api_tokens(user_id);
|
|
|
|
CREATE TABLE sources (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
kind TEXT NOT NULL, -- 'rss' | 'agentic'
|
|
name TEXT NOT NULL,
|
|
url TEXT,
|
|
weight REAL NOT NULL DEFAULT 1.0,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
last_polled_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE(user_id, name)
|
|
);
|
|
CREATE INDEX idx_sources_user ON sources(user_id);
|
|
|
|
CREATE TABLE interests (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
label TEXT NOT NULL,
|
|
weight REAL NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
UNIQUE(user_id, label)
|
|
);
|
|
CREATE INDEX idx_interests_user ON interests(user_id);
|
|
|
|
CREATE TABLE items (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
source_id TEXT REFERENCES sources(id) ON DELETE SET NULL,
|
|
external_id TEXT NOT NULL,
|
|
url TEXT,
|
|
title TEXT NOT NULL,
|
|
summary TEXT,
|
|
author TEXT,
|
|
tags TEXT NOT NULL DEFAULT '[]', -- json array of strings
|
|
media TEXT NOT NULL DEFAULT '[]', -- json array of Media
|
|
published_at TEXT,
|
|
ingested_at TEXT NOT NULL,
|
|
state TEXT NOT NULL DEFAULT 'candidate',
|
|
score REAL NOT NULL DEFAULT 0.0,
|
|
UNIQUE(user_id, external_id)
|
|
);
|
|
-- Feed reads order by score within a user's live items.
|
|
CREATE INDEX idx_items_feed ON items(user_id, state, score DESC, id DESC);
|
|
|
|
CREATE TABLE signals (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
item_id TEXT NOT NULL REFERENCES items(id) ON DELETE CASCADE,
|
|
action TEXT NOT NULL, -- 'view' | 'click' | 'save' | 'dismiss'
|
|
created_at TEXT NOT NULL
|
|
);
|
|
CREATE INDEX idx_signals_item ON signals(item_id);
|