[CHORE] Auto-generate Supabase types on build

[github-actions]

Problem Statement

Currently, developers manually write TypeScript interfaces for database rows, leading to out-of-sync types when the schema changes.

Proposed Solution

Integrate the Supabase CLI into the project. Add a generate-types script to package.json that fetches the schema from the local/remote project and outputs database.types.ts. Update the codebase to use these strict types.

Alternatives Considered

Using Prisma, but we want to stick to the native Supabase client for edge compatibility.

Additional Context

Requires setting up a local Supabase environment in Docker or defining a clear workflow in CONTRIBUTING.md.

[SakethSumanBathini]

Hey @PRODHOSH can you assign me this issue please??

[SakethSumanBathini]

Hi @PRODHOSH — a question before I start, because there are two ways to do this and they have
different failure modes.

The usual route is supabase gen types typescript --project-id <id>, which needs the project ID and
an access token. I don't have either, so I can't run it against the live database.

But supabase/schema.sql is in the repo, so I can apply it to a local Postgres and generate the
types from that instead — no credentials needed, and it's reproducible in CI.

The catch, and the reason I'm asking rather than just doing it: that's only safe if schema.sql is
actually in sync with the live database. It wasn't, until very recently — the visibility CHECK
constraint in schema.sql had drifted from the migrations, which I only found while working on #407
(fixed in that PR). If there's any other drift I haven't spotted, generated types would be
confidently wrong, which is worse than the anys they'd replace — the compiler would start
vouching for a shape the database doesn't have.

So:

1. If you're happy that schema.sql is authoritative, I'll generate from it, commit
   src/types/supabase.ts, and replace the anys (profileRow, badges in [username]/page.tsx
   are the obvious ones). I can also add a CI step that regenerates and diffs, so drift can't creep
   back in silently.
2. If you'd rather generate from the live project, that needs to be run by someone with the
   credentials — happy to do the "replace the anys" half once the types file exists.

Which would you prefer?

[PRODHOSH]

thanks for checking first lets use schema sql as the source for now go ahead with that approach and add the ci check as well if we find any schema drift later well update it accordingly @SakethSumanBathini

[SakethSumanBathini]

@PRODHOSH — found drift immediately, and it's bigger than the visibility constraint from #407.

schema.sql is missing two tables that migrations create and the app actively queries:

• organizations — added by 20260707000001_add_organizations.sql, queried at explore/page.tsx:58
• profile_snapshots — added by 20260712000000_add_profile_snapshots.sql. This is the table the
  whole DB-first snapshot path reads from: getProfileSnapshot(), the profile page render, the new
  RSS feed. Without it, every profile sits in the "cold profile / syncing" state forever.

So a fresh setup run from schema.sql gets a database that can't serve /explore and can't render a
profile. That's worth fixing regardless of this issue.

It also blocks the approach we agreed. I applied schema.sql to a local Postgres and generated types
from it — and as soon as I typed the Supabase client, TypeScript started rejecting valid queries:

explore/page.tsx(58,15): Argument of type '"organizations"' is not assignable
                         to parameter of type '"profiles" | "profile_score_snapshots"'

That's the generated types correctly reporting that the schema they came from is incomplete. Exactly
the failure mode I was worried about — types that confidently vouch for a shape the database doesn't
have are worse than the anys they replace.

Two ways forward:

1. Fix schema.sql first — port the two missing tables across from the migrations so it's genuinely
   authoritative, then generate types from it as agreed. I'd do the schema fix as its own PR, since
   it's a real bug that stands on its own, and it makes the types PR clean.

2. Generate from the migrations instead — the CI check would run supabase db start (which applies
   migrations) and generate from that. Migrations are the actual source of truth today. The drift check
   would then also surface any future schema.sql divergence, which is a bonus.

I lean towards 1 then 2: fix schema.sql, then generate from migrations in CI so the two can never
silently diverge again. But it's your call on scope — happy to do either.

One more thing worth knowing: schema.sql also can't be applied to a stock Postgres at all.
array_to_string(top_languages, ' ') in the search_text generated column is STABLE, not IMMUTABLE,
and Postgres rejects non-immutable expressions in generated columns. Supabase's image tolerates it;
a plain services: postgres CI job wouldn't. So whichever route we take, the CI check needs to run
against Supabase's own Postgres (supabase db start) rather than a bare container.