mirror of
https://github.com/R0m1k3/FlowReader.git
synced 2026-10-11 17:28:05 +02:00
Antigravity AgentandClaude Opus 5.5
03e57e4308
Security: - WebSocket events are routed to their owner only (no cross-user leak); hub close is idempotent (fixes double-close panic), adds ping/pong and write deadlines. - Session tokens stored as SHA-256 (migration 008 keeps sessions valid); single-query auth middleware puts the user in the request context. - Client IP only trusts X-Forwarded-For from TRUSTED_PROXIES; rate limiter map is bounded; per-user limit on AI summaries. - Argon2id at OWASP minimum with a concurrency cap; constant-time login for unknown emails; atomic first-admin bootstrap; REGISTRATION_ENABLED. - CSP/HSTS/COOP headers, same-origin guard on mutations, body size limits, wider SSRF denylist, bounded feed/page/AI response reads, generic errors. - Upgrade chi, pgx, x/net, x/text, x/crypto (known CVEs); commit go.sum. Performance: - List endpoints return a plain-text excerpt and reading time instead of full HTML; content is sanitized once at ingest (legacy rows backfilled). - Keyset pagination on (sort_at, id) with matching partial indexes; redundant indexes dropped (migration 007). - Fetcher: bounded worker pool, conditional GET (ETag/Last-Modified), exponential backoff, dedupe before insert, column-safe truncation, retention-aware ingest, per-user refresh coalescing. - Read/favorite/read-all are single ownership-scoped statements. - gzip compression, immutable caching for hashed assets, path-safe SPA handler, server timeouts; expired sessions purged. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
39 lines
2.5 KiB
SQL
39 lines
2.5 KiB
SQL
-- Migration: 007_perf_reading
|
|
-- Description: list-friendly derived columns, keyset-pagination indexes,
|
|
-- conditional GET / backoff state for feeds, and removal of redundant indexes.
|
|
|
|
-- Derived article columns. Filled at ingest; legacy rows are backfilled by the
|
|
-- application at startup (word_count IS NULL marks a row as not yet processed).
|
|
ALTER TABLE articles ADD COLUMN IF NOT EXISTS excerpt TEXT;
|
|
ALTER TABLE articles ADD COLUMN IF NOT EXISTS word_count INTEGER;
|
|
|
|
-- Non-null sort key so keyset pagination works on (sort_at, id).
|
|
ALTER TABLE articles ADD COLUMN IF NOT EXISTS sort_at TIMESTAMPTZ
|
|
GENERATED ALWAYS AS (COALESCE(published_at, created_at)) STORED;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_articles_feed_sort ON articles (feed_id, sort_at DESC, id DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_articles_feed_unread_sort ON articles (feed_id, sort_at DESC, id DESC) WHERE NOT is_read;
|
|
CREATE INDEX IF NOT EXISTS idx_articles_sort ON articles (sort_at DESC, id DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_articles_unread_sort ON articles (sort_at DESC, id DESC) WHERE NOT is_read;
|
|
CREATE INDEX IF NOT EXISTS idx_articles_fav_sort ON articles (sort_at DESC, id DESC) WHERE is_favorite;
|
|
CREATE INDEX IF NOT EXISTS idx_articles_unread_feed ON articles (feed_id) WHERE NOT is_read;
|
|
CREATE INDEX IF NOT EXISTS idx_articles_cleanup ON articles (created_at) WHERE NOT is_favorite;
|
|
CREATE INDEX IF NOT EXISTS idx_articles_backfill ON articles (id) WHERE word_count IS NULL;
|
|
|
|
-- Redundant or unusable indexes: each one costs a write on every INSERT and
|
|
-- every is_read UPDATE.
|
|
DROP INDEX IF EXISTS idx_articles_feed_id; -- prefix of UNIQUE(feed_id, guid)
|
|
DROP INDEX IF EXISTS idx_articles_published_at; -- wrong NULLS order, unused
|
|
DROP INDEX IF EXISTS idx_articles_is_read; -- replaced by partial indexes
|
|
DROP INDEX IF EXISTS idx_articles_is_favorite; -- replaced by partial index
|
|
DROP INDEX IF EXISTS idx_users_email; -- duplicate of UNIQUE(email)
|
|
DROP INDEX IF EXISTS idx_sessions_token; -- duplicate of UNIQUE(token)
|
|
DROP INDEX IF EXISTS idx_feeds_user_id; -- prefix of UNIQUE(user_id, url)
|
|
|
|
-- Feed fetch state: HTTP conditional GET + exponential backoff on errors.
|
|
ALTER TABLE feeds ADD COLUMN IF NOT EXISTS etag TEXT;
|
|
ALTER TABLE feeds ADD COLUMN IF NOT EXISTS last_modified TEXT;
|
|
ALTER TABLE feeds ADD COLUMN IF NOT EXISTS error_count INTEGER NOT NULL DEFAULT 0;
|
|
ALTER TABLE feeds ADD COLUMN IF NOT EXISTS next_fetch_at TIMESTAMPTZ;
|
|
CREATE INDEX IF NOT EXISTS idx_feeds_next_fetch ON feeds (next_fetch_at NULLS FIRST);
|