How we built a local social publishing desk with SQLite

Meta will not fetch an image from localhost, and our public site cannot safely hold a Meta token. Those two constraints shaped the whole tool. We built Social Desk as a localhost app that reads the GattyWorks site, checks Threads and Instagram, and stores every decision in one local SQLite file.

Four pages, one job

The job is to move a published GattyWorks article to social media without losing track of the copy, media, approval, or live result. Social Desk has four pages:

• Content compares every published blog and news item with prepared packages and recent live posts.
• Publish shows the Threads post, Instagram carousel, captions, and first comments as they will appear.
• Comments puts the public conversation next to an editable suggested reply.
• Activity records approvals, writes, failures, removals, and the local operator.

There is no scheduler. The person at the dashboard decides what happens now, and a future service can own scheduling if we need it.

Why we kept it outside Next.js

GattyWorks is a static Next.js export. It has no server routes, and that is a useful boundary. Putting Meta credentials into the public app would turn a local publishing task into a secret-leak problem.

The dashboard lives under tooling/social instead. A small Node server binds to 127.0.0.1. A separate Preact and Vite client handles the interface. The website build never sees a Threads or Instagram token.

npm run social:setup
npm run social

That starts the local server and opens the four-page app. The browser can read status and save an approval. Every public action still needs an explicit second confirmation.

SQLite replaced two half-stores

The first version wrote approvals to Markdown and activity to JSON. Both were readable, but neither was a good source of truth once the dashboard could search, update, and publish.

Node 24 already gave us node:sqlite, so we added no database package. tooling/social/.data/social-manager.sqlite3 now stores approvals, activity, and the ignore list. It is gitignored. Human-readable Markdown mirrors still show the exact approved posts and replies to Codex, Claude Code, and a person checking the queue.

The ignore table is deliberately small:

CREATE TABLE content_ignores (
  kind TEXT NOT NULL,
  slug TEXT NOT NULL,
  ignored_at TEXT NOT NULL,
  ignored_by_email TEXT NOT NULL,
  PRIMARY KEY (kind, slug)
);

The local Git email is the operator identity. It is good enough for a localhost tool and stays off Meta. It would not be enough for a hosted multi-user service.

Public text is data

The Comments page reads words written by strangers. A public comment can contain an instruction aimed at an agent. We treat the whole comment as data, never as a command.

A reply passes through these steps:

1. The dashboard shows the original post, the incoming comment, detected language, and a local suggestion.
2. A person edits and approves the exact reply.
3. SQLite stores that exact text with the operator's Git email.
4. A separate confirmation sends it now, or the commenting agent later reloads the approved record by ID.

The write path never accepts replacement copy. It reloads what the person approved. This keeps a hostile comment from turning itself into an instruction to read secrets or publish something else.

Live state beats local memory

A SQLite row can say a post was published while another person has already deleted it. Another clone can publish the same package without touching this database.

Content reads recent Threads and Instagram history on load, every 60 seconds, when the window regains focus, and immediately before a write. Exact live copy wins over local activity. The final pre-write check blocks a duplicate even when two operators opened the dashboard at the same time.

Local activity is still useful when a platform read is unavailable, but it is evidence, not the final authority.

An ignore button was part of correctness

Some website posts should never become social posts. Without an ignore action, those items sit in Pending forever and the count stops meaning anything.

Ignore is reversible. The Pending and Complete filters hide ignored items. The Ignored filter reveals them, shows who made the choice, and has a Restore button. The dashboard records ignore and restore actions in Activity with the same Git email audit identity.

We keep the real platform state underneath. An ignored item can still say that its Threads package is ready or its Instagram post is live. Ignore changes queue intent, not history.

Meta's limits shaped the interface

Meta must fetch media from public HTTPS URLs. We stage banners and carousel slides under gattyworks.com/social-media/active, even when an image does not appear inside the article. After a successful publish, the local package can move to archive.

Removal controls follow the APIs we actually have. Threads can delete our posts and replies. It can hide an audience reply, but it cannot delete somebody else's words. Instagram lets us delete comments on our media. Its publishing API does not give this tool a supported feed-post delete endpoint, so the dashboard opens the live Instagram post and tells the person to delete it in the app.

Instagram Stories are manual. Scheduling is deferred. The current tool does immediate, human-approved work only.

What broke first

OAuth failed before publishing code mattered. Meta blocked an insecure redirect, then rejected a redirect URI that was not whitelisted, then enforced developer roles for the Instagram account. Each error belonged in app configuration, not in a retry loop.

Media was the next constraint. A correct local file path is useless to Meta. The dashboard now fills public URLs from the prepared package and checks them before approval.

The first interface also became too tall to scan. Moving the client to Preact and Vite gave us reusable search, filters, confirmation dialogs, spinners, skeletons, and a real Instagram carousel preview without pulling that code into the public site.

How we use it

The normal run is short:

1. Publish the blog or news item and stage its social media files on gattyworks.com.
2. Open Content. Ignore the item if it should not enter the social queue.
3. Open Publish. Edit the Threads copy, Instagram caption, and first comments inside their previews.
4. Approve for an agent, or confirm an immediate publish.
5. Open Comments later, edit a suggested reply, and approve the exact text.
6. Check Activity for the result and operator email.

No OpenAI API sits behind the buttons. Codex or Claude Code can help write local copy and inspect the queue. The Node scripts make deterministic reads and send only the exact approved payload.

What comes next

If scheduling becomes necessary, it gets a separate durable queue and a fresh security review. If the dashboard leaves 127.0.0.1, Git email and a local CSRF token stop being enough. It will need real authentication and shared audit storage.

The immediate next step is smaller: use the ignored queue for the next ten posts and watch whether Pending stays trustworthy. If people still work around the queue, the activity log will show exactly where the model is wrong.