Adopting Supabase — Adding Visitors, Guestbook, and Leaderboards to a Static Site

This post looks back on bringing Supabase into a statically-deployed blog to add two-way features. Rather than a surface-level "I attached a database", I want to focus on how a static deployment can still flow live data without a backend server, and how I handled the security problems that surfaced along the way.

Why Adopt It?

This blog runs on Next.js with output: 'export', producing pure static files deployed to S3 + CloudFront. The upsides are obvious — server costs are negligible and it loads fast worldwide. But there's a catch: no runtime server is a wall for anything interactive.

• Want to count visitors — where do I store counts?
• Want guestbook comments per post — which server receives them?
• Want game leaderboards — where do scores live?

The traditional answer was to spin up a separate backend API. But for a low-traffic personal blog, running a DB server for this felt like overengineering.

What Is Supabase?

Supabase is an open-source BaaS (Backend as a Service) built around PostgreSQL. It's often compared to Firebase, but the decisive difference is a relational DB at the core.

Key pieces:

• Database — Plain PostgreSQL. If you know SQL, you're ready.
• Auth — Email, OAuth, magic link, etc. built in
• Storage — S3-compatible file storage
• Realtime — Subscribe to table changes over WebSockets
• Edge Functions — Deno-based serverless functions
• Auto API — REST/GraphQL endpoints generated automatically from your tables

The most distinctive piece is Supabase's embrace of Row Level Security (RLS). It allows the client to call the DB directly — no backend — and defends access with Postgres' row-level policies. This design is what makes database access possible on a purely static site.

When Does It Fit?

Supabase isn't always the right pick. From experience, good fits:

• Static sites that need CMS-grade dynamic features — exactly this case
• When you need PostgreSQL's expressiveness — triggers, functions, joins
• Rapid MVP development — Auto API gives a huge head start
• Solo side projects — minimal operational overhead
• Realtime subscriptions — chat, collaboration, live dashboards

Be more careful when:

• You're at production scale and need to audit limits and pricing carefully
• Your data is overwhelmingly document-shaped — a DocumentDB-family choice might fit better
• You have dedicated backend engineers — rolling your own might be more flexible

The Three Features I Built

I'm skipping specific implementation details and sticking to structure.

1. Visitor Count (Unique Visitors)

UV-style — one record per session per day. If every page transition logged an event, the numbers would be meaningless inflation, so uniqueness is session ID + visit date.

[Client] Generate UUID into sessionStorage
     ↓
[RPC call] track_visit(session_id, date)
     ↓
[Server] INSERT into visit_log (silently ignore conflicts)

2. Guestbook

Comments per blog post. Pagination, owner-only delete, server-side profanity filter, rate limiting — all included.

3. Game Leaderboards

High scores for 5 canvas games on the blog. Usernames are anonymous and randomly assigned, with ownership checks preventing other sessions from hijacking a name.

Security Issues I Hit (and Fixes)

Letting the client call the DB directly is convenient, but it widens the attack surface significantly. Below are the real issues and how I defused each.

1. The Anon Key Is Public

Supabase's anon_key is embedded in the client bundle. Anyone can pull it out of DevTools. Mistaking this key for "private" is catastrophic.

Fix: Push all access control down to RLS, and design assuming the anon key is public. Keep admin keys like service_role completely out of client bundles.

2. Same Session Spamming the Visit Log

A naive INSERT means one user pressing F5 a hundred times writes a hundred records.

Fix:

• UNIQUE constraint on (session_id, visit_date) in visit_log
• Wrap insertion in a SECURITY DEFINER RPC with ON CONFLICT DO NOTHING
• Clients can't issue raw INSERTs; they can only call that RPC

3. Guestbook Spam — Bots Flooding

RLS alone couldn't fully stop "same session posting all day".

Fix:

• Honeypot field (hidden input in the form — only bots fill it)
• 30-second cooldown (limits consecutive inserts from the same session)
• Daily cap per session: 10 (BEFORE INSERT trigger counts and blocks)
• Per-post cap per session: 3 (prevents domination of a single post)

With four layers stacked, real users barely notice while most automated abuse is blocked.

4. Profanity / Content Filter

Client-only filtering is trivially bypassed via DevTools.

Fix:

• Client has a UX-focused first-pass filter (instant feedback)
• Server stores profanity_words + a PL/pgSQL function
• A BEFORE INSERT trigger re-validates on the server
• Inappropriate content rejected with a custom errcode → client catches the code and shows a friendly message

5. Leaderboard Username Hijacking

Even for anonymous names, "someone grabs my name" is a problem.

Fix:

• username_owner table registering (username, session_id) pairs
• submit_score RPC checks whether the current session owns the name
• Other sessions trying the same name get rejected with P0001

6. Owner-Only Delete via user_id

"I can only delete what I wrote" — obvious, but implementation requires care.

Fix:

• Persistent user_id UUID stored in localStorage (session-independent)
• Store user_id on INSERT
• DELETE RLS policy checks user_id match
• Threat tradeoff: "someone could forge a user_id by tampering with localStorage" — accepted. UUIDs aren't guessable, so it's practically safe.

Traps I Ran Into

useMemo(() => localStorage..., []) in Static Export

Even 'use client' components are SSR-rendered at build time by Next.js. At that moment window isn't defined, so localStorage access returns null, and since useMemo has empty deps it caches null permanently — no re-run after hydration.

→ Switch to useState + useEffect so the real value is injected after mount. Surfaced as a bug where the "delete my comment" button never appeared.

crypto.randomUUID Requires a Secure Context

Local dev on HTTP threw crypto.randomUUID is not a function. Browser policy blocks this API in non-secure contexts.

→ Added a Math.random-based UUID v4 fallback.

Retrospective

Attaching a DB to a static site turned out to be a good call. Adoption cost was low (runs well inside Supabase's free tier) and operational overhead is minimal. The two lessons that stuck with me:

1. "Call the DB directly from the frontend" only works if RLS is airtight. When policies and triggers are designed rigorously up front, no-server systems are robust — but if they're sloppy, it's basically an open admin console.

2. Make server-side re-validation a habit. Client-side checks are UX, not security. Profanity filters, spam limits, ownership checks — each must be re-enforced in server triggers/functions.

If you're considering a similar adoption, I'd emphasize one thing: bake security design in from the start. Retrofitting it later forces you to restructure the data model.