pgsky

An AT Protocol / Bluesky client implemented as a PostgreSQL extension. The database is the application.

(Proper extension packaging — CREATE EXTENSION pgsky; — is planned for a future version. Today, install is a series of SQL files; see below.)

pgsky is a Bluesky client where the entire client lives inside PostgreSQL — schema, OAuth flow, DPoP signing, XRPC calls, scheduled refresh, message bus, all of it. There is no application server. Front-ends (psql, the pgsky TUI) are stateless caches over the database.

CALL atproto.create_post_proc('hello from postgres');
SELECT * FROM atproto.timeline;
CALL atproto.like_n(3);

The terminal client renders Bluesky as if it were a database admin tool, because that's honest to what's underneath.

---

Status

Version 0.2 — working, autonomous, public-facing. Running continuously on the maintainer's machine for over a week with a 97%+ success rate on cron-driven operations. See CHANGELOG.md for what changed since 0.1.

Install is documented in docs/pgsky-setup-v0.2.md — six SQL files plus environment setup, 45–60 minutes from a clean Ubuntu/Debian box. Test-account suggested for first install.

Currently working:

• OAuth 2.1 + DPoP + PAR sign-in flow, with browser redirect
• Token refresh on a 45-minute cron schedule (no manual re-auth for weeks)
• Timeline polling, notifications polling
• Create / reply / like / repost / delete via SQL procedures
• Thread drill-down, full-text search across cached posts
• A Rust TUI with reactive LISTEN/NOTIFY updates and a database-admin-tool aesthetic
• Clickable procedure dialogs in the TUI for invoking any pgsky operation through a form
• 12 procedures, 11 tables, 3 views, 4 cron jobs
• Encrypted-at-rest DPoP keypairs (pgsodium)

Not yet implemented:

• Extension packaging (CREATE EXTENSION pgsky CASCADE;) — coming in 0.3
• Firehose ingester (currently 30-second polling instead of sub-second push)
• Multiple concurrent sessions
• Quote posts, lists, feed generators, labels — out of v0.x scope

---

Why

Most Bluesky clients are application servers that use a database. pgsky is the inverse: the database is the application, and the client is whatever speaks SQL. This has some interesting consequences:

• Operational — psql is your debugger. EXPLAIN your timeline query. pg_dump for backup. Everything you already know about Postgres works.
• Architectural — LISTEN/NOTIFY is the message bus. pg_cron is the event loop. Procedures are the API. Views are the read layer.
• Conceptual — instead of a custom protocol between app and DB, you have one substrate that holds identity, data, and behavior.

---

Architecture

pgsky lives entirely inside PostgreSQL. The atproto schema holds 11 tables (sessions, posts, notifications, DPoP keys, OAuth state, etc.), 12 procedures (OAuth flow, XRPC operations, refresh), and 3 views (timeline, notifications, status). Background work runs on pg_cron. HTTP calls go through pg_net. DPoP private keys are encrypted at rest with pgsodium.

Front-ends — psql, the pgsky TUI, or anything else that speaks SQL — are stateless caches over the database. They read from views and call procedures. The database is the source of truth.

Key architectural finding: pg_net inherits Postgres MVCC. A function calling net.http_post cannot get a response back, because the background worker can't see uncommitted queue rows. Every HTTP-using operation in pgsky is a procedure, called via CALL not SELECT. This is the single most important pattern in the codebase.

A longer architectural narrative will land in docs/architecture.md later.

---

Install

Full install is documented in docs/pgsky-setup-v0.2.md. It takes 45–60 minutes from a clean Ubuntu/Debian box. Short version:

1. PostgreSQL 17 from PGDG, plus postgresql-17-cron, postgresql-plpython3-17
2. Build pg_net and pgsodium from source (one make && make install each)
3. Set up the pgsodium master key
4. Set shared_preload_libraries = 'pg_net,pg_cron,pgsodium', restart
5. Create database, configure pg_hba.conf, load the extension/sql/*.sql files
6. Run the OAuth flow once to get a session
7. Schedule the cron jobs, and it runs itself

The setup doc covers six hard-won install gotchas (pg_net + pgsodium source build quirks, pg_hba.conf rule ordering, pg_net.database_name, the loopback OAuth client_id encoding, etc.). Read it before starting.

Test-account suggestion: consider using a throwaway Bluesky account for your first install. pgsky is v0.2 — it works, but delete_record_proc exists and bugs are possible.

---

TUI

The pgsky binary in tui/ is a Rust/ratatui terminal client. Styled like pgAdmin or DbVisualizer — object browser on the left, result grid in the middle, row detail at the bottom. Vim-style keys. Reactive via LISTEN/NOTIFY.

cd tui
cargo build --release
PGSKY_DSN="host=/var/run/postgresql dbname=pgsky" ./target/release/pgsky

Key	Action
Tab	Switch focus between Object Browser and result grid
j / k	Move cursor
g / G	Jump to top / bottom of grid
Enter	Expand folder / load view / open procedure dialog
p	Compose post
r	Reply to selected post
l / R	Like / repost
d	Delete (timeline only, your own posts)
t	Open thread for selected post
/	Search cached posts
F5	Manual refresh
q	Quit

Clicking a procedure in the Object Browser opens a parameter dialog with field hints and SQL preview — useful for AT Proto debugging without writing SQL by hand.

See tui/README.md for more.

---

Usage examples

-- Post
CALL atproto.create_post_proc('hello from postgres');
SELECT body->>'uri' FROM atproto.last_http;

-- Like the 3rd post in your timeline
CALL atproto.like_n(3);

-- Reply to the 5th
CALL atproto.reply_n(5, 'good point');

-- Repost the 7th
CALL atproto.repost_n(7);

-- View your timeline
SELECT * FROM atproto.timeline;

-- View notifications
SELECT * FROM atproto.notifications;

-- Search cached posts
SELECT * FROM atproto.search('postgres');

-- Dashboard
SELECT * FROM atproto.status;

-- Inspect any AT URI (yours or someone else's)
CALL atproto.get_post_thread_proc('at://did:plc:.../app.bsky.feed.post/...');
SELECT body FROM atproto.last_http;

---

License

GPL v3 — see LICENSE.

---

Built by Former Lab · @formerlab.bsky.social. Issues and pull requests welcome.

Reach for Me.