Best practices & production guide

1. Model for table-CRUD

The runtime data API is one table at a time — no JOINs, no upserts, and at most 200 rows per page. If you design a normalized schema and then try to stitch it back together with client-side fan-out, every screen costs a round-trip per relation. Model for your read paths instead.

• Denormalize where it saves a round-trip — copy the handful of fields a list view needs onto the row it renders, rather than fetching the parent separately.
• Create a VIEW in a migration for anything genuinely relational, then select() from it exactly like a table. The JOIN runs in Postgres; the client sees one flat result.
• Use stable surrogate keys — bigint generated always as identity or uuid — never a mutable natural key.
• Stamp a user_id column on every user-owned row so RLS has something to key off.

-- a read-path view: join once in the DB, select() it like a table
create view order_lines_expanded as
select l.id, l.order_id, l.qty, p.name as product_name, p.price
from   order_lines l
join   products p on p.id = l.product_id;

See the Database guide for the full query-builder surface and the no-JOIN/upsert rule.

2. Paginate & count without scanning

select() returns at most 200 rows. Reach for pagination early — it's not an optimization, it's the ceiling. Order by an indexed column and page with .limit() and .range().

const { data } = await lingcode
  .from('posts')
  .order('created_at', { ascending: false })  // indexed column
  .range(0, 49)                               // first 50
  .select();

Counting is the trap. Don't count by fetching every page and summing lengths — that's an O(n) scan over the whole table on every view. Instead:

• Keep a counter column you maintain as rows are written, or
• Expose totals and aggregates through a VIEW computed in the database and select() the single aggregate row.

More on filters, ordering, and ranges in the Database guide.

3. Index what you filter and order by

An unindexed filter or sort scans the table. That's invisible at 50 rows and fatal at 50,000. In a migration, add an index for every column you filter or order by at scale, and for every foreign-key column.

create index if not exists idx_posts_author    on posts (author_id);
create index if not exists idx_posts_created_at on posts (created_at desc);

pgvector columns need a vector index once the table grows — see the Vector search guide. And you don't have to find these by hand: the advisors scanner flags missing indexes and unindexed foreign keys for you. Run it before you ship — see Scan your backend with advisors. Migrations basics are in the Database guide.

4. Security checklist

The anon key ships in your browser bundle, so the server is the only place security actually happens. Treat the database as hostile-by-default and let RLS gate every read and write.

• Enable RLS on every table the anon key can reach, and write a per-command policy (select / insert / update / delete) — RLS-on with no policy denies everything; RLS-off exposes everything.
• The anon key is public by design — never treat it as a secret or rely on its obscurity.
• Keep private vendor keys in the Secrets vault and read them only from functions, which run server-side. See the Secrets guide.
• Run the advisors before every ship to catch tables left open.
• Test policies as a signed-in app user, not from the console — the console bypasses RLS, so a query that works there can still be blocked (or wide open) for real users.

The full policy model is in the Security guide.

5. Server logic & secrets

If a call needs a private key, it can't run in the browser — the key would be visible to anyone who opens dev tools. Vendor API calls and any key-bearing logic belong in functions that read ctx.secrets:

// function — the secret never reaches the client
export default async (req, ctx) => {
  const key = ctx.secrets.STRIPE_SECRET_KEY;
  // ...call the vendor server-side, return only what's safe...
};

Functions are short-lived, though. Two cases belong in a hosted Worker route instead of a function:

• A raw-body webhook (a Stripe webhook, for example) that needs the unparsed request body for signature verification.
• Request-time SSR — rendering on every request as part of a full-stack app.

See the Functions guide and the Hosting guide.

6. Quotas & cost

Every tier has caps, and hitting one in production is a worse surprise than planning for it. Know yours before launch:

• Number of tables, objects, and total storage.
• Function count and function wall-clock — 3s / 10s / 30s depending on tier.
• Emails per day.

The backend flags storage at 80% and 95% — watch for those warnings rather than waiting for a hard stop. And because functions are short-lived (≤30s), anything longer must be offloaded — split the work, queue it, or move it to a hosted Worker. Tier caps are listed on the pricing page.

7. Migrations discipline

Schema drift between environments is the quiet killer of "works on my backend." Make migrations the only way schema changes — never click-edit structure ad hoc.

• Run all DDL through the console's SQL tab or apply_migration.
• Write idempotent DDL — create table if not exists, create index if not exists — so re-applying is safe.
• Keep indexes, constraints, views, and RLS policies in migrations alongside the tables they belong to, not as one-off console edits.

create table if not exists invites (
  id         uuid primary key default gen_random_uuid(),
  email      text not null,
  created_at timestamptz not null default now()
);
create index if not exists idx_invites_email on invites (email);

Migration mechanics live in the Database guide.

8. Performance

Three habits keep an app responsive without extra infrastructure:

• Subscribe, don't poll. Use realtime subscribe() to get row changes pushed over SSE instead of re-fetching on a timer — fewer requests, lower latency. See the Realtime guide.
• Embed vectors at write time so search is just a query, not an embed-then-search round-trip. See the Vector search guide.
• Batch telemetry and let it auto-flush rather than sending an event per interaction. See the Telemetry guide.