Automated Weekly Summaries, Tenant Rotation, and Valuation Type Normalization in PlayaMXCRM API

TL;DR: I added a weekly‑summary cron, a translation service, tenant‑rotation logic, and fixed inconsistent type values in the sales valuation pipeline. The changes tighten data integrity, automate reporting, and keep the API ready for multi‑tenant growth.

---

The Problem

Our PlayaMXCRM API had three concrete pain points that surfaced in production:

1. Inconsistent type values in the ventas (sales) table ('DEPARTAMENTO' vs 'DEPARTAMENTO ' vs lower‑case) broke the valuation comparables query, throwing operator does not exist: unknown = text errors and returning wrong pricing suggestions.

2. No automated weekly summary – the product team requested a Monday‑8 AM email summarizing occupancy, overdue payments, and upcoming expirations. We tried a simple setTimeout in main.ts, but it only ran once after the server started, leaving the team without the expected cadence.

3. Missing tenant rotation and translation – the CRM needed to rotate tenant assignments automatically and provide English translations of property descriptions for foreign buyers. Both features were on the backlog (suggestions 7‑10) but not yet implemented.

These issues were blocking the sales team (incorrect valuations) and the marketing team (no weekly KPI email), and they threatened our roadmap for multi‑tenant support.

---

What I Tried First

1. Quick string replace for type

I added a naïve replaceAll(' ', '') right before the comparables query:

// apps/api/src/ventas/ventas.controller.ts (initial attempt)
const normalizedType = venta.type.replaceAll(' ', '');

It fixed the immediate error for a few rows but missed cases with mixed casing ('departamento') and introduced a new bug where legitimate spaces in other fields were stripped.

2. One‑off setTimeout for the summary

In main.ts I wrote:

setTimeout(() => {
  weeklySummaryService.send();
}, msUntilNextMonday);

The function executed once after a server restart, but never again because the timeout wasn’t re‑scheduled. The team still got no weekly email.

3. Hard‑coded tenant rotation in the controller

I added a direct UPDATE query inside TenantsController to flip the active flag. This worked for a single tenant but didn’t scale; there was no transaction safety, and the logic was duplicated across services.

All three attempts were fragile, incomplete, and not testable. I needed a systematic solution.

---

The Implementation

1. Database migration & index

First, I added a migration to ensure the auto_generated_posts table is indexed for the new weekly‑summary queries:

--- a/apps/api/src/db/db.ts
+++ b/apps/api/src/db/db.ts
@@ -2241,5 +2241,10 @@ export async function migrate(): Promise<void> {
     );
     create index if not exists idx_auto_posts_property on auto_generated_posts(property_id, created_at desc);

+    -- Added for weekly summary performance
+    create index if not exists idx_ventas_type on ventas(type);
+

The new index (idx_ventas_type) speeds up the WHERE type = $1 clause we introduced later.

2. Normalizing type before comparison

I moved the normalization into a reusable helper inside ventas.controller.ts. The logic now trims, upper‑cases, and validates the value before any comparables lookup.

// apps/api/src/ventas/ventas.controller.ts
function normalizeVentaType(raw: string): string {
  // Remove leading/trailing spaces, collapse internal spaces, force upper case
  return raw.trim().replace(/\s+/g, ' ').toUpperCase();
}

// Usage in the valuation pipeline
const normalizedType = normalizeVentaType(venta.type);
const comparable = await db(`
  SELECT * FROM valuations
  WHERE type = $1
  ORDER BY similarity_score DESC
  LIMIT 5
`, [normalizedType]);

I also added a comment explaining the previous bug:

@@ -80,8 +80,11 @@ export class VentasController {
         OR ($3::uuid IS NULL
-            -- 'type' tiene valores inconsistentes en datos reales
-            -- (ej. 'DEPARTAMENTO' vs 'DE
+            -- 'type' tiene valores inconsistentes en datos reales
+            -- (ej. 'DEPARTAMENTO' vs 'DEPARTAMENTO ' vs 'departamento')
+            -- Normalizamos antes de comparar
+            $3 = normalizeVentaType(type)

Now the query runs consistently, and the valuation engine receives clean type values.

3. Weekly summary cron (setInterval)

I created a dedicated cron module that uses setInterval with a 24 h period, anchored to the next Monday 08:00 AM.

// apps/api/src/shared/weekly-summary.cron.ts
import { WeeklySummaryService } from "./weekly-summary.service.js";

export function startWeeklySummaryCron(service: WeeklySummaryService) {
  const now = new Date();
  const nextMonday = new Date(
    now.getFullYear(),
    now.getMonth(),
    now.getDate() + ((8 - now.getDay()) % 7),
    8, 0, 0, 0
  );
  const initialDelay = nextMonday.getTime() - now.getTime();

  setTimeout(() => {
    service.sendWeeklySummary();
    // After first run, repeat every 24h
    setInterval(() => service.sendWeeklySummary(), 24 * 60 * 60 * 1000);
  }, initialDelay);
}

The service itself builds the email payload:

ts
// apps/api/src/shared/weekly-summary.service.ts
import { query as db } from "../db/db.js";

export class WeeklySummaryService {
  async sendWeeklySummary() {
    const occupancy = await db(`SELECT COUNT(*) FROM properties WHERE status='occupied'`);
    const overdue = await db(`SELECT COUNT(*) FROM payments WHERE due_date < now() AND paid = false`);
    const expiring = await db(`SELECT COUNT(*) FROM leases WHERE end_date BETWEEN now() AND now() + interval '7 days'`);

    const body = `
      Weekly KPI Summary
      ------------------
      Occupied properties: ${occupancy.rows[0].count}
      Overdue payments: ${overdue.rows[0].count}
      Leases expiring next 7 days: ${expiring.rows[0].count}
    `;

    // Send via our internal mailer (omitted for brevity)
    await this.mailer.send({

---

*Part of my [Build in Public](https://dev.to/zaerohell) series — sharing the real process of building Building PlayaMXCRM from Playa del Carmen, México.*

*Repo: `zaerohell/VS` · 2026-09-04*

\#playadev #buildinpublic