Service Layer Refactoring Plan

Status: COMPLETE — All 10 original steps + extended scope done (2026-04-30)

Goal

Three targeted improvements to the API data layer, in priority order:

1. Service function extraction — pull business logic out of fat route handlers into apps/api/src/services/
2. Soft-delete query helpers — eliminate manual deletedAt: null / isActive: true filtering scattered across routes
3. Transaction wrapping — ensure multi-step writes succeed or fail atomically

Architecture Decision: Where Services Live

apps/api/src/services/ already exists with 4 service files:

• lead.service.ts
• connected-channel.service.ts
• zoho-books-sync.ts
• baseline-sync.service.ts

We extend this directory. No new shared package is needed because all three refactoring targets (blog approval, social approval, campaign import) are HTTP-triggered. Workers that create content (blog-writer, social-post-writer) use agent prompt logic — they do not share approval business logic with routes.

Workers continue to export pure functions with injected db as they already do.

Phase 1 — Soft-Delete Query Helpers

Context

Three models use deletedAt DateTime? for soft deletion; nine use isActive Boolean for soft filtering. In both cases, developers must manually append the filter to every query — which is error-prone and already showing inconsistency.

Models with deletedAt (schema lines 1741, 1785, 1843):

• RagDataset, RagFile, TenantDoc

Models with isActive (9 models):

• AgentConfig, DeliverableTemplate, RepurposingTemplate, Offering, Region, Plan, ChannelMaster, Competitor, AIVisibilityPrompt, BacklinkDirectory

Files to Create

packages/db/src/helpers/soft-delete.ts

Files to Modify

Helper Pattern

// packages/db/src/helpers/soft-delete.ts
 
/** Use in where clauses for RagDataset, RagFile, TenantDoc */
export function notDeleted() {
  return { deletedAt: null } as const;
}
 
/** Use in where clauses for AgentConfig, Plan, Offering, Region, etc. */
export function activeOnly() {
  return { isActive: true } as const;
}
 
// Usage in route handlers:
const dataset = await db.ragDataset.findFirst({
  where: { tenantId, name: "published_content", ...notDeleted() },
});
 
const plan = await db.plan.findFirst({
  where: { id: planId, ...activeOnly() },
});

Status: [ ] TODO

Phase 2 — Service Function Extraction

Context

Three route files contain mixed HTTP transport + business logic. Identified by fat handler size and logic that would need to be called from multiple places:

Files to Create

apps/api/src/services/blog.service.ts
apps/api/src/services/social.service.ts
apps/api/src/services/campaign.service.ts

blog.service.ts

approveBlogPost(db, blogPostId, tenantId, approvedByUserId)

Extracted from: blog.ts:241–319

DB writes (will be wrapped in transaction in Phase 3):

1. db.blogPost.update — status → client_approved, set clientApprovedBy, clientApprovedAt, clear rejectionNote
2. db.activity.update — status → done (conditional on activityId present)
3. db.deliverable.updateMany — status → approved
4. db.ragDataset.findFirst — find published_content dataset (uses notDeleted() helper)
5. db.ragFile.create — create KB entry if dataset found

Returns: { post, ragFileCreated: boolean }

Side effects (caller’s responsibility, outside transaction):

• enqueueRagContent(...) if ragFile created
• writeAuditLog(...)
• enqueueSearchSync(...)
• pushToWordPress(...) (existing helper in blog.ts, keep in place)

rejectBlogPost(db, blogPostId, tenantId, rejectedByUserId, rejectionNote)

Extracted from: blog.ts rejection flow

DB writes:

1. db.blogPost.update — status → dm_review, set rejectionNote, clear approval fields
2. db.deliverable.updateMany — status → in_review

updateBlogPost(db, blogPostId, tenantId, data)

Extracted from: blog.ts:207–237

Logic: validate ownership, update title/content/wordCount/faqs

social.service.ts

approveSocialPost(db, socialPostId, tenantId, approvedByUserId)

Extracted from: social.ts:319–432

DB writes (transaction in Phase 3):

1. db.socialPost.update — status → client_approved
2. db.activity.update — status → done (conditional)
3. db.deliverable.updateMany — status → approved
4. db.ragDataset.findFirst — find published_content dataset
5. db.ragFile.create — if dataset found and bodyText present

Returns: { post, ragFileCreated: boolean }

Side effects (caller’s responsibility):

• enqueueSocialPublisher(...)
• enqueueRagContent(...) if ragFile created
• writeAuditLog(...)
• enqueueSearchSync(...)

rejectSocialPost(db, socialPostId, tenantId, rejectionNote)

Extracted from: social.ts:436–481

DB writes:

1. db.socialPost.update — status → dm_review, set rejectionNote
2. db.deliverable.updateMany — status → in_review

campaign.service.ts

importCampaigns(db, tenantId, campaigns)

Extracted from: campaigns.ts:166–196

Current problem: campaign create + externalMapping create run in parallel promises with no rollback — orphaned campaigns if mapping fails.

New logic: wrap each pair in db.$transaction():

const created = await Promise.all(
  campaigns.map((c) =>
    db.$transaction(async (tx) => {
      const campaign = await tx.campaign.create({ data: { tenantId, ...c } });
      await tx.campaignExternalMapping.create({ data: { campaignId: campaign.id, ...c.mapping } });
      return campaign;
    })
  )
);

createCampaign(db, tenantId, data)

Extracted from: campaigns.ts:301–335

Logic: create campaign, return it. Side effects (search sync, audit log) stay in the route handler.

Route Handler Pattern After Extraction

// Before — 80 lines of business logic in the route:
fastify.patch('/blog/:id/approve', async (req, reply) => {
  const post = await db.blogPost.findUnique(...);
  // ... validation, multiple db calls, conditional logic ...
});
 
// After — thin handler, service owns the logic:
fastify.patch('/blog/:id/approve', async (req, reply) => {
  const { tenantId, userId } = req.user;
  const { post, ragFileCreated } = await approveBlogPost(db, req.params.id, tenantId, userId);
  if (ragFileCreated) enqueueRagContent({ ... });
  writeAuditLog({ ... });
  enqueueSearchSync({ ... });
  reply.send(post);
});

Status: [ ] TODO

Phase 3 — Transaction Wrapping

Context

Multi-step writes that must succeed or fail atomically are not wrapped in db.$transaction(). Failure mid-sequence leaves records in inconsistent state (e.g., post marked client_approved but deliverable still in in_review).

Locations to Fix

3.1 Blog Post Approval — blog.service.ts:approveBlogPost

Wrap in a single transaction:

const result = await db.$transaction(async (tx) => {
  const post = await tx.blogPost.update({ ... });         // status change
  if (post.activityId) await tx.activity.update({ ... }); // mark done
  await tx.deliverable.updateMany({ ... });               // sync status
  const dataset = await tx.ragDataset.findFirst({ where: { ...notDeleted() } });
  const ragFile = dataset ? await tx.ragFile.create({ ... }) : null;
  return { post, ragFile };
});
 
// Side effects OUTSIDE the transaction:
if (result.ragFile) enqueueRagContent({ ... });
writeAuditLog({ ... });
enqueueSearchSync({ ... });

Why keep side effects outside: queue operations and audit logs must not roll back if a downstream side effect fails — the approval itself succeeded.

3.2 Social Post Approval — social.service.ts:approveSocialPost

Same pattern as 3.1.

3.3 Campaign Import — campaign.service.ts:importCampaigns

Each campaign + externalMapping pair wrapped atomically (see Phase 2 pattern above).

3.4 Tenant Delete N+1 — tenant-delete.worker.ts:335–346

Not a transaction issue — a query efficiency fix. Replace the per-user count loop:

// Before (N queries for N users):
for (const user of tenantUsers) {
  const count = await db.tenantMember.count({
    where: { userId: user.id, tenantId: { not: tenantId } },
  });
  if (count === 0) orphanIds.push(user.id);
}
 
// After (single query):
const membersElsewhere = await db.tenantMember.groupBy({
  by: ["userId"],
  where: {
    userId: { in: tenantUsers.map((u) => u.id) },
    tenantId: { not: tenantId },
  },
  _count: { userId: true },
});
const hasOtherTenant = new Set(membersElsewhere.map((m) => m.userId));
const orphanIds = tenantUsers.map((u) => u.id).filter((id) => !hasOtherTenant.has(id));

Status: [ ] TODO

Migration Steps

Testing Checklist (run after each step)

• pnpm typecheck passes across all affected packages
• Existing unit tests pass (pnpm test --filter @leadmetrics/db, pnpm test --filter @leadmetrics/agents)
• Manual smoke test: blog post approval flow in dashboard
• Manual smoke test: social post approval flow
• Manual smoke test: campaign import via API
• Playwright E2E suite passes for affected flows (blog, social, campaigns)

Extended Scope (2026-04-30)

After the original 10 steps, the refactoring was extended to all remaining API routers.

Additional Service Files Created

Additional Routers Covered

auth.ts, admin/tenants.ts, admin/billing.ts, brand-voice.ts, channels.ts, campaigns.ts, channel-connect.ts, dm/calendar.ts

Dead Code Cleaned Up

• Removed duplicate generateInvoiceNumber from admin/tenants.ts (advisory lock inlined into registration transaction)
• Removed local generateInvoiceNumber from auth.ts; both now call generateInvoiceNumber(db, now) from their respective service files

Notes

• Async side effects stay outside transactions — queue operations (enqueueRagContent, enqueueSearchSync, writeAuditLog) must not be rolled back if a downstream failure occurs after a successful DB write. The pattern is: transaction commits → then enqueue.
• isActive is not a soft delete — it signals whether a feature/config is enabled, not whether a record is deleted. Keep notDeleted() and activeOnly() as separate helpers with distinct semantics.
• Do not touch worker approval logic — workers create content; they do not approve it. Approval is always user-triggered via HTTP. Workers and services do not need to share code for these flows.
• Advisory lock pattern — pg_advisory_xact_lock(hashtext(prefix)) must be the first statement inside a db.$transaction(). Never nest transactions.
• Service functions that need db — take db: PrismaClient as the first parameter (e.g. generateInvoiceNumber(db, now)). Pure helpers (no DB) take only business params.