tomdebone
c586fd39c9
AT Protocol PDS + AppView + Tauri Desktop Client, 160-char post limit. - PDS (Rust + axum + sqlx) - Auth: createAccount, createSession, refreshSession - Records: createRecord, deleteRecord (race-safe via SELECT FOR UPDATE) - Feed: feed.like.create, feed.repost.create - Sync: getRepo, getBlocks, getLatestCommit, getRecord (with MST proof), listRepos - Identity: resolveHandle - MST: spec-conformant (at-mst crate, 27 tests) - Repo: signed commits, TID counter (monotonic, 4096 wrap safe) - AppView (Rust + axum + sqlx) - Jetstream consumer (WebSocket, exponential backoff, 38k+ events indexed) - REST API: timeline/home (graph-aware), profile, search, post (with thread hydration) - Handle-sync worker (did:plc + did:web) - JSONB embed storage + thread columns (migration 0003) - Like/repost counter cache (migration 0004) - Tauri 2 + Svelte 5 Desktop Client - System tray (Show/Compose/Quit menu) - OS notifications (tauri-plugin-notification) - Auto-update (tauri-plugin-updater, placeholder endpoint) - Window-state (tauri-plugin-window-state) - 160-char compose with live counter - Image/Link embed rendering - LocalStorage-persisted like state - Timeline with poll (prepend new posts) - Custom TitleBar (transparent, no decorations) - Orange/IBM Plex Mono maarcade design Tests: 231 Rust + 9 vitest = 240 passed.
95 lines
3.6 KiB
SQL
95 lines
3.6 KiB
SQL
-- AppView database schema (initial)
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- =====================================================
|
|
-- posts
|
|
-- =====================================================
|
|
CREATE TABLE posts (
|
|
uri TEXT PRIMARY KEY, -- at://did/rkey/app.twi.post
|
|
did TEXT NOT NULL,
|
|
handle TEXT NOT NULL,
|
|
rkey TEXT NOT NULL,
|
|
collection TEXT NOT NULL, -- app.twi.post | app.bsky.feed.post
|
|
text TEXT NOT NULL,
|
|
cid TEXT NOT NULL,
|
|
parent_uri TEXT, -- reply parent
|
|
root_uri TEXT, -- thread root
|
|
langs TEXT[],
|
|
indexed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
CREATE INDEX posts_did_idx ON posts (did);
|
|
CREATE INDEX posts_created_at_idx ON posts (created_at DESC);
|
|
CREATE INDEX posts_collection_idx ON posts (collection);
|
|
CREATE INDEX posts_parent_idx ON posts (parent_uri) WHERE parent_uri IS NOT NULL;
|
|
CREATE INDEX posts_root_idx ON posts (root_uri) WHERE root_uri IS NOT NULL;
|
|
|
|
-- =====================================================
|
|
-- likes
|
|
-- =====================================================
|
|
CREATE TABLE likes (
|
|
uri TEXT PRIMARY KEY,
|
|
did TEXT NOT NULL,
|
|
post_uri TEXT NOT NULL,
|
|
post_cid TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
indexed_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX likes_post_idx ON likes (post_uri);
|
|
CREATE INDEX likes_did_idx ON likes (did);
|
|
|
|
-- =====================================================
|
|
-- reposts
|
|
-- =====================================================
|
|
CREATE TABLE reposts (
|
|
uri TEXT PRIMARY KEY,
|
|
did TEXT NOT NULL,
|
|
post_uri TEXT NOT NULL,
|
|
post_cid TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
indexed_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX reposts_post_idx ON reposts (post_uri);
|
|
CREATE INDEX reposts_did_idx ON reposts (did);
|
|
|
|
-- =====================================================
|
|
-- follows
|
|
-- =====================================================
|
|
CREATE TABLE follows (
|
|
follower_did TEXT NOT NULL,
|
|
subject_did TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
indexed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (follower_did, subject_did)
|
|
);
|
|
CREATE INDEX follows_subject_idx ON follows (subject_did);
|
|
|
|
-- =====================================================
|
|
-- timeline cache (materialized per-user timelines)
|
|
-- =====================================================
|
|
CREATE TABLE timeline_cache (
|
|
did TEXT NOT NULL,
|
|
post_uri TEXT NOT NULL,
|
|
score DOUBLE PRECISION NOT NULL,
|
|
ranked_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (did, post_uri)
|
|
);
|
|
CREATE INDEX timeline_did_score_idx ON timeline_cache (did, score DESC, ranked_at DESC);
|
|
|
|
-- =====================================================
|
|
-- search (simple trigram)
|
|
-- =====================================================
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
CREATE INDEX posts_text_trgm_idx ON posts USING GIN (text gin_trgm_ops);
|
|
|
|
-- =====================================================
|
|
-- jetstream cursor
|
|
-- =====================================================
|
|
CREATE TABLE jetstream_cursor (
|
|
id INTEGER PRIMARY KEY DEFAULT 1 CHECK (id = 1),
|
|
cursor BIGINT NOT NULL DEFAULT 0,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
INSERT INTO jetstream_cursor (id, cursor) VALUES (1, 0);
|