Neon + Clerk cutover

NovaReport's backend moved from Supabase to three services:

• Cloudflare Worker (workers/news-api): the only server. News proxy, article cache, accounts API, admin API, Clerk webhooks.
• Neon (serverless Postgres): article cache, user roles, admin console data, audit log.
• Clerk: sign-in, sign-up, email verification, password reset, Google/Microsoft sign-in.

The code for all three is in place and the Worker is deployed. What remains is creating the two accounts and storing their keys. Until then, news works normally and the account pages say "Accounts are temporarily unavailable".

Secrets

Every value marked secret below goes only into wrangler secret put or a local shell variable. Never commit it, never put it in a VITE_* variable, and never paste it into a chat or issue.

0. Check the current state

curl -s https://novareport-news-api.michelduek.workers.dev/api/health

checks lists each piece as configured / not_configured (database, auth, webhooks, newsdata, key_encryption). ready becomes true once the database and Clerk are both connected.

1. Neon database

1. Sign in at console.neon.tech and create a project (Free plan is enough at current traffic). Region: one close to your readers, e.g. AWS US East (N. Virginia). Postgres 17.

2. Connect → choose the neondb database and the owner role → copy the connection string with Connection pooling off (postgresql://…@ep-….neon.tech/neondb?sslmode=require). It is a secret.

3. Create the tables (from the repository root):

   cd workers/news-api
   read -rs DATABASE_URL && export DATABASE_URL   # paste, Enter (not echoed)
   npm run db:migrate          # applies migrations/0001_init.sql
   npm run db:status           # every migration listed as applied

   The migration runner records a checksum per file and refuses to continue if an applied migration was edited later.

4. Give the Worker the same connection string:

   npx wrangler secret put DATABASE_URL     # paste it when prompted

5. curl …/api/health now shows "database": "ok". The article cache starts filling on the next news requests; the daily cron (03:17 UTC) prunes articles older than 30 days.

2. Clerk application

1. Sign in at dashboard.clerk.com and Create application "NovaReport". Sign-in options: Email (with password) plus Google and Microsoft if wanted.

2. Configure → Email, phone, username: require email verification (verification code or link). Configure → Password: keep breach detection on.

3. Configure → Paths: sign-in page /login, sign-up page /register on the application domain; after sign-out /.

4. Switch to production (Clerk creates a production instance for novareport.net). Clerk then lists DNS records to add (CNAMEs such as clerk.novareport.net and the email DKIM records). Add exactly those records at the DNS provider (IONOS). They are new subdomain records only; the website's own records and nameservers stay as they are. Wait until Clerk shows every record as verified.

5. Social sign-in in production needs your own OAuth credentials: Google Cloud Console → OAuth client (web), redirect URI as shown by Clerk; Microsoft Entra → app registration, likewise. Paste them into Clerk's SSO connection settings.

6. Collect the keys from Configure → API keys (production instance):

   • Publishable key pk_live_… — public.
   • Secret key sk_live_… — secret.
   • JWT public key (Show JWT public key → PEM) — public, but store it as a Worker secret so it can be rotated without a code change.

7. Store the Worker secrets:

   cd workers/news-api
   npx wrangler secret put CLERK_SECRET_KEY        # sk_live_…
   npx wrangler secret put CLERK_JWT_KEY           # the whole PEM, including BEGIN/END lines
   openssl rand -base64 48 | npx wrangler secret put ADMIN_KEYS_ENCRYPTION_KEY

   ADMIN_KEYS_ENCRYPTION_KEY encrypts provider API keys stored through the admin console. Keep a copy in a password manager: losing it makes the stored keys unreadable (they can be re-entered).

8. Configure → Webhooks → Add endpoint:

   • URL: https://novareport-news-api.michelduek.workers.dev/api/webhooks/clerk
   • Events: user.created, user.updated, user.deleted
   • Copy the Signing secret (whsec_…, secret) and run npx wrangler secret put CLERK_WEBHOOK_SIGNING_SECRET.
   • The Worker checks the Svix signature and rejects events older than 5 minutes. After the first real sign-up (step 3), the endpoint's Message attempts must show 200.

9. Turn sign-in on in the website. Lovable builds do not read environment variables, so the publishable key is committed. In src/config/backend.ts set:

   const COMMITTED_CLERK_PUBLISHABLE_KEY = 'pk_live_…';

   Open a pull request, let CI pass, merge, then publish in Lovable (see Building & deployment).

10. curl …/api/health → "ready": true.

3. Make yourself an administrator

Nobody becomes an administrator by signing up, by their email address, or by anything the browser sends. The role is set in the database by an operator, with a reason that is written to the audit log:

1. Sign up on https://www.novareport.net/register and verify your email.
2. Find your user ID (user_…) in Clerk → Users.

3. cd workers/news-api
   read -rs DATABASE_URL && export DATABASE_URL
   npm run role:grant -- user_XXXXXXXX admin --reason "site owner"

4. Sign in at https://www.novareport.net/admin. Every admin request is checked again on the server (valid Clerk session and role = 'admin' in the database).

To remove the role later: npm run role:grant -- user_XXXXXXXX user --reason "…".

4. Move the old Supabase accounts (optional)

The old project (dryddsriublbepecaprq) is paused, not deleted. Its accounts can be recreated in Clerk with their existing passwords (Supabase stores bcrypt hashes, which Clerk accepts), so people sign in as before.

1. Supabase dashboard → the project → Restore project. Wait until it is healthy.

2. SQL Editor → run each query → Download CSV. Save the files outside the repository (they contain personal data and password hashes); delete them after the import.

   -- profiles.csv
   select user_id, email, name, role, created_at from public.profiles;
   
   -- auth-users.csv
   select u.id, u.email, u.encrypted_password, u.email_confirmed_at, u.created_at,
          u.raw_user_meta_data ->> 'full_name' as full_name
     from auth.users u;

3. Dry runs (they print counts only, never emails):

   cd workers/news-api
   npm run legacy:import -- ~/novareport-export/profiles.csv
   npm run clerk:import  -- ~/novareport-export/auth-users.csv

4. Apply. Both scripts read DATABASE_URL; the Clerk import also needs the secret key:

   read -rs DATABASE_URL && export DATABASE_URL
   read -rs CLERK_SECRET_KEY && export CLERK_SECRET_KEY
   npm run legacy:import -- ~/novareport-export/profiles.csv --apply
   npm run clerk:import  -- ~/novareport-export/auth-users.csv --apply

   • Only accounts with a confirmed email are created; unconfirmed ones are counted as skipped.
   • Each Clerk user gets external_id = the old Supabase ID, so the link is traceable. Re-running skips accounts already imported.
   • Roles are never imported. Former administrators are listed in legacy_identity_links.legacy_role for reference only; grant the role again with step 3 if it is still wanted.
   • Someone who signs up later with the same verified email is linked to their old profile automatically, but only when exactly one old profile has that email.

5. Pause the Supabase project again. Keep it, and the export, until you have checked a few migrated sign-ins.

5. NewsData (optional)

npx wrangler secret put NEWSDATA_API_KEY

The feed then adds NewsData articles to GNews results. Without the key the browser asks once per session, gets configured: false, and stops asking.

6. Verify

Check	Expected
GET /api/health	ready: true, every needed check ok / configured
Home page	News loads; no requests to *.supabase.co in DevTools → Network
/register	Clerk sign-up, verification email arrives
/login → /profile	Name and member-since date shown; "Manage account and security" opens Clerk's account page
/admin as a regular user	"This account is not a NovaReport administrator."
/admin after role:grant	Dashboard; Providers / Keys / Logs / Blocklists / Overrides load
select count(*) from news_articles (Neon SQL editor)	Grows as the feed is used
Cloudflare → Workers → novareport-news-api → Logs	One JSON line per /api request with requestId, no level: "error" lines

Rollback

Each step can be undone on its own; news reading never depends on any of them.

To undo	Do this	Effect
Sign-in on the website	Set COMMITTED_CLERK_PUBLISHABLE_KEY back to '', merge, publish in Lovable	Account pages show "temporarily unavailable"; news unaffected
The Worker's database	npx wrangler secret delete DATABASE_URL	/api/* data routes answer 503; the browser stops asking for the cache for 5 minutes at a time
Clerk verification on the Worker	npx wrangler secret delete CLERK_JWT_KEY and CLERK_SECRET_KEY	Signed-in API calls answer 503 auth_not_configured
A Worker release	npx wrangler deployments list, then npx wrangler rollback <version-id>	Previous Worker version serves immediately
A website release	Revert the merge commit on main (pull request), publish in Lovable	Previous frontend
Back to Supabase entirely	Not supported by the current code: the Supabase client was removed. Revert the migration pull request, restore the Supabase project, publish	Old stack (only if the Supabase project still exists)

Do not delete the Supabase project yet

It still holds the only copy of the old accounts. Delete it only after step 4 is done and checked, and the export is archived somewhere safe — or after deciding the old accounts are not needed.