Database

Zeno uses Supabase Postgres for the database and Drizzle for type-safe schema definitions, migrations, and server-side queries. The @zeno-lib/db package ties those pieces together so an app can define tables in TypeScript, generate SQL migrations, and run queries with Supabase Row Level Security (RLS) applied correctly.

To read this guide, you do not need to have used Drizzle, RLS, or @zeno-lib/db before.

Concepts

Supabase Postgres

Every Supabase project includes a Postgres database. You can query it directly from trusted server code, and Supabase can also expose it through APIs such as PostgREST and Supabase client libraries.

The important security rule is: user-scoped tables need Row Level Security. RLS is a Postgres feature that attaches policies to tables. You can think of a policy as database-level authorization logic that Postgres applies whenever a role tries to read, insert, update, or delete rows.

Postgres requests run as database roles. The roles you meet most often are:

• anon: an unauthenticated request. Use this for data that can be read or written without a signed-in user, if your app allows that.
• authenticated: a request with a valid signed-in Supabase user. Use this for data that depends on who the user is.
• service_role: a trusted role that bypasses RLS, for code paths where you intentionally do not want user-scoped policies to apply.
• postgres: the role the database client connects as by default. It also bypasses RLS.

Zeno connects to Postgres directly rather than through Supabase's API, so it maps onto these roles itself: RLS-scoped queries run as anon or authenticated, trusted admin queries run as the postgres connection role, and a dedicated client runs as service_role for trusted work that should bypass RLS through Supabase's BYPASSRLS grant. A user token can only ever resolve to anon or authenticated; service_role is reachable only through that explicit server-side client, never from a JWT.

Inside RLS policies, Supabase helpers expose information about the current request:

• auth.uid() returns the current user's id.
• auth.jwt() returns the current user's JWT claims. Use trusted app metadata for authorization data, not user-editable metadata.

Drizzle

Drizzle schema files let you declare Postgres tables in TypeScript. Those declarations become the types you use in application queries.

Drizzle Kit migrations read those schema files, generate SQL migration files, and apply them to the database. In Zeno, the normal workflow is codebase-first: update the TypeScript schema, generate migrations, review the generated SQL, and apply it.

Drizzle also supports RLS policies, including Supabase's built-in roles and helpers.

Why Drizzle for data queries?

Supabase also ships a JavaScript client whose data API (supabase.from(...).select(...)) reaches Postgres through PostgREST. It is convenient to start with, but for application data Zeno queries Postgres directly with Drizzle. Here is the reasoning.

• SQL, not a query meta-language. The Supabase data API builds queries through PostgREST's filter and resource-embedding syntax, a meta-language layered over SQL. It reads nicely for simple lookups, but you soon meet its edges: joins beyond embedded resources, richer aggregations, conditional logic, and other advanced SQL features. Drizzle expresses all of these because it is SQL with TypeScript types.
• No forced workarounds. When the meta-language cannot express a query you either split it into several round-trips or push it into an RPC function. Both tend to produce slower queries than the single statement you would have written in SQL, so the limitation quietly costs performance.
• Relational writes, not just reads. PostgREST can embed related rows when reading, but it has no relational insert or update, so nested writes turn into several calls you sequence yourself. Drizzle gives you the full range of writes, and real transactions to group them.
• Fewer layers between app and database. The JavaScript client sends HTTP to PostgREST, which parses the request, plans it, and serializes rows to JSON. Drizzle uses a direct postgres-js connection to Postgres and skips the PostgREST and HTTP hop, which lowers latency and serialization overhead.
• Transactions on your terms. PostgREST runs each request as its own transaction, so you cannot group several calls into one atomic unit. Drizzle runs over a direct connection and lets you decide how to manage transactions, so related writes can commit or roll back together.
• One TypeScript schema, migrations handled for you. Tables are defined once in TypeScript; Drizzle Kit diffs that schema and generates SQL migrations, versioned in git. You are not changing schema in the dashboard and then regenerating client types to match.
• End-to-end types from the source of truth. Query result types are inferred from the same table objects that drive migrations, so a schema change surfaces as a type error at every call site, with no separate generated-types file to regenerate and keep in sync.
• No runaway type inference. The Supabase client derives result types from a generated types file, and on larger schemas or nested embeds TypeScript can give up with Type instantiation is excessively deep and possibly infinite (supabase-js #808). Drizzle infers types straight from your table objects, so it does not hit that wall.
• Seeding in TypeScript too. Seed scripts run through the same Drizzle schema and client as the rest of the app, so you write seed data in TypeScript with full types instead of maintaining separate SQL or dashboard steps. That is more flexible and stays in sync with the schema as it changes.
• Less locked in to Supabase. Zeno relies on Supabase for authentication, Realtime, and Storage, but keeping data access in Drizzle and standard Postgres means the data layer itself is not tied to Supabase. That keeps dependency on any single vendor smaller and leaves room to move or change the database without rewriting every query.
• No drift from Postgres. Drizzle stays close to standard SQL, so your queries and your team's knowledge stay portable rather than tied to a Supabase-specific query dialect.

Installation

Install the database and Supabase packages in the package or app that owns your schema:

pnpm add @zeno-lib/db @zeno-lib/supabase drizzle-orm@1.0.0-rc.3 postgres
pnpm add -D drizzle-kit@1.0.0-rc.3 dotenv

Package scripts

Add these scripts to the same package.json. They drive local Supabase, migrations, and seeding:

{
  "scripts": {
    "dev": "pnpm exec supabase start",
    "stop": "pnpm exec supabase stop",
    "reset": "pnpm exec supabase db reset && pnpm db:seed",
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:seed": "node src/seed.ts"
  }
}

What each script does:

• dev starts your local Supabase stack, including local Postgres.
• stop stops the local Supabase stack.
• reset rebuilds the local database from migration files, then runs your seed script.
• db:generate reads your TypeScript schema and writes a SQL migration under supabase/migrations.
• db:migrate applies generated migrations to a database and records them in Drizzle Kit's migration table.
• db:seed runs your project-owned seed script.

Environment

Both the runtime clients and the Drizzle Kit preset read SUPABASE_DATABASE_URL from the environment unless you pass an explicit connectionString override.

For local Supabase:

SUPABASE_DATABASE_URL=postgresql://postgres:postgres@127.0.0.1:54322/postgres

Anatomy of the connection URL

SUPABASE_DATABASE_URL is a standard PostgreSQL connection URI:

postgresql://[user[:password]@][host][:port][/dbname][?param=value&...]

Find a deployed project's string in the Supabase dashboard via the Connect button. The direct connection uses port 5432; the transaction pooler uses 6543.

For deployed Supabase projects, prefer the Supabase connection pooler. The runtime client sets prepare: false so the transaction-mode pooler works; see Querying for details.

Next steps

Work through the package in the order you would build with it:

• Tables: declare RLS-ready Postgres tables in TypeScript with Zeno's Drizzle helpers.
• Migrations: configure Drizzle Kit, then generate, apply, and seed SQL migrations.
• Querying: create clients and run RLS-scoped, admin, and lower-level queries.