Database

lib/db/
├── index.ts              # Drizzle client
└── schema/
    ├── index.ts           # Barrel re-export
    ├── activity.ts        # activity_logs
    ├── affiliates.ts      # affiliates, referrals, affiliate_payouts
    ├── auth.ts            # auth_events
    ├── billing.ts         # subscriptions, purchases, invoices
    ├── blog.ts            # posts, blog_categories
    ├── legal.ts           # generated_legal_docs, user_legal_consents
    ├── marketing.ts       # contact_submissions, waitlist, newsletter_subscribers
    ├── notifications.ts   # notification_preferences
    ├── organizations.ts   # organizations, org_members, org_invitations
    ├── projects.ts        # projects
    ├── security.ts        # passkeys, webauthn_challenges
    ├── support.ts         # ai_conversations, ai_messages, knowledge_base, tickets, replies
    ├── users.ts           # profiles
    └── webhooks.ts        # webhook_logs

import { db } from "@/lib/db";
import { profiles, subscriptions } from "@/lib/db/schema";
import { eq, and, desc } from "drizzle-orm";

// Select
const user = await db
  .select({
    id: profiles.id,
    email: profiles.email,
    role: profiles.role,
  })
  .from(profiles)
  .where(eq(profiles.id, userId))
  .limit(1);

// Join
const userWithSub = await db
  .select({
    userId: profiles.id,
    email: profiles.email,
    tier: profiles.subscriptionTier,
    subscriptionStatus: subscriptions.status,
  })
  .from(profiles)
  .leftJoin(subscriptions, eq(profiles.id, subscriptions.userId))
  .where(eq(profiles.id, userId));

// Insert
const [newProject] = await db
  .insert(projects)
  .values({ name: "My Project", userId: user.id })
  .returning();

// Update with ownership gate
await db
  .update(projects)
  .set({ name: "Updated" })
  .where(and(eq(projects.id, projectId), eq(projects.userId, user.id)));

// Delete with ownership gate
await db
  .delete(projects)
  .where(and(eq(projects.id, projectId), eq(projects.userId, user.id)));

CREATE OR REPLACE FUNCTION public.deduct_credits(
  p_user_id UUID,
  p_amount INTEGER DEFAULT 1,
  p_action TEXT DEFAULT 'api_call'
) RETURNS JSONB
  LANGUAGE plpgsql
  SECURITY DEFINER
AS $$
DECLARE
  v_current_tier TEXT;
  v_credits_used INTEGER;
  v_credits_limit INTEGER;
  v_remaining INTEGER;
BEGIN
  IF p_amount <= 0 THEN
    RETURN jsonb_build_object('success', false, 'error', 'INVALID_AMOUNT');
  END IF;

  SELECT COALESCE(subscription_tier, 'free'), COALESCE(credits_used, 0)
    INTO v_current_tier, v_credits_used
    FROM profiles WHERE id = p_user_id FOR UPDATE;

  -- Dynamic tier-to-credits lookup from tier_credits table.
  SELECT COALESCE(tc.credits_limit, 100)
  INTO v_credits_limit
  FROM (SELECT v_current_tier AS tid) AS q
  LEFT JOIN public.tier_credits tc ON tc.tier_id = q.tid;

  -- Handle unlimited credits sentinel (-1).
  IF v_credits_limit = -1 THEN
    RETURN jsonb_build_object('success', true, 'credits_limit', -1);
  END IF;

  v_remaining := v_credits_limit - v_credits_used;

  IF v_remaining < p_amount THEN
    RETURN jsonb_build_object('success', false, 'error', 'INSUFFICIENT_CREDITS');
  END IF;

  UPDATE profiles
     SET credits_used = credits_used + p_amount
   WHERE id = p_user_id;

  INSERT INTO activity_logs (user_id, action, entity_type, entity_id, metadata)
  VALUES (
    p_user_id, 'credits_deducted', 'profile', p_user_id,
    jsonb_build_object('cost', p_amount, 'tier_limit', v_credits_limit)
  );

  RETURN jsonb_build_object('success', true, 'credits_remaining', v_remaining - p_amount);
END;
$$;

### Serializable Application Logic

While SQL functions like `deduct_credits` handle atomicity for single records, multi-table flows (like **Affiliate Payouts**) use Drizzle's `db.transaction(..., { isolationLevel: 'serializable' })`. This guarantees that the read snapshot used for payout calculation remains consistent throughout the update and insertion phase, even under high concurrency from external webhooks.


-- Restrict to service_role only
REVOKE ALL ON FUNCTION public.deduct_credits(UUID, INTEGER) FROM PUBLIC;
REVOKE ALL ON FUNCTION public.deduct_credits(UUID, INTEGER) FROM anon;
REVOKE ALL ON FUNCTION public.deduct_credits(UUID, INTEGER) FROM authenticated;
GRANT EXECUTE ON FUNCTION public.deduct_credits(UUID, INTEGER) TO service_role;

CREATE OR REPLACE FUNCTION search_knowledge_base(
  query_embedding vector(3072),
  match_threshold float DEFAULT 0.7,
  match_count int DEFAULT 5
)
RETURNS TABLE (id UUID, title TEXT, content TEXT, similarity float) AS $$
BEGIN
  RETURN QUERY
  SELECT kb.id, kb.title, kb.content,
    1 - (kb.embedding <=> query_embedding) AS similarity
  FROM knowledge_base kb
  WHERE 1 - (kb.embedding <=> query_embedding) > match_threshold
  ORDER BY kb.embedding <=> query_embedding
  LIMIT match_count;
END;
$$ LANGUAGE plpgsql;

CREATE INDEX idx_api_usage_logs_key_created ON api_usage_logs(api_key_id, created_at);
CREATE INDEX idx_referrals_affiliate_created ON referrals(affiliate_id, created_at);
CREATE INDEX idx_org_members_org_user ON org_members(org_id, user_id);

CREATE INDEX idx_subscriptions_active ON subscriptions(user_id)
  WHERE status IN ('active', 'trialing');

CREATE INDEX idx_posts_published ON posts(published_at)
  WHERE published_at IS NOT NULL;

CREATE INDEX idx_org_invitations_pending ON org_invitations(email)
  WHERE accepted_at IS NULL AND expires_at > NOW();

CREATE UNIQUE INDEX webhook_logs_provider_event_id_unique
  ON webhook_logs(provider, event_id)
  WHERE event_id IS NOT NULL;

CREATE UNIQUE INDEX subscriptions_provider_subscription_id_unique
  ON subscriptions(provider, provider_subscription_id)
  WHERE provider_subscription_id IS NOT NULL;

CREATE UNIQUE INDEX purchases_provider_payment_id_unique
  ON purchases(provider, provider_payment_id)
  WHERE provider_payment_id IS NOT NULL;

CREATE INDEX idx_profiles_role ON profiles(id, role);

CREATE INDEX idx_knowledge_base_embedding ON knowledge_base
  USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own projects"
  ON projects FOR SELECT USING ((select auth.uid()) = user_id);

CREATE POLICY "Users can create projects"
  ON projects FOR INSERT WITH CHECK ((select auth.uid()) = user_id);

CREATE POLICY "Users can update own projects"
  ON projects FOR UPDATE USING ((select auth.uid()) = user_id);

CREATE POLICY "Admins can view all projects"
  ON projects FOR SELECT USING (
    EXISTS (SELECT 1 FROM profiles WHERE id = (select auth.uid()) AND role = 'super-admin')
  );

npm run db:push      # Push to Supabase (dev)
npm run db:generate  # Generate SQL migrations (production)
npm run db:studio    # Drizzle Studio (localhost:4983)