Data & Systems

Postgres is not "a spreadsheet with SQL." It's a relational engine with a serious type system, and your first job as a data operator is to use that type system to make impossible states literally unrepresentable. Before you write a single CREATE TABLE, list the domain's entities and the relationships between them. In MOONKEY LAB the real entities are: a user (profiles), their progress per module (progress), the proofs they upload (proofs), the feedback they leave (feedback), and the captured leads. Each one is a table. Each 1-to-N relationship between them is a foreign key. Don't start with the columns; start with the arrows between boxes.

The identifier. In Supabase the profiles table hangs off auth.users, so its primary key is NOT a freshly generated id: it's `id uuid primary key references auth.users(id) on delete cascade`. This is the canonical Supabase pattern and it's deliberate — the authenticated user's uuid IS the identity throughout your whole app, and `auth.uid()` (which you'll use in every RLS policy) returns exactly that uuid. For child tables like progress or proofs you do use your own key: `id uuid primary key default gen_random_uuid()`, plus a `user_id uuid not null references auth.users(id) on delete cascade` column. Note the `not null` and the `on delete cascade`: if you delete a user, their proofs go with them; if you allowed a null user_id, you'd have orphan rows that no `user_id = auth.uid()` policy could either show or protect.

Types matter and they're cheap to get right at the start. For timestamps ALWAYS use `timestamptz` (with time zone), never plain `timestamp` — a project with users in Chiang Mai, Mallorca, and Madrid can't afford time-zone ambiguity, and `timestamptz` stores in UTC and converts at the boundary. For money, `numeric`, never `float` (binary floats can't represent exactly 0.10). For a field with a closed set of values — the operator's rank: monkey, gorilla, eagle — you have two honest options: a Postgres `enum`, or `text` with a `check (role in ('monkey','gorilla','eagle'))`. I prefer the CHECK over the enum in young projects: adding a value to an enum requires `ALTER TYPE` and carries transactional friction, whereas changing a CHECK is an ordinary table ALTER. For semi-structured data — a proof's payload, flexible metadata — use `jsonb`, not `json`: jsonb is binary, indexable with GIN, and deduplicates keys.

Defaults and NOT NULL are your first line of validation, ahead of any RLS or any app-level check. `created_at timestamptz not null default now()` means it's impossible to insert a row without a date. `status text not null default 'forming'` means the status is never null, and therefore your code never needs `if status is null` branches. Every column you leave nullable is a conditional branch you pay for forever in the code that reads it. The rule: a column is NOT NULL by default, and you only make it nullable when the absence of a value is a legitimate domain state, distinct from an empty value.

Uniqueness and indexes. The relationship "a user has exactly one progress record per module" is not a comment: it's `unique (user_id, module_code)`. That unique constraint also gives you the index you need for the upsert (`insert ... on conflict (user_id, module_code) do update`), which is exactly how MOONKEY persists progress. Beyond keys, create indexes on the columns you actually filter by: if you query proofs by `user_id` constantly, `create index on proofs (user_id)`. But don't index reflexively — every index slows down INSERTs and takes up space; index what you've measured being queried, not what you imagine will be queried.

Apply the schema as a versioned migration, never by hand in the SQL editor. In Supabase that's `apply_migration` (a named, timestamped .sql file), not loose `execute_sql`. The difference is auditability: six months from now you'll want to know why proofs has that column, and the answer lives in the migration history, not in your memory. After applying, run `generate_typescript_types` so that the client's TypeScript type derives from the real schema — that way the DB is the single source of truth and the front end can't lie about the shape of the data.

A concrete, complete example for proofs, the table where an operator uploads evidence that they completed a deliverable: its own uuid key, a NOT NULL user_id with FK and cascade, a module_code with a CHECK against the list of valid codes, a jsonb payload, a created_at timestamptz NOT NULL default now(), and an index on user_id. That design makes it so that (a) every row belongs to an existing user, (b) no row is ever orphaned, (c) no made-up module_code can get in, and (d) the "my proofs" query is fast. On top of that foundation — and only on top of it — does it make sense to put RLS in the next module.

Internalize the threat model before you touch a single policy. On an SSG with Supabase, the attacker does NOT use your interface. They open the browser console, grab the anon key (which is in the bundle, because it has to be), instantiate their own Supabase client, and call `supabase.from('profiles').select('*')`. Your admin.astro file that "hides" the panel does not exist for them. The disabled button does not exist for them. The only thing standing between their SELECT and every row of every user is Postgres Row Level Security. That's why the module's phrase: the client gate is UX (it improves the experience for the legitimate user), the RLS is the security (it stops the illegitimate one).

RLS is enabled per table and defaults to deny-all. `alter table profiles enable row level security` — and at that instant, with no policy at all, NO ONE (except the service role and the table owner) can read or write anything. This is correct: you start from zero permissions and open exactly what you need. The catastrophic mistake is enabling RLS and forgetting one operation: if you set SELECT policies but no INSERT policy, INSERTs fail silently; worse, if you do NOT enable RLS on a new table, it's wide open to the anon key. That's why DS-06 introduces the advisors: they detect exactly the table without RLS that you forgot.

A policy has two clauses that confuse everyone: USING and WITH CHECK. USING filters which existing rows the operation sees or touches (SELECT, UPDATE, DELETE). WITH CHECK validates which new or modified rows you're allowed to write (INSERT, UPDATE). SELECT only has USING. INSERT only has WITH CHECK. UPDATE has BOTH: USING says which rows you can update, WITH CHECK says what they can become. A classic mistake is putting USING on an INSERT where it does nothing, or putting only USING on an UPDATE and letting the user move their row to a different user_id. MOONKEY's self-or-admin pattern: `using (user_id = auth.uid() or is_admin())`.

The canonical self pattern. For proofs, MOONKEY's real insert policy is `proofs_insert_self`: `create policy proofs_insert_self on proofs for insert to authenticated with check (user_id = auth.uid())`. Read it out loud: an authenticated user can insert a proof only if the user_id of the row they're inserting equals their own identity. They can't create a proof in someone else's name, because WITH CHECK would reject any row with user_id ≠ auth.uid(). Note the `to authenticated`: the policy doesn't even apply to the anon role, so a visitor without a session can't insert anything. For SELECT, the self-or-admin pattern: `using (id = auth.uid() or is_admin())` on profiles — you see your row, or all of them if you're an admin.

auth.uid() is the heart of the system and it's worth knowing what it is: a Supabase SECURITY DEFINER function that extracts the `sub` from the JWT the client sends on every request. The client can't forge it because the JWT is signed by Supabase with a secret the client doesn't have; tampering with it invalidates the signature and Postgres rejects the session. That's why `user_id = auth.uid()` is trustworthy in a way that `user_id = <value the client sent>` never would be. Authority lives in the token's signature, not in the front end's good faith.

Verification by impersonation — this is what separates a serious operator from one who "believes" their RLS works. Reading the policy and nodding is not enough. In the Supabase SQL editor you can simulate being a specific user by setting the role and the claim: `set local role authenticated; set local request.jwt.claims to '{"sub":"<user-A-uuid>"}';` and then `select * from profiles`. If you see other users' rows, your RLS is broken. MOONKEY's security model says explicitly "verified by impersonation": a non-admin can't read other users' rows, and that was PROVEN, not assumed. Do the same: impersonate user A, try to read user B's row, and demand that the result be zero rows.

Two final traps. First: the `service_role` role (the service key, NEVER in the client) bypasses all RLS — which is why it never lives in an SSG and is only used in trusted edge functions. Second: policies are permissive by default and combine with OR, not AND. If you have two SELECT policies on the same table, a row visible to EITHER of them is visible. This surprises people: adding a policy never restricts, it only widens. To restrict you need explicit RESTRICTIVE policies, or — simpler and the usual approach — a single well-written policy per operation.

How the magic-link works, step by step. The client calls `supabase.auth.signInWithOtp({ email })`. Supabase generates a single-use token, associates it with the email, and sends a message with a link pointing to your app with that token in the URL fragment. The user clicks; your app, on load, detects the token, exchanges it with Supabase for an access_token / refresh_token pair, and from then on the client is authenticated. The access_token is a signed JWT with a short expiry (one hour by default); the refresh_token is long-lived and is used to obtain new access tokens without the user logging in again. The SDK orchestrates all of this; your job is to understand the flow, not to reimplement it.

Where the session lives. By default, the Supabase SDK in the browser persists the session in localStorage. This has a security consequence you must say out loud: a token in localStorage is readable by any JavaScript running on your page. That means your number-one attack surface is XSS — if an attacker manages to inject JS into your site (a compromised third-party script, an innerHTML with unsanitized input), they can read the token and steal the session. The defense isn't to hide the token; it's to have no XSS: don't inject unsanitized user HTML, audit every front-end dependency, and treat every third-party `<script>` as code that will see your tokens.

The honest distinction that gives the module its name: the session is identity, not authorization. The client holding a valid access_token proves WHO they are (auth.uid() will return their uuid), but it does NOT grant them permission to do anything by itself. Permission is decided by RLS on every query. This is liberating: you don't have to defend your data on the front end. Even if an attacker steals a session, they can only do what RLS allows THAT user — see their own rows, not anyone else's. A stolen ordinary-user session grants no admin access, because is_admin() revalidates against the profiles row, not against a claim the client can manipulate.

Session-state handling in the app. The SDK exposes `supabase.auth.getSession()` (reads the current session, possibly from localStorage) and `supabase.auth.onAuthStateChange((event, session) => ...)` (notifies you of SIGNED_IN, SIGNED_OUT, TOKEN_REFRESHED). In an SSG like MOONKEY you have no server-side rendering of the auth state, so the page loads first in an "unknown" state and then, client-side, you resolve whether there's a session. Design for that: show a neutral loading state, don't flicker between "guest" and "logged in." The visual gate (showing /cuenta only if there's a session) is legitimate UX — remember DS-02: it's UX, the security still lives in the RLS.

The trigger that closes the loop. When a user registers for the first time via magic-link, Supabase creates a row in auth.users. But your app needs a corresponding row in profiles. The client does NOT do this (it must not be able to choose its own role or founder_badge). A SECURITY DEFINER trigger in the database does it: `handle_new_user`, which fires `after insert on auth.users` and creates the profiles row with safe default values (role='monkey', never admin). That way profile creation is server-authoritative: the user can't be born admin because the trigger, not the client, decides the initial values. You develop this pattern in depth in DS-04.

Logout and expiry, done honestly. `supabase.auth.signOut()` deletes the tokens from localStorage and revokes the refresh_token on the server. Important: if you only clear localStorage by hand without calling signOut, the refresh_token remains valid on the server — always do it through the SDK. On expiry: don't promise a "session forever." The access_token expires in an hour and the SDK refreshes it transparently with the refresh_token; if the refresh_token is revoked or expires, the user logs in again. Communicate this to the user honestly instead of faking eternal persistence.

Configure the redirect URLs in the Supabase panel (Auth > URL Configuration). The magic-link redirects to a URL that MUST be on the allowlist, or Supabase rejects the exchange — this prevents an attacker from making the link redirect to a domain they control. In MOONKEY, the redirect URLs include the production domain (moonkeylab.pages.dev) and localhost for development, and nothing else. A lax redirect allowlist is a real phishing vector.

What SECURITY DEFINER means. A normal Postgres function runs with the permissions of whoever CALLS it (SECURITY INVOKER, the default). A SECURITY DEFINER function runs with the permissions of whoever CREATED it (typically a privileged role that owns the tables). This lets it do things the caller couldn't do directly — for example, read profiles to check a role, even though RLS would deny that user the SELECT. It's powerful and it's dangerous: a SECURITY DEFINER is a small piece of code that runs above the RLS. Each one is an exception to your security model, so each one must be audited as such.

The is_admin() case. You need to know whether the current user is an admin to use it in policies (`using (... or is_admin())`). But if the SELECT policy on profiles depends on reading profiles to know the role, you have infinite recursion: to read your row you need to know if you're an admin, to know that you read profiles, which fires the policy again. The solution is is_admin() as SECURITY DEFINER: it runs with the owner's privileges, reads profiles WITHOUT going through RLS, and returns a boolean. Crucially, it does NOT return sensitive data — only true/false about the caller (`select role = 'admin' from profiles where id = auth.uid()`). A safe SECURITY DEFINER exposes the minimum information: a decision, not a dataset.

Lock down the search_path — this is the classic vulnerability and the one the advisors flag without mercy. A SECURITY DEFINER that doesn't pin its search_path is exploitable: an attacker creates a table or function with the same name as one your function uses, in a schema that comes earlier in the search_path, and your privileged function executes the attacker's code with owner permissions. The defense is mandatory: `create function is_admin() ... security definer set search_path = '' as $$ ... $$;` (or `set search_path = pg_catalog, public` while qualifying explicitly). With an empty search_path, you reference every table with its full schema: `public.profiles`, not `profiles`. Without this, your security function IS the hole.

RPCs: business logic callable from the client. An RPC in Supabase is a Postgres function exposed via `supabase.rpc('name', args)`. In MOONKEY the real RPCs are update_operator_rank (promotes the rank while revalidating), my_referral_stats (returns the user's referral statistics), and is_admin. The golden pattern: the RPC does NOT trust the client's arguments for identity. update_operator_rank does not receive "which user to promote" as a free parameter — it uses auth.uid() internally. If it received a user_id as an argument, an attacker would call rpc('update_operator_rank', { user_id: 'someone else's' }). Identity ALWAYS comes from auth.uid() inside the function, never from a parameter the client controls.

The RPC revalidates, it doesn't obey. update_operator_rank is not "set my rank to X because I asked." It revalidates the rules: did the user complete the proofs required for that rank? The comment in MOONKEY's security model is explicit — "Ranks only via update_operator_rank (revalidates role)." The client can't jump from monkey to admin by asking; the RPC checks the real conditions in the DB and only then writes. This is the difference between an RPC that is a business API (it verifies invariants) and one that is a hole (it writes whatever it's told). Grant EXECUTE only to `authenticated`, never to anon: `grant execute on function update_operator_rank to authenticated`.

Triggers: invariants enforced no matter what. Some invariants can't depend on the app respecting them. "A non-admin can never change their own role or their founder_badge" is one: if it depended on the app, any direct UPDATE via the anon key would bypass it. MOONKEY's solution is the guard_privileged_profile_columns trigger, which fires `before update on profiles` and, if the caller is not an admin and is trying to change role or founder_badge, raises an exception that aborts the transaction. Combined with handle_new_user (after insert on auth.users, creates profiles with role='monkey'), the result is that a user is BORN as a monkey and CANNOT self-promote — not through the app, not through a raw UPDATE with the anon key. The trigger is the net beneath the RLS.

The discipline of auditing. For every SECURITY DEFINER you write: does it have its search_path pinned? Does it return the minimum possible information? Does it derive identity from auth.uid() and not from a parameter? Is its EXECUTE restricted to the correct role? After creating or changing any of these functions, run get_advisors (DS-06): the security advisor flags SECURITY DEFINER functions without a search_path and functions with lax permissions. A SECURITY DEFINER is privileged code; treat it with the paranoia that code running above your own security deserves.

The real problem isn't "copy data." It's reconciling two sources of truth that evolved separately: this browser's localStorage and the row in Postgres (which may have changed from another device). Before you write code, decide the conflict policy explicitly, because "whatever happens" is the recipe for data loss. The honest options: last-write-wins (the most recent timestamp wins, simple but can lose concurrent edits), per-field merge (you combine field by field according to rules), or append-only (you never overwrite, only add, and derive the state). For learning progress — which is monotonic, only advancing — the best policy is usually "the maximum wins": if local says module 3 completed and the cloud says module 5, the result is 5; you never go backward.

localStorage as a layer, not as the truth. Espejo's Store seam pattern is the right abstraction: your app doesn't call localStorage or Supabase directly, it calls a Store with an interface (get, set, list). There's a LocalStore implementation (localStorage) and a SupabaseStore one (Postgres). The app doesn't know which it's using. This turns "move to the cloud" from a rewrite into a swap of implementation behind the same interface. The sync, then, is an operation between two Stores: read everything from LocalStore, reconcile with SupabaseStore, write the result to both. Build the seam BEFORE you need the cloud; it's cheap at the start and brutally expensive to retrofit.

Idempotency is the property that saves you. The sync will be interrupted: the user closes the tab halfway, the network drops, the SDK retries. If your sync isn't idempotent, a second run creates duplicates. The tool is the upsert with a natural key: `insert into progress (user_id, module_code, completed_at) values (...) on conflict (user_id, module_code) do update set completed_at = greatest(progress.completed_at, excluded.completed_at)`. That unique (user_id, module_code) from DS-01 is exactly what makes the upsert possible. Run the sync twice in a row: if the final state is identical, it's idempotent. If the second run duplicates rows or changes something, you have a bug that in production shows up as corrupted data at midnight.

The critical moment: the first login after working as a guest. The user progressed in localStorage without a session, then does the magic-link (DS-03) and obtains an auth.uid(). Now you have to adopt the anonymous state under their identity. The safe flow: when onAuthStateChange fires with SIGNED_IN, you read the progress from LocalStore, upload it with an upsert binding user_id = auth.uid(), and only then mark the local as synced. Do NOT delete the local until you've confirmed the cloud received it (the confirmation is the upsert's error-free response). If you delete first and the upload fails, you lost the data. Order: upload, confirm, mark synced, optionally clean up.

RLS is still in charge during the sync. When you upload the progress with the user's session, the upsert goes with their JWT, so the policy `with check (user_id = auth.uid())` applies: you can't upload progress in someone else's name, even if localStorage said otherwise. This is good — the sync is not a back door to security. It means you must bind user_id to auth.uid() at the moment of upload, not use a user_id you were carrying from the anonymous state (which had no real identity). The sync respects the model: the server remains the authority over whose each row is.

Cross-device conflicts, the case people forget. The user progresses on their phone (uploads to the cloud), then opens their laptop, which had old local state. Without care, the laptop overwrites the cloud with stale data. The defense: reconciliation is NOT "local overwrites cloud," it's "reconcile both according to the policy." For monotonic progress, you pull the cloud, do the "maximum wins" merge with the local, and write the result to both. That way the laptop learns what the phone did instead of erasing it. For non-monotonic data you need per-field timestamps (updated_at) and per-field last-write-wins, which requires storing those timestamps from the start — another reason for the timestamptz columns from DS-01.

Sync states, visible and honest. Model them explicitly: synced (local == cloud), pending (there are local changes not uploaded), syncing (in progress), error (failed, retry). Don't lie to the user with a green checkmark if the upload failed. An honest "unsaved changes" indicator stops the user from closing the tab believing they were safe. Silent sync that fails silently is worse than no sync: the user trusts it and loses data without knowing.

Migrations: the database as versioned code. Every schema change — a table, a column, a policy, a function — is a timestamped .sql file in supabase/migrations/, applied with apply_migration, never with execute_sql by hand. The difference is the same as between committing and editing files in production over SSH: one gives you history, review, and rollback; the other gives you amnesia. The file name (`<timestamp>_create_proofs.sql`, `<timestamp>_add_proofs_rls.sql`) tells the story of the schema. When six months from now you wonder why a column exists, the answer is in the migration that introduced it, with its name and its date — not in your memory or anyone else's.

execute_sql is for READING, apply_migration is for CHANGING. This operational rule prevents the most common mistake. Use execute_sql to inspect (select, explain, check the state), to impersonate and verify RLS (DS-02), for exploration. The moment the SQL alters the schema or the policies in a way you want to persist, it goes in a migration. A security change applied with execute_sql that works but isn't versioned is debt: no one knows it exists, no one can review it, and when the project is recreated it disappears.

Supabase branches so you don't break production. Before applying a high-impact migration, create it on a branch (create_branch), test it there — including the impersonation verification from DS-02 and the get_advisors from DS-04 — and only then merge it to production (merge_branch). The branch has its own ephemeral database; you break whatever you want without touching real users. This is especially critical in MOONKEY because the DB is shared: a migration that mistakenly touches an iron_* or world_* table from XHUB gets tested and discarded on the branch, not on the live database serving two projects.

The advisors are your security linter. get_advisors(type='security') runs a set of checks that detect exactly the holes these modules teach you to avoid: tables with RLS disabled (DS-02), SECURITY DEFINER functions with a mutable search_path (DS-04), policies that expose data, unprotected columns. get_advisors(type='performance') flags the rest: foreign keys without an index, duplicate indexes, uncovered queries. The non-negotiable discipline from MOONKEY's CLAUDE.md: 'RLS/security changes: apply as a versioned migration + get_advisors afterward.' Every time you touch security, the advisor is the close of the loop — you don't assume it's fine, you verify it with the tool.

How to read an advisor and act. An advisor warning isn't noise to silence; it's a concrete vulnerability with a concrete remedy. 'RLS disabled on public.proofs' means anyone with the anon key reads the table — the remedy is enable row level security + policies, in a migration. 'Function public.is_admin has a role mutable search_path' means the function is hijackable — the remedy is `alter function ... set search_path = ''`, in a migration. The mature routine: you touch something → migration → get_advisors → if there's a warning, another migration that closes it → clean get_advisors. There's no 'I'll fix it later' for security advisors; later is after the breach.

Isolation in the shared DB, MOONKEY's specific risk. The database hosts MOONKEY tables (profiles, progress, feedback, leads, proofs) and XHUB IRON ones (iron_*, world_*, focus_*, daily_focus_history). Your migration discipline must respect that boundary: a MOONKEY migration must NEVER alter — not even through a careless DROP or an overly broad ALTER — a table from the other project. Before applying, read the migration's diff like an adversary: does it touch only the tables I said? The advisor and the SQL review are the two nets. In a shared DB, a poorly scoped change isn't your bug, it's another project's incident.

Auditing as a habit, not an event. Auditing isn't something you do before a launch; it's the default state of operating data seriously. list_migrations gives you the complete history of how the schema got to where it is. get_logs shows you what's failing in real time. get_advisors is the health check you run after each change and periodically even when you change nothing (because Supabase adds new checks and because the context changes). The operator who treats the database as a living system that audits itself continuously is the one who doesn't get the 3am call — because they saw the warning on the branch, a week earlier, with get_advisors.