Data Access Layer

skill: data-access-layer

purpose

Standardize all MongoDB database interactions through thin service wrappers per collection. No ORMs, no vendor lock-in, full control.

pattern

Every MongoDB collection gets a service file in lib/db/collections/[name].ts. Each service:

• validates input with Zod before writes
• uses a Pino child logger for operation logging
• returns typed objects (never raw MongoDB documents to consumers)
• handles errors consistently
• defines its indexes explicitly

template

// lib/db/collections/users.ts
import { z } from "zod";
import { ObjectId, type Collection, type Db } from "mongodb";
import { logger } from "@/lib/logger";

const log = logger.child({ service: "users" });

// Schema — single source of truth for validation
export const userSchema = z.object({
  name: z.string().min(1).max(200),
  email: z.string().email(),
  role: z.enum(["admin", "member", "viewer"]),
  organizationId: z.string(),
  createdAt: z.date().optional(),
  updatedAt: z.date().optional(),
});

export const userUpdateSchema = userSchema.partial().omit({ createdAt: true });

export type User = z.infer<typeof userSchema> & { _id: ObjectId };
export type CreateUserInput = z.infer<typeof userSchema>;
export type UpdateUserInput = z.infer<typeof userUpdateSchema>;

// Indexes — run once at startup via setup-indexes script
export const userIndexes = [
  { key: { email: 1 }, unique: true },
  { key: { organizationId: 1 } },
  { key: { role: 1, organizationId: 1 } },
];

export function createUsersService(db: Db) {
  const collection: Collection = db.collection("users");

  return {
    async findById(id: string): Promise<User | null> {
      log.debug({ id }, "findById");
      const doc = await collection.findOne({ _id: new ObjectId(id) });
      return doc as User | null;
    },

    async findByEmail(email: string): Promise<User | null> {
      log.debug({ email }, "findByEmail");
      const doc = await collection.findOne({ email });
      return doc as User | null;
    },

    async findByOrganization(
      organizationId: string,
      options?: { page?: number; limit?: number }
    ) {
      const page = options?.page ?? 1;
      const limit = options?.limit ?? 20;
      const skip = (page - 1) * limit;

      log.debug({ organizationId, page, limit }, "findByOrganization");

      const [docs, total] = await Promise.all([
        collection
          .find({ organizationId })
          .skip(skip)
          .limit(limit)
          .toArray(),
        collection.countDocuments({ organizationId }),
      ]);

      return {
        data: docs as User[],
        meta: {
          page,
          limit,
          total,
          totalPages: Math.ceil(total / limit),
        },
      };
    },

    async create(input: CreateUserInput): Promise<User> {
      const validated = userSchema.parse(input);
      const now = new Date();
      const doc = { ...validated, createdAt: now, updatedAt: now };

      log.info({ email: validated.email }, "creating user");
      const result = await collection.insertOne(doc);

      return { ...doc, _id: result.insertedId } as User;
    },

    async update(id: string, input: UpdateUserInput): Promise<User | null> {
      const validated = userUpdateSchema.parse(input);

      log.info({ id, fields: Object.keys(validated) }, "updating user");
      const result = await collection.findOneAndUpdate(
        { _id: new ObjectId(id) },
        { $set: { ...validated, updatedAt: new Date() } },
        { returnDocument: "after" }
      );

      return result as User | null;
    },

    async delete(id: string): Promise<boolean> {
      log.info({ id }, "deleting user");
      const result = await collection.deleteOne({ _id: new ObjectId(id) });
      return result.deletedCount === 1;
    },
  };
}