Database Layer API

All data access in DirectoryLaunch goes through db — a singleton exported from lib/supabase/database.ts. It wraps Supabase with a MongoDB-inspired API, so queries look familiar if you've used Mongo before.

import { db } from '@/lib/supabase/database';

Since 2.1.0 the shape of that singleton is stated as an interface — DatabaseAdapter in types/database-adapter.ts — and config/database.config.ts selects the implementation. It is the seam for replacing Supabase: write an adapter, register it, and set the provider. An unregistered value makes the app refuse to start rather than fall back silently.

Under the hood it uses the service role client — RLS is bypassed. Only call it from server code (API routes, server components, scripts). Never import into client components.

Methods

db.find(table, query, options) — multiple rows

const apps = await db.find('apps', { status: 'live' }, {
  sort: { upvotes: -1, created_at: -1 },
  skip: 0,
  limit: 20,
  projection: { slug: 1, name: 1, upvotes: 1 },
});

db.findOne(table, query, options) — single row

const app = await db.findOne('apps', { slug: 'my-app' });
if (!app) return notFound();

db.insertOne(table, doc) — insert one

const { insertedId } = await db.insertOne('apps', {
  name: 'My App',
  slug: 'my-app',
  status: 'pending',
  user_id: user.id,
});

db.insertMany(table, docs)

const { insertedIds, insertedCount } = await db.insertMany('categories', seedCategories);

db.updateOne(table, filter, update) — update one

Use $set, $inc and/or $unset. The filter must select at most one row — asserted in development.

await db.updateOne('apps', { id }, {
  $set: { status: 'live', approved_at: new Date().toISOString() },
});
 
await db.updateOne('apps', { id }, {
  $inc: { views: 1 },
});
 
await db.updateOne('apps', { id }, {
  $unset: { pending_plan: '' },   // sets the column to NULL
});

db.updateMany(table, filter, update)

await db.updateMany('apps',
  { status: 'pending', created_at: { $lt: cutoff } },
  { $set: { status: 'expired' } }
);

db.deleteOne(table, filter) / db.deleteMany(table, filter)

deleteOne deletes one row; deleteMany deletes every match. Before 2.1.0 deleteOne deleted every matching row and deleteMany was an alias for it — every call site filters on a unique key, so nothing was over-deleted, but the names now mean what they say.

await db.deleteOne('comments', { id: commentId });
await db.deleteMany('analytics', { created_at: { $lt: aMonthAgo } });

db.upsert(table, conflictKeys, doc) — insert, or update the colliding row

await db.upsert('site_settings', ['key'], { key: 'theme', value: theme });

db.count(table, query) — fast row count

const total = await db.count('apps', { status: 'live' });

db.rpc(fn, params) — call a PostgreSQL function

Use this when you need transactional or concurrency-safe operations:

await db.rpc('increment_upvotes', { app_id: id });

db.incrementField(table, id, field, delta) — quick increment

Read-modify-write. Not atomic — for hot counters, use rpc with a DB function.

await db.incrementField('apps', id, 'upvotes', 1);

Values are clamped to >= 0.

Query operators

JSONB gotcha

Columns like categories and tags are JSONB arrays. On these columns, $in internally becomes .overlaps() — it matches if any value in the array overlaps, not strict equality. For scalar columns $in means strict "value is one of."

Update operators

Common recipes

"Get top 10 live apps"

const top = await db.find('apps',
  { status: 'live' },
  { sort: { upvotes: -1 }, limit: 10 }
);

"Get this user's projects"

const mine = await db.find('apps',
  { user_id: user.id },
  { sort: { created_at: -1 } }
);

"Projects in a category"

const inCategory = await db.find('apps',
  { status: 'live', categories: { $overlaps: [categorySlug] } },
  { sort: { upvotes: -1 }, limit: 40 }
);

"Paginate"

const page = 3;
const pageSize = 40;
 
const items = await db.find('apps',
  { status: 'live' },
  { sort: { created_at: -1 }, skip: (page - 1) * pageSize, limit: pageSize }
);
 
const total = await db.count('apps', { status: 'live' });

Tables available via db

• apps
• users
• categories
• competitions
• payments
• newsletter
• sidebar_content
• backlinks
• analytics
• external_webhooks
• partners
• bookmarks
• ratings
• comments
• promotions

site_settings is reached through lib/site-settings.ts — getSetting(), getSettings(), setSetting() — which uses db.upsert() underneath. Eleven call sites used to reach past the data layer to read or write it; none do now.

When NOT to use db

• From client components — db uses the service role and must not reach the browser. Use a server API route instead.
• High-contention counters — $inc isn't atomic. Use rpc() with a PostgreSQL function.
• Complex joins — do it with direct Supabase .from().select('...') for full SQL power.

Who is calling?

Routes resolve the caller through lib/auth/session.ts rather than building their own client:

import { currentUser, requireUser } from '@/lib/auth/session';
 
const user = await currentUser();   // the caller, or null when signed out
 
// In a route — mirrors featureGuard's shape:
const { user, response } = await requireUser();
if (response) return response;      // a ready-made 401

Twenty-one routes each built a Supabase client to answer that question before 2.1.0, and nine admin routes each carried their own copy of the admin guard. Both are one helper now.

See also

• Schema Overview — what tables exist
• API Reference Overview — how API routes use db