Backend Patterns

Database helpers, tRPC router procedures, IP extraction utilities, and role-based middleware patterns for the broker management system.

Database Helpers

Add these functions to server/db.ts. They provide CRUD operations and counter increments for broker landing pages.

server/db.ts — Broker Landing Pages CRUD

// ─── Broker Landing Pages ───────────────────────────────────────
export async function getBrokerLandingPageBySlug(slug: string) {
  const rows = await db.select().from(brokerLandingPages)
    .where(eq(brokerLandingPages.slug, slug)).limit(1);
  return rows[0] ?? null;
}

export async function getBrokerLandingPageByBrokerId(brokerId: number) {
  const rows = await db.select().from(brokerLandingPages)
    .where(eq(brokerLandingPages.brokerId, brokerId)).limit(1);
  return rows[0] ?? null;
}

export async function getAllBrokerLandingPages() {
  return db.select().from(brokerLandingPages)
    .orderBy(brokerLandingPages.createdAt);
}

export async function createBrokerLandingPage(data: InsertBrokerLandingPage) {
  const [result] = await db.insert(brokerLandingPages).values(data);
  return result.insertId;
}

export async function updateBrokerLandingPage(
  id: number,
  data: Partial<InsertBrokerLandingPage>
) {
  await db.update(brokerLandingPages)
    .set(data)
    .where(eq(brokerLandingPages.id, id));
}

export async function deleteBrokerLandingPage(id: number) {
  await db.delete(brokerLandingPages)
    .where(eq(brokerLandingPages.id, id));
}

export async function incrementLandingPageViews(id: number) {
  await db.update(brokerLandingPages)
    .set({ viewCount: sql`${brokerLandingPages.viewCount} + 1` })
    .where(eq(brokerLandingPages.id, id));
}

export async function incrementLandingPageApplications(id: number) {
  await db.update(brokerLandingPages)
    .set({ applicationCount: sql`${brokerLandingPages.applicationCount} + 1` })
    .where(eq(brokerLandingPages.id, id));
}

IP-Aware Task Helpers

These helpers extend existing task operations to record the client's IP address alongside each action.

server/db.ts — IP-Aware Task Functions

export async function createTaskWithIp(
  data: InsertTask & { createdByIp?: string }
) {
  const [result] = await db.insert(applicationTasks).values(data);
  return result.insertId;
}

export async function updateTaskWithIp(
  id: number,
  data: Partial<InsertTask> & { lastUpdatedByIp?: string }
) {
  await db.update(applicationTasks)
    .set(data)
    .where(eq(applicationTasks.id, id));
}

export async function completeTaskWithIp(id: number, ip?: string) {
  await db.update(applicationTasks).set({
    isCompleted: true,
    completedAt: new Date(),
    completedByIp: ip,
  }).where(eq(applicationTasks.id, id));
}

IP Extraction Helper

Place this utility at the top of your router file. It extracts the client's real IP from proxy headers, falling back to the direct connection address.

server/routers.ts — IP Extraction

function getClientIp(req: any): string | undefined {
  const forwarded = req.headers?.["x-forwarded-for"];
  if (typeof forwarded === "string")
    return forwarded.split(",")[0].trim();
  return req.ip || req.connection?.remoteAddress;
}

Broker Landing Router

The complete tRPC router with public, admin, and broker self-service procedures. Register it in the main appRouter.

Procedure	Auth	Purpose
getBySlug	public	Fetch page by slug, increment views, return null if inactive
listAll	admin	List all landing pages
create	admin	Create page, reject duplicate slugs
adminUpdate	admin	Update any page fields
delete	admin	Delete a page
getMyPage	protected	Broker gets own page by userId
updateMyPage	protected	Broker edits own page (not slug/active)

server/routers.ts — Broker Landing Router

const brokerLandingRouter = t.router({
  // Public: get page by slug (increments view count)
  getBySlug: publicProcedure
    .input(z.object({ slug: z.string() }))
    .query(async ({ input }) => {
      const page = await getBrokerLandingPageBySlug(input.slug);
      if (!page || !page.isActive) return null;
      incrementLandingPageViews(page.id); // fire-and-forget
      return page;
    }),

  // Admin: list all, create, update, delete
  listAll: adminProcedure.query(() => getAllBrokerLandingPages()),

  create: adminProcedure
    .input(z.object({
      brokerId: z.number(),
      slug: z.string().min(2).max(128),
      displayName: z.string().optional(),
      displayEmail: z.string().email().optional(),
      // ... other optional fields
    }))
    .mutation(async ({ input }) => {
      const existing = await getBrokerLandingPageBySlug(input.slug);
      if (existing)
        throw new TRPCError({ code: "CONFLICT", message: "Slug already in use" });
      const id = await createBrokerLandingPage(input);
      return { id };
    }),

  adminUpdate: adminProcedure
    .input(z.object({ id: z.number(), /* partial fields */ }))
    .mutation(async ({ input }) => {
      const { id, ...data } = input;
      await updateBrokerLandingPage(id, data);
      return { success: true };
    }),

  delete: adminProcedure
    .input(z.object({ id: z.number() }))
    .mutation(async ({ input }) => {
      await deleteBrokerLandingPage(input.id);
      return { success: true };
    }),

  // Broker self-service
  getMyPage: protectedProcedure.query(async ({ ctx }) => {
    return getBrokerLandingPageByBrokerId(ctx.user.id);
  }),

  updateMyPage: protectedProcedure
    .input(z.object({
      headline: z.string().optional(),
      bio: z.string().optional(),
      // ... other editable fields
    }))
    .mutation(async ({ ctx, input }) => {
      const page = await getBrokerLandingPageByBrokerId(ctx.user.id);
      if (!page)
        throw new TRPCError({ code: "NOT_FOUND", message: "No landing page found" });
      await updateBrokerLandingPage(page.id, input);
      return { success: true };
    }),
});

Role-Based Middleware

server/routers.ts — Admin Middleware

const adminProcedure = protectedProcedure.use(({ ctx, next }) => {
  if (ctx.user.role !== "admin")
    throw new TRPCError({ code: "FORBIDDEN" });
  return next({ ctx });
});

Registering in Main Router

server/routers.ts — App Router

export const appRouter = t.router({
  // ... existing routers
  brokerLanding: brokerLandingRouter,
});