0 Fork 0

Files

T

patrickandClaude Sonnet 5 4a19335a1e feat: rework starter kit for self-hosted deploy (Vite/React + Fastify/Drizzle/Postgres)

Replace Next.js/Supabase/Vercel stack with a self-hostable setup: Vite+React
frontend, Fastify+Drizzle+PostgreSQL backend with own JWT auth, deploy via
git pull + rsync + systemd (checkout/service separated, updates pulled from
Gitea). Adds a reference auth route (register/login/me) as the canonical
pattern for new routes. Updates all skills/rules/agents/docs to match.

Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01BMdBJfD9TT6XUU2NqGG1PN

2026-09-08 11:42:47 +02:00

2.7 KiB

Raw Permalink Blame History

Database Optimization

1. Indexing

Create indexes on columns used in WHERE, ORDER BY, or JOIN clauses:

// backend/src/db/schema.ts
export const tasks = pgTable("tasks", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: uuid("user_id").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
}, (table) => ({
  userCreatedIdx: index("idx_tasks_user_id_created").on(table.userId, table.createdAt.desc()),
}));

Rule of thumb: If a column appears in WHERE or ORDER BY and the table will have >1000 rows, add an index.

Always include indexes in the schema alongside the table definition, so they ship with the same migration.

2. Avoid N+1 Queries

The most common performance problem with ORMs and query builders:

// Bad: N+1 (1 query for users + N queries for tasks)
const allUsers = await db.select().from(users);
for (const user of allUsers) {
  const userTasks = await db.select().from(tasks).where(eq(tasks.userId, user.id));
}

// Good: single query with a join
const rows = await db
  .select()
  .from(users)
  .leftJoin(tasks, eq(tasks.userId, users.id));

// Or with Drizzle's relational query API (if relations are defined)
const usersWithTasks = await db.query.users.findMany({
  with: { tasks: true },
});

3. Always Limit Results

Never return unbounded results from the database:

// Bad: returns ALL rows
const rows = await db.select().from(tasks);

// Good: capped
const rows = await db.select().from(tasks).limit(50);

// Better: paginated
const rows = await db.select().from(tasks).limit(50).offset(page * 50);

4. Caching Strategy

For data that changes rarely (dashboard stats, config, categories), cache in the backend process (or a small in-memory LRU) rather than hitting Postgres on every request:

let cachedCategories: Category[] | null = null;
let cachedAt = 0;

export async function getCategories() {
  if (cachedCategories && Date.now() - cachedAt < 3600_000) {
    return cachedCategories;
  }
  cachedCategories = await db.select().from(categories);
  cachedAt = Date.now();
  return cachedCategories;
}

When to cache:

Data that changes less than once per hour
Expensive aggregation queries
Data shared across all users (not user-specific)

When NOT to cache:

User-specific data that changes frequently
Data that needs to be live across multiple backend instances (use a shared cache like Redis instead of in-process memory)

5. Select Only What You Need

// Bad: fetches all columns
const rows = await db.select().from(users);

// Good: fetches only needed columns
const rows = await db.select({ id: users.id, email: users.email }).from(users);

2.7 KiB Raw Permalink Blame History