privatecloud builder
95bef8bf7b
CI / build (push) Successful in 2m0s
Vite/React + shadcn/ui frontend with Superhuman-style keyboard nav (j/k/o/v/e/s/u, feed switching, localStorage caching for instant loads) backed by an Express API that polls the HN Firebase API into Postgres and serves cached feeds/comments. Instrumented with OpenTelemetry (traces/metrics/logs via OTLP/HTTP).
39 lines
1.7 KiB
SQL
39 lines
1.7 KiB
SQL
-- Schema for hn-reader: cached Hacker News stories + local read state.
|
|
-- Single-user app: no user table, state is keyed directly by story id.
|
|
|
|
CREATE TABLE IF NOT EXISTS stories (
|
|
id BIGINT PRIMARY KEY, -- Hacker News item id
|
|
type TEXT NOT NULL DEFAULT 'story',
|
|
title TEXT NOT NULL,
|
|
url TEXT,
|
|
domain TEXT,
|
|
by_user TEXT,
|
|
score INTEGER NOT NULL DEFAULT 0,
|
|
descendants INTEGER NOT NULL DEFAULT 0, -- comment count
|
|
story_text TEXT, -- self-text for Ask HN / Show HN
|
|
hn_created_at TIMESTAMPTZ NOT NULL,
|
|
fetched_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Which feed(s) a story currently ranks in, and at what rank, so the
|
|
-- frontend can render top/new/best/ask/show/job lists from one table.
|
|
CREATE TABLE IF NOT EXISTS feed_items (
|
|
feed TEXT NOT NULL, -- top | new | best | ask | show | job
|
|
rank INTEGER NOT NULL,
|
|
story_id BIGINT NOT NULL REFERENCES stories(id) ON DELETE CASCADE,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (feed, rank)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS feed_items_story_idx ON feed_items(story_id);
|
|
|
|
-- Per-story local state: read/starred/archived, superhuman-style.
|
|
CREATE TABLE IF NOT EXISTS story_state (
|
|
story_id BIGINT PRIMARY KEY REFERENCES stories(id) ON DELETE CASCADE,
|
|
read BOOLEAN NOT NULL DEFAULT false,
|
|
starred BOOLEAN NOT NULL DEFAULT false,
|
|
archived BOOLEAN NOT NULL DEFAULT false,
|
|
read_at TIMESTAMPTZ,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|