fix(reports): render renewal-letter premium lines as booleans

`r.netPremium` / `r.total` are strings, so an empty-string value leaked
""` into the JSX instead of rendering nothing. Wrap in Boolean() so the
guard is a real conditional.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

.env.example

@@ -3,3 +3,16 @@ DATABASE_URL=mysql://jorgecuadros:jorgecuadros@localhost:3306/jorgecuadros
 SESSION_SECRET=change-me-to-a-random-string
 WEB_ORIGIN=http://localhost:3000
 NEXT_PUBLIC_API_ORIGIN=http://localhost:3001
+
+# Company info — printed in the header of every report (PDF + browser
+# print). Leave blank to use the placeholders. COMPANY_LOGO_PATH is
+# optional; when unset the API falls back to apps/api/assets/company_logo.png.
+COMPANY_NAME=Jorge Cuadros & Asociados
+COMPANY_ADDRESS_LINE1=
+COMPANY_ADDRESS_LINE2=
+COMPANY_CITY_STATE=
+COMPANY_PHONE=
+COMPANY_EMAIL=
+COMPANY_TAX_ID=
+COMPANY_WEBSITE=
+COMPANY_LOGO_PATH=

.gitea/workflows/deploy.yml

@@ -0,0 +1,134 @@
+# Manual PROD deploy to Portainer.
+#
+# This does NOT build — build.yml already builds + pushes the api/web images.
+# This workflow (re)applies the deploy/*.stack.yml files to the Portainer Swarm.
+# Trigger it by hand from the Actions tab ("Run workflow") and choose:
+#   - tag:   which already-published image tag to ship (default: latest)
+#   - scope: how much to deploy
+#       app  = web + api only (the usual app release)      [default]
+#       full = db + minio + web + api (bring up / update the whole platform)
+#
+# cssnr/portainer-stack-deploy-action creates each stack on first run and updates
+# it on every run, so no manual stack pre-creation in the Portainer UI. On a
+# `full` deploy the db + minio stacks are applied BEFORE the app (the API depends
+# on them). db + minio are stateful + pinned to node label jorgecuadros_db=true
+# (see their stack files) — re-applying them is idempotent and keeps their data.
+#
+# Prereqs (once):
+#  - one swarm node labelled jorgecuadros_db=true (db + minio + api pin there).
+#  - Gitea repo secrets set (Settings > Actions > Secrets):
+#      # Portainer
+#      PORTAINER_URL              https://192.168.4.212:9443
+#      PORTAINER_API_KEY          Portainer access token
+#      PORTAINER_ENDPOINT_ID      2  (the local Swarm endpoint)
+#      PORTAINER_APP_STACK_NAME   e.g. jorgecuadros-prod-app
+#      PORTAINER_DB_STACK_NAME    e.g. jorgecuadros-prod-db      (full only)
+#      PORTAINER_MINIO_STACK_NAME e.g. jorgecuadros-prod-minio   (full only)
+#      # App runtime
+#      DATABASE_URL               mysql://jorgecuadros:<pass>@192.168.4.212:3306/jorgecuadros
+#      SESSION_SECRET             64-hex (openssl rand -hex 32)
+#      APP_API_ORIGIN             http://192.168.4.212:3001  (browser-facing API URL)
+#      APP_WEB_ORIGIN             http://192.168.4.212:3000  (web public origin, API CORS)
+#      APP_S3_ENDPOINT            http://192.168.4.212:9000  (server-side minio URL)
+#      # Object storage (app + minio stack)
+#      MINIO_ROOT_USER            minio access key
+#      MINIO_ROOT_PASSWORD        minio secret key
+#      # Database stack (full only)
+#      MYSQL_PASSWORD             app-user password (matches DATABASE_URL)
+#      MYSQL_ROOT_PASSWORD        mysql root password
+
+name: Deploy to Portainer
+
+on:
+  workflow_dispatch:
+    inputs:
+      tag:
+        description: "Image tag to deploy (latest, sha-<short>, or vX.Y.Z)"
+        required: true
+        default: "latest"
+      scope:
+        description: "What to deploy"
+        type: choice
+        required: true
+        default: "app"
+        options:
+          - app
+          - full
+
+env:
+  REGISTRY: git.mancinas.io
+
+jobs:
+  deploy:
+    name: Deploy (${{ github.event.inputs.scope }})
+    runs-on: docker
+    container:
+      image: node:18-alpine
+    steps:
+      - uses: actions/checkout@v4
+
+      # --- full only: database ---------------------------------------------
+      - name: Deploy database stack
+        if: ${{ github.event.inputs.scope == 'full' }}
+        uses: cssnr/portainer-stack-deploy-action@v1
+        with:
+          url: ${{ secrets.PORTAINER_URL }}
+          token: ${{ secrets.PORTAINER_API_KEY }}
+          name: ${{ secrets.PORTAINER_DB_STACK_NAME }}
+          file: deploy/jorgecuadros-db.stack.yml
+          type: file
+          endpoint_id: ${{ secrets.PORTAINER_ENDPOINT_ID }}
+          env_data: |
+            {
+              "MYSQL_SERVER_ID": "1",
+              "MYSQL_PORT": "3306",
+              "MYSQL_DATABASE": "jorgecuadros",
+              "MYSQL_USER": "jorgecuadros",
+              "MYSQL_PASSWORD": "${{ secrets.MYSQL_PASSWORD }}",
+              "MYSQL_ROOT_PASSWORD": "${{ secrets.MYSQL_ROOT_PASSWORD }}"
+            }
+
+      # --- full only: object storage ---------------------------------------
+      - name: Deploy minio stack
+        if: ${{ github.event.inputs.scope == 'full' }}
+        uses: cssnr/portainer-stack-deploy-action@v1
+        with:
+          url: ${{ secrets.PORTAINER_URL }}
+          token: ${{ secrets.PORTAINER_API_KEY }}
+          name: ${{ secrets.PORTAINER_MINIO_STACK_NAME }}
+          file: deploy/jorgecuadros-minio.stack.yml
+          type: file
+          endpoint_id: ${{ secrets.PORTAINER_ENDPOINT_ID }}
+          env_data: |
+            {
+              "MINIO_API_PORT": "9000",
+              "MINIO_CONSOLE_PORT": "9001",
+              "MINIO_ROOT_USER": "${{ secrets.MINIO_ROOT_USER }}",
+              "MINIO_ROOT_PASSWORD": "${{ secrets.MINIO_ROOT_PASSWORD }}"
+            }
+
+      # --- always: the app (web + api) -------------------------------------
+      - name: Deploy app stack
+        uses: cssnr/portainer-stack-deploy-action@v1
+        with:
+          url: ${{ secrets.PORTAINER_URL }}
+          token: ${{ secrets.PORTAINER_API_KEY }}
+          name: ${{ secrets.PORTAINER_APP_STACK_NAME }}
+          file: deploy/jorgecuadros-app.stack.yml
+          type: file
+          pull_image: true
+          endpoint_id: ${{ secrets.PORTAINER_ENDPOINT_ID }}
+          env_data: |
+            {
+              "APP_TAG": "${{ github.event.inputs.tag }}",
+              "API_PORT": "3001",
+              "WEB_PORT": "3000",
+              "S3_BUCKET": "jorgecuadros-documents",
+              "API_ORIGIN": "${{ secrets.APP_API_ORIGIN }}",
+              "WEB_ORIGIN": "${{ secrets.APP_WEB_ORIGIN }}",
+              "S3_ENDPOINT": "${{ secrets.APP_S3_ENDPOINT }}",
+              "DATABASE_URL": "${{ secrets.DATABASE_URL }}",
+              "SESSION_SECRET": "${{ secrets.SESSION_SECRET }}",
+              "MINIO_ROOT_USER": "${{ secrets.MINIO_ROOT_USER }}",
+              "MINIO_ROOT_PASSWORD": "${{ secrets.MINIO_ROOT_PASSWORD }}"
+            }

RESUME.md

@@ -127,8 +127,14 @@ To rerun (from `migration/`, venv at `migration/.venv`):
 ```bash
 ./.venv/bin/python load_staging.py --output-dir ./output   # re-extract from Access (needs mdbtools + the source files)
 ./.venv/bin/python run_all.py --env dev                    # full transform+load; add --stage to re-extract first
+./.venv/bin/python run_all.py --env dev --sync             # additive sync: upsert legacy by provenance, keep manual rows, prune legacy empties
 ```
 
+`--sync` mode (Phase B) upserts legacy-owned rows by their provenance keys and preserves
+manual rows (`legacyId IS NULL`); every transform reuses each row's existing PK, rebuilds
+legacy-owned children by scoped delete + reinsert, and drops legacy rows gone from source.
+Verified end-to-end against dev 2026-07-24 — see §6 item 6.
+
 ## 5. Infrastructure & sync architecture (designed, not yet built)
 
 - **Internal server** — on-prem, private IP `192.168.1.xx`, no inbound internet exposure. Runs the platform + canonical MySQL (source of truth).
@@ -162,17 +168,30 @@ the reconciliation pass (done, then corrected) are all closed. See §3 and §8.
 5. **`TRASPASOS PAYPAL` is a clearing account, not a customer** — carries -7.03M MXN over
    309 movements and therefore tops the adeudo worklist. Deliberately not special-cased in
    code; needs a business decision on how to model it.
-6. **DB Operations — Phase B (additive sync) — IMPLEMENTED, verification pending.** Phase A provides
+6. **DB Operations — Phase B (additive sync) — VERIFIED END-TO-END against dev DB 2026-07-24.**
-   the admin-only `/operaciones` page + `ops` API module (ability `db:manage`, ADMIN), ingest
+   Phase A provides the admin-only `/operaciones` page + `ops` API module (ability `db:manage`,
-   folder, backup, restore, and destructive re-import. Phase B now enables `SYNC`: `OpsService`
+   ADMIN), ingest folder, backup, restore, and destructive re-import. Phase B enables `SYNC`:
-   creates a safety backup and runs `run_all.py --sync`; transforms upsert legacy-owned rows by
+   `OpsService` creates a safety backup and runs `run_all.py --sync`; transforms upsert
-   provenance keys while preserving existing PKs and rows whose `legacyId IS NULL` (manual).
+   legacy-owned rows by provenance keys while preserving existing PKs and rows whose
-   Prisma now enforces provenance uniqueness for properties, policies, transactions, vehicles,
+   `legacyId IS NULL` (manual). Prisma enforces provenance uniqueness for properties, policies,
-   and bank transactions. Sync intentionally skips prune/blob steps so manual customers and
+   transactions, and bank transactions (the vehicle unique was **removed** — one legacy policy
-   document pointers are not removed. Python compilation plus API/web production builds pass;
+   row carries up to 3 vehicles that share a `legacyId`, so provenance is not unique per
-   still required before production use: push updated Prisma schema and run an end-to-end sync
+   vehicle; vehicles are rebuilt by scoped delete + reinsert). Sync skips blob extraction, and
-   against a disposable/dev DB proving stable PKs, manual-row preservation, changed-row updates,
+   runs a **manual-safe prune** (`prune_empty_customers.py --sync` — only prunes empties that
-   and legacy-delete handling.
+   carry a legacy ref, never manually-added customers) because the customer upsert otherwise
+   re-creates every previously-pruned empty from Parquet.
+
+   **The as-written sync was broken and had never been run; a batch of bugs were fixed on
+   2026-07-24 before it passed** (fresh-uuid child FKs in policies/properties, unconditional
+   child inserts, a `zip(customers, refs)` mispairing in transform_customers, invalid vehicle
+   unique, lookup tables built with fresh uuids but never upserted, a `updatedAt=NOW()` on a
+   table with no such column, and report crashes on NULL `legacySourceTable` for manual rows).
+   Verified with `migration/` `verify_sync.py`-style harness: two consecutive `run_all.py --sync`
+   runs both exit 0 and pass 32/32 assertions (stable PKs, manual-row preservation, changed-row
+   updates, legacy-delete, no child duplication, zero FK orphans), idempotent (customers stable
+   at 1537). Schema pushed to dev, Prisma client regenerated, API build clean. Migration/web
+   changes uncommitted as of this update. Still open before production: run the same sync from
+   the `/operaciones` UI (OpsService path) and against a prod-shaped DB.
 
 ## 7. Environment notes (current macOS machine)
 
@@ -385,15 +404,20 @@ for what's actually next.
 
 ---
 
-- **Sync implementation — DONE, validation pending.** `run_all.py --sync` performs the
+- **Sync implementation — DONE + VALIDATED end-to-end against dev 2026-07-24.** `run_all.py
-  non-destructive legacy upsert path for customers, properties, policies, transactions, and
+  --sync` performs the non-destructive legacy upsert path for customers, properties, policies,
-  bank rows. It preserves manual rows and stable legacy-owned primary keys; the admin SYNC job
+  transactions, and bank rows, plus a manual-safe empty-customer prune. It preserves manual rows
-  automatically creates a pre-sync backup. Next validation: apply schema changes, then exercise
+  and stable legacy-owned primary keys; the admin SYNC job auto-creates a pre-sync backup. The
-  sync against a disposable DB with added, changed, removed, and manually-created rows.
+  as-written code was broken and had never been run — a batch of bugs was fixed before it passed
+  (see §6 item 6). Two consecutive syncs both exit 0 and pass 32/32 assertions (added, changed,
+  removed, and manually-created rows), idempotent. Remaining: exercise the same path from the
+  `/operaciones` admin UI and against a prod-shaped DB.
 - **Plan step 9: portal sync worker** remains separate and blocked on VPS provisioning. This
   Phase B feature synchronizes Access source files into the internal platform; it does not yet
   poll `utility_dbo` inbox tables or replicate portal-facing data to a VPS.
 - **Small / open:** (a) `TRASPASOS PAYPAL` clearing account still tops the adeudo worklist
   (§6.4d) — a business modelling call, not code. (b) Credential rotation on the old repo's
-  exposed MySQL password. (c) The `/estado-cuenta` browser visual pass — `/banco` was verified
+  exposed MySQL password. (c) ~~The `/estado-cuenta` browser visual pass.~~ **DONE 2026-07-24** —
-  in-browser this session; `/estado-cuenta` still worth a look.
+  verified vs dev: Anular buttons admin-gated, voided rows struck + excluded from totals,
+  clicking Anular voids end-to-end (note: it uses a blocking `window.confirm`). Customer-detail
+  mini tx list now also strikes voided rows ("(anulado)" tag) — was the last void-UI gap.

apps/api/package.json

@@ -11,6 +11,7 @@
     "test": "jest"
   },
   "dependencies": {
+    "@aws-sdk/client-s3": "^3.665.0",
     "@jorgecuadros/database": "workspace:*",
     "@nestjs/common": "^10.4.4",
     "@nestjs/config": "^3.3.0",
@@ -20,7 +21,9 @@
     "argon2": "^0.41.1",
     "class-transformer": "^0.5.1",
     "class-validator": "^0.14.1",
+    "exceljs": "^4.4.0",
     "express-session": "^1.18.0",
+    "pdfkit": "^0.15.1",
     "passport": "^0.7.0",
     "passport-local": "^1.0.0",
     "reflect-metadata": "^0.2.2",
@@ -31,6 +34,7 @@
     "@nestjs/testing": "^10.4.4",
     "@types/express": "^4.17.21",
     "@types/express-session": "^1.18.0",
+    "@types/pdfkit": "^0.13.5",
     "@types/jest": "^29.5.13",
     "@types/node": "^20.16.11",
     "@types/passport": "^1.0.17",

apps/api/src/app.module.ts

@@ -1,6 +1,7 @@
 import { Module } from "@nestjs/common";
 import { ConfigModule } from "@nestjs/config";
 import { PrismaModule } from "./prisma/prisma.module";
+import { StorageModule } from "./storage/storage.module";
 import { CommonModule } from "./common/common.module";
 import { UsersModule } from "./users/users.module";
 import { AuthModule } from "./auth/auth.module";
@@ -10,12 +11,14 @@ import { PropertiesModule } from "./properties/properties.module";
 import { BillingModule } from "./billing/billing.module";
 import { BankModule } from "./bank/bank.module";
 import { OpsModule } from "./ops/ops.module";
+import { ReportsModule } from "./reports/reports.module";
 import { AppController } from "./app.controller";
 
 @Module({
   imports: [
     ConfigModule.forRoot({ isGlobal: true }),
     PrismaModule,
+    StorageModule,
     CommonModule,
     UsersModule,
     AuthModule,
@@ -25,6 +28,7 @@ import { AppController } from "./app.controller";
     BillingModule,
     BankModule,
     OpsModule,
+    ReportsModule,
   ],
   controllers: [AppController],
 })

apps/api/src/billing/billing.service.ts

@@ -112,6 +112,26 @@ function dec(v: Prisma.Decimal | null | undefined): string {
  */
 const NOT_VOIDED: Prisma.TransactionWhereInput = { voidedAt: null };
 
+/**
+ * Source tables excluded from the customer-facing statement.
+ *
+ * The legacy portal's `datosfreak` table was materialized from DATOS2 only
+ * (`objects.json:1358`), so the customer's "current balance" never saw
+ * EFECTIVO / EFECTIVO FM3 / CHEQUE FM3 / EFECTIVO_BACKUP cash receipts, nor
+ * the IVA 2015 snapshot. The unified `transactions` table has all of them, so
+ * the statement must drop them to match the legacy number the customer has
+ * been quoted for years. The staff-facing balances worklist and movement
+ * browser keep them — they're real money, just tracked separately
+ * (FM3 = visa fee stream, EFECTIVO = cash receipt stream).
+ */
+const STATEMENT_EXCLUDED_SOURCE_TABLES: readonly string[] = [
+  "EFECTIVO",
+  "EFECTIVO_BACKUP",
+  "EFECTIVO FM3",
+  "CHEQUE FM3",
+  "IVA 2015",
+];
+
 @Injectable()
 export class BillingService {
   constructor(private readonly prisma: PrismaService) {}
@@ -579,7 +599,10 @@ export class BillingService {
     }
 
     const rows = await this.prisma.transaction.findMany({
-      where: { customerId },
+      where: {
+        customerId,
+        legacySourceTable: { notIn: STATEMENT_EXCLUDED_SOURCE_TABLES as string[] },
+      },
       orderBy: [{ transactionDate: "asc" }, { id: "asc" }],
       select: {
         id: true,

apps/api/src/ops/ops.controller.ts

@@ -44,7 +44,7 @@ export class OpsController {
 
   @Post("ingest/:name")
   @UseInterceptors(
-    FileInterceptor("file", { limits: { fileSize: 500 * 1024 * 1024 } }),
+    FileInterceptor("file", { limits: { fileSize: 2 * 1024 * 1024 * 1024 } }),
   )
   async uploadIngest(
     @Param("name") name: string,

apps/api/src/ops/ops.service.ts

@@ -43,8 +43,11 @@ interface MysqlConn {
 export class OpsService implements OnModuleInit {
   private readonly logger = new Logger(OpsService.name);
 
+  // Resolve from this source file so it works regardless of process.cwd()
+  // (the API runs from apps/api/, but the Python ETL lives at repo-root migration/).
   private readonly migrationDir =
-    process.env.MIGRATION_DIR ?? path.resolve(process.cwd(), "migration");
+    process.env.MIGRATION_DIR ??
+    path.resolve(__dirname, "..", "..", "..", "..", "migration");
   private readonly ingestDir =
     process.env.INGEST_DIR ?? path.join(this.migrationDir, "ingest");
   private readonly backupDir =

apps/api/src/policies/policies.controller.ts

@@ -8,9 +8,15 @@ import {
   Post,
   Query,
   Req,
+  Res,
+  StreamableFile,
+  UploadedFile,
   UseGuards,
+  UseInterceptors,
 } from "@nestjs/common";
-import { Request } from "express";
+import { FileInterceptor } from "@nestjs/platform-express";
+import { Request, Response } from "express";
+import { downloadName, type UploadedFileLike } from "../storage/upload-file";
 import { AuthenticatedGuard } from "../auth/authenticated.guard";
 import { AbilityGuard } from "../auth/ability.guard";
 import { RequireAbility } from "../auth/require-ability.decorator";
@@ -240,4 +246,40 @@ export class PoliciesController {
   removeClaim(@Param("id") id: string, @Param("childId") childId: string) {
     return this.policies.removeClaim(id, childId);
   }
+
+  // --- documents ------------------------------------------------------------
+
+  @Post(":id/documents")
+  @RequireAbility("policy:update")
+  @UseInterceptors(
+    FileInterceptor("file", { limits: { fileSize: 50 * 1024 * 1024 } }),
+  )
+  addDocument(
+    @Param("id") id: string,
+    @UploadedFile() file: UploadedFileLike | undefined,
+    @Query("type") type: string | undefined,
+  ) {
+    if (!file) throw new Error("No se recibió ningún archivo.");
+    return this.policies.addDocument(id, file, type);
+  }
+
+  @Get(":id/documents/:childId/download")
+  async downloadDocument(
+    @Param("id") id: string,
+    @Param("childId") childId: string,
+    @Res({ passthrough: true }) res: Response,
+  ): Promise<StreamableFile> {
+    const { row, stream, contentType } = await this.policies.getDocument(id, childId);
+    res.set({
+      "Content-Type": contentType ?? "application/octet-stream",
+      "Content-Disposition": `attachment; filename="${downloadName(row.storageKey, row.documentType)}"`,
+    });
+    return new StreamableFile(stream);
+  }
+
+  @Delete(":id/documents/:childId")
+  @RequireAbility("policy:update")
+  removeDocument(@Param("id") id: string, @Param("childId") childId: string) {
+    return this.policies.removeDocument(id, childId);
+  }
 }

apps/api/src/policies/policies.service.ts

@@ -1,6 +1,9 @@
 import { Injectable, NotFoundException } from "@nestjs/common";
+import { randomUUID } from "node:crypto";
 import { Prisma } from "@jorgecuadros/database";
 import { PrismaService } from "../prisma/prisma.service";
+import { StorageService } from "../storage/storage.service";
+import { extForUpload, type UploadedFileLike } from "../storage/upload-file";
 import { toDate } from "../common/coerce";
 import { CreatePolicyDto, UpdatePolicyDto } from "./policy.dto";
 import {
@@ -77,7 +80,10 @@ function daysUntil(policyTo: Date | null, from: Date): number | null {
 
 @Injectable()
 export class PoliciesService {
-  constructor(private readonly prisma: PrismaService) {}
+  constructor(
+    private readonly prisma: PrismaService,
+    private readonly storage: StorageService,
+  ) {}
 
   private statusWhere(
     status: PolicyStatus | undefined,
@@ -479,6 +485,47 @@ export class PoliciesService {
     };
   }
 
+  // --- documents ------------------------------------------------------------
+  // Blob in object storage under `policy/<policyId>/…`; row is the pointer.
+
+  async addDocument(
+    policyId: string,
+    file: UploadedFileLike,
+    documentType?: string,
+  ) {
+    await this.ensurePolicy(policyId);
+    const key = `policy/${policyId}/${randomUUID()}${extForUpload(file)}`;
+    await this.storage.put(key, file.buffer, file.mimetype);
+    return this.prisma.policyDocument.create({
+      data: {
+        policyId,
+        documentType: documentType?.trim() || "DOCUMENT",
+        storageKey: key,
+      },
+    });
+  }
+
+  async getDocument(policyId: string, id: string) {
+    const row = await this.prisma.policyDocument.findFirst({
+      where: { id, policyId },
+    });
+    if (!row) throw new NotFoundException(`Document ${id} not found on policy ${policyId}`);
+    const blob = await this.storage.getStream(row.storageKey);
+    return { row, ...blob };
+  }
+
+  async removeDocument(policyId: string, id: string) {
+    await this.ensurePolicy(policyId);
+    const row = await this.prisma.policyDocument.findFirst({
+      where: { id, policyId },
+      select: { id: true, storageKey: true },
+    });
+    if (!row) throw new NotFoundException(`Document ${id} not found on policy ${policyId}`);
+    const deleted = await this.prisma.policyDocument.delete({ where: { id } });
+    await this.storage.delete(row.storageKey);
+    return deleted;
+  }
+
   // --- lookups (providers / policy types / adjusters) -----------------------
 
   listLookups() {

apps/api/src/properties/properties.controller.ts

@@ -9,10 +9,16 @@ import {
   Put,
   Query,
   Req,
+  Res,
+  StreamableFile,
+  UploadedFile,
   UseGuards,
+  UseInterceptors,
 } from "@nestjs/common";
+import { FileInterceptor } from "@nestjs/platform-express";
 import { ServiceKind } from "@jorgecuadros/database";
-import { Request } from "express";
+import { Request, Response } from "express";
+import { downloadName, type UploadedFileLike } from "../storage/upload-file";
 import { AuthenticatedGuard } from "../auth/authenticated.guard";
 import { AbilityGuard } from "../auth/ability.guard";
 import { RequireAbility } from "../auth/require-ability.decorator";
@@ -196,7 +202,35 @@ export class PropertiesController {
     return this.properties.removeTrust(id);
   }
 
-  // --- documents (remove pointer only) --------------------------------------
+  // --- documents ------------------------------------------------------------
+
+  @Post(":id/documents")
+  @RequireAbility("property:update")
+  @UseInterceptors(
+    FileInterceptor("file", { limits: { fileSize: 50 * 1024 * 1024 } }),
+  )
+  addDocument(
+    @Param("id") id: string,
+    @UploadedFile() file: UploadedFileLike | undefined,
+    @Query("type") type: string | undefined,
+  ) {
+    if (!file) throw new Error("No se recibió ningún archivo.");
+    return this.properties.addDocument(id, file, type);
+  }
+
+  @Get(":id/documents/:childId/download")
+  async downloadDocument(
+    @Param("id") id: string,
+    @Param("childId") childId: string,
+    @Res({ passthrough: true }) res: Response,
+  ): Promise<StreamableFile> {
+    const { row, stream, contentType } = await this.properties.getDocument(id, childId);
+    res.set({
+      "Content-Type": contentType ?? "application/octet-stream",
+      "Content-Disposition": `attachment; filename="${downloadName(row.storageKey, row.documentType)}"`,
+    });
+    return new StreamableFile(stream);
+  }
 
   @Delete(":id/documents/:childId")
   @RequireAbility("property:update")

apps/api/src/properties/properties.service.ts

@@ -1,6 +1,9 @@
 import { Injectable, NotFoundException } from "@nestjs/common";
+import { randomUUID } from "node:crypto";
 import { Prisma, ServiceKind } from "@jorgecuadros/database";
 import { PrismaService } from "../prisma/prisma.service";
+import { StorageService } from "../storage/storage.service";
+import { extForUpload } from "../storage/upload-file";
 import { toDate } from "../common/coerce";
 import {
   CreatePropertyDto,
@@ -83,7 +86,10 @@ function daysUntil(dueDate: Date | null | undefined, from: Date): number | null
 
 @Injectable()
 export class PropertiesService {
-  constructor(private readonly prisma: PrismaService) {}
+  constructor(
+    private readonly prisma: PrismaService,
+    private readonly storage: StorageService,
+  ) {}
 
   private trustWhere(
     trust: TrustFilter | undefined,
@@ -543,16 +549,45 @@ export class PropertiesService {
   }
 
   // --- documents ------------------------------------------------------------
-  // Removing a pointer row only; uploading files needs the object-storage
+  // The blob lives in object storage (MinIO); the row is just the pointer. Keys
-  // client wired into the API (today only the migration writes to MinIO).
+  // stay under the `service/<propertyId>/…` prefix the migration established.
+
+  async addDocument(
+    propertyId: string,
+    file: { buffer: Buffer; originalname?: string; mimetype?: string },
+    documentType?: string,
+  ) {
+    await this.ensureProperty(propertyId);
+    const ext = extForUpload(file);
+    const key = `service/${propertyId}/${randomUUID()}${ext}`;
+    await this.storage.put(key, file.buffer, file.mimetype);
+    return this.prisma.serviceDocument.create({
+      data: {
+        propertyId,
+        documentType: documentType?.trim() || "DOCUMENT",
+        storageKey: key,
+      },
+    });
+  }
+
+  async getDocument(propertyId: string, id: string) {
+    const row = await this.prisma.serviceDocument.findFirst({
+      where: { id, propertyId },
+    });
+    if (!row) throw new NotFoundException(`Document ${id} not found on property ${propertyId}`);
+    const blob = await this.storage.getStream(row.storageKey);
+    return { row, ...blob };
+  }
 
   async removeDocument(propertyId: string, id: string) {
     await this.ensureProperty(propertyId);
     const row = await this.prisma.serviceDocument.findFirst({
       where: { id, propertyId },
-      select: { id: true },
+      select: { id: true, storageKey: true },
     });
     if (!row) throw new NotFoundException(`Document ${id} not found on property ${propertyId}`);
-    return this.prisma.serviceDocument.delete({ where: { id } });
+    const deleted = await this.prisma.serviceDocument.delete({ where: { id } });
+    await this.storage.delete(row.storageKey);
+    return deleted;
   }
 }

apps/api/src/reports/company.ts

@@ -0,0 +1,103 @@
+/**
+ * Company info used on every report header (PDF + print). Read from
+ * the environment so the office can edit it without a code change —
+ * the .env.example file lists the keys; defaults below are placeholders
+ * the office should override for production.
+ *
+ * Single source of truth: the API renders the header. The web header
+ * (login + AppShell) still reads the static "Jorge Cuadros & Asociados"
+ * strings for now — those are visual brand, the API's COMPANY_INFO
+ * block is the legal/locator block on printed documents.
+ */
+
+import * as fs from "node:fs";
+import * as path from "node:path";
+
+export interface CompanyInfo {
+  name: string;
+  /** Street address — line 1. */
+  addressLine1: string;
+  /** Street address — line 2 (suite, floor, etc.). Optional. */
+  addressLine2: string;
+  /** "City, State, ZIP, Country" — single line. */
+  cityState: string;
+  phone: string;
+  email: string;
+  /** Mexican tax ID ("RFC"). Optional. */
+  taxId: string;
+  website: string;
+  /** Absolute path to the logo PNG. Null when missing — renderers fall
+   *  back to a text mark. */
+  logoPath: string | null;
+  /** Logo buffer + intrinsic size, eagerly loaded so the PDF renderer
+   *  doesn't do a sync read on every report. Null when no logo. */
+  logo: { buffer: Buffer; width: number; height: number } | null;
+}
+
+function envOr(key: string, fallback: string): string {
+  const v = process.env[key];
+  return v && v.trim() ? v : fallback;
+}
+
+function resolveLogoPath(): string | null {
+  const explicit = process.env.COMPANY_LOGO_PATH;
+  if (explicit) {
+    return fs.existsSync(explicit) ? explicit : null;
+  }
+  // Default: look in apps/api/assets/company_logo.png (copied from
+  // apps/web/public/images/company_logo.png — single canonical image
+  // kept in lock-step; see .env.example for the override path).
+  const candidates = [
+    path.resolve(__dirname, "..", "..", "assets", "company_logo.png"),
+    path.resolve(__dirname, "..", "..", "..", "web", "public", "images", "company_logo.png"),
+  ];
+  for (const c of candidates) {
+    if (fs.existsSync(c)) return c;
+  }
+  return null;
+}
+
+let cached: CompanyInfo | null = null;
+
+export function getCompanyInfo(): CompanyInfo {
+  if (cached) return cached;
+  const logoPath = resolveLogoPath();
+  let logo: CompanyInfo["logo"] = null;
+  if (logoPath) {
+    try {
+      const buf = fs.readFileSync(logoPath);
+      // Intrinsic PNG size: read IHDR (bytes 16-23 of the file).
+      // Width = BE uint32 at offset 16, height = BE uint32 at offset 20.
+      const w =
+        logoPath.endsWith(".png") && buf.length >= 24
+          ? buf.readUInt32BE(16)
+          : 0;
+      const h =
+        logoPath.endsWith(".png") && buf.length >= 24
+          ? buf.readUInt32BE(20)
+          : 0;
+      logo = { buffer: buf, width: w, height: h };
+    } catch {
+      logo = null;
+    }
+  }
+  cached = {
+    name: envOr("COMPANY_NAME", "Jorge Cuadros & Asociados"),
+    addressLine1: envOr(
+      "COMPANY_ADDRESS_LINE1",
+      "Av. Revolución 1234, Int. 5",
+    ),
+    addressLine2: envOr("COMPANY_ADDRESS_LINE2", ""),
+    cityState: envOr(
+      "COMPANY_CITY_STATE",
+      "Tijuana, Baja California 22000, México",
+    ),
+    phone: envOr("COMPANY_PHONE", "(664) 000-0000"),
+    email: envOr("COMPANY_EMAIL", "contacto@jorgecuadros.local"),
+    taxId: envOr("COMPANY_TAX_ID", ""),
+    website: envOr("COMPANY_WEBSITE", "jorgecuadros.local"),
+    logoPath,
+    logo,
+  };
+  return cached;
+}

apps/api/src/reports/outputs.ts

@@ -0,0 +1,433 @@
+/**
+ * Output renderers for the reports module.
+ *
+ * Every report's `run` returns `{ columns, rows, totals?, subtitle? }`.
+ * CSV/XLSX/PDF all derive from the same shape so adding a report = one
+ * registry entry, no per-format template.
+ *
+ * PDF uses pdfkit. The statement format (edo-cuenta-datos) uses a
+ * different layout than the tabular one — handled inline.
+ */
+
+// pdfkit exports its constructor via `module.exports = PDFDocument`, so a
+// namespace import gets the type, and `import = require()` gets the value.
+import PDFDocument = require("pdfkit");
+import * as ExcelJS from "exceljs";
+import type { ColumnDef, ReportResult } from "./reports.types";
+import { getCompanyInfo } from "./company";
+
+type Doc = PDFKit.PDFDocument;
+
+/* ----------------------------------------------------------------- CSV */
+
+function csvCell(v: unknown): string {
+  if (v === null || v === undefined) return "";
+  const s = String(v);
+  if (s.includes(",") || s.includes('"') || s.includes("\n") || s.includes("\r")) {
+    return `"${s.replace(/"/g, '""')}"`;
+  }
+  return s;
+}
+
+export function renderCsv(columns: ColumnDef[], result: ReportResult): string {
+  const headers = columns.map((c) => csvCell(c.label)).join(",");
+  const lines = result.rows.map((r) =>
+    columns
+      .map((c) => {
+        const v = r[c.key];
+        if (typeof v === "number") return v;
+        return csvCell(v);
+      })
+      .join(","),
+  );
+  const totals: string[] = [];
+  if (result.totals) {
+    for (const [k, v] of Object.entries(result.totals)) {
+      totals.push(csvCell(k), csvCell(v));
+    }
+  }
+  return [headers, ...lines, ...(totals.length ? [totals.join(",")] : [])].join(
+    "\n",
+  );
+}
+
+/* ----------------------------------------------------------------- XLSX */
+
+export async function renderXlsx(
+  columns: ColumnDef[],
+  result: ReportResult,
+): Promise<Buffer> {
+  const wb = new ExcelJS.Workbook();
+  wb.creator = "Jorge Cuadros & Asociados";
+  const ws = wb.addWorksheet("Reporte", {
+    views: [{ state: "frozen", ySplit: 1 }],
+  });
+  ws.columns = columns.map((c) => ({
+    header: c.label,
+    key: c.key,
+    width: Math.max(10, Math.min(40, (c.label.length + 2) * 1.2)),
+  }));
+  ws.getRow(1).font = { bold: true };
+  ws.getRow(1).fill = {
+    type: "pattern",
+    pattern: "solid",
+    fgColor: { argb: "FFE2EDE9" }, // brand-tint
+  };
+  for (const row of result.rows) {
+    ws.addRow(row);
+  }
+  // Number formatting for money columns.
+  for (const col of columns) {
+    if (col.type === "money" || col.type === "number") {
+      ws.getColumn(col.key).numFmt =
+        col.type === "money" ? "#,##0.00" : "#,##0";
+      ws.getColumn(col.key).alignment = { horizontal: "right" };
+    }
+  }
+  if (result.totals) {
+    const last = ws.addRow({});
+    let i = 1;
+    for (const [k, v] of Object.entries(result.totals)) {
+      const cell = ws.getCell(last.number, i);
+      cell.value = `${k}: ${v}`;
+      cell.font = { bold: true };
+      i++;
+    }
+  }
+  const buf = await wb.xlsx.writeBuffer();
+  return Buffer.from(buf);
+}
+
+/* ----------------------------------------------------------------- PDF */
+
+const BRAND = "#0c322d";
+const ACCENT = "#bf5a34";
+const MUTED = "#756c5c";
+const LINE = "#e4dccb";
+
+function fmtMoney(v: unknown): string {
+  if (v === null || v === undefined || v === "") return "";
+  const n = Number(v);
+  if (!Number.isFinite(n)) return String(v);
+  return n.toLocaleString("es-MX", { minimumFractionDigits: 2, maximumFractionDigits: 2 });
+}
+
+function pdfRow(
+  doc: Doc,
+  y: number,
+  cols: Array<{ label: string; width: number; align?: "left" | "right" }>,
+  values: Array<{ text: string; align?: "left" | "right" }>,
+  x: number,
+): number {
+  let cx = x;
+  for (let i = 0; i < cols.length; i++) {
+    const c = cols[i];
+    const v = values[i] ?? { text: "" };
+    const align = v.align ?? c.align ?? "left";
+    const w = c.width;
+    doc
+      .font("Helvetica")
+      .fontSize(9)
+      .fillColor("#211d17")
+      .text(v.text, cx, y, {
+        width: w - 4,
+        align,
+        ellipsis: true,
+        lineBreak: false,
+        height: 16,
+      });
+    cx += w;
+  }
+  return y + 18;
+}
+
+export function renderPdf(
+  columns: ColumnDef[],
+  result: ReportResult,
+  title: string,
+): Promise<Buffer> {
+  return new Promise((resolve, reject) => {
+    const doc = new PDFDocument({
+      size: "LETTER",
+      layout: "landscape",
+      margins: { top: 96, bottom: 56, left: 48, right: 48 },
+      bufferPages: true,
+      info: {
+        Title: title,
+        Author: "Jorge Cuadros & Asociados",
+        Subject: "Reporte",
+        Creator: "Jorge Cuadros Platform — Reports module",
+      },
+    });
+    const chunks: Buffer[] = [];
+    doc.on("data", (c: Buffer) => chunks.push(c));
+    doc.on("end", () => resolve(Buffer.concat(chunks)));
+    doc.on("error", reject);
+
+    const company = getCompanyInfo();
+    const pageW = doc.page.width - 96;
+
+    /** The header is repeated on every page (via addPage + manual draw). */
+    const drawHeader = () => {
+      // Background bar (brand pine) for the masthead.
+      doc.rect(0, 0, doc.page.width, 60).fill(BRAND);
+
+      // Logo, fitted to a 40px box, with 8px padding.
+      let textX = 48;
+      if (company.logo) {
+        const targetH = 40;
+        const scale = targetH / company.logo.height;
+        const w = company.logo.width * scale;
+        doc.image(company.logo.buffer, 48, 10, { height: targetH });
+        textX = 48 + w + 14;
+      }
+
+      // Company name (large) + "Reporte" tag below it.
+      doc
+        .fillColor("#f5f1e8")
+        .font("Helvetica-Bold")
+        .fontSize(15)
+        .text(company.name, textX, 14, { width: pageW - (textX - 48), lineBreak: false });
+      doc
+        .font("Helvetica")
+        .fontSize(8)
+        .fillColor("#cde0db")
+        .text("Reporte", textX, 36, { lineBreak: false });
+
+      // Right-aligned company locator (address + phone + email).
+      const rightLines = [
+        company.addressLine1,
+        company.addressLine2,
+        [company.cityState].filter(Boolean).join(" · "),
+        [company.phone, company.email].filter(Boolean).join(" · "),
+        company.taxId ? `RFC: ${company.taxId}` : "",
+      ].filter(Boolean);
+      doc.font("Helvetica").fontSize(8).fillColor("#cde0db");
+      let ry = 12;
+      for (const line of rightLines) {
+        doc.text(line, 48, ry, {
+          width: pageW,
+          align: "right",
+          lineBreak: false,
+          ellipsis: true,
+        });
+        ry += 10;
+      }
+
+      // Thin accent line under the masthead.
+      doc.rect(0, 60, doc.page.width, 2).fill(ACCENT);
+
+      // Title + subtitle + printed-at.
+      doc
+        .font("Helvetica-Bold")
+        .fontSize(15)
+        .fillColor(BRAND)
+        .text(title, 48, 72, { lineBreak: false });
+      let metaY = 92;
+      if (result.subtitle) {
+        doc
+          .font("Helvetica")
+          .fontSize(9)
+          .fillColor(MUTED)
+          .text(result.subtitle, 48, metaY, { lineBreak: false });
+        metaY += 12;
+      }
+      const printedAt = new Date().toLocaleString("es-MX");
+      doc
+        .font("Helvetica")
+        .fontSize(8)
+        .fillColor(MUTED)
+        .text(`Impreso: ${printedAt}`, 48, metaY, { lineBreak: false });
+    };
+
+    drawHeader();
+
+    // Column widths: distribute page width minus margins, weighted.
+    const totalW = columns.reduce((s, c) => s + (c.width ?? 12), 0);
+    const cols = columns.map((c) => ({
+      label: c.label,
+      width: ((c.width ?? 12) / totalW) * pageW,
+      align: c.align,
+    }));
+
+    let y = 130;
+    const drawTableHeader = () => {
+      doc.rect(48, y, pageW, 18).fill("#faf6ee");
+      y = pdfRow(
+        doc,
+        y + 4,
+        cols,
+        cols.map((c) => ({ text: c.label, align: c.align })),
+        48,
+      );
+      doc
+        .moveTo(48, y)
+        .lineTo(48 + pageW, y)
+        .strokeColor(LINE)
+        .lineWidth(0.5)
+        .stroke();
+    };
+    drawTableHeader();
+
+    // Body rows.
+    for (const r of result.rows) {
+      if (y > doc.page.height - 64) {
+        doc.addPage({ layout: "landscape", margins: { top: 96, bottom: 56, left: 48, right: 48 } });
+        drawHeader();
+        y = 130;
+        drawTableHeader();
+      }
+      const vals = columns.map((c) => {
+        const v = r[c.key];
+        const text = c.type === "money" ? fmtMoney(v) : v == null ? "" : String(v);
+        return { text, align: c.align };
+      });
+      y = pdfRow(doc, y + 4, cols, vals, 48);
+      doc
+        .moveTo(48, y)
+        .lineTo(48 + pageW, y)
+        .strokeColor("#e4dccb")
+        .lineWidth(0.4)
+        .stroke();
+    }
+
+    // Totals.
+    if (result.totals) {
+      y += 6;
+      doc.rect(48, y, pageW, 18).fill(ACCENT);
+      doc
+        .font("Helvetica-Bold")
+        .fontSize(9)
+        .fillColor("#f5f1e8")
+        .text(
+          Object.entries(result.totals)
+            .map(([k, v]) => `${k}: ${v}`)
+            .join("   ·   "),
+          52,
+          y + 5,
+          { width: pageW - 8, align: "left" },
+        );
+    }
+
+    doc.end();
+  });
+}
+
+/* ----------------------------------------------------------------- print (HTML) */
+
+/**
+ * Print-stylesheet-friendly HTML. The web app's print stylesheet hides
+ * nav, but otherwise this is a plain table the browser paginates itself.
+ *
+ * The header carries the company info (logo + name + locator) so printed
+ * pages stand alone — staff can hand one to a customer and the office
+ * identification is on every sheet, not buried in the cover page.
+ */
+export function renderPrintHtml(
+  columns: ColumnDef[],
+  result: ReportResult,
+  title: string,
+): string {
+  const company = getCompanyInfo();
+  const head = (label: string, align?: "left" | "right") =>
+    `<th style="text-align:${align ?? "left"};padding:6px 8px;border-bottom:2px solid #0c322d;background:#faf6ee;font-size:11px">${escapeHtml(label)}</th>`;
+  const cell = (v: unknown, c: ColumnDef) => {
+    const text = c.type === "money" ? fmtMoney(v) : v == null ? "" : String(v);
+    const align = c.align ?? "left";
+    return `<td style="text-align:${align};padding:4px 8px;border-bottom:1px solid #e4dccb;font-size:11px;${c.type === "money" ? "font-variant-numeric:tabular-nums" : ""}">${escapeHtml(text)}</td>`;
+  };
+  const rows = result.rows
+    .map(
+      (r) =>
+        `<tr>${columns
+          .map((c) => cell(r[c.key], c))
+          .join("")}</tr>`,
+    )
+    .join("");
+  const totals = result.totals
+    ? `<tr><td colspan="${columns.length}" style="padding:8px;background:#bf5a34;color:#f5f1e8;font-weight:600;font-size:11px">${Object.entries(
+        result.totals,
+      )
+        .map(([k, v]) => `${escapeHtml(k)}: ${escapeHtml(String(v))}`)
+        .join(" &nbsp;·&nbsp; ")}</td></tr>`
+    : "";
+
+  // Logo embedded as base64 data URL — the print page is opened as a
+  // new tab and printed standalone, so a relative path to the web app
+  // wouldn't resolve when launched outside the web's origin.
+  const logoDataUrl = company.logo
+    ? `data:image/png;base64,${company.logo.buffer.toString("base64")}`
+    : null;
+
+  const locatorLines = [
+    company.addressLine1,
+    company.addressLine2,
+    company.cityState,
+    [company.phone, company.email].filter(Boolean).join(" · "),
+    company.taxId ? `RFC: ${company.taxId}` : "",
+  ].filter(Boolean);
+
+  return `<!doctype html>
+<html lang="es"><head>
+<meta charset="utf-8" />
+<title>${escapeHtml(title)} — ${escapeHtml(company.name)}</title>
+<style>
+  @page { size: letter landscape; margin: 0.5in; }
+  body { font-family: -apple-system, "Helvetica Neue", Helvetica, Arial, sans-serif; color: #211d17; margin: 0; }
+  .masthead { display: flex; align-items: flex-start; gap: 16px; padding: 12px 16px; background: #0c322d; color: #f5f1e8; border-radius: 6px 6px 0 0; }
+  .masthead-logo { flex: 0 0 auto; }
+  .masthead-logo img { display: block; height: 56px; width: auto; }
+  .masthead-text { flex: 1; min-width: 0; }
+  .masthead-name { font-family: Georgia, "Times New Roman", serif; font-size: 20px; font-weight: 600; line-height: 1.1; }
+  .masthead-tag { font-size: 11px; color: #cde0db; margin-top: 2px; text-transform: uppercase; letter-spacing: 0.06em; }
+  .masthead-locator { font-size: 10px; color: #cde0db; text-align: right; line-height: 1.35; white-space: nowrap; }
+  .accent { height: 3px; background: #bf5a34; }
+  .head { padding: 12px 4px 8px; }
+  .head h1 { font-family: Georgia, "Times New Roman", serif; font-size: 18px; margin: 0; color: #0c322d; }
+  .head p  { font-size: 11px; color: #756c5c; margin: 2px 0 0; }
+  table { width: 100%; border-collapse: collapse; }
+  @media print {
+    .noprint { display: none; }
+    .masthead { border-radius: 0; }
+  }
+  .noprint { padding: 8px 0; }
+  .noprint button { padding: 6px 12px; background: #0c322d; color: #f5f1e8; border: 0; border-radius: 4px; cursor: pointer; font-size: 12px; }
+  .footer { margin-top: 16px; font-size: 9px; color: #756c5c; border-top: 1px solid #e4dccb; padding-top: 6px; display: flex; justify-content: space-between; }
+</style>
+</head><body>
+<div class="noprint"><button onclick="window.print()">Imprimir / Guardar PDF</button></div>
+<div class="masthead">
+  ${logoDataUrl ? `<div class="masthead-logo"><img src="${logoDataUrl}" alt="" /></div>` : ""}
+  <div class="masthead-text">
+    <div class="masthead-name">${escapeHtml(company.name)}</div>
+    <div class="masthead-tag">Reporte</div>
+  </div>
+  <div class="masthead-locator">
+    ${locatorLines.map((l) => escapeHtml(l)).join("<br/>")}
+    ${company.website ? `<br/>${escapeHtml(company.website)}` : ""}
+  </div>
+</div>
+<div class="accent"></div>
+<div class="head">
+  <h1>${escapeHtml(title)}</h1>
+  ${result.subtitle ? `<p>${escapeHtml(result.subtitle)}</p>` : ""}
+  <p>Impreso: ${new Date().toLocaleString("es-MX")}</p>
+</div>
+<table>
+  <thead><tr>${columns.map((c) => head(c.label, c.align)).join("")}</tr></thead>
+  <tbody>${rows}${totals}</tbody>
+</table>
+<div class="footer">
+  <span>${escapeHtml(company.name)} · ${escapeHtml(company.phone)} · ${escapeHtml(company.email)}</span>
+  <span>${escapeHtml(title)}</span>
+</div>
+</body></html>`;
+}
+
+function escapeHtml(s: string): string {
+  return s
+    .replace(/&/g, "&amp;")
+    .replace(/</g, "&lt;")
+    .replace(/>/g, "&gt;")
+    .replace(/"/g, "&quot;");
+}

apps/api/src/reports/reports.controller.ts

@@ -0,0 +1,130 @@
+import {
+  Body,
+  Controller,
+  Get,
+  Header,
+  Param,
+  Post,
+  Query,
+  Res,
+  UseGuards,
+} from "@nestjs/common";
+import type { Response } from "express";
+import { AuthenticatedGuard } from "../auth/authenticated.guard";
+import { ReportsService } from "./reports.service";
+import {
+  renderCsv,
+  renderPdf,
+  renderPrintHtml,
+  renderXlsx,
+} from "./outputs";
+import { findReport } from "./reports.registry";
+
+/**
+ * Reports routes. Every report is dispatched by slug; outputs are
+ * differentiated by `?format=...` (default `json`). Reads only — gated
+ * by AuthenticatedGuard alone, like every other read in the app.
+ */
+@UseGuards(AuthenticatedGuard)
+@Controller("reports")
+export class ReportsController {
+  constructor(private readonly reports: ReportsService) {}
+
+  /** Catalog of all registered reports (the /reportes index). */
+  @Get()
+  catalog() {
+    return { items: this.reports.catalog() };
+  }
+
+  /** Run a report and return the JSON result (rows + totals + the def's columns). */
+  @Get(":slug")
+  async runJson(
+    @Param("slug") slug: string,
+    @Query() query: Record<string, string | undefined>,
+  ) {
+    const def = findReport(slug);
+    const result = await this.reports.run(slug, query);
+    return { ...result, columns: def?.columns ?? [] };
+  }
+
+  /** CSV download. */
+  @Get(":slug/csv")
+  @Header("Content-Type", "text/csv; charset=utf-8")
+  async runCsv(
+    @Param("slug") slug: string,
+    @Query() query: Record<string, string | undefined>,
+    @Res() res: Response,
+  ) {
+    const def = findReport(slug);
+    const result = await this.reports.run(slug, query);
+    const filename = `${def?.title ?? slug}-${new Date().toISOString().slice(0, 10)}.csv`;
+    res.setHeader(
+      "Content-Disposition",
+      `attachment; filename="${filename.replace(/[^\wÀ-ſ .-]/g, "_")}"`,
+    );
+    res.send(renderCsv(def?.columns ?? [], result));
+  }
+
+  /** XLSX download. */
+  @Get(":slug/xlsx")
+  async runXlsx(
+    @Param("slug") slug: string,
+    @Query() query: Record<string, string | undefined>,
+    @Res() res: Response,
+  ) {
+    const def = findReport(slug);
+    const result = await this.reports.run(slug, query);
+    const filename = `${def?.title ?? slug}-${new Date().toISOString().slice(0, 10)}.xlsx`;
+    const buf = await renderXlsx(def?.columns ?? [], result);
+    res.setHeader(
+      "Content-Type",
+      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
+    );
+    res.setHeader(
+      "Content-Disposition",
+      `attachment; filename="${filename.replace(/[^\wÀ-ſ .-]/g, "_")}"`,
+    );
+    res.send(buf);
+  }
+
+  /** PDF download. */
+  @Get(":slug/pdf")
+  async runPdf(
+    @Param("slug") slug: string,
+    @Query() query: Record<string, string | undefined>,
+    @Res() res: Response,
+  ) {
+    const def = findReport(slug);
+    const result = await this.reports.run(slug, query);
+    const buf = await renderPdf(def?.columns ?? [], result, def?.title ?? slug);
+    const filename = `${def?.title ?? slug}-${new Date().toISOString().slice(0, 10)}.pdf`;
+    res.setHeader("Content-Type", "application/pdf");
+    res.setHeader(
+      "Content-Disposition",
+      `attachment; filename="${filename.replace(/[^\wÀ-ſ .-]/g, "_")}"`,
+    );
+    res.send(buf);
+  }
+
+  /** Browser-printable HTML view (the user hits Print → Save as PDF). */
+  @Get(":slug/print")
+  @Header("Content-Type", "text/html; charset=utf-8")
+  async runPrint(
+    @Param("slug") slug: string,
+    @Query() query: Record<string, string | undefined>,
+  ) {
+    const def = findReport(slug);
+    const result = await this.reports.run(slug, query);
+    return renderPrintHtml(def?.columns ?? [], result, def?.title ?? slug);
+  }
+
+  /** POST a customer-picker-driven report (statement). Mirrors GET to keep
+   *  the param contract simple: same body shape, same response. */
+  @Post(":slug")
+  async runPost(
+    @Param("slug") slug: string,
+    @Body() body: Record<string, string | undefined>,
+  ) {
+    return this.reports.run(slug, body);
+  }
+}

apps/api/src/reports/reports.module.ts

@@ -0,0 +1,9 @@
+import { Module } from "@nestjs/common";
+import { ReportsController } from "./reports.controller";
+import { ReportsService } from "./reports.service";
+
+@Module({
+  controllers: [ReportsController],
+  providers: [ReportsService],
+})
+export class ReportsModule {}

apps/api/src/reports/reports.registry.ts

@@ -0,0 +1,966 @@
+/**
+ * The catalog. One entry per report. Adding a new report is one new entry
+ * here — no new route, no new page, no new component.
+ *
+ * Filter behavior to keep in mind:
+ *   - Currency balances are NEVER summed across currencies (912 customers
+ *     carry both MXN and USD; the legacy data has no FX per row, so any
+ *     cross-currency total would be invented). Every report that touches
+ *     the ledger accepts a `currency` filter and reports per currency.
+ *   - Voided transactions must be excluded from totals (NOT_VOIDED). The
+ *     UI still shows them struck-through; the SQL drops them.
+ *   - The legacy `REPORTE DE EFECTIVO` covered cash receipts only. In the
+ *     new schema those are `Transaction` rows with `legacySourceTable` in
+ *     the EFECTIVO* set OR `checkNumber` null + amount > 0 (true cash).
+ */
+
+import { Prisma } from "@jorgecuadros/database";
+import {
+  intParam,
+  NOT_VOIDED,
+  parseDate,
+  type ReportDef,
+} from "./reports.types";
+
+/* ------------------------------------------------------------------ helpers */
+
+function nameOf(c: { name: string; nameMissing: boolean }): string {
+  return c.nameMissing ? "(sin nombre)" : c.name;
+}
+
+/* ------------------------------------------------------------------ reports */
+
+/**
+ * LISTADO EN ROJO — overdue customers worklist.
+ * Same data as the receivables worklist with balance=owing, but presented
+ * as a printable report rather than a paginated browser.
+ */
+const listadoEnRojo: ReportDef = {
+  slug: "listado-en-rojo",
+  title: "Clientes en rojo",
+  description:
+    "Cartera vencida: clientes con saldo deudor en la moneda seleccionada, " +
+    "ordenados del más antiguo al más reciente.",
+  domain: "estado-cuenta",
+  legacyName: "LISTADO EN ROJO",
+  format: "tabular",
+  params: [
+    {
+      key: "currency",
+      label: "Moneda",
+      kind: "select",
+      options: [
+        { value: "MXN", label: "MXN" },
+        { value: "USD", label: "USD" },
+      ],
+      defaultValue: "MXN",
+    },
+    {
+      key: "query",
+      label: "Buscar (nombre o ciudad)",
+      kind: "text",
+      placeholder: "Ej. Pérez, Tijuana…",
+    },
+  ],
+  columns: [
+    { key: "id", label: "#", type: "text" },
+    { key: "name", label: "Cliente", type: "text" },
+    { key: "city", label: "Ciudad", type: "text" },
+    { key: "movements", label: "Movs.", type: "number", align: "right" },
+    { key: "balance", label: "Saldo", type: "money", align: "right" },
+    { key: "lastMovement", label: "Último movimiento", type: "date" },
+  ],
+  async run(prisma, p) {
+    const currency = (p.currency === "USD" ? "USD" : "MXN") as "MXN" | "USD";
+    const q = p.query?.trim();
+    const nameFilter = q
+      ? Prisma.sql`AND (c.name LIKE ${`%${q}%`} OR c.city LIKE ${`%${q}%`})`
+      : Prisma.empty;
+
+    const bal =
+      currency === "USD"
+        ? Prisma.sql`SUM(CASE WHEN t.currency = 'USD' THEN t.amount ELSE 0 END)`
+        : Prisma.sql`SUM(CASE WHEN t.currency = 'MXN' THEN t.amount ELSE 0 END)`;
+
+    const rows = await prisma.$queryRaw<
+      Array<{
+        id: string;
+        name: string;
+        nameMissing: boolean;
+        city: string | null;
+        movements: bigint | number | string;
+        balance: Prisma.Decimal | null;
+        lastMovement: Date | null;
+      }>
+    >`
+      SELECT c.id, c.name, c.nameMissing, c.city,
+             COUNT(*) AS movements,
+             ${bal} AS balance,
+             MAX(t.transactionDate) AS lastMovement
+      FROM customers c
+      JOIN transactions t ON t.customerId = c.id
+      WHERE t.voidedAt IS NULL ${nameFilter}
+      GROUP BY c.id, c.name, c.nameMissing, c.city
+      HAVING ${bal} < -0.005
+      ORDER BY MAX(t.transactionDate) ASC, c.nameMissing ASC, c.name ASC
+    `;
+
+    let totalBalance = new Prisma.Decimal(0);
+    let totalMovs = 0;
+    const out = rows.map((r) => {
+      const b = r.balance ?? new Prisma.Decimal(0);
+      totalBalance = totalBalance.plus(b);
+      totalMovs += Number(r.movements);
+      return {
+        id: r.id.slice(0, 8),
+        name: nameOf(r),
+        city: r.city ?? "—",
+        movements: Number(r.movements),
+        balance: b.toFixed(2),
+        lastMovement: r.lastMovement
+          ? r.lastMovement.toISOString().slice(0, 10)
+          : "—",
+      };
+    });
+
+    return {
+      rows: out,
+      totals: {
+        customers: out.length,
+        movements: totalMovs,
+        balance: totalBalance.toFixed(2),
+        currency,
+      },
+      subtitle: `Moneda: ${currency} · ${out.length} clientes con saldo deudor`,
+    };
+  },
+};
+
+/**
+ * PAGOS NO EFECTUADOS (AGUA / LUZ / TEL).
+ * Customers enrolled in a service (by PropertyService.kind) with no
+ * related ledger charge in the last N days. Heuristic: any non-voided
+ * charge-type transaction in the window counts as "they paid". The
+ * report groups by property so the same customer with two water meters
+ * appears once per property.
+ */
+const pagosNoEfectuados: ReportDef = {
+  slug: "pagos-no-efectuados",
+  title: "Pagos no efectuados",
+  description:
+    "Clientes con un servicio contratado (agua, luz o teléfono) sin " +
+    "movimientos de cargo en los últimos N días. Heurística basada en el " +
+    "servicio registrado en la propiedad y la ausencia de cargos en el " +
+    "periodo seleccionado.",
+  domain: "servicios",
+  legacyName: "PAGOS NO EFECTUADOS AGUA/LUZ/TEL",
+  format: "tabular",
+  params: [
+    {
+      key: "serviceKind",
+      label: "Servicio",
+      kind: "select",
+      options: [
+        { value: "WATER", label: "Agua" },
+        { value: "ELECTRICITY", label: "Luz" },
+        { value: "PHONE", label: "Teléfono" },
+      ],
+      defaultValue: "WATER",
+    },
+    {
+      key: "days",
+      label: "Días sin movimiento",
+      kind: "number",
+      defaultValue: "60",
+    },
+    {
+      key: "currency",
+      label: "Moneda",
+      kind: "select",
+      options: [
+        { value: "MXN", label: "MXN" },
+        { value: "USD", label: "USD" },
+      ],
+      defaultValue: "MXN",
+    },
+  ],
+  columns: [
+    { key: "customerId", label: "Cliente #", type: "text" },
+    { key: "customerName", label: "Cliente", type: "text" },
+    { key: "propertyAddress", label: "Dirección", type: "text" },
+    { key: "accountNumber", label: "Cuenta / Medidor", type: "text" },
+    { key: "lastCharge", label: "Último cargo", type: "date" },
+    { key: "balance", label: "Saldo", type: "money", align: "right" },
+  ],
+  async run(prisma, p) {
+    const kind = (p.serviceKind ?? "WATER") as
+      | "WATER"
+      | "ELECTRICITY"
+      | "PHONE";
+    const days = intParam(p, "days", 60, 1, 365);
+    const currency = (p.currency === "USD" ? "USD" : "MXN") as "MXN" | "USD";
+    const cutoff = new Date(Date.now() - days * 86400000);
+
+    // Customers enrolled in the service on a non-archived property, with no
+    // charge-type transaction in the window. The subquery picks up
+    // *anything* the customer paid (any domain, any type) — close enough
+    // for the staff's "who's overdue" view; the precise per-service match
+    // would need a per-service typeId taxonomy that doesn't exist in the
+    // legacy data.
+    const rows = await prisma.$queryRaw<
+      Array<{
+        customerId: string;
+        customerName: string;
+        nameMissing: boolean;
+        propertyId: string;
+        propertyAddress: string | null;
+        accountNumber: string | null;
+        lastCharge: Date | null;
+        balance: Prisma.Decimal | null;
+      }>
+    >`
+      SELECT
+        c.id          AS customerId,
+        c.name        AS customerName,
+        c.nameMissing AS nameMissing,
+        pr.id         AS propertyId,
+        pr.addressLine1 AS propertyAddress,
+        ps.accountNumber AS accountNumber,
+        (SELECT MAX(t.transactionDate) FROM transactions t
+            WHERE t.customerId = c.id AND t.voidedAt IS NULL
+              AND t.amount < 0
+              AND t.transactionDate >= ${cutoff}) AS lastCharge,
+        (SELECT SUM(t.amount) FROM transactions t
+            WHERE t.customerId = c.id AND t.voidedAt IS NULL
+              AND t.currency = ${currency}) AS balance
+      FROM property_services ps
+      JOIN properties pr ON pr.id = ps.propertyId AND pr.archivedAt IS NULL
+      JOIN customers c   ON c.id = pr.customerId AND c.archivedAt IS NULL
+      WHERE ps.kind = ${kind} AND ps.active = 1
+        AND NOT EXISTS (
+          SELECT 1 FROM transactions t
+          WHERE t.customerId = c.id AND t.voidedAt IS NULL
+            AND t.amount < 0 AND t.transactionDate >= ${cutoff}
+        )
+      ORDER BY c.nameMissing ASC, c.name ASC, pr.addressLine1 ASC
+    `;
+
+    let totalBalance = new Prisma.Decimal(0);
+    const out = rows.map((r) => {
+      const b = r.balance ?? new Prisma.Decimal(0);
+      totalBalance = totalBalance.plus(b);
+      return {
+        customerId: r.customerId.slice(0, 8),
+        customerName: nameOf({
+          name: r.customerName,
+          nameMissing: r.nameMissing,
+        }),
+        propertyAddress: r.propertyAddress ?? "—",
+        accountNumber: r.accountNumber ?? "—",
+        lastCharge: r.lastCharge
+          ? r.lastCharge.toISOString().slice(0, 10)
+          : "—",
+        balance: b.toFixed(2),
+      };
+    });
+
+    return {
+      rows: out,
+      totals: {
+        rows: out.length,
+        balance: totalBalance.toFixed(2),
+        currency,
+      },
+      subtitle: `Servicio: ${
+        kind === "WATER" ? "Agua" : kind === "ELECTRICITY" ? "Luz" : "Teléfono"
+      } · ${days} días · ${out.length} propiedades sin cargo reciente`,
+    };
+  },
+};
+
+/**
+ * FALTANTES DE (AGUA / LUZ / TEL).
+ * Data-quality report: properties enrolled in a service that are missing
+ * the key identifier the legacy system required (account/meter/route).
+ * Different `faltante` per service kind in the legacy because the
+ * service's identifier fields differ; here we flag any of the three
+ * common identifiers being blank.
+ */
+const faltantes: ReportDef = {
+  slug: "faltantes",
+  title: "Faltantes de datos por servicio",
+  description:
+    "Calidad de datos: propiedades con un servicio contratado que no " +
+    "tienen número de cuenta, medidor o ruta registrado. El reporte que " +
+    "en la legacy corría como FALTANTES DE AGUA / LUZ / TEL.",
+  domain: "servicios",
+  legacyName: "FALTANTES DE AGUA/LUZ/TEL",
+  format: "tabular",
+  params: [
+    {
+      key: "serviceKind",
+      label: "Servicio",
+      kind: "select",
+      options: [
+        { value: "WATER", label: "Agua" },
+        { value: "ELECTRICITY", label: "Luz" },
+        { value: "PHONE", label: "Teléfono" },
+      ],
+      defaultValue: "WATER",
+    },
+  ],
+  columns: [
+    { key: "customerId", label: "Cliente #", type: "text" },
+    { key: "customerName", label: "Cliente", type: "text" },
+    { key: "propertyAddress", label: "Dirección", type: "text" },
+    { key: "missing", label: "Faltante", type: "text" },
+    { key: "dueDay", label: "Día de vencimiento", type: "text" },
+  ],
+  async run(prisma, p) {
+    const kind = (p.serviceKind ?? "WATER") as
+      | "WATER"
+      | "ELECTRICITY"
+      | "PHONE";
+
+    // A row per (property, missing field). The "missing" string describes
+    // what's blank so the report is self-explanatory when printed.
+    const rows = await prisma.$queryRaw<
+      Array<{
+        customerId: string;
+        customerName: string;
+        nameMissing: boolean;
+        propertyId: string;
+        propertyAddress: string | null;
+        dueDay: string | null;
+        missing: string;
+      }>
+    >`
+      SELECT
+        c.id          AS customerId,
+        c.name        AS customerName,
+        c.nameMissing AS nameMissing,
+        pr.id         AS propertyId,
+        pr.addressLine1 AS propertyAddress,
+        ps.dueDay     AS dueDay,
+        CASE
+          WHEN ps.accountNumber IS NULL OR ps.accountNumber = '' THEN 'Sin número de cuenta'
+          WHEN ps.meterNumber   IS NULL OR ps.meterNumber   = '' THEN 'Sin número de medidor'
+          WHEN ps.route         IS NULL OR ps.route         = '' THEN 'Sin ruta'
+          ELSE ''
+        END AS missing
+      FROM property_services ps
+      JOIN properties pr ON pr.id = ps.propertyId AND pr.archivedAt IS NULL
+      JOIN customers c   ON c.id = pr.customerId AND c.archivedAt IS NULL
+      WHERE ps.kind = ${kind} AND ps.active = 1
+        AND (
+          ps.accountNumber IS NULL OR ps.accountNumber = ''
+          OR ps.meterNumber IS NULL OR ps.meterNumber = ''
+          OR ps.route IS NULL OR ps.route = ''
+        )
+      ORDER BY c.nameMissing ASC, c.name ASC, pr.addressLine1 ASC
+    `;
+
+    const out = rows.map((r) => ({
+      customerId: r.customerId.slice(0, 8),
+      customerName: nameOf({
+        name: r.customerName,
+        nameMissing: r.nameMissing,
+      }),
+      propertyAddress: r.propertyAddress ?? "—",
+      missing: r.missing,
+      dueDay: r.dueDay ?? "—",
+    }));
+
+    return {
+      rows: out,
+      totals: { rows: out.length },
+      subtitle: `Servicio: ${
+        kind === "WATER" ? "Agua" : kind === "ELECTRICITY" ? "Luz" : "Teléfono"
+      } · ${out.length} propiedades con datos faltantes`,
+    };
+  },
+};
+
+/**
+ * REPORTE DE EFECTIVO — cash reconciliation.
+ * Credits in the EFECTIVO* legacy source tables OR with no cheque number
+ * (true cash) within a date range. Excludes voided rows. Matches the
+ * shape of the legacy REPORTE DE EFECTIVO report.
+ */
+const reporteDeEfectivo: ReportDef = {
+  slug: "reporte-de-efectivo",
+  title: "Reporte de efectivo",
+  description:
+    "Recibos de efectivo en el periodo seleccionado. Cubre los abonos " +
+    "provenientes de las tablas legacy EFECTIVO* y los créditos sin " +
+    "número de cheque (efectivo real). El match del reporte original.",
+  domain: "chequera",
+  legacyName: "REPORTE DE EFECTIVO",
+  format: "tabular",
+  params: [
+    { key: "from", label: "Desde", kind: "date" },
+    { key: "to", label: "Hasta", kind: "date", endOfDay: true },
+    {
+      key: "currency",
+      label: "Moneda",
+      kind: "select",
+      options: [
+        { value: "MXN", label: "MXN" },
+        { value: "USD", label: "USD" },
+      ],
+      defaultValue: "MXN",
+    },
+  ],
+  columns: [
+    { key: "date", label: "Fecha", type: "date" },
+    { key: "customerName", label: "Cliente", type: "text" },
+    { key: "concept", label: "Concepto", type: "text" },
+    { key: "source", label: "Origen", type: "text" },
+    { key: "amount", label: "Monto", type: "money", align: "right" },
+  ],
+  async run(prisma, p) {
+    const from = parseDate(p.from);
+    const to = parseDate(p.to, true);
+    const currency = (p.currency === "USD" ? "USD" : "MXN") as "MXN" | "USD";
+
+    const ands: Prisma.TransactionWhereInput[] = [
+      NOT_VOIDED,
+      { amount: { gt: 0 } },
+      { currency },
+      {
+        OR: [
+          { legacySourceTable: { in: ["EFECTIVO", "EFECTIVO_BACKUP"] } },
+          {
+            AND: [
+              { checkNumber: null },
+              { legacySourceTable: { not: "CHEQUE FM3" } },
+            ],
+          },
+        ],
+      },
+    ];
+    if (from || to) {
+      ands.push({
+        transactionDate: {
+          ...(from ? { gte: from } : {}),
+          ...(to ? { lte: to } : {}),
+        },
+      });
+    }
+
+    const rows = await prisma.transaction.findMany({
+      where: { AND: ands },
+      orderBy: { transactionDate: "asc" },
+      select: {
+        transactionDate: true,
+        amount: true,
+        reference: true,
+        checkNumber: true,
+        message: true,
+        legacySourceTable: true,
+        type: { select: { nameEs: true, nameEn: true } },
+        customer: { select: { name: true, nameMissing: true } },
+      },
+    });
+
+    let total = new Prisma.Decimal(0);
+    const out = rows.map((r) => {
+      total = total.plus(r.amount);
+      return {
+        date: r.transactionDate.toISOString().slice(0, 10),
+        customerName: nameOf(r.customer),
+        concept: r.message ?? r.type?.nameEs ?? r.type?.nameEn ?? "—",
+        source: r.legacySourceTable ?? "—",
+        amount: r.amount.toFixed(2),
+      };
+    });
+
+    return {
+      rows: out,
+      totals: {
+        rows: out.length,
+        total: total.toFixed(2),
+        currency,
+      },
+      subtitle: `Efectivo · ${currency} · ${out.length} recibos${
+        from ? ` desde ${p.from}` : ""
+      }${to ? ` hasta ${p.to}` : ""}`,
+    };
+  },
+};
+
+/**
+ * VIGENTE (LIC / INCEN / MULT / …) — policies up for renewal.
+ * Wraps the policy listing with status=expiring and a policy-type filter,
+ * sorted by soonest expiry. The legacy VIGENTE LIC / INCEN / MULT
+ * reports are the same data; the new filter is a dropdown.
+ */
+const vigente: ReportDef = {
+  slug: "vigente",
+  title: "Pólizas por vencer",
+  description:
+    "Pólizas que vencen en los próximos N días, filtradas por ramo. " +
+    "Equivalente a los reportes VIGENTE LIC / INCEN / MULT de la legacy.",
+  domain: "polizas",
+  legacyName: "VIGENTE LIC/INCEN/MULT",
+  format: "tabular",
+  params: [
+    {
+      key: "typeName",
+      label: "Ramo",
+      kind: "select",
+      options: [
+        { value: "LICENCIAS", label: "Licencias" },
+        { value: "INCENDIO", label: "Incendio" },
+        { value: "MULT", label: "Multirriesgo" },
+        { value: "MCA2", label: "MCA2 (auto)" },
+        { value: "ME", label: "ME" },
+        { value: "MF", label: "MF" },
+        { value: "RC", label: "RC" },
+        { value: "INCEN", label: "Incen" },
+        { value: "TAMPL", label: "TAMPL" },
+        { value: "FAMILIAR", label: "Familiar" },
+      ],
+      defaultValue: "LICENCIAS",
+    },
+    {
+      key: "days",
+      label: "Ventana (días)",
+      kind: "number",
+      defaultValue: "30",
+    },
+  ],
+  columns: [
+    { key: "policyNumber", label: "Póliza", type: "text" },
+    { key: "customerName", label: "Cliente", type: "text" },
+    { key: "provider", label: "Aseguradora", type: "text" },
+    { key: "agent", label: "Agente", type: "text" },
+    { key: "from", label: "Desde", type: "date" },
+    { key: "to", label: "Vence", type: "date" },
+    { key: "daysToExpire", label: "Días", type: "number", align: "right" },
+    { key: "premium", label: "Prima neta", type: "money", align: "right" },
+  ],
+  async run(prisma, p) {
+    const typeName = p.typeName ?? "LICENCIAS";
+    const days = intParam(p, "days", 30, 1, 365);
+    const now = new Date();
+    const today = new Date(
+      Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()),
+    );
+    const soon = new Date(today.getTime() + days * 86400000);
+
+    const rows = await prisma.policy.findMany({
+      where: {
+        policyType: { name: typeName },
+        archivedAt: null,
+        policyTo: { gte: today, lte: soon },
+      },
+      orderBy: { policyTo: "asc" },
+      select: {
+        policyNumber: true,
+        policyFrom: true,
+        policyTo: true,
+        netPremium: true,
+        agentName: true,
+        customer: { select: { name: true, nameMissing: true } },
+        insuranceProvider: { select: { name: true } },
+      },
+    });
+
+    let totalPremium = new Prisma.Decimal(0);
+    const out = rows.map((r) => {
+      const daysTo = r.policyTo
+        ? Math.round(
+            (r.policyTo.getTime() - today.getTime()) / 86400000,
+          )
+        : 0;
+      if (r.netPremium) totalPremium = totalPremium.plus(r.netPremium);
+      return {
+        policyNumber: r.policyNumber,
+        customerName: nameOf(r.customer),
+        provider: r.insuranceProvider?.name ?? "—",
+        agent: r.agentName ?? "—",
+        from: r.policyFrom ? r.policyFrom.toISOString().slice(0, 10) : "—",
+        to: r.policyTo ? r.policyTo.toISOString().slice(0, 10) : "—",
+        daysToExpire: daysTo,
+        premium: r.netPremium ? r.netPremium.toFixed(2) : "0.00",
+      };
+    });
+
+    return {
+      rows: out,
+      totals: {
+        rows: out.length,
+        premium: totalPremium.toFixed(2),
+      },
+      subtitle: `Ramo: ${typeName} · ${days} días · ${out.length} pólizas por vencer`,
+    };
+  },
+};
+
+/**
+ * AVISO DE RENOVACION — insurance renewal notice.
+ *
+ * Replaces ~40 legacy report clones (one per carrier per coverage tier —
+ * `AMPL R RENEW X MES NEW ATLAS 13`, `... QUALITAS ...`, `LIC RENEW X
+ * VENCE ATLAS 2013`, etc., see docs/RENEWAL_NOTICES.md) with one
+ * parameterized report: pick the ramo, the expiry month/year, which
+ * notice generation (1st/2nd/3rd, mirroring the legacy RENEW/RENEW2/
+ * RENEW3 escalation), and optionally a carrier filter.
+ *
+ * The legacy reports hardcoded per-policy figures (deductible, CSL limit,
+ * premium) as static label text re-typed by hand for every new rate/
+ * carrier clone. Here they're read from real columns / `coveragesJson`
+ * (see docs/RENEWAL_NOTICES.md's column-mapping table) so one template
+ * covers every carrier and tier instead of a clone per combination.
+ *
+ * `sentStatus` is read from `RenewalNotice` (schema.prisma) — the
+ * replacement for the legacy `CONTROL <ramo> RENEW[2/3] X MES` paper log
+ * — but this report is read-only; marking a notice as sent is a separate
+ * mutation (not yet built) that would upsert `RenewalNotice` by
+ * `[policyId, generation]`.
+ */
+const avisoRenovacion: ReportDef = {
+  slug: "aviso-renovacion",
+  title: "Aviso de renovación",
+  description:
+    "Cartas de aviso de renovación para pólizas por vencer en el mes y " +
+    "año seleccionados, con la generación de aviso (1a/2a/3a) y filtro " +
+    "opcional por aseguradora. Sustituye a los ~40 reportes clonados por " +
+    "aseguradora/cobertura de la legacy (ver docs/RENEWAL_NOTICES.md).",
+  domain: "polizas",
+  legacyName:
+    "AMPL R RENEW X MES NEW ATLAS 13 / RC RENEW X MES NEW ATLAS 13 / " +
+    "LIC RENEW X VENCE ATLAS 2013 / RCR RENEW X MES NEWATLAS 2013 (y " +
+    "sus clones por aseguradora y cobertura)",
+  format: "letter",
+  params: [
+    {
+      key: "policyType",
+      label: "Ramo",
+      kind: "select",
+      options: [
+        { value: "AUTO", label: "Auto" },
+        { value: "LICENCIAS", label: "Licencias" },
+        { value: "INCENDIO", label: "Incendio" },
+        { value: "MULT", label: "Multirriesgo" },
+        { value: "M_EMPR", label: "M Empresarial" },
+      ],
+      defaultValue: "AUTO",
+    },
+    {
+      key: "month",
+      label: "Mes de vencimiento (1-12)",
+      kind: "number",
+      defaultValue: String(new Date().getUTCMonth() + 1),
+    },
+    {
+      key: "year",
+      label: "Año de vencimiento",
+      kind: "number",
+      defaultValue: String(new Date().getUTCFullYear()),
+    },
+    {
+      key: "generation",
+      label: "Generación de aviso",
+      kind: "select",
+      options: [
+        { value: "1", label: "1er aviso" },
+        { value: "2", label: "2o aviso" },
+        { value: "3", label: "3er aviso" },
+      ],
+      defaultValue: "1",
+    },
+    {
+      key: "provider",
+      label: "Aseguradora (opcional)",
+      kind: "text",
+      placeholder: "Ej. ATLAS, QUALITAS…",
+    },
+  ],
+  // Flat columns so CSV/XLSX/generic PDF exports stay useful even though
+  // the on-screen view renders each row as a full letter (LetterLayout in
+  // ReportRunner.tsx) — same trade-off edoCuentaDatos makes for "statement".
+  columns: [
+    { key: "policyNumber", label: "Póliza", type: "text" },
+    { key: "customerName", label: "Cliente", type: "text" },
+    { key: "provider", label: "Aseguradora", type: "text" },
+    { key: "policyTo", label: "Vence", type: "date" },
+    { key: "netPremium", label: "Prima neta", type: "money", align: "right" },
+    { key: "total", label: "Total", type: "money", align: "right" },
+    { key: "generation", label: "Generación", type: "number" },
+    { key: "sentAt", label: "Enviado", type: "date" },
+  ],
+  async run(prisma, p) {
+    const typeName = p.policyType ?? "AUTO";
+    const month = intParam(p, "month", new Date().getUTCMonth() + 1, 1, 12);
+    const year = intParam(p, "year", new Date().getUTCFullYear(), 1990, 2100);
+    const generation = intParam(p, "generation", 1, 1, 3);
+    const provider = p.provider?.trim();
+
+    const from = new Date(Date.UTC(year, month - 1, 1));
+    const to = new Date(Date.UTC(year, month, 1));
+
+    const rows = await prisma.policy.findMany({
+      where: {
+        policyType: { name: typeName },
+        archivedAt: null,
+        policyTo: { gte: from, lt: to },
+        ...(provider
+          ? { insuranceProvider: { name: { contains: provider } } }
+          : {}),
+      },
+      orderBy: { policyTo: "asc" },
+      select: {
+        id: true,
+        policyNumber: true,
+        policyTo: true,
+        netPremium: true,
+        policyFee: true,
+        total: true,
+        currency: true,
+        coveragesJson: true,
+        customer: { select: { name: true, nameMissing: true } },
+        insuranceProvider: { select: { name: true } },
+        vehicles: {
+          take: 1,
+          select: {
+            make: true,
+            model: true,
+            modelYear: true,
+            bodyType: true,
+            engineNumber: true,
+            licensePlate: true,
+          },
+        },
+        renewalNotices: {
+          where: { generation },
+          select: { sentAt: true, channel: true },
+        },
+      },
+    });
+
+    let totalPremium = new Prisma.Decimal(0);
+    let sentCount = 0;
+    const out = rows.map((r) => {
+      if (r.netPremium) totalPremium = totalPremium.plus(r.netPremium);
+      const notice = r.renewalNotices[0];
+      if (notice?.sentAt) sentCount++;
+      // Legacy coverage columns not modeled as first-class Policy fields —
+      // see docs/RENEWAL_NOTICES.md's column-mapping table. Keys are best-
+      // effort (derived from the source schema, not yet verified against a
+      // live migrated DB) — confirm before relying on them in production.
+      const cov = (r.coveragesJson ?? {}) as Record<string, unknown>;
+      return {
+        __kind: "letter",
+        policyId: r.id,
+        policyNumber: r.policyNumber,
+        customerName: nameOf(r.customer),
+        provider: r.insuranceProvider?.name ?? "—",
+        policyTo: r.policyTo ? r.policyTo.toISOString().slice(0, 10) : "—",
+        netPremium: r.netPremium ? r.netPremium.toFixed(2) : null,
+        policyFee: r.policyFee ? r.policyFee.toFixed(2) : null,
+        total: r.total ? r.total.toFixed(2) : null,
+        currency: r.currency,
+        coverageDays: cov.cobertura ?? null,
+        cslLimit: cov.csl_limite ?? null,
+        medicalCoverage: cov.gastos_medico ?? null,
+        propertyDamage: cov.propiedades ?? null,
+        perPersonLiability: cov.personas ?? null,
+        additionalService: cov.servicio_adicional ?? cov.servicio_adiconal ?? null,
+        vehicle: r.vehicles[0]
+          ? {
+              make: r.vehicles[0].make,
+              model: r.vehicles[0].model,
+              modelYear: r.vehicles[0].modelYear,
+              bodyType: r.vehicles[0].bodyType,
+              engineNumber: r.vehicles[0].engineNumber,
+              licensePlate: r.vehicles[0].licensePlate,
+            }
+          : null,
+        generation,
+        sentAt: notice?.sentAt
+          ? notice.sentAt.toISOString().slice(0, 10)
+          : null,
+      };
+    });
+
+    return {
+      rows: out,
+      totals: {
+        cartas: out.length,
+        enviadas: sentCount,
+        pendientes: out.length - sentCount,
+        primaTotal: totalPremium.toFixed(2),
+      },
+      subtitle: `Ramo: ${typeName} · vencen ${String(month).padStart(2, "0")}/${year} · generación ${generation}${
+        provider ? ` · aseguradora: ${provider}` : ""
+      } · ${out.length} avisos`,
+    };
+  },
+};
+
+/**
+ * EDO CUENTA DATOS — per-customer account statement.
+ * Wraps the existing BillingService.statement() output. The full layout
+ * (header, balance per currency, by-domain split, by-type breakdown,
+ * full movement list with running balance) is rendered by the statement
+ * page; this report is the same data with print/PDF/CSV/XLSX outputs.
+ */
+const edoCuentaDatos: ReportDef = {
+  slug: "edo-cuenta-datos",
+  title: "Estado de cuenta",
+  description:
+    "Estado de cuenta de un cliente: saldos por moneda, desglose por " +
+    "ramo y concepto, y el historial completo de movimientos con saldo " +
+    "corrido. El reporte del cliente final.",
+  domain: "estado-cuenta",
+  legacyName: "EDO CUENTA DATOS",
+  format: "statement",
+  params: [
+    { key: "customerId", label: "Cliente", kind: "customer-picker" },
+  ],
+  columns: [
+    // Statement rows carry synthetic `__kind` discriminators instead of
+    // column keys; the runner renders the special cases inline. These
+    // columns drive CSV/XLSX when the user wants a flat movement export.
+    { key: "date", label: "Fecha", type: "date" },
+    { key: "concept", label: "Concepto", type: "text" },
+    { key: "reference", label: "Referencia", type: "text" },
+    { key: "amount", label: "Cargo / Abono", type: "money", align: "right" },
+    { key: "balanceAfter", label: "Saldo", type: "money", align: "right" },
+  ],
+  async run(prisma, p) {
+    const customerId = p.customerId;
+    if (!customerId) {
+      return { rows: [], subtitle: "Selecciona un cliente" };
+    }
+    const customer = await prisma.customer.findUnique({
+      where: { id: customerId },
+      select: {
+        id: true,
+        name: true,
+        nameMissing: true,
+        addressLine1: true,
+        city: true,
+        state: true,
+        email: true,
+        phone: true,
+      },
+    });
+    if (!customer) return { rows: [], subtitle: "Cliente no encontrado" };
+
+    // Reuse the same NOT_VOIDED + STATEMENT_EXCLUDED_SOURCE_TABLES filter
+    // as BillingService.statement so the numbers match what the customer
+    // already sees in /estado-cuenta/[id].
+    const rows = await prisma.transaction.findMany({
+      where: {
+        customerId,
+        voidedAt: null,
+        legacySourceTable: {
+          notIn: [
+            "EFECTIVO",
+            "EFECTIVO_BACKUP",
+            "EFECTIVO FM3",
+            "CHEQUE FM3",
+            "IVA 2015",
+          ],
+        },
+      },
+      orderBy: [{ transactionDate: "asc" }, { id: "asc" }],
+      select: {
+        id: true,
+        transactionDate: true,
+        domain: true,
+        amount: true,
+        currency: true,
+        reference: true,
+        period: true,
+        checkNumber: true,
+        message: true,
+        legacySourceTable: true,
+        type: { select: { nameEs: true, nameEn: true } },
+      },
+    });
+
+    // Compute running balance per currency, then return newest-first.
+    const running = new Map<string, Prisma.Decimal>();
+    const movements = rows.map((r) => {
+      const prev = running.get(r.currency) ?? new Prisma.Decimal(0);
+      const next = prev.plus(r.amount);
+      running.set(r.currency, next);
+      return {
+        date: r.transactionDate.toISOString().slice(0, 10),
+        domain: r.domain,
+        currency: r.currency,
+        reference: r.reference ?? "",
+        period: r.period ?? "",
+        checkNumber: r.checkNumber ?? "",
+        concept: r.type?.nameEs ?? r.type?.nameEn ?? "—",
+        amount: r.amount.toFixed(2),
+        balanceAfter: next.toFixed(2),
+      };
+    });
+    movements.reverse();
+
+    // Per-currency summary + per-domain breakdown.
+    const perCurrency = new Map<
+      string,
+      { currency: string; charges: Prisma.Decimal; credits: Prisma.Decimal; count: number }
+    >();
+    for (const r of rows) {
+      const c =
+        perCurrency.get(r.currency) ??
+        {
+          currency: r.currency,
+          charges: new Prisma.Decimal(0),
+          credits: new Prisma.Decimal(0),
+          count: 0,
+        };
+      c.count += 1;
+      if (r.amount.lessThan(0)) c.charges = c.charges.plus(r.amount);
+      else c.credits = c.credits.plus(r.amount);
+      perCurrency.set(r.currency, c);
+    }
+
+    return {
+      rows: [
+        {
+          __kind: "header",
+          name: nameOf(customer),
+          address: customer.addressLine1 ?? "",
+          city: [customer.city, customer.state].filter(Boolean).join(", "),
+          phone: customer.phone ?? "",
+          email: customer.email ?? "",
+        },
+        ...[...perCurrency.values()].map((c) => ({
+          __kind: "summary",
+          currency: c.currency,
+          charges: c.charges.toFixed(2),
+          credits: c.credits.toFixed(2),
+          balance: c.charges.plus(c.credits).toFixed(2),
+          count: c.count,
+        })),
+        { __kind: "movements-header" },
+        ...movements,
+      ],
+      subtitle: `${nameOf(customer)} · ${rows.length} movimientos`,
+    };
+  },
+};
+
+/* ------------------------------------------------------------------ export */
+
+export const REPORTS: ReportDef[] = [
+  listadoEnRojo,
+  pagosNoEfectuados,
+  faltantes,
+  reporteDeEfectivo,
+  vigente,
+  avisoRenovacion,
+  edoCuentaDatos,
+];
+
+export function findReport(slug: string): ReportDef | undefined {
+  return REPORTS.find((r) => r.slug === slug);
+}

apps/api/src/reports/reports.service.ts

@@ -0,0 +1,45 @@
+import { Injectable, NotFoundException } from "@nestjs/common";
+import { PrismaService } from "../prisma/prisma.service";
+import { findReport, REPORTS } from "./reports.registry";
+import type { ReportDef } from "./reports.types";
+
+/**
+ * The reports service. Two responsibilities:
+ *  1. Run a report by slug with the given params — just dispatch.
+ *  2. Return the catalog for the /reportes index page.
+ *
+ * Output rendering (CSV/XLSX/PDF/HTML print) lives in `outputs.ts`; this
+ * service is data only. The controller maps URLs to (slug, format) and
+ * hands the result to outputs.
+ */
+@Injectable()
+export class ReportsService {
+  constructor(private readonly prisma: PrismaService) {}
+
+  /** List every registered report, in display order. */
+  catalog(): Array<{
+    slug: string;
+    title: string;
+    description: string;
+    domain: string;
+    legacyName: string | null;
+    format: string;
+    params: ReportDef["params"];
+  }> {
+    return REPORTS.map((r) => ({
+      slug: r.slug,
+      title: r.title,
+      description: r.description,
+      domain: r.domain,
+      legacyName: r.legacyName,
+      format: r.format,
+      params: r.params,
+    }));
+  }
+
+  async run(slug: string, params: Record<string, string | undefined>) {
+    const def = findReport(slug);
+    if (!def) throw new NotFoundException(`Reporte "${slug}" no encontrado`);
+    return def.run(this.prisma, params);
+  }
+}

apps/api/src/reports/reports.types.ts

@@ -0,0 +1,142 @@
+/**
+ * The reports module — plan step 10.
+ *
+ * Each "report" is one entry in `reports.registry.ts`. The entry declares
+ * its slug (URL id), title, what filters it accepts, what columns it
+ * returns, and a `run` function that produces the data from Prisma. The
+ * service dispatches on slug; the controller exposes JSON + CSV + XLSX +
+ * PDF + HTML print; the catalog endpoint exposes the registry itself so
+ * the `/reportes` page can render the same data.
+ *
+ * Output philosophy: a report returns a uniform shape — `columns` (typed
+ * schema) + `rows` (any[] of values matching the column types) + `totals`
+ * (record of column key → summary value). All three output formats
+ * (CSV/XLSX/PDF/print) derive from this same shape so adding a new
+ * report is one entry, never a per-format template.
+ */
+
+import { Prisma } from "@jorgecuadros/database";
+import { PrismaService } from "../prisma/prisma.service";
+
+/** Top-level grouping for the catalog page; matches the existing nav. */
+export type ReportDomain =
+  | "clientes"
+  | "polizas"
+  | "servicios"
+  | "estado-cuenta"
+  | "chequera";
+
+/** How the runner should render rows: a grid, a per-customer statement, or
+ *  one printable letter per row (e.g. renewal notices — see `format:
+ *  "letter"` reports for the `__kind: "letter"` row shape they emit). */
+export type ReportFormat = "tabular" | "statement" | "letter";
+
+/** Filter controls the report's UI should render. */
+export type ParamDef =
+  | {
+      key: string;
+      label: string;
+      kind: "text" | "number";
+      placeholder?: string;
+      defaultValue?: string;
+    }
+  | {
+      key: string;
+      label: string;
+      kind: "date";
+      /** Inclusive bound, true for `to`, false for `from`. */
+      endOfDay?: boolean;
+      defaultValue?: string;
+    }
+  | {
+      key: string;
+      label: string;
+      kind: "select";
+      options: { value: string; label: string }[];
+      defaultValue?: string;
+    }
+  | {
+      key: string;
+      label: string;
+      kind: "customer-picker";
+    };
+
+/** One column of the output table. */
+export interface ColumnDef {
+  key: string;
+  label: string;
+  /** Render hint for the on-screen + print table. */
+  type: "text" | "number" | "money" | "date";
+  /** Right-align numbers/money; default false (left). */
+  align?: "left" | "right";
+  /** Used for column-width hints in the print/PDF layout. */
+  width?: number;
+}
+
+/** Shape every report's `run` resolves to. Columns come from the def. */
+export interface ReportResult {
+  rows: Array<Record<string, unknown>>;
+  totals?: Record<string, string | number>;
+  /** Optional free-form subtitle for print/PDF (e.g. date range, scope). */
+  subtitle?: string;
+}
+
+/** A report's static declaration. */
+export interface ReportDef {
+  slug: string;
+  title: string;
+  description: string;
+  domain: ReportDomain;
+  /** The original Access report name (per docs/LEGACY_DATABASES_OBJECTS.md)
+   *  for traceability. Null when this is a new report with no legacy equiv. */
+  legacyName: string | null;
+  format: ReportFormat;
+  params: ParamDef[];
+  columns: ColumnDef[];
+  /**
+   * Run the report. Receives the Prisma client and the validated params
+   * record (keys are the `key` from ParamDef, values are the strings the
+   * runner collected; numeric/date params arrive as strings — the report
+   * parses them). Must apply the same NOT_VOIDED filter on transactions as
+   * the billing module so totals match.
+   */
+  run: (
+    prisma: PrismaService,
+    params: Record<string, string | undefined>,
+  ) => Promise<ReportResult>;
+}
+
+/** A typed bag of helpers for the report functions. */
+export interface ReportCtx {
+  prisma: PrismaService;
+  params: Record<string, string | undefined>;
+}
+
+/** Helper: a `YYYY-MM-DD` bound; unparseable is undefined. */
+export function parseDate(
+  v: string | undefined,
+  endOfDay = false,
+): Date | undefined {
+  if (!v) return undefined;
+  const d = new Date(endOfDay ? `${v}T23:59:59.999Z` : `${v}T00:00:00.000Z`);
+  return Number.isNaN(d.getTime()) ? undefined : d;
+}
+
+/** Helper: integer param with default. */
+export function intParam(
+  p: Record<string, string | undefined>,
+  key: string,
+  def: number,
+  min = 1,
+  max = 1000,
+): number {
+  const n = Number(p[key]);
+  if (!Number.isFinite(n)) return def;
+  return Math.min(max, Math.max(min, Math.round(n)));
+}
+
+/** Helper: not-voided filter, shared with billing.service. */
+export const NOT_VOIDED: Prisma.TransactionWhereInput = { voidedAt: null };
+export const NOT_VOIDED_BANK: Prisma.BankTransactionWhereInput = {
+  voidedAt: null,
+};

apps/api/src/storage/storage.module.ts

@@ -0,0 +1,10 @@
+import { Global, Module } from "@nestjs/common";
+import { StorageService } from "./storage.service";
+
+/** Global so any feature module can inject StorageService without re-importing. */
+@Global()
+@Module({
+  providers: [StorageService],
+  exports: [StorageService],
+})
+export class StorageModule {}

apps/api/src/storage/storage.service.ts

@@ -0,0 +1,122 @@
+import {
+  Injectable,
+  Logger,
+  OnModuleInit,
+  ServiceUnavailableException,
+} from "@nestjs/common";
+import { ConfigService } from "@nestjs/config";
+import {
+  CreateBucketCommand,
+  DeleteObjectCommand,
+  GetObjectCommand,
+  HeadBucketCommand,
+  PutObjectCommand,
+  S3Client,
+} from "@aws-sdk/client-s3";
+import type { Readable } from "node:stream";
+
+/**
+ * S3 / MinIO object storage for document blobs. MySQL keeps only the pointer
+ * (`storageKey`) + metadata; the bytes live here. Same bucket the migration's
+ * `blob_extract.py` writes to, so keys stay under the `service/…` and
+ * `policy/…` prefixes it established.
+ *
+ * Env (see deploy/.env.dev): S3_ENDPOINT, S3_BUCKET, and creds — S3_ACCESS_KEY
+ * / S3_SECRET_KEY, falling back to MINIO_ROOT_USER / MINIO_ROOT_PASSWORD so a
+ * single MinIO credential set drives both the migration and the API.
+ */
+@Injectable()
+export class StorageService implements OnModuleInit {
+  private readonly logger = new Logger(StorageService.name);
+  private readonly client: S3Client | null;
+  readonly bucket: string;
+
+  constructor(config: ConfigService) {
+    const endpoint = config.get<string>("S3_ENDPOINT");
+    this.bucket = config.get<string>("S3_BUCKET") ?? "jorgecuadros-documents";
+    const accessKeyId =
+      config.get<string>("S3_ACCESS_KEY") ?? config.get<string>("MINIO_ROOT_USER");
+    const secretAccessKey =
+      config.get<string>("S3_SECRET_KEY") ?? config.get<string>("MINIO_ROOT_PASSWORD");
+
+    if (!endpoint || !accessKeyId || !secretAccessKey) {
+      this.logger.warn(
+        "Object storage not configured (missing S3_ENDPOINT / credentials); " +
+          "document upload & download are disabled.",
+      );
+      this.client = null;
+      return;
+    }
+
+    this.client = new S3Client({
+      endpoint,
+      region: config.get<string>("S3_REGION") ?? "us-east-1",
+      credentials: { accessKeyId, secretAccessKey },
+      forcePathStyle: true, // MinIO needs path-style addressing
+    });
+  }
+
+  /** Best-effort bucket check on boot; never blocks API startup. */
+  async onModuleInit() {
+    if (!this.client) return;
+    try {
+      await this.client.send(new HeadBucketCommand({ Bucket: this.bucket }));
+    } catch {
+      try {
+        await this.client.send(new CreateBucketCommand({ Bucket: this.bucket }));
+        this.logger.log(`Created bucket "${this.bucket}".`);
+      } catch (err) {
+        this.logger.warn(
+          `Could not verify/create bucket "${this.bucket}": ${(err as Error).message}`,
+        );
+      }
+    }
+  }
+
+  private require(): S3Client {
+    if (!this.client) {
+      throw new ServiceUnavailableException(
+        "El almacenamiento de documentos no está configurado.",
+      );
+    }
+    return this.client;
+  }
+
+  async put(key: string, body: Buffer, contentType?: string): Promise<void> {
+    await this.require().send(
+      new PutObjectCommand({
+        Bucket: this.bucket,
+        Key: key,
+        Body: body,
+        ContentType: contentType,
+      }),
+    );
+  }
+
+  async getStream(key: string): Promise<{
+    stream: Readable;
+    contentType?: string;
+    contentLength?: number;
+  }> {
+    const out = await this.require().send(
+      new GetObjectCommand({ Bucket: this.bucket, Key: key }),
+    );
+    return {
+      stream: out.Body as Readable,
+      contentType: out.ContentType,
+      contentLength: out.ContentLength,
+    };
+  }
+
+  /** Best-effort blob delete; a missing object is not an error. */
+  async delete(key: string): Promise<void> {
+    if (!this.client) return;
+    try {
+      await this.client.send(
+        new DeleteObjectCommand({ Bucket: this.bucket, Key: key }),
+      );
+    } catch (err) {
+      this.logger.warn(`Failed to delete blob "${key}": ${(err as Error).message}`);
+    }
+  }
+}

apps/api/src/storage/upload-file.ts

@@ -0,0 +1,32 @@
+import { extname } from "node:path";
+
+/** Multer file shape we rely on (subset of Express.Multer.File). */
+export interface UploadedFileLike {
+  buffer: Buffer;
+  originalname?: string;
+  mimetype?: string;
+  size?: number;
+}
+
+const MIME_EXT: Record<string, string> = {
+  "application/pdf": ".pdf",
+  "image/jpeg": ".jpg",
+  "image/png": ".png",
+  "image/gif": ".gif",
+  "image/tiff": ".tif",
+  "image/bmp": ".bmp",
+};
+
+/** File extension for a stored blob, from the original name, else the mimetype. */
+export function extForUpload(file: UploadedFileLike): string {
+  const fromName = file.originalname ? extname(file.originalname).toLowerCase() : "";
+  if (fromName) return fromName;
+  return (file.mimetype && MIME_EXT[file.mimetype]) || "";
+}
+
+/** Download filename for a stored document, from its key + document type. */
+export function downloadName(storageKey: string, documentType: string): string {
+  const ext = extname(storageKey) || "";
+  const base = documentType.replace(/[^\w.-]+/g, "_") || "document";
+  return base.toLowerCase().endsWith(ext.toLowerCase()) ? base : `${base}${ext}`;
+}

apps/api/src/users/users.controller.ts

@@ -1,7 +1,9 @@
 import {
   Body,
   Controller,
+  Delete,
   Get,
+  HttpCode,
   Param,
   Patch,
   Post,
@@ -69,4 +71,12 @@ export class UsersController {
     void this.audit.log(this.actingId(req), "user.reset_password", { userId: id });
     return user;
   }
+
+  @Delete(":id")
+  @HttpCode(204)
+  async remove(@Param("id") id: string, @Req() req: Request) {
+    const actingId = this.actingId(req);
+    await this.users.remove(id, actingId);
+    void this.audit.log(actingId, "user.delete", { userId: id });
+  }
 }

apps/api/src/users/users.service.ts

@@ -111,6 +111,30 @@ export class UsersService {
     });
   }
 
+  /**
+   * Hard-delete a user. The schema's ActivityLog.userId FK would otherwise
+   * block the row (default `Restrict`), so null it out in the same
+   * transaction. Rows + the actor id captured in the `message` JSON stay
+   * intact for the audit trail.
+   */
+  async remove(id: string, actingUserId: string): Promise<void> {
+    if (id === actingUserId) {
+      throw new BadRequestException("No puede eliminar su propia cuenta");
+    }
+    await this.ensureExists(id);
+    try {
+      await this.prisma.$transaction([
+        this.prisma.activityLog.updateMany({
+          where: { userId: id },
+          data: { userId: null },
+        }),
+        this.prisma.user.delete({ where: { id } }),
+      ]);
+    } catch (e) {
+      throw this.mapError(e);
+    }
+  }
+
   private async ensureExists(id: string): Promise<void> {
     const found = await this.prisma.user.findUnique({ where: { id }, select: { id: true } });
     if (!found) throw new NotFoundException(`Usuario ${id} no encontrado`);

apps/web/package.json

@@ -3,7 +3,7 @@
   "version": "0.1.0",
   "private": true,
   "scripts": {
-    "dev": "next dev",
+    "dev": "next dev -p 4500",
     "build": "next build",
     "start": "next start",
     "lint": "next lint"

apps/web/src/app/banco/page.tsx

@@ -2,6 +2,7 @@
 
 import { useCallback, useEffect, useRef, useState } from "react";
 import { AppShell } from "@/components/AppShell";
+import { ContextReports } from "@/components/ContextReports";
 import {
   createBankMovement,
   getBankFacets,
@@ -208,6 +209,13 @@ function BankBrowser() {
           parte del estado de cuenta de los clientes y sus cifras no se suman
           con las de ellos.
         </p>
+        <div style={{ marginTop: 8 }}>
+          <ContextReports
+            entries={[
+              { slug: "reporte-de-efectivo", label: "Reporte de efectivo" },
+            ]}
+          />
+        </div>
       </div>
 
       <div className="toolbar">

apps/web/src/app/clientes/[id]/page.tsx

@@ -3,7 +3,14 @@
 import { useEffect, useState } from "react";
 import Link from "next/link";
 import { AppShell } from "@/components/AppShell";
-import { archiveCustomer, getCustomer, restoreCustomer } from "@/lib/api";
+import { ContextReports } from "@/components/ContextReports";
+import {
+  archiveCustomer,
+  getCustomer,
+  policyDocumentDownloadUrl,
+  propertyDocumentDownloadUrl,
+  restoreCustomer,
+} from "@/lib/api";
 import { useCan } from "@/lib/abilities";
 import {
   domainLabel,
@@ -89,6 +96,15 @@ function Detail({ id }: { id: string }) {
     <div className="rise">
       <div className="detail-actionbar">
         <BackLink />
+        <ContextReports
+          entries={[
+            {
+              slug: "edo-cuenta-datos",
+              label: "Estado de cuenta (reporte)",
+              params: { customerId: data.id },
+            },
+          ]}
+        />
         <CustomerActions
           customer={data}
           onChange={() => getCustomer(id).then(setData).catch(() => {})}
@@ -765,9 +781,10 @@ function TxRow({ t }: { t: Transaction }) {
   const tipo =
     t.type?.nameEs || t.type?.nameEn || "—";
   const concept = t.message || t.period || "—";
+  const voided = !!t.voidedAt;
 
   return (
-    <tr>
+    <tr style={voided ? { textDecoration: "line-through", opacity: 0.55 } : undefined}>
       <td className="mono" style={{ whiteSpace: "nowrap" }}>
         {formatDate(t.transactionDate)}
       </td>
@@ -777,7 +794,14 @@ function TxRow({ t }: { t: Transaction }) {
       </td>
       <td>{tipo}</td>
       <td className="tx-ref">{t.reference || "—"}</td>
-      <td className="tx-concept">{concept}</td>
+      <td className="tx-concept">
+        {concept}
+        {voided && (
+          <span className="tx-cur" style={{ marginLeft: 6, textDecoration: "none" }}>
+            (anulado)
+          </span>
+        )}
+      </td>
       <td className="num">
         <span className={`tx-amount ${sign}`}>
           {formatMoney(t.amount, t.currency)}
@@ -790,15 +814,15 @@ function TxRow({ t }: { t: Transaction }) {
 
 /* ----------------------------------------------------------- Documentos */
 function DocumentosSection({ data }: { data: CustomerDetail }) {
-  type Doc = { type: string; key: string | null; scope: string };
+  type Doc = { type: string; scope: string; href: string | null };
   const docs: Doc[] = [];
   data.properties.forEach((p) => {
     const label = [p.addressLine1].filter(Boolean).join("") || "Propiedad";
     p.documents.forEach((d) =>
       docs.push({
         type: d.documentType || "Documento",
-        key: d.storageKey,
         scope: label,
+        href: d.id ? propertyDocumentDownloadUrl(p.id, d.id) : null,
       }),
     );
   });
@@ -806,8 +830,8 @@ function DocumentosSection({ data }: { data: CustomerDetail }) {
     p.documents.forEach((d) =>
       docs.push({
         type: d.documentType || "Documento",
-        key: d.storageKey,
         scope: `Póliza ${p.policyNumber ?? ""}`.trim(),
+        href: d.id ? policyDocumentDownloadUrl(p.id, d.id) : null,
       }),
     );
   });
@@ -821,28 +845,24 @@ function DocumentosSection({ data }: { data: CustomerDetail }) {
             No hay documentos registrados para este cliente.
           </div>
         ) : (
-          <>
           <div className="doc-list">
             {docs.map((d, i) => (
               <div className="doc-item" key={i}>
                 <span className="doc-icon" aria-hidden>
                   ▤
                 </span>
-                  <div style={{ minWidth: 0 }}>
+                <div style={{ minWidth: 0, flex: 1 }}>
                   <div className="doc-type">{d.type}</div>
                   <div className="doc-key">{d.scope}</div>
                 </div>
+                {d.href && (
+                  <a className="btn btn-ghost" href={d.href}>
+                    Descargar
+                  </a>
+                )}
               </div>
             ))}
           </div>
-            <div
-              className="section-note"
-              style={{ padding: "0 22px 18px" }}
-            >
-              Los archivos se almacenan en el object storage
-              (storageKey); no se descargan desde esta vista.
-            </div>
-          </>
         )}
       </div>
     </section>

apps/web/src/app/clientes/page.tsx

@@ -3,6 +3,7 @@
 import { useCallback, useEffect, useRef, useState } from "react";
 import Link from "next/link";
 import { AppShell } from "@/components/AppShell";
+import { ContextReports } from "@/components/ContextReports";
 import { getStats, listCustomers } from "@/lib/api";
 import { useCan } from "@/lib/abilities";
 import { formatNumber, SIN_NOMBRE } from "@/lib/labels";
@@ -99,6 +100,12 @@ function ClientesBrowser() {
         <div style={{ display: "flex", alignItems: "center", gap: 16 }}>
           <h1 className="page-title" style={{ margin: 0 }}>Clientes</h1>
           <span style={{ flex: 1 }} />
+          <ContextReports
+            entries={[
+              { slug: "listado-en-rojo", label: "En rojo" },
+              { slug: "pagos-no-efectuados", label: "Sin pagos (agua)" },
+            ]}
+          />
           {canCreate && (
             <Link href="/clientes/nuevo" className="btn btn-primary">
               + Nuevo cliente

apps/web/src/app/estado-cuenta/page.tsx

@@ -3,6 +3,7 @@
 import { useCallback, useEffect, useRef, useState } from "react";
 import Link from "next/link";
 import { AppShell } from "@/components/AppShell";
+import { ContextReports } from "@/components/ContextReports";
 import { MovementForm } from "@/components/MovementForm";
 import {
   getBillingFacets,
@@ -256,6 +257,15 @@ function BillingBrowser() {
         <LedgerTotalsStrip stats={stats} />
       </div>
 
+      <div style={{ marginTop: 12 }}>
+        <ContextReports
+          entries={[
+            { slug: "listado-en-rojo", label: "En rojo" },
+            { slug: "reporte-de-efectivo", label: "Reporte de efectivo" },
+          ]}
+        />
+      </div>
+
       <div className="toolbar">
         <div className="search-box">
           <span className="search-icon" aria-hidden>

apps/web/src/app/globals.css

@@ -200,19 +200,20 @@ button {
   text-decoration: none;
 }
 .brand-mark {
-  width: 34px;
+  width: 38px;
-  height: 34px;
+  height: 38px;
   border-radius: 9px;
-  background: radial-gradient(circle at 30% 25%, var(--brand-500), var(--brand-700));
+  background: #fbf8f0;
   border: 1px solid rgba(255, 255, 255, 0.18);
-  display: grid;
+  object-fit: contain;
-  place-items: center;
+  padding: 3px;
-  font-family: var(--font-display);
-  font-weight: 600;
-  font-size: 16px;
-  color: #f5efe0;
   box-shadow: inset 0 1px 1px rgba(255, 255, 255, 0.2);
 }
+.login-brand .brand-mark {
+  width: 46px;
+  height: 46px;
+  border-radius: 10px;
+}
 .brand-text {
   display: flex;
   flex-direction: column;
@@ -2204,3 +2205,566 @@ button {
   padding: 20px;
   z-index: 50;
 }
+
+/* ============================================================================
+   Home dashboard (/inicio)
+   ========================================================================== */
+.home {
+  display: flex;
+  flex-direction: column;
+  gap: 36px;
+  padding-top: 8px;
+}
+.home-last-seen {
+  margin-top: 14px;
+  font-size: 13px;
+}
+.home-section {
+  display: flex;
+  flex-direction: column;
+  gap: 14px;
+}
+.section-head {
+  display: flex;
+  align-items: baseline;
+  justify-content: space-between;
+  gap: 16px;
+  flex-wrap: wrap;
+}
+.section-title {
+  font-family: var(--font-display);
+  font-size: 20px;
+  font-weight: 560;
+  margin: 0;
+  color: var(--ink);
+  letter-spacing: -0.005em;
+}
+.section-sub {
+  font-size: 12.5px;
+  color: var(--muted);
+}
+
+/* KPI cards row — 4 across on wide screens, 2 on tablet, 1 on mobile */
+.home-section.kpi-row {
+  display: grid;
+}
+.home > .home-section:first-of-type {
+  display: grid;
+  grid-template-columns: repeat(4, 1fr);
+  gap: 14px;
+}
+@media (max-width: 1080px) {
+  .home > .home-section:first-of-type {
+    grid-template-columns: repeat(2, 1fr);
+  }
+}
+@media (max-width: 560px) {
+  .home > .home-section:first-of-type {
+    grid-template-columns: 1fr;
+  }
+}
+
+.kpi-card {
+  display: flex;
+  flex-direction: column;
+  padding: 18px 18px 16px;
+  text-decoration: none;
+  color: inherit;
+  transition: transform 0.18s var(--ease-out-expo), box-shadow 0.18s;
+  position: relative;
+}
+.kpi-card:hover {
+  transform: translateY(-2px);
+  box-shadow: var(--shadow-md);
+}
+.kpi-label {
+  font-size: 11.5px;
+  text-transform: uppercase;
+  letter-spacing: 0.06em;
+  color: var(--muted);
+  font-weight: 700;
+}
+.kpi-primary {
+  font-family: var(--font-display);
+  font-size: 32px;
+  font-weight: 560;
+  color: var(--ink);
+  margin-top: 8px;
+  font-feature-settings: "tnum" 1;
+  letter-spacing: -0.015em;
+  line-height: 1.1;
+  min-height: 36px;
+}
+.kpi-sub {
+  list-style: none;
+  padding: 0;
+  margin: 12px 0 0;
+  display: flex;
+  flex-direction: column;
+  gap: 4px;
+  font-size: 12.5px;
+  color: var(--ink-soft);
+}
+.kpi-sub li {
+  line-height: 1.35;
+}
+.kpi-cta {
+  margin-top: 14px;
+  font-size: 12px;
+  font-weight: 700;
+  color: var(--brand-700);
+  letter-spacing: 0.02em;
+  text-transform: uppercase;
+}
+.kpi-card:hover .kpi-cta {
+  color: var(--brand-800);
+}
+
+/* Attention grid — auto-fill so the row of cards stays balanced */
+.attention-grid {
+  display: grid;
+  grid-template-columns: repeat(auto-fill, minmax(240px, 1fr));
+  gap: 14px;
+}
+.attention-card {
+  display: flex;
+  flex-direction: column;
+  gap: 6px;
+  padding: 16px 18px 18px;
+  text-decoration: none;
+  color: inherit;
+  border-left: 3px solid var(--line-strong);
+  transition: transform 0.18s var(--ease-out-expo), box-shadow 0.18s;
+  position: relative;
+}
+.attention-card:hover {
+  transform: translateY(-2px);
+  box-shadow: var(--shadow-md);
+}
+.attention-card.tone-warn {
+  border-left-color: var(--seguros);
+}
+.attention-card.tone-info {
+  border-left-color: var(--brand-600);
+}
+.attention-card.tone-muted {
+  border-left-color: var(--muted-2);
+}
+.attention-head {
+  display: flex;
+  align-items: center;
+  justify-content: space-between;
+  gap: 10px;
+}
+.attention-title {
+  font-size: 12px;
+  text-transform: uppercase;
+  letter-spacing: 0.06em;
+  color: var(--muted);
+  font-weight: 700;
+}
+.attention-arrow {
+  color: var(--muted-2);
+  font-size: 14px;
+  transition: transform 0.18s var(--ease-out-expo), color 0.18s;
+}
+.attention-card:hover .attention-arrow {
+  color: var(--brand-700);
+  transform: translateX(3px);
+}
+.attention-primary {
+  font-family: var(--font-display);
+  font-size: 26px;
+  font-weight: 560;
+  color: var(--ink);
+  font-feature-settings: "tnum" 1;
+  letter-spacing: -0.01em;
+  line-height: 1.1;
+  min-height: 30px;
+  margin-top: 2px;
+}
+.attention-sub {
+  font-size: 12.5px;
+  color: var(--ink-soft);
+  line-height: 1.4;
+}
+.attention-meta {
+  font-size: 11.5px;
+  color: var(--muted);
+  margin-top: 4px;
+}
+
+/* Currency totals inside an attention card */
+.home-currency-totals {
+  display: inline-flex;
+  flex-wrap: wrap;
+  gap: 8px 14px;
+  align-items: baseline;
+}
+.home-currency-totals-row {
+  display: inline-flex;
+  align-items: baseline;
+  gap: 6px;
+}
+.home-currency-totals-num {
+  font-family: var(--font-display);
+  font-feature-settings: "tnum" 1;
+  font-size: 20px;
+  font-weight: 560;
+}
+
+/* Quick links row */
+.quick-grid {
+  display: grid;
+  grid-template-columns: repeat(auto-fill, minmax(180px, 1fr));
+  gap: 12px;
+}
+.quick-link {
+  display: flex;
+  flex-direction: column;
+  padding: 14px 16px;
+  text-decoration: none;
+  color: inherit;
+  position: relative;
+  transition: transform 0.18s var(--ease-out-expo), box-shadow 0.18s;
+}
+.quick-link:hover {
+  transform: translateY(-2px);
+  box-shadow: var(--shadow-md);
+}
+.quick-label {
+  font-family: var(--font-display);
+  font-size: 17px;
+  font-weight: 560;
+  color: var(--ink);
+}
+.quick-sub {
+  font-size: 12px;
+  color: var(--muted);
+  margin-top: 2px;
+}
+.quick-arrow {
+  position: absolute;
+  top: 14px;
+  right: 16px;
+  color: var(--muted-2);
+  font-size: 14px;
+  transition: transform 0.18s var(--ease-out-expo), color 0.18s;
+}
+.quick-link:hover .quick-arrow {
+  color: var(--brand-700);
+  transform: translateX(3px);
+}
+
+/* Money tone helpers (used in chequera card) */
+.money-pos { color: var(--positive); }
+.money-neg { color: var(--negative); }
+
+/* ============================================================================
+   Reports module
+   ========================================================================== */
+
+.report-catalog {
+  display: flex;
+  flex-direction: column;
+  gap: 28px;
+  margin-top: 20px;
+}
+.report-catalog-group {
+  display: flex;
+  flex-direction: column;
+  gap: 10px;
+}
+.report-catalog-title {
+  font-family: var(--font-display);
+  font-weight: 500;
+  font-size: 20px;
+  color: var(--brand-800);
+  margin: 0;
+  padding-bottom: 6px;
+  border-bottom: 1px solid var(--line);
+}
+.report-catalog-list {
+  list-style: none;
+  margin: 0;
+  padding: 0;
+  display: grid;
+  grid-template-columns: repeat(auto-fill, minmax(320px, 1fr));
+  gap: 12px;
+}
+.report-catalog-item {
+  margin: 0;
+}
+.report-catalog-link {
+  display: flex;
+  flex-direction: column;
+  gap: 6px;
+  padding: 14px 16px;
+  background: var(--surface);
+  border: 1px solid var(--line);
+  border-radius: 8px;
+  color: inherit;
+  text-decoration: none;
+  transition: border-color 0.18s, transform 0.18s var(--ease-out-expo);
+}
+.report-catalog-link:hover {
+  border-color: var(--brand-500);
+  transform: translateY(-1px);
+}
+.report-catalog-item-title {
+  font-weight: 600;
+  font-size: 15px;
+  color: var(--ink);
+}
+.report-catalog-item-desc {
+  font-size: 13px;
+  color: var(--muted);
+  line-height: 1.4;
+}
+.report-catalog-item-meta {
+  display: flex;
+  flex-wrap: wrap;
+  gap: 6px;
+  margin-top: 4px;
+}
+.report-catalog-tag {
+  font-size: 11px;
+  background: var(--brand-tint);
+  color: var(--brand-800);
+  padding: 2px 8px;
+  border-radius: 999px;
+  font-family: var(--font-mono);
+}
+.report-catalog-tag.muted {
+  background: var(--paper-2);
+  color: var(--muted);
+}
+
+/* Runner */
+.report-runner {
+  display: flex;
+  flex-direction: column;
+  gap: 16px;
+  margin-top: 20px;
+}
+.report-filters {
+  display: flex;
+  flex-wrap: wrap;
+  gap: 12px;
+  align-items: flex-end;
+  background: var(--surface);
+  border: 1px solid var(--line);
+  border-radius: 8px;
+  padding: 14px 16px;
+}
+.report-filters-actions {
+  display: flex;
+  align-items: center;
+  gap: 8px;
+  margin-left: auto;
+}
+.report-error {
+  background: #fbeae3;
+  color: var(--negative);
+  border: 1px solid #e6b6a4;
+  border-radius: 6px;
+  padding: 10px 14px;
+  font-size: 13px;
+}
+.report-loading {
+  font-size: 12px;
+  color: var(--muted);
+  padding: 4px 0;
+}
+.report-result {
+  display: flex;
+  flex-direction: column;
+  gap: 12px;
+}
+.report-result-head {
+  display: flex;
+  align-items: flex-end;
+  gap: 16px;
+  flex-wrap: wrap;
+}
+.report-result-meta {
+  flex: 1;
+  min-width: 200px;
+}
+.report-output-buttons {
+  display: flex;
+  flex-wrap: wrap;
+  gap: 6px;
+}
+.btn-sm {
+  padding: 4px 10px;
+  font-size: 12px;
+}
+.report-table-wrap {
+  overflow-x: auto;
+  background: var(--surface);
+  border: 1px solid var(--line);
+  border-radius: 8px;
+}
+.report-table {
+  width: 100%;
+  border-collapse: collapse;
+  font-size: 13px;
+}
+.report-table th,
+.report-table td {
+  padding: 8px 12px;
+  border-bottom: 1px solid var(--line);
+}
+.report-table th {
+  background: var(--paper-2);
+  color: var(--ink-soft);
+  font-weight: 600;
+  text-align: left;
+  position: sticky;
+  top: 0;
+  z-index: 1;
+}
+.report-table tbody tr:hover {
+  background: var(--paper-2);
+}
+.report-totals {
+  background: var(--accent);
+  color: #f5f1e8;
+  font-weight: 600;
+  padding: 10px 14px;
+}
+.statement {
+  display: flex;
+  flex-direction: column;
+  gap: 20px;
+}
+.statement-head {
+  background: var(--surface);
+  border: 1px solid var(--line);
+  border-radius: 8px;
+  padding: 16px 18px;
+}
+.statement-name {
+  font-family: var(--font-display);
+  font-size: 22px;
+  font-weight: 600;
+  color: var(--ink);
+  margin: 0 0 4px;
+}
+.statement-section-title {
+  font-family: var(--font-display);
+  font-size: 16px;
+  font-weight: 500;
+  color: var(--brand-800);
+  margin: 0 0 8px;
+}
+
+/* Renewal notices (aviso-renovacion) — one card per policy due, see
+   docs/RENEWAL_NOTICES.md for the legacy report this replaces. */
+.renewal-letters {
+  display: flex;
+  flex-direction: column;
+  gap: 20px;
+}
+.renewal-letter {
+  background: var(--surface);
+  border: 1px solid var(--line);
+  border-radius: 8px;
+  padding: 18px 20px;
+  break-inside: avoid;
+}
+.renewal-letter-head {
+  display: flex;
+  justify-content: space-between;
+  align-items: flex-start;
+  border-bottom: 1px solid var(--line);
+  padding-bottom: 10px;
+  margin-bottom: 12px;
+}
+.renewal-letter-title {
+  font-family: var(--font-display);
+  font-size: 18px;
+  font-weight: 600;
+  color: var(--ink);
+  margin: 0;
+}
+.renewal-letter-grid {
+  display: grid;
+  grid-template-columns: repeat(4, 1fr);
+  gap: 12px;
+  margin-bottom: 12px;
+}
+.renewal-letter-vehicle {
+  background: var(--paper-2);
+  border-radius: 6px;
+  padding: 10px 12px;
+  margin-bottom: 12px;
+}
+.renewal-letter-coverage {
+  display: grid;
+  grid-template-columns: repeat(auto-fit, minmax(140px, 1fr));
+  gap: 12px;
+  margin-bottom: 12px;
+}
+.renewal-letter-coverage-item {
+  border-left: 2px solid var(--line-strong);
+  padding-left: 10px;
+}
+.renewal-letter-value {
+  font-size: 14px;
+  font-weight: 600;
+  color: var(--ink);
+  margin: 2px 0 0;
+}
+.renewal-letter-premium {
+  display: flex;
+  gap: 20px;
+  font-size: 14px;
+  border-top: 1px solid var(--line);
+  padding-top: 10px;
+}
+.renewal-letter-total {
+  font-weight: 700;
+  color: var(--brand-800);
+}
+@media print {
+  .renewal-letter {
+    page-break-inside: avoid;
+    page-break-after: always;
+  }
+  .renewal-letter:last-child {
+    page-break-after: auto;
+  }
+}
+
+/* Context buttons (the inline shortcut on existing pages) */
+.context-reports {
+  display: flex;
+  flex-wrap: wrap;
+  gap: 6px;
+  align-items: center;
+  margin-left: auto;
+}
+.context-reports-label {
+  font-size: 11px;
+  color: var(--muted-2);
+  text-transform: uppercase;
+  letter-spacing: 0.06em;
+  margin-right: 4px;
+  font-weight: 500;
+}
+.context-report-link {
+  font-size: 12px;
+  padding: 4px 10px;
+  border: 1px solid var(--line);
+  background: var(--surface);
+  color: var(--ink);
+  border-radius: 999px;
+  text-decoration: none;
+  transition: border-color 0.15s, color 0.15s;
+}
+.context-report-link:hover {
+  border-color: var(--brand-500);
+  color: var(--brand-700);
+}

apps/web/src/app/inicio/page.tsx

@@ -0,0 +1,455 @@
+"use client";
+
+import { useEffect, useState } from "react";
+import Link from "next/link";
+import { AppShell } from "@/components/AppShell";
+import {
+  EXPIRY_WINDOW_DAYS,
+  getBankStats,
+  getBillingStats,
+  getPolicyStats,
+  getPropertyStats,
+  getStats,
+} from "@/lib/api";
+import { useAuth } from "@/lib/abilities";
+import {
+  balancePhrase,
+  formatDate,
+  formatMoney,
+  formatNumber,
+  policyStatusLabel,
+  trustStatusLabel,
+} from "@/lib/labels";
+import type {
+  BankStats,
+  BillingStats,
+  CustomerStats,
+  PolicyStats,
+  PropertyStats,
+} from "@/lib/types";
+
+export default function InicioPage() {
+  return (
+    <AppShell>
+      <HomeDashboard />
+    </AppShell>
+  );
+}
+
+interface DashboardData {
+  customers: CustomerStats | null;
+  policies: PolicyStats | null;
+  properties: PropertyStats | null;
+  billing: BillingStats | null;
+  bank: BankStats | null;
+}
+
+function HomeDashboard() {
+  const user = useAuth();
+  const [data, setData] = useState<DashboardData>({
+    customers: null,
+    policies: null,
+    properties: null,
+    billing: null,
+    bank: null,
+  });
+  const [loading, setLoading] = useState(true);
+
+  useEffect(() => {
+    let alive = true;
+    Promise.allSettled([
+      getStats(),
+      getPolicyStats(),
+      getPropertyStats(),
+      getBillingStats(),
+      getBankStats(),
+    ]).then((results) => {
+      if (!alive) return;
+      setData({
+        customers: results[0].status === "fulfilled" ? results[0].value : null,
+        policies: results[1].status === "fulfilled" ? results[1].value : null,
+        properties: results[2].status === "fulfilled" ? results[2].value : null,
+        billing: results[3].status === "fulfilled" ? results[3].value : null,
+        bank: results[4].status === "fulfilled" ? results[4].value : null,
+      });
+      setLoading(false);
+    });
+    return () => {
+      alive = false;
+    };
+  }, []);
+
+  const greeting = greetingFor(user?.name);
+  const lastBillingMovement = data.billing?.lastMovement ?? null;
+  const lastBankMovement = data.bank?.lastMovement ?? null;
+
+  return (
+    <div className="home rise">
+      <div className="page-head">
+        <p className="eyebrow">Resumen general</p>
+        <h1 className="page-title">{greeting}</h1>
+        <p className="muted" style={{ marginTop: 6, maxWidth: 640 }}>
+          Vista rápida del estado de la cartera de clientes, las pólizas
+          activas, los fideicomisos y los movimientos recientes.
+        </p>
+        <LastSeenLine
+          billing={lastBillingMovement}
+          bank={lastBankMovement}
+          loading={loading}
+        />
+      </div>
+
+      <section aria-label="Indicadores principales" className="home-section">
+        <KpiCard
+          href="/clientes"
+          label="Clientes"
+          loading={loading}
+          primary={data.customers ? formatNumber(data.customers.customers) : "—"}
+          sub={
+            data.customers
+              ? [
+                  `${formatNumber(data.customers.withUtilities)} con servicios`,
+                  `${formatNumber(data.customers.withInsurance)} con seguros`,
+                  `${formatNumber(data.customers.bothLines)} en ambos ramos`,
+                ]
+              : []
+          }
+        />
+        <KpiCard
+          href="/servicios"
+          label="Propiedades"
+          loading={loading}
+          primary={data.properties ? formatNumber(data.properties.properties) : "—"}
+          sub={
+            data.properties
+              ? [
+                  `${formatNumber(data.properties.services)} servicios`,
+                  `${formatNumber(data.properties.trusts)} fideicomisos`,
+                  `${formatNumber(data.properties.trustExpiring)} por vencer`,
+                ]
+              : []
+          }
+        />
+        <KpiCard
+          href="/polizas"
+          label="Pólizas"
+          loading={loading}
+          primary={data.policies ? formatNumber(data.policies.total) : "—"}
+          sub={
+            data.policies
+              ? [
+                  `${formatNumber(data.policies.active)} vigentes`,
+                  `${formatNumber(data.policies.expiring)} por vencer (${EXPIRY_WINDOW_DAYS} d)`,
+                  `${formatNumber(data.policies.expired + data.policies.undated)} vencidas o sin fecha`,
+                ]
+              : []
+          }
+        />
+        <KpiCard
+          href="/estado-cuenta"
+          label="Movimientos"
+          loading={loading}
+          primary={data.billing ? formatNumber(data.billing.movements) : "—"}
+          sub={
+            data.billing
+              ? [
+                  `${formatNumber(data.billing.ledgerCustomers)} clientes con cargo`,
+                  `${formatNumber(data.billing.crossLineCustomers)} con ambos ramos`,
+                  lastBillingMovement
+                    ? `Último: ${formatDate(lastBillingMovement)}`
+                    : "Sin movimientos",
+                ]
+              : []
+          }
+        />
+      </section>
+
+      <section aria-label="Atención" className="home-section">
+        <div className="section-head">
+          <h2 className="section-title">Atención</h2>
+          <span className="section-sub">
+            Lo que conviene revisar antes de cerrar el día
+          </span>
+        </div>
+
+        <div className="attention-grid">
+          <AttentionCard
+            href="/polizas?status=expiring"
+            tone="warn"
+            loading={loading}
+            title="Pólizas por vencer"
+            primary={
+              data.policies ? formatNumber(data.policies.expiring) : "—"
+            }
+            sub={
+              data.policies
+                ? `En los próximos ${EXPIRY_WINDOW_DAYS} días — ventana: ${policyStatusLabel("expiring")}`
+                : undefined
+            }
+            meta={
+              data.policies
+                ? `${formatNumber(data.policies.active)} vigentes · ${formatNumber(data.policies.expired)} vencidas`
+                : undefined
+            }
+          />
+          <AttentionCard
+            href="/servicios?trust=expiring"
+            tone="warn"
+            loading={loading}
+            title="Fideicomisos por vencer"
+            primary={
+              data.properties ? formatNumber(data.properties.trustExpiring) : "—"
+            }
+            sub={
+              data.properties
+                ? `Renueva en los próximos ${EXPIRY_WINDOW_DAYS} días (${trustStatusLabel("expiring")})`
+                : undefined
+            }
+            meta={
+              data.properties
+                ? `${formatNumber(data.properties.trusts)} fideicomisos en cartera`
+                : undefined
+            }
+          />
+          <AttentionCard
+            href="/polizas?status=undated"
+            tone="muted"
+            loading={loading}
+            title="Pólizas sin vigencia"
+            primary={data.policies ? formatNumber(data.policies.undated) : "—"}
+            sub={
+              data.policies
+                ? "Sin fecha de fin registrada — revisar y completar"
+                : undefined
+            }
+            meta={
+              data.policies
+                ? `${formatNumber(data.policies.liquidated)} liquidadas`
+                : undefined
+            }
+          />
+          <AttentionCard
+            href="/estado-cuenta"
+            tone="info"
+            loading={loading}
+            title="Clientes con adeudo"
+            primary={
+              data.billing ? (
+                <CurrencyTotals
+                  totals={data.billing.byCurrency}
+                  field="owing"
+                />
+              ) : (
+                "—"
+              )
+            }
+            sub={
+              data.billing
+                ? `En ${formatNumber(data.billing.ledgerCustomers)} expedientes con cargo`
+                : undefined
+            }
+            meta={
+              data.billing
+                ? `${formatNumber(data.billing.crossLineCustomers)} clientes con cargo en ambos ramos`
+                : undefined
+            }
+          />
+          <AttentionCard
+            href="/banco"
+            tone="info"
+            loading={loading}
+            title="Chequera del despacho"
+            primary={
+              data.bank ? (
+                <span className={data.bank.net.startsWith("-") ? "money-neg" : "money-pos"}>
+                  {formatMoney(data.bank.net, "MXN")}
+                </span>
+              ) : (
+                "—"
+              )
+            }
+            sub={
+              data.bank
+                ? balancePhrase(data.bank.net)
+                : undefined
+            }
+            meta={
+              data.bank
+                ? `${formatNumber(data.bank.movements)} movimientos · ${formatNumber(data.bank.pending)} pendientes`
+                : undefined
+            }
+          />
+        </div>
+      </section>
+
+      <section aria-label="Accesos rápidos" className="home-section">
+        <div className="section-head">
+          <h2 className="section-title">Accesos rápidos</h2>
+          <span className="section-sub">
+            Ir directo a cada módulo
+          </span>
+        </div>
+        <div className="quick-grid">
+          <QuickLink href="/clientes" label="Clientes" sub="Directorio unificado" />
+          <QuickLink href="/servicios" label="Propiedades" sub="Servicios y fideicomisos" />
+          <QuickLink href="/polizas" label="Pólizas" sub="Vigencias y liquidaciones" />
+          <QuickLink href="/estado-cuenta" label="Estado de cuenta" sub="Cargos y abonos" />
+          <QuickLink href="/banco" label="Chequera" sub="Ingresos y egresos" />
+        </div>
+      </section>
+    </div>
+  );
+}
+
+function greetingFor(name?: string): string {
+  const hour = new Date().getHours();
+  const partOfDay =
+    hour < 12 ? "Buenos días" : hour < 19 ? "Buenas tardes" : "Buenas noches";
+  const display = (name ?? "").trim().split(/\s+/)[0];
+  return display ? `${partOfDay}, ${display}` : partOfDay;
+}
+
+function LastSeenLine({
+  billing,
+  bank,
+  loading,
+}: {
+  billing: string | null;
+  bank: string | null;
+  loading: boolean;
+}) {
+  if (loading) {
+    return (
+      <p className="muted home-last-seen" aria-hidden="true">
+        <span className="skeleton" style={{ display: "inline-block", width: 220, height: 12 }} />
+      </p>
+    );
+  }
+  if (!billing && !bank) return null;
+  return (
+    <p className="muted home-last-seen">
+      {billing && <>Último movimiento de cartera: <strong>{formatDate(billing)}</strong></>}
+      {billing && bank && <span aria-hidden="true"> · </span>}
+      {bank && <>Último movimiento de chequera: <strong>{formatDate(bank)}</strong></>}
+    </p>
+  );
+}
+
+function KpiCard({
+  href,
+  label,
+  primary,
+  sub,
+  loading,
+}: {
+  href: string;
+  label: string;
+  primary: React.ReactNode;
+  sub: string[];
+  loading: boolean;
+}) {
+  return (
+    <Link href={href} className="kpi-card card">
+      <div className="kpi-label">{label}</div>
+      <div className="kpi-primary">
+        {loading ? (
+          <span
+            className="skeleton"
+            style={{ display: "inline-block", width: 90, height: 30 }}
+          />
+        ) : (
+          primary
+        )}
+      </div>
+      <ul className="kpi-sub">
+        {loading
+          ? Array.from({ length: 3 }).map((_, i) => (
+              <li key={i} className="skeleton" style={{ height: 10 }} />
+            ))
+          : sub.map((line) => <li key={line}>{line}</li>)}
+      </ul>
+      <span className="kpi-cta" aria-hidden="true">
+        Ver módulo →
+      </span>
+    </Link>
+  );
+}
+
+function AttentionCard({
+  href,
+  tone,
+  title,
+  primary,
+  sub,
+  meta,
+  loading,
+}: {
+  href: string;
+  tone: "warn" | "info" | "muted";
+  title: string;
+  primary: React.ReactNode;
+  sub?: string;
+  meta?: string;
+  loading: boolean;
+}) {
+  return (
+    <Link href={href} className={`attention-card tone-${tone} card`}>
+      <div className="attention-head">
+        <span className="attention-title">{title}</span>
+        <span className="attention-arrow" aria-hidden="true">→</span>
+      </div>
+      <div className="attention-primary">
+        {loading ? (
+          <span
+            className="skeleton"
+            style={{ display: "inline-block", width: 70, height: 28 }}
+          />
+        ) : (
+          primary
+        )}
+      </div>
+      {sub && !loading && <div className="attention-sub">{sub}</div>}
+      {meta && !loading && <div className="attention-meta">{meta}</div>}
+    </Link>
+  );
+}
+
+function QuickLink({
+  href,
+  label,
+  sub,
+}: {
+  href: string;
+  label: string;
+  sub: string;
+}) {
+  return (
+    <Link href={href} className="quick-link card">
+      <span className="quick-label">{label}</span>
+      <span className="quick-sub">{sub}</span>
+      <span className="quick-arrow" aria-hidden="true">→</span>
+    </Link>
+  );
+}
+
+function CurrencyTotals({
+  totals,
+  field,
+}: {
+  totals: BillingStats["byCurrency"];
+  field: "owing" | "inCredit";
+}) {
+  if (!totals || totals.length === 0) return <>—</>;
+  return (
+    <span className="home-currency-totals">
+      {totals.map((c) => (
+        <span key={c.currency} className="home-currency-totals-row">
+          <span className="badge badge-count">{c.currency}</span>
+          <span className="home-currency-totals-num">
+            {formatNumber(c[field])}
+          </span>
+        </span>
+      ))}
+    </span>
+  );
+}

apps/web/src/app/layout.tsx

@@ -7,10 +7,29 @@ export const metadata = {
     "Plataforma interna unificada de clientes, servicios y seguros.",
 };
 
+// The browser talks to the API cross-origin, so it needs the API URL at
+// runtime. NEXT_PUBLIC_* would bake it at build time (one URL per image); we
+// want the URL to come from the deploy .env instead. So read it here on the
+// server per request and inject it as window.__API_ORIGIN__ (see lib/api.ts).
+// force-dynamic guarantees process.env is read at request time, never baked
+// into a static prerender.
+export const dynamic = "force-dynamic";
+
 export default function RootLayout({ children }: { children: ReactNode }) {
+  const apiOrigin =
+    process.env.API_ORIGIN ??
+    process.env.NEXT_PUBLIC_API_ORIGIN ??
+    "http://localhost:3001";
+
   return (
     <html lang="es">
       <head>
+        {/* Must run before the app bundle so lib/api.ts sees it at import. */}
+        <script
+          dangerouslySetInnerHTML={{
+            __html: `window.__API_ORIGIN__=${JSON.stringify(apiOrigin)};`,
+          }}
+        />
         {/* Google Fonts via <link> so an offline build still runs with the
             system fallback stacks defined in globals.css. */}
         <link rel="preconnect" href="https://fonts.googleapis.com" />

apps/web/src/app/login/page.tsx

@@ -16,7 +16,7 @@ export default function LoginPage() {
   useEffect(() => {
     let alive = true;
     me()
-      .then(() => router.replace("/clientes"))
+      .then(() => router.replace("/inicio"))
       .catch(() => {
         if (alive) setBootChecking(false);
       });
@@ -31,7 +31,7 @@ export default function LoginPage() {
     setSubmitting(true);
     try {
       await login(email.trim(), password);
-      router.replace("/clientes");
+      router.replace("/inicio");
     } catch (err) {
       if (err instanceof ApiError && err.status === 401) {
         setError("Correo o contraseña incorrectos");
@@ -65,9 +65,11 @@ export default function LoginPage() {
       <aside className="login-aside" aria-hidden="false">
         <div className="login-aside-top">
           <div className="login-brand">
-            <span className="brand-mark" aria-hidden>
+            <img
-              JC
+              src="/images/company_logo.png"
-            </span>
+              alt=""
+              className="brand-mark"
+            />
             <div className="brand-text">
               <span className="brand-name" style={{ color: "#f6f3ec" }}>
                 Jorge Cuadros

apps/web/src/app/operaciones/page.tsx

@@ -27,6 +27,8 @@ import type {
   OpsJobKind,
 } from "@/lib/types";
 
+const INGEST_MAX_BYTES = 2 * 1024 * 1024 * 1024;
+
 export default function OperacionesPage() {
   return (
     <AppShell>
@@ -37,6 +39,7 @@ export default function OperacionesPage() {
 
 type ConfirmState =
   | { kind: "REIMPORT" }
+  | { kind: "SYNC" }
   | { kind: "RESTORE"; file: string }
   | null;
 
@@ -176,6 +179,7 @@ function Operaciones() {
     const c = confirm;
     setConfirm(null);
     if (c.kind === "REIMPORT") await start("REIMPORT");
+    else if (c.kind === "SYNC") await start("SYNC");
     else await start("RESTORE", c.file);
   }
 
@@ -224,7 +228,7 @@ function Operaciones() {
         <h2 className="section-title">Carpeta de ingesta</h2>
         <p className="inline-form-note">
           Los cuatro archivos originales de Access. La reimportación y la
-          sincronización leen de aquí.
+          sincronización leen de aquí. Tamaño máximo por archivo: {formatBytes(INGEST_MAX_BYTES)}.
         </p>
         <div className="tx-scroll">
           <table className="tx-table">
@@ -306,11 +310,11 @@ function Operaciones() {
           />
           <OpTile
             title="Sincronizar"
-            desc="Conserva los datos actuales e importa solo lo nuevo del legado. Disponible en la Fase B."
+            desc="Respalda, luego importa lo nuevo del legado. Borra del sistema los registros del legado que ya no aparecen en los archivos de ingesta. Se conservan los datos capturados a mano."
-            action="Próximamente"
+            action="Sincronizar"
-            tone="muted"
+            tone="primary"
-            disabled
+            disabled={jobRunning || starting || !ingestReady}
-            onClick={() => {}}
+            onClick={() => askConfirm({ kind: "SYNC" })}
           />
         </div>
         {!ingestReady && (
@@ -446,11 +450,17 @@ function Operaciones() {
         <div className="modal-backdrop" role="dialog" aria-modal="true">
           <div className="card" style={{ padding: 24, maxWidth: 480 }}>
             <h2 className="section-title" style={{ marginTop: 0 }}>
-              {confirm.kind === "REIMPORT" ? "Confirmar reimportación" : "Confirmar restauración"}
+              {confirm.kind === "REIMPORT"
+                ? "Confirmar reimportación"
+                : confirm.kind === "SYNC"
+                  ? "Confirmar sincronización"
+                  : "Confirmar restauración"}
             </h2>
             <p className="inline-form-note">
               {confirm.kind === "REIMPORT"
                 ? "Esto BORRA todos los datos actuales (incluidos los capturados a mano) y reconstruye desde los archivos de ingesta. Se creará un respaldo previo automático."
+                : confirm.kind === "SYNC"
+                  ? "Se creará un respaldo previo automático. Luego se importarán al sistema los registros nuevos del legado y se eliminarán los del legado que ya no aparezcan en los archivos de ingesta. Los datos capturados a mano NO se borran."
                   : `Esto sobreescribe la base de datos completa con “${confirm.file}”. Se recomienda crear un respaldo antes.`}
             </p>
             <label className="field">
@@ -464,12 +474,16 @@ function Operaciones() {
             </label>
             <div className="form-actions">
               <button
-                className="btn btn-danger"
+                className={confirm.kind === "SYNC" ? "btn btn-primary" : "btn btn-danger"}
                 type="button"
                 disabled={confirmText !== "CONFIRMAR" || starting}
                 onClick={runConfirmed}
               >
-                {confirm.kind === "REIMPORT" ? "Reimportar" : "Restaurar"}
+                {confirm.kind === "REIMPORT"
+                  ? "Reimportar"
+                  : confirm.kind === "SYNC"
+                    ? "Sincronizar"
+                    : "Restaurar"}
               </button>
               <button className="btn btn-outline" type="button" onClick={() => setConfirm(null)}>
                 Cancelar

apps/web/src/app/page.tsx

@@ -1,5 +1,5 @@
 import { redirect } from "next/navigation";
 
 export default function HomePage() {
-  redirect("/clientes");
+  redirect("/inicio");
 }

apps/web/src/app/polizas/[id]/page.tsx

@@ -3,14 +3,18 @@
 import { useEffect, useState } from "react";
 import Link from "next/link";
 import { AppShell } from "@/components/AppShell";
+import { ContextReports } from "@/components/ContextReports";
 import {
   addPolicyChild,
   archivePolicy,
   getLookups,
   getPolicy,
+  policyDocumentDownloadUrl,
   removePolicyChild,
+  removePolicyDocument,
   restorePolicy,
   updatePolicyChild,
+  uploadPolicyDocument,
 } from "@/lib/api";
 import { useCan } from "@/lib/abilities";
 import { ChildCollection, type ChildConfig } from "@/components/ChildCollection";
@@ -89,6 +93,15 @@ function Detail({ id }: { id: string }) {
     <div className="rise">
       <div className="detail-actionbar">
         <BackLink />
+        <ContextReports
+          entries={[
+            {
+              slug: "edo-cuenta-datos",
+              label: "Estado de cuenta del cliente",
+              params: { customerId: data.customer.id },
+            },
+          ]}
+        />
         <PolicyActions data={data} onChange={reload} />
       </div>
       <Hero data={data} />
@@ -101,7 +114,7 @@ function Detail({ id }: { id: string }) {
       )}
       {data.claims.length > 0 && <SiniestrosSection data={data} />}
       <CoberturasSection data={data} />
-      <DocumentosSection data={data} />
+      <DocumentosSection data={data} onChange={reload} />
       <ChildrenEditor data={data} onChange={reload} />
     </div>
   );
@@ -664,7 +677,35 @@ function CoberturasSection({ data }: { data: PolicyDetail }) {
 }
 
 /* -------------------------------------------------------- Documentos */
-function DocumentosSection({ data }: { data: PolicyDetail }) {
+function DocumentosSection({
+  data,
+  onChange,
+}: {
+  data: PolicyDetail;
+  onChange: () => void;
+}) {
+  const canEdit = useCan("policy:update");
+  const [file, setFile] = useState<File | null>(null);
+  const [type, setType] = useState("");
+  const [busy, setBusy] = useState(false);
+  const [error, setError] = useState<string | null>(null);
+
+  async function upload() {
+    if (!file) return;
+    setBusy(true);
+    setError(null);
+    try {
+      await uploadPolicyDocument(data.id, file, type.trim() || undefined);
+      setFile(null);
+      setType("");
+      onChange();
+    } catch (e) {
+      setError((e as Error)?.message ?? "No se pudo subir el archivo.");
+    } finally {
+      setBusy(false);
+    }
+  }
+
   return (
     <section className="section">
       <SectionHead rule="docs" title="Documentos" count={data.documents.length} />
@@ -674,25 +715,74 @@ function DocumentosSection({ data }: { data: PolicyDetail }) {
             No hay documentos registrados para esta póliza.
           </div>
         ) : (
-          <>
           <div className="doc-list">
             {data.documents.map((d, i) => (
               <div className="doc-item" key={d.id ?? i}>
                 <span className="doc-icon" aria-hidden>
                   ▤
                 </span>
-                  <div style={{ minWidth: 0 }}>
+                <div style={{ minWidth: 0, flex: 1 }}>
                   <div className="doc-type">{d.documentType || "Documento"}</div>
                   <div className="doc-key">{d.storageKey || "—"}</div>
                 </div>
+                {d.id && (
+                  <a
+                    className="btn btn-ghost"
+                    href={policyDocumentDownloadUrl(data.id, d.id)}
+                  >
+                    Descargar
+                  </a>
+                )}
+                {canEdit && d.id && (
+                  <button
+                    type="button"
+                    className="btn btn-ghost"
+                    onClick={async () => {
+                      if (!window.confirm("¿Eliminar este documento?")) return;
+                      try {
+                        await removePolicyDocument(data.id, d.id!);
+                        onChange();
+                      } catch (e) {
+                        window.alert((e as Error)?.message ?? "No se pudo eliminar.");
+                      }
+                    }}
+                  >
+                    Eliminar
+                  </button>
+                )}
               </div>
             ))}
           </div>
-            <div className="section-note" style={{ padding: "0 22px 18px" }}>
+        )}
-              Los archivos se almacenan en el object storage (storageKey); no se
+        {canEdit && (
-              descargan desde esta vista.
+          <div style={{ padding: "0 22px 18px" }}>
+            {error && (
+              <div className="state-box state-error" style={{ marginBottom: 12 }}>
+                {error}
+              </div>
+            )}
+            <div className="inline-form">
+              <input
+                className="input"
+                placeholder="Tipo (ej. CARATULA)"
+                value={type}
+                onChange={(e) => setType(e.target.value)}
+              />
+              <input
+                type="file"
+                className="input"
+                onChange={(e) => setFile(e.target.files?.[0] ?? null)}
+              />
+              <button
+                type="button"
+                className="btn btn-primary"
+                disabled={!file || busy}
+                onClick={upload}
+              >
+                {busy ? "Subiendo…" : "Subir"}
+              </button>
+            </div>
           </div>
-          </>
         )}
       </div>
     </section>

apps/web/src/app/polizas/page.tsx

@@ -3,6 +3,7 @@
 import { useCallback, useEffect, useRef, useState } from "react";
 import Link from "next/link";
 import { AppShell } from "@/components/AppShell";
+import { ContextReports } from "@/components/ContextReports";
 import { useCan } from "@/lib/abilities";
 import {
   EXPIRY_WINDOW_DAYS,
@@ -127,6 +128,13 @@ function PolizasBrowser() {
         <div style={{ display: "flex", alignItems: "center", gap: 16 }}>
           <h1 className="page-title" style={{ margin: 0 }}>Pólizas</h1>
           <span style={{ flex: 1 }} />
+          <ContextReports
+            entries={[
+              { slug: "vigente", label: "Por vencer (Lic.)", params: { typeName: "LICENCIAS" } },
+              { slug: "vigente", label: "Por vencer (Mult.)", params: { typeName: "MULT" } },
+              { slug: "vigente", label: "Por vencer (Incen.)", params: { typeName: "INCEN" } },
+            ]}
+          />
           {canCreate && (
             <Link href="/polizas/nuevo" className="btn btn-primary">+ Nueva póliza</Link>
           )}

apps/web/src/app/reportes/[slug]/page.tsx

@@ -0,0 +1,110 @@
+"use client";
+
+import Link from "next/link";
+import { useEffect, useState } from "react";
+import { AppShell } from "@/components/AppShell";
+import { ReportRunner } from "@/components/ReportRunner";
+import { getReportCatalog } from "@/lib/api";
+import type { ReportDef } from "@/lib/types";
+
+/**
+ * /reportes/[slug] — one report's runner page. The whole page is
+ * data-driven from the catalog; if a slug isn't in the registry the
+ * page shows a "not found" inline message.
+ */
+export default function ReporteRunnerPage({
+  params,
+  searchParams,
+}: {
+  params: { slug: string };
+  searchParams?: Record<string, string | string[] | undefined>;
+}) {
+  return (
+    <AppShell>
+      <Runner slug={params.slug} searchParams={searchParams} />
+    </AppShell>
+  );
+}
+
+function Runner({
+  slug,
+  searchParams,
+}: {
+  slug: string;
+  searchParams?: Record<string, string | string[] | undefined>;
+}) {
+  const [def, setDef] = useState<ReportDef | null>(null);
+  const [missing, setMissing] = useState(false);
+  const [error, setError] = useState<string | null>(null);
+
+  useEffect(() => {
+    getReportCatalog()
+      .then((c) => {
+        const found = c.items.find((r) => r.slug === slug);
+        if (found) setDef(found);
+        else setMissing(true);
+      })
+      .catch((e) => setError(e?.message ?? "No se pudo cargar el reporte."));
+  }, [slug]);
+
+  if (error) {
+    return (
+      <>
+        <BackLink />
+        <div className="report-error">{error}</div>
+      </>
+    );
+  }
+  if (missing) {
+    return (
+      <>
+        <BackLink />
+        <div className="empty-inline">Reporte "{slug}" no encontrado.</div>
+      </>
+    );
+  }
+  if (!def) {
+    return (
+      <>
+        <BackLink />
+        <div className="empty-inline">Cargando reporte…</div>
+      </>
+    );
+  }
+
+  const initialParams: Record<string, string> = {};
+  if (searchParams) {
+    const allowed = new Set(def.params.map((p) => p.key));
+    for (const [k, v] of Object.entries(searchParams)) {
+      if (!allowed.has(k)) continue;
+      const s = Array.isArray(v) ? v[0] : v;
+      if (s) initialParams[k] = s;
+    }
+  }
+
+  return (
+    <>
+      <div className="page-head rise">
+        <p className="eyebrow">Reportes</p>
+        <h1 className="page-title">{def.title}</h1>
+        <p className="muted" style={{ marginTop: 6, maxWidth: 720 }}>
+          {def.description}
+        </p>
+        {def.legacyName && (
+          <p className="muted small" style={{ marginTop: 4 }}>
+            Equivalente en Access: <code>{def.legacyName}</code>
+          </p>
+        )}
+      </div>
+      <ReportRunner def={def} initialParams={initialParams} />
+    </>
+  );
+}
+
+function BackLink() {
+  return (
+    <Link href="/reportes" className="back-link">
+      ← Reportes
+    </Link>
+  );
+}

apps/web/src/app/reportes/page.tsx

@@ -0,0 +1,100 @@
+"use client";
+
+import Link from "next/link";
+import { useEffect, useState } from "react";
+import { AppShell } from "@/components/AppShell";
+import { getReportCatalog } from "@/lib/api";
+import type { ReportCatalog, ReportDef, ReportDomain } from "@/lib/types";
+
+/**
+ * The /reportes catalog. Index of every registered report, grouped by
+ * domain (matches the main nav). Discovery layer for the 280+ reports
+ * the system will eventually surface; for now we ship 6 in v1.
+ */
+export default function ReportesPage() {
+  return (
+    <AppShell>
+      <ReportesCatalog />
+    </AppShell>
+  );
+}
+
+const DOMAIN_LABEL: Record<ReportDomain, string> = {
+  clientes: "Clientes",
+  polizas: "Pólizas",
+  servicios: "Servicios",
+  "estado-cuenta": "Estado de cuenta",
+  chequera: "Chequera",
+};
+
+const DOMAIN_ORDER: ReportDomain[] = [
+  "clientes",
+  "estado-cuenta",
+  "polizas",
+  "servicios",
+  "chequera",
+];
+
+function ReportesCatalog() {
+  const [catalog, setCatalog] = useState<ReportCatalog | null>(null);
+  const [error, setError] = useState<string | null>(null);
+
+  useEffect(() => {
+    getReportCatalog()
+      .then(setCatalog)
+      .catch((e) => setError(e?.message ?? "No se pudo cargar el catálogo."));
+  }, []);
+
+  const grouped = new Map<ReportDomain, ReportDef[]>();
+  if (catalog) {
+    for (const r of catalog.items) {
+      const list = grouped.get(r.domain) ?? [];
+      list.push(r);
+      grouped.set(r.domain, list);
+    }
+  }
+
+  return (
+    <>
+      <div className="page-head rise">
+        <p className="eyebrow">Reportes</p>
+        <h1 className="page-title">Catálogo de reportes</h1>
+        <p className="muted" style={{ marginTop: 6, maxWidth: 640 }}>
+          {catalog
+            ? `${catalog.items.length} reportes disponibles. Cada uno corre como consulta sobre el esquema actual — los filtros y totales se recalculan en vivo.`
+            : "Cargando…"}
+        </p>
+      </div>
+
+      {error && <div className="report-error">{error}</div>}
+
+      <div className="report-catalog">
+        {DOMAIN_ORDER.filter((d) => grouped.has(d)).map((d) => (
+          <section key={d} className="report-catalog-group">
+            <h2 className="report-catalog-title">{DOMAIN_LABEL[d]}</h2>
+            <ul className="report-catalog-list">
+              {(grouped.get(d) ?? []).map((r) => (
+                <li key={r.slug} className="report-catalog-item">
+                  <Link href={`/reportes/${r.slug}`} className="report-catalog-link">
+                    <div className="report-catalog-item-title">{r.title}</div>
+                    <div className="report-catalog-item-desc">{r.description}</div>
+                    <div className="report-catalog-item-meta">
+                      {r.legacyName && (
+                        <span className="report-catalog-tag">
+                          Legacy: {r.legacyName}
+                        </span>
+                      )}
+                      <span className="report-catalog-tag muted">
+                        {r.format === "statement" ? "Estado de cuenta" : "Tabular"}
+                      </span>
+                    </div>
+                  </Link>
+                </li>
+              ))}
+            </ul>
+          </section>
+        ))}
+      </div>
+    </>
+  );
+}

apps/web/src/app/servicios/[id]/page.tsx

@@ -7,11 +7,13 @@ import {
   addService,
   archiveProperty,
   getProperty,
+  propertyDocumentDownloadUrl,
   removePropertyDocument,
   removeService,
   removeTrust,
   restoreProperty,
   updateService,
+  uploadPropertyDocument,
   upsertTrust,
 } from "@/lib/api";
 import { useCan } from "@/lib/abilities";
@@ -206,9 +208,44 @@ function PropertyEditor({
 
       <TrustEditor data={data} onChange={onChange} />
 
-      {data.documents.length > 0 && (
+      <DocumentsEditor data={data} onChange={onChange} />
+    </section>
+  );
+}
+
+/** Upload / download / delete document blobs stored in object storage (MinIO). */
+function DocumentsEditor({
+  data,
+  onChange,
+}: {
+  data: PropertyDetail;
+  onChange: () => void;
+}) {
+  const [file, setFile] = useState<File | null>(null);
+  const [type, setType] = useState("");
+  const [busy, setBusy] = useState(false);
+  const [error, setError] = useState<string | null>(null);
+
+  async function upload() {
+    if (!file) return;
+    setBusy(true);
+    setError(null);
+    try {
+      await uploadPropertyDocument(data.id, file, type.trim() || undefined);
+      setFile(null);
+      setType("");
+      onChange();
+    } catch (e) {
+      setError((e as Error)?.message ?? "No se pudo subir el archivo.");
+    } finally {
+      setBusy(false);
+    }
+  }
+
+  return (
     <div className="card" style={{ padding: 16 }}>
       <h3 className="section-title" style={{ marginTop: 0 }}>Documentos</h3>
+      {data.documents.length > 0 && (
         <div className="tx-scroll">
           <table className="tx-table">
             <thead>
@@ -221,6 +258,14 @@ function PropertyEditor({
                   <td className="mono">{d.storageKey ?? "—"}</td>
                   <td>
                     <div className="row-actions">
+                      {d.id && (
+                        <a
+                          className="btn btn-ghost"
+                          href={propertyDocumentDownloadUrl(data.id, d.id)}
+                        >
+                          Descargar
+                        </a>
+                      )}
                       <button
                         type="button"
                         className="btn btn-ghost"
@@ -244,13 +289,30 @@ function PropertyEditor({
             </tbody>
           </table>
         </div>
-          <p className="inline-form-note">
-            La carga de nuevos documentos requiere el almacenamiento de archivos
-            (pendiente); aquí solo se pueden eliminar los existentes.
-          </p>
-        </div>
       )}
-    </section>
+      {error && <div className="state-box state-error" style={{ marginTop: 12 }}>{error}</div>}
+      <div className="inline-form" style={{ marginTop: 12 }}>
+        <input
+          className="input"
+          placeholder="Tipo (ej. RECIBO)"
+          value={type}
+          onChange={(e) => setType(e.target.value)}
+        />
+        <input
+          type="file"
+          className="input"
+          onChange={(e) => setFile(e.target.files?.[0] ?? null)}
+        />
+        <button
+          type="button"
+          className="btn btn-primary"
+          disabled={!file || busy}
+          onClick={upload}
+        >
+          {busy ? "Subiendo…" : "Subir"}
+        </button>
+      </div>
+    </div>
   );
 }
 
@@ -744,17 +806,21 @@ function DocumentosSection({ data }: { data: PropertyDetail }) {
                   <span className="doc-icon" aria-hidden>
                     ▤
                   </span>
-                  <div style={{ minWidth: 0 }}>
+                  <div style={{ minWidth: 0, flex: 1 }}>
                     <div className="doc-type">{d.documentType || "Documento"}</div>
                     <div className="doc-key">{d.storageKey || "—"}</div>
                   </div>
+                  {d.id && (
+                    <a
+                      className="btn btn-ghost"
+                      href={propertyDocumentDownloadUrl(data.id, d.id)}
+                    >
+                      Descargar
+                    </a>
+                  )}
                 </div>
               ))}
             </div>
-            <div className="section-note" style={{ padding: "0 22px 18px" }}>
-              Los archivos se almacenan en el object storage (storageKey); no se
-              descargan desde esta vista.
-            </div>
           </>
         )}
       </div>

apps/web/src/app/servicios/page.tsx

@@ -3,6 +3,7 @@
 import { useCallback, useEffect, useRef, useState } from "react";
 import Link from "next/link";
 import { AppShell } from "@/components/AppShell";
+import { ContextReports } from "@/components/ContextReports";
 import { useCan } from "@/lib/abilities";
 import {
   EXPIRY_WINDOW_DAYS,
@@ -169,6 +170,13 @@ function ServiciosBrowser() {
         <div style={{ display: "flex", alignItems: "center", gap: 16 }}>
           <h1 className="page-title" style={{ margin: 0 }}>Propiedades</h1>
           <span style={{ flex: 1 }} />
+          <ContextReports
+            entries={[
+              { slug: "faltantes", label: "Faltantes agua", params: { serviceKind: "WATER" } },
+              { slug: "faltantes", label: "Faltantes luz", params: { serviceKind: "ELECTRICITY" } },
+              { slug: "faltantes", label: "Faltantes tel.", params: { serviceKind: "PHONE" } },
+            ]}
+          />
           {canCreate && (
             <Link href="/servicios/nuevo" className="btn btn-primary">+ Nueva propiedad</Link>
           )}

apps/web/src/app/usuarios/page.tsx

@@ -6,6 +6,7 @@ import { useAuth, useCan } from "@/lib/abilities";
 import { ROLE_LABEL, ROLES_DESC } from "@/lib/labels";
 import {
   createUser,
+  deleteUser,
   listUsers,
   resetUserPassword,
   updateUser,
@@ -133,6 +134,22 @@ function UsuariosAdmin() {
     }
   }
 
+  async function submitDelete(u: UserRow) {
+    if (!window.confirm(`¿Eliminar al usuario "${u.name}"? Esta acción no se puede deshacer.`)) {
+      return;
+    }
+    setError(null);
+    setNotice(null);
+    try {
+      await deleteUser(u.id);
+      if (editingId === u.id) startCreate();
+      setNotice("Usuario eliminado.");
+      refresh();
+    } catch (e) {
+      setError((e as Error)?.message ?? "No se pudo eliminar el usuario.");
+    }
+  }
+
   return (
     <>
       <div className="page-head">
@@ -312,6 +329,19 @@ function UsuariosAdmin() {
                           >
                             Contraseña
                           </button>
+                          <button
+                            className="btn btn-ghost btn-danger"
+                            type="button"
+                            disabled={u.id === me?.id}
+                            title={
+                              u.id === me?.id
+                                ? "No puede eliminar su propia cuenta"
+                                : "Eliminar usuario"
+                            }
+                            onClick={() => submitDelete(u)}
+                          >
+                            Eliminar
+                          </button>
                         </div>
                       )}
                     </td>

apps/web/src/components/AppShell.tsx

@@ -14,12 +14,14 @@ import type { AuthUser, Ability } from "@/lib/types";
  * content. Provides the AuthContext so any page can read the user's
  * abilities. Used by every authenticated page.
  */
-const NAV: { href: string; label: string; ability?: Ability }[] = [
+const NAV: { href: string; label: string; ability?: Ability; exact?: boolean }[] = [
+  { href: "/inicio", label: "Inicio", exact: true },
   { href: "/clientes", label: "Clientes" },
   { href: "/servicios", label: "Propiedades" },
   { href: "/polizas", label: "Pólizas" },
   { href: "/estado-cuenta", label: "Estado de cuenta" },
   { href: "/banco", label: "Chequera" },
+  { href: "/reportes", label: "Reportes" },
   { href: "/catalogos", label: "Catálogos", ability: "lookup:manage" },
   { href: "/usuarios", label: "Usuarios", ability: "user:manage" },
   { href: "/operaciones", label: "Operaciones", ability: "db:manage" },
@@ -78,10 +80,12 @@ export function AppShell({ children }: { children: ReactNode }) {
     <AuthContext.Provider value={user}>
       <header className="appbar">
         <div className="appbar-inner">
-          <Link href="/clientes" className="brand">
+          <Link href="/inicio" className="brand">
-            <span className="brand-mark" aria-hidden>
+            <img
-              JC
+              src="/images/company_logo.png"
-            </span>
+              alt=""
+              className="brand-mark"
+            />
             <span className="brand-text">
               <span className="brand-name">Jorge Cuadros</span>
               <span className="brand-sub">& Asociados</span>
@@ -90,7 +94,9 @@ export function AppShell({ children }: { children: ReactNode }) {
           <nav className="appbar-nav" aria-label="Principal">
             {NAV.filter((item) => !item.ability || can(user, item.ability)).map(
               (item) => {
-                const active = pathname?.startsWith(item.href) ?? false;
+                const active = item.exact
+                  ? pathname === item.href
+                  : pathname?.startsWith(item.href) ?? false;
                 return (
                   <Link
                     key={item.href}

apps/web/src/components/ContextReports.tsx

@@ -0,0 +1,57 @@
+"use client";
+
+import Link from "next/link";
+
+/**
+ * The context-report shortcut. One row of pill buttons that link to
+ * pre-filtered /reportes/[slug] pages. Used in the page header of
+ * domain pages (clientes, polizas, servicios, estado-cuenta, banco).
+ *
+ * `entries` accepts both static links (slug + label) and pre-filtered
+ * links (slug + params object). Pre-filtered ones build the query
+ * string automatically; the runner pre-fills the form.
+ */
+export interface ReportLink {
+  slug: string;
+  label: string;
+  /** Optional pre-fill for the report's filter form. */
+  params?: Record<string, string>;
+  /** When true, opens the report in a new tab (for "see the catalog"
+   *  style entries where the user is going to look around). */
+  external?: boolean;
+}
+
+export function ContextReports({
+  label = "Reportes",
+  entries,
+}: {
+  label?: string;
+  entries: ReportLink[];
+}) {
+  if (entries.length === 0) return null;
+  return (
+    <div className="context-reports" aria-label={label}>
+      <span className="context-reports-label">{label}</span>
+      {entries.map((e) => {
+        const qs = e.params
+          ? "?" +
+            new URLSearchParams(
+              Object.entries(e.params).filter(([, v]) => v != null && v !== ""),
+            ).toString()
+          : "";
+        const href = `/reportes/${e.slug}${qs}`;
+        return (
+          <Link
+            key={`${e.slug}-${JSON.stringify(e.params ?? {})}`}
+            href={href}
+            className="context-report-link"
+            target={e.external ? "_blank" : undefined}
+            rel={e.external ? "noopener" : undefined}
+          >
+            {e.label}
+          </Link>
+        );
+      })}
+    </div>
+  );
+}

apps/web/src/components/ReportRunner.tsx

@@ -0,0 +1,635 @@
+"use client";
+
+import { useCallback, useEffect, useMemo, useState } from "react";
+import {
+  API_ORIGIN,
+  reportDownloadUrl,
+  runReport,
+} from "@/lib/api";
+import { CustomerPicker } from "@/components/CustomerPicker";
+import { formatMoney, formatNumber } from "@/lib/labels";
+import type {
+  ReportDef,
+  ReportParam,
+  ReportRunResult,
+} from "@/lib/types";
+
+/**
+ * The shared runner. Renders the filter form, fetches the result, and
+ * shows the table + output buttons. One component, every report — the
+ * per-report shape comes entirely from the def the API returns.
+ */
+export function ReportRunner({
+  def,
+  initialParams,
+}: {
+  def: ReportDef;
+  /** Pre-filled param values (e.g. when launched with a customerId from
+   *  a context button on a customer detail page). */
+  initialParams?: Record<string, string>;
+}) {
+  // The form state, keyed by param.key. Initialised from defaults +
+  // initialParams (initialParams wins for explicitly-set keys).
+  const [params, setParams] = useState<Record<string, string>>(() => {
+    const seed: Record<string, string> = {};
+    for (const p of def.params) {
+      if (p.kind === "select" || p.kind === "text" || p.kind === "number" || p.kind === "date") {
+        if (p.defaultValue != null) seed[p.key] = p.defaultValue;
+      }
+    }
+    if (initialParams) Object.assign(seed, initialParams);
+    return seed;
+  });
+
+  const [result, setResult] = useState<ReportRunResult | null>(null);
+  const [loading, setLoading] = useState(false);
+  const [error, setError] = useState<string | null>(null);
+
+  const run = useCallback(
+    (p: Record<string, string>) => {
+      setLoading(true);
+      setError(null);
+      runReport(def.slug, p)
+        .then(setResult)
+        .catch((e) => {
+          setError(e?.message ?? "No se pudo correr el reporte.");
+          setResult(null);
+        })
+        .finally(() => setLoading(false));
+    },
+    [def.slug],
+  );
+
+  // Auto-run on mount so the runner opens with results, not blank.
+  useEffect(() => {
+    run(params);
+    // eslint-disable-next-line react-hooks/exhaustive-deps
+  }, []);
+
+  function updateParam(key: string, value: string) {
+    setParams((prev) => ({ ...prev, [key]: value }));
+  }
+
+  function applyFilters(e?: React.FormEvent) {
+    e?.preventDefault();
+    run(params);
+  }
+
+  return (
+    <div className="report-runner">
+      <form className="report-filters" onSubmit={applyFilters}>
+        {def.params.map((p) => (
+          <ParamField
+            key={p.key}
+            param={p}
+            value={params[p.key] ?? ""}
+            onChange={(v) => updateParam(p.key, v)}
+          />
+        ))}
+        <div className="report-filters-actions">
+          <button
+            type="submit"
+            className="btn btn-primary"
+            disabled={loading}
+          >
+            {loading ? "Corriendo…" : "Correr reporte"}
+          </button>
+        </div>
+      </form>
+
+      {error && <div className="report-error">{error}</div>}
+
+      {result && (
+        <ResultBlock def={def} result={result} params={params} loading={loading} />
+      )}
+    </div>
+  );
+}
+
+/* ---------------------------------------------------------- one param field */
+
+function ParamField({
+  param,
+  value,
+  onChange,
+}: {
+  param: ReportParam;
+  value: string;
+  onChange: (v: string) => void;
+}) {
+  const label = (
+    <span className="filter-label">
+      {param.label}
+      {param.kind === "customer-picker" && !value && (
+        <span className="muted" style={{ marginLeft: 6, fontWeight: 400 }}>
+          (requerido)
+        </span>
+      )}
+    </span>
+  );
+
+  if (param.kind === "select") {
+    return (
+      <label className="filter-field">
+        {label}
+        <select
+          className="input select"
+          value={value}
+          onChange={(e) => onChange(e.target.value)}
+        >
+          {!param.defaultValue && <option value="">—</option>}
+          {param.options.map((o) => (
+            <option key={o.value} value={o.value}>
+              {o.label}
+            </option>
+          ))}
+        </select>
+      </label>
+    );
+  }
+  if (param.kind === "date") {
+    return (
+      <label className="filter-field">
+        {label}
+        <input
+          type="date"
+          className="input"
+          value={value}
+          onChange={(e) => onChange(e.target.value)}
+        />
+      </label>
+    );
+  }
+  if (param.kind === "number") {
+    return (
+      <label className="filter-field">
+        {label}
+        <input
+          type="number"
+          className="input"
+          value={value}
+          onChange={(e) => onChange(e.target.value)}
+          placeholder={param.defaultValue}
+        />
+      </label>
+    );
+  }
+  if (param.kind === "customer-picker") {
+    return (
+      <div className="filter-field">
+        {label}
+        <CustomerPicker
+          value={value}
+          onPick={(id) => onChange(id)}
+        />
+      </div>
+    );
+  }
+  return (
+    <label className="filter-field">
+      {label}
+      <input
+        type="text"
+        className="input"
+        value={value}
+        onChange={(e) => onChange(e.target.value)}
+        placeholder={param.placeholder ?? ""}
+      />
+    </label>
+  );
+}
+
+/* ---------------------------------------------------------- results block */
+
+function ResultBlock({
+  def,
+  result,
+  params,
+  loading,
+}: {
+  def: ReportDef;
+  result: ReportRunResult;
+  params: Record<string, string>;
+  loading: boolean;
+}) {
+  return (
+    <div className="report-result">
+      <div className="report-result-head">
+        <div className="report-result-meta">
+          {result.subtitle && <p className="muted">{result.subtitle}</p>}
+          <p className="muted small">
+            {formatNumber(result.rows.length)} fila
+            {result.rows.length === 1 ? "" : "s"}
+            {result.totals && (
+              <>
+                {" "}·{" "}
+                {Object.entries(result.totals)
+                  .map(([k, v]) => `${k}: ${v}`)
+                  .join(" · ")}
+              </>
+            )}
+          </p>
+        </div>
+        <div className="report-output-buttons">
+          <a
+            className="btn btn-outline btn-sm"
+            href={reportDownloadUrl(def.slug, "print", params)}
+            target="_blank"
+            rel="noopener"
+          >
+            Imprimir
+          </a>
+          <a
+            className="btn btn-outline btn-sm"
+            href={reportDownloadUrl(def.slug, "csv", params)}
+            download
+          >
+            CSV
+          </a>
+          <a
+            className="btn btn-outline btn-sm"
+            href={reportDownloadUrl(def.slug, "xlsx", params)}
+            download
+          >
+            Excel
+          </a>
+          <a
+            className="btn btn-outline btn-sm"
+            href={reportDownloadUrl(def.slug, "pdf", params)}
+            download
+          >
+            PDF
+          </a>
+        </div>
+      </div>
+
+      {loading && <div className="report-loading">Actualizando…</div>}
+
+      {def.format === "statement" ? (
+        <StatementLayout result={result} />
+      ) : def.format === "letter" ? (
+        <LetterLayout result={result} />
+      ) : (
+        <TabularLayout result={result} />
+      )}
+    </div>
+  );
+}
+
+/* ---------------------------------------------------------- tabular layout */
+
+function TabularLayout({ result }: { result: ReportRunResult }) {
+  if (result.rows.length === 0) {
+    return (
+      <div className="empty-inline">
+        No se encontraron filas con los filtros actuales.
+      </div>
+    );
+  }
+  return (
+    <div className="report-table-wrap">
+      <table className="report-table">
+        <thead>
+          <tr>
+            {result.columns.map((c) => (
+              <th
+                key={c.key}
+                style={{
+                  textAlign: c.align ?? "left",
+                  width: c.width ? `${c.width * 6}px` : undefined,
+                }}
+              >
+                {c.label}
+              </th>
+            ))}
+          </tr>
+        </thead>
+        <tbody>
+          {result.rows.map((r, i) => (
+            <tr key={i}>
+              {result.columns.map((c) => {
+                const v = r[c.key];
+                return (
+                  <td
+                    key={c.key}
+                    style={{
+                      textAlign: c.align ?? "left",
+                      fontVariantNumeric:
+                        c.type === "money" || c.type === "number"
+                          ? "tabular-nums"
+                          : undefined,
+                    }}
+                  >
+                    {formatCell(v, c.type)}
+                  </td>
+                );
+              })}
+            </tr>
+          ))}
+        </tbody>
+        {result.totals && (
+          <tfoot>
+            <tr>
+              <td
+                colSpan={result.columns.length}
+                className="report-totals"
+              >
+                {Object.entries(result.totals)
+                  .map(([k, v]) => `${k}: ${v}`)
+                  .join("   ·   ")}
+              </td>
+            </tr>
+          </tfoot>
+        )}
+      </table>
+    </div>
+  );
+}
+
+function formatCell(v: unknown, type: string): string {
+  if (v === null || v === undefined || v === "") return "—";
+  if (type === "money") {
+    const n = Number(v);
+    return Number.isFinite(n)
+      ? new Intl.NumberFormat("es-MX", {
+          minimumFractionDigits: 2,
+          maximumFractionDigits: 2,
+        }).format(n)
+      : String(v);
+  }
+  if (type === "number") {
+    const n = Number(v);
+    return Number.isFinite(n) ? formatNumber(n) : String(v);
+  }
+  return String(v);
+}
+
+/* --------------------------------------------------------- statement layout */
+
+function StatementLayout({ result }: { result: ReportRunResult }) {
+  // The edo-cuenta-datos report synthesises a list with __kind
+  // discriminators (header, summary, movements-header, plain movement).
+  // Group by kind and render each block inline.
+  const header = result.rows.find((r) => r.__kind === "header") as
+    | Record<string, unknown>
+    | undefined;
+  const summaries = result.rows.filter((r) => r.__kind === "summary");
+  const movements = result.rows.filter(
+    (r) => r.__kind !== "header" && r.__kind !== "summary" && r.__kind !== "movements-header",
+  );
+
+  if (!header) {
+    return (
+      <div className="empty-inline">
+        Selecciona un cliente y corre el reporte para ver el estado de cuenta.
+      </div>
+    );
+  }
+
+  return (
+    <div className="statement">
+      <header className="statement-head">
+        <h2 className="statement-name">{String(header.name ?? "—")}</h2>
+        {Boolean(header.address) && (
+          <p className="muted">{String(header.address)}</p>
+        )}
+        {Boolean(header.city) && <p className="muted">{String(header.city)}</p>}
+        {Boolean(header.phone || header.email) && (
+          <p className="muted small">
+            {String(header.phone ?? "")}
+            {header.phone && header.email ? " · " : ""}
+            {String(header.email ?? "")}
+          </p>
+        )}
+      </header>
+
+      {summaries.length > 0 && (
+        <section className="statement-summary">
+          <h3 className="statement-section-title">Resumen por moneda</h3>
+          <table className="report-table">
+            <thead>
+              <tr>
+                <th>Moneda</th>
+                <th style={{ textAlign: "right" }}>Cargos</th>
+                <th style={{ textAlign: "right" }}>Abonos</th>
+                <th style={{ textAlign: "right" }}>Saldo</th>
+                <th style={{ textAlign: "right" }}>Movs.</th>
+              </tr>
+            </thead>
+            <tbody>
+              {summaries.map((s, i) => (
+                <tr key={i}>
+                  <td>{String(s.currency)}</td>
+                  <td style={{ textAlign: "right", fontVariantNumeric: "tabular-nums" }}>
+                    {formatCell(s.charges, "money")}
+                  </td>
+                  <td style={{ textAlign: "right", fontVariantNumeric: "tabular-nums" }}>
+                    {formatCell(s.credits, "money")}
+                  </td>
+                  <td style={{ textAlign: "right", fontVariantNumeric: "tabular-nums" }}>
+                    {formatCell(s.balance, "money")}
+                  </td>
+                  <td style={{ textAlign: "right" }}>{formatCell(s.count, "number")}</td>
+                </tr>
+              ))}
+            </tbody>
+          </table>
+        </section>
+      )}
+
+      {movements.length > 0 && (
+        <section className="statement-movements">
+          <h3 className="statement-section-title">Movimientos</h3>
+          <div className="report-table-wrap">
+            <table className="report-table">
+              <thead>
+                <tr>
+                  <th>Fecha</th>
+                  <th>Concepto</th>
+                  <th>Referencia</th>
+                  <th style={{ textAlign: "right" }}>Cargo / Abono</th>
+                  <th style={{ textAlign: "right" }}>Saldo</th>
+                </tr>
+              </thead>
+              <tbody>
+                {movements.map((m, i) => (
+                  <tr key={i}>
+                    <td>{String(m.date ?? "")}</td>
+                    <td>{String(m.concept ?? "")}</td>
+                    <td>{String(m.reference ?? "")}</td>
+                    <td style={{ textAlign: "right", fontVariantNumeric: "tabular-nums" }}>
+                      {formatCell(m.amount, "money")}
+                    </td>
+                    <td style={{ textAlign: "right", fontVariantNumeric: "tabular-nums" }}>
+                      {formatCell(m.balanceAfter, "money")}
+                    </td>
+                  </tr>
+                ))}
+              </tbody>
+            </table>
+          </div>
+        </section>
+      )}
+    </div>
+  );
+}
+
+/* ----------------------------------------------------------- letter layout */
+
+const GENERATION_LABEL: Record<string, string> = {
+  "1": "1er aviso",
+  "2": "2o aviso",
+  "3": "3er aviso",
+};
+
+interface LetterVehicle {
+  make?: string | null;
+  model?: string | null;
+  modelYear?: string | number | null;
+  bodyType?: string | null;
+  engineNumber?: string | null;
+  licensePlate?: string | null;
+}
+
+/**
+ * One card per policy due for renewal — the parameterized replacement for
+ * the ~40 cloned "AVISO DE RENOVACION" Access reports (see
+ * docs/RENEWAL_NOTICES.md). Coverage figures (CSL limit, medical coverage,
+ * etc.) come from data (`aviso-renovacion`'s ReportDef reads
+ * `Policy.coveragesJson`) instead of the legacy's hand-typed label text, so
+ * one layout renders every carrier/coverage combination.
+ */
+function LetterLayout({ result }: { result: ReportRunResult }) {
+  const letters = result.rows.filter((r) => r.__kind === "letter");
+
+  if (letters.length === 0) {
+    return (
+      <div className="empty-inline">
+        No hay pólizas por vencer con los filtros actuales.
+      </div>
+    );
+  }
+
+  return (
+    <div className="renewal-letters">
+      {letters.map((r, i) => {
+        const vehicle = r.vehicle as LetterVehicle | null;
+        const generation = String(r.generation ?? "1");
+        return (
+          <article className="renewal-letter" key={String(r.policyId ?? i)}>
+            <header className="renewal-letter-head">
+              <div>
+                <h2 className="renewal-letter-title">Aviso de renovación</h2>
+                <p className="muted small">
+                  {GENERATION_LABEL[generation] ?? `Aviso ${generation}`}
+                </p>
+              </div>
+              <div className="renewal-letter-status">
+                {r.sentAt ? (
+                  <span className="badge badge-positive">
+                    <span className="dot" />
+                    Enviado {String(r.sentAt)}
+                  </span>
+                ) : (
+                  <span className="badge badge-neutral">
+                    <span className="dot" />
+                    Pendiente
+                  </span>
+                )}
+              </div>
+            </header>
+
+            <div className="renewal-letter-grid">
+              <div>
+                <p className="muted small">Cliente</p>
+                <p className="renewal-letter-value">
+                  {String(r.customerName ?? "—")}
+                </p>
+              </div>
+              <div>
+                <p className="muted small">Póliza</p>
+                <p className="renewal-letter-value">
+                  {String(r.policyNumber ?? "—")}
+                </p>
+              </div>
+              <div>
+                <p className="muted small">Aseguradora</p>
+                <p className="renewal-letter-value">
+                  {String(r.provider ?? "—")}
+                </p>
+              </div>
+              <div>
+                <p className="muted small">Vence</p>
+                <p className="renewal-letter-value">
+                  {String(r.policyTo ?? "—")}
+                </p>
+              </div>
+            </div>
+
+            {vehicle && (
+              <div className="renewal-letter-vehicle">
+                <p className="muted small">Vehículo asegurado</p>
+                <p className="renewal-letter-value">
+                  {[vehicle.modelYear, vehicle.make, vehicle.model]
+                    .filter(Boolean)
+                    .join(" ")}
+                  {vehicle.bodyType ? ` · ${vehicle.bodyType}` : ""}
+                </p>
+                <p className="muted small">
+                  {[
+                    vehicle.engineNumber && `Motor: ${vehicle.engineNumber}`,
+                    vehicle.licensePlate && `Placa: ${vehicle.licensePlate}`,
+                  ]
+                    .filter(Boolean)
+                    .join(" · ")}
+                </p>
+              </div>
+            )}
+
+            <div className="renewal-letter-coverage">
+              <CoverageItem label="Cobertura (días)" value={r.coverageDays} />
+              <CoverageItem label="CSL" value={r.cslLimit} money />
+              <CoverageItem label="Gastos médicos" value={r.medicalCoverage} money />
+              <CoverageItem label="Daños a propiedad" value={r.propertyDamage} money />
+              <CoverageItem label="Responsabilidad por persona" value={r.perPersonLiability} money />
+            </div>
+
+            <div className="renewal-letter-premium">
+              {Boolean(r.netPremium) && (
+                <span>Prima neta: {formatCell(r.netPremium, "money")}</span>
+              )}
+              {Boolean(r.total) && (
+                <span className="renewal-letter-total">
+                  Total: {formatCell(r.total, "money")} {String(r.currency ?? "")}
+                </span>
+              )}
+            </div>
+          </article>
+        );
+      })}
+    </div>
+  );
+}
+
+function CoverageItem({
+  label,
+  value,
+  money,
+}: {
+  label: string;
+  value: unknown;
+  money?: boolean;
+}) {
+  if (value === null || value === undefined || value === "") return null;
+  return (
+    <div className="renewal-letter-coverage-item">
+      <p className="muted small">{label}</p>
+      <p className="renewal-letter-value">
+        {money ? formatCell(value, "money") : String(value)}
+      </p>
+    </div>
+  );
+}
+
+// Hint to the bundler that API_ORIGIN is part of the API surface used here.
+void API_ORIGIN;

apps/web/src/lib/api.ts

@@ -44,6 +44,8 @@ import type {
   PropertyListResponse,
   PropertySort,
   PropertyStats,
+  ReportCatalog,
+  ReportRunResult,
   ServiceInput,
   TrustInput,
   Role,
@@ -55,8 +57,24 @@ import type {
   UserRow,
 } from "./types";
 
-export const API_ORIGIN =
+// Resolve the API origin at runtime, not build time. In the browser it comes
-  process.env.NEXT_PUBLIC_API_ORIGIN ?? "http://localhost:3001";
+// from window.__API_ORIGIN__, injected server-side by the root layout from the
+// deploy .env (API_ORIGIN) — so one built image serves any deployment. On the
+// server (SSR) read process.env directly. NEXT_PUBLIC_API_ORIGIN stays as the
+// dev/build fallback.
+function resolveApiOrigin(): string {
+  if (typeof window !== "undefined") {
+    const injected = (window as { __API_ORIGIN__?: string }).__API_ORIGIN__;
+    if (injected) return injected;
+  }
+  return (
+    process.env.API_ORIGIN ??
+    process.env.NEXT_PUBLIC_API_ORIGIN ??
+    "http://localhost:3001"
+  );
+}
+
+export const API_ORIGIN = resolveApiOrigin();
 
 export class ApiError extends Error {
   status: number;
@@ -413,6 +431,47 @@ export function removePropertyDocument(
   });
 }
 
+export function propertyDocumentDownloadUrl(
+  propertyId: string,
+  documentId: string,
+): string {
+  return `${API_ORIGIN}/properties/${propertyId}/documents/${documentId}/download`;
+}
+
+export function uploadPropertyDocument(
+  propertyId: string,
+  file: File,
+  type?: string,
+): Promise<unknown> {
+  const q = type ? `?type=${encodeURIComponent(type)}` : "";
+  return uploadFile(`/properties/${propertyId}/documents${q}`, file);
+}
+
+export function removePolicyDocument(
+  policyId: string,
+  documentId: string,
+): Promise<unknown> {
+  return apiFetch(`/policies/${policyId}/documents/${documentId}`, {
+    method: "DELETE",
+  });
+}
+
+export function policyDocumentDownloadUrl(
+  policyId: string,
+  documentId: string,
+): string {
+  return `${API_ORIGIN}/policies/${policyId}/documents/${documentId}/download`;
+}
+
+export function uploadPolicyDocument(
+  policyId: string,
+  file: File,
+  type?: string,
+): Promise<unknown> {
+  const q = type ? `?type=${encodeURIComponent(type)}` : "";
+  return uploadFile(`/policies/${policyId}/documents${q}`, file);
+}
+
 /* ------------------------------------------- Billing / statements module */
 
 export interface MovementQuery {
@@ -598,17 +657,29 @@ export function resetUserPassword(id: string, password: string): Promise<UserRow
   });
 }
 
+export function deleteUser(id: string): Promise<void> {
+  return apiFetch<void>(`/users/${id}`, { method: "DELETE" });
+}
+
 /* ------------------------------------------- DB operations (admin only) */
 
 export function listIngest(): Promise<IngestFile[]> {
   return apiFetch<IngestFile[]>("/ops/ingest");
 }
 
-/** Multipart upload — not JSON, so it bypasses apiFetch's Content-Type. */
+/**
-export async function uploadIngest(name: string, file: File): Promise<void> {
+ * Multipart upload — not JSON, so it bypasses apiFetch's Content-Type. `path`
+ * is API-relative (may include a query string); `filename` overrides the part
+ * name sent to the server.
+ */
+export async function uploadFile(
+  path: string,
+  file: File,
+  filename?: string,
+): Promise<unknown> {
   const body = new FormData();
-  body.append("file", file, name);
+  body.append("file", file, filename ?? file.name);
-  const res = await fetch(`${API_ORIGIN}/ops/ingest/${encodeURIComponent(name)}`, {
+  const res = await fetch(`${API_ORIGIN}${path}`, {
     method: "POST",
     credentials: "include",
     body,
@@ -623,6 +694,11 @@ export async function uploadIngest(name: string, file: File): Promise<void> {
     }
     throw new ApiError(res.status, message);
   }
+  return res.status === 204 ? undefined : res.json().catch(() => undefined);
+}
+
+export function uploadIngest(name: string, file: File): Promise<unknown> {
+  return uploadFile(`/ops/ingest/${encodeURIComponent(name)}`, file, name);
 }
 
 export function deleteIngest(name: string): Promise<unknown> {
@@ -656,3 +732,37 @@ export function startOpsJob(kind: OpsJobKind, file?: string): Promise<OpsJob> {
     body: JSON.stringify({ kind, file }),
   });
 }
+
+/* ----------------------------------------------------------- Reports module */
+
+export function getReportCatalog(): Promise<ReportCatalog> {
+  return apiFetch<ReportCatalog>("/reports");
+}
+
+export function runReport(
+  slug: string,
+  params: Record<string, string | undefined>,
+): Promise<ReportRunResult> {
+  const qs = new URLSearchParams();
+  for (const [k, v] of Object.entries(params)) {
+    if (v != null && v !== "") qs.set(k, v);
+  }
+  const tail = qs.toString();
+  return apiFetch<ReportRunResult>(`/reports/${slug}${tail ? `?${tail}` : ""}`);
+}
+
+/** Build a download URL for a report's file output. The session cookie
+ *  travels with the browser's same-origin navigation, so a plain `href`
+ *  is enough — no fetch-with-credentials dance. */
+export function reportDownloadUrl(
+  slug: string,
+  format: "csv" | "xlsx" | "pdf" | "print",
+  params: Record<string, string | undefined>,
+): string {
+  const qs = new URLSearchParams();
+  for (const [k, v] of Object.entries(params)) {
+    if (v != null && v !== "") qs.set(k, v);
+  }
+  const tail = qs.toString();
+  return `${API_ORIGIN}/reports/${slug}/${format}${tail ? `?${tail}` : ""}`;
+}

apps/web/src/lib/types.ts

@@ -667,6 +667,8 @@ export interface Transaction {
   period?: string | null;
   message: string | null;
   checkNumber: string | null;
+  /** App-voided (`voidedAt` set). UI strikes; totals exclude. */
+  voidedAt?: string | null;
   type: TransactionType | null;
 }
 
@@ -1025,3 +1027,58 @@ export interface BankSummary {
   /** Cumulative figure the selected year opened on. */
   opening: string;
 }
+
+/* ----------------------------------------------------------- Reports module */
+
+export type ReportDomain =
+  | "clientes"
+  | "polizas"
+  | "servicios"
+  | "estado-cuenta"
+  | "chequera";
+
+export type ReportFormat = "tabular" | "statement" | "letter";
+
+/** Param declaration a report exposes to its filter form. */
+export type ReportParam =
+  | { key: string; label: string; kind: "text"; placeholder?: string; defaultValue?: string }
+  | { key: string; label: string; kind: "number"; defaultValue?: string }
+  | { key: string; label: string; kind: "date"; endOfDay?: boolean; defaultValue?: string }
+  | {
+      key: string;
+      label: string;
+      kind: "select";
+      options: { value: string; label: string }[];
+      defaultValue?: string;
+    }
+  | { key: string; label: string; kind: "customer-picker" };
+
+export interface ReportColumn {
+  key: string;
+  label: string;
+  type: "text" | "number" | "money" | "date";
+  align?: "left" | "right";
+  width?: number;
+}
+
+export interface ReportDef {
+  slug: string;
+  title: string;
+  description: string;
+  domain: ReportDomain;
+  legacyName: string | null;
+  format: ReportFormat;
+  params: ReportParam[];
+  columns: ReportColumn[];
+}
+
+export interface ReportRunResult {
+  columns: ReportColumn[];
+  rows: Array<Record<string, unknown>>;
+  totals?: Record<string, string | number>;
+  subtitle?: string;
+}
+
+export interface ReportCatalog {
+  items: ReportDef[];
+}

deploy/jorgecuadros-app.env.example

@@ -0,0 +1,34 @@
+# Stack env for deploy/jorgecuadros-app.stack.yml (PROD).
+# Paste these into the Portainer stack's "Environment variables" at deploy time.
+# Do NOT commit real secrets — this file is a template only.
+#
+# HOST below = the swarm host the db/minio/app stacks publish on (cubex).
+
+# Which built image tag to run. latest = default-branch build; or pin sha-<x> / vX.Y.Z.
+APP_TAG=latest
+
+# --- Public URLs (what the end user's BROWSER hits) ---------------------------
+# API_ORIGIN is injected into the web app at runtime and used for browser fetches
+# + document download links, so it must be browser-reachable (not swarm-internal).
+# WEB_ORIGIN is the web app's own public origin; the API allows it via CORS.
+API_ORIGIN=http://192.168.4.212:3001
+WEB_ORIGIN=http://192.168.4.212:3000
+
+# Published ports on the swarm host.
+API_PORT=3001
+WEB_PORT=3000
+
+# --- Database (points at the jorgecuadros-prod-db stack) ----------------------
+# prod db publishes 3306 on the host (see deploy/jorgecuadros-db.stack.yml).
+DATABASE_URL=mysql://jorgecuadros:CHANGE_ME@192.168.4.212:3306/jorgecuadros
+
+# --- Auth --------------------------------------------------------------------
+# 64-hex random. Generate: openssl rand -hex 32
+SESSION_SECRET=CHANGE_ME
+
+# --- Object storage (points at the jorgecuadros-prod-minio stack) -------------
+# Server-side only; prod minio API publishes 9000 on the host.
+S3_ENDPOINT=http://192.168.4.212:9000
+S3_BUCKET=jorgecuadros-documents
+MINIO_ROOT_USER=jc_minio
+MINIO_ROOT_PASSWORD=CHANGE_ME

deploy/jorgecuadros-app.stack.yml

@@ -0,0 +1,96 @@
+# Application stack for the Jorge Cuadros platform: the NestJS API + the Next.js
+# web front-end. The two images are built + pushed by .gitea/workflows/build.yml:
+#   git.mancinas.io/rmancinas/jorgecuadros-api
+#   git.mancinas.io/rmancinas/jorgecuadros-web
+#
+# This stack does NOT ship MySQL or MinIO — those are their own stacks
+# (deploy/jorgecuadros-db.stack.yml, deploy/jorgecuadros-minio.stack.yml). The
+# API reaches them over the network via DATABASE_URL / S3_ENDPOINT, which point
+# at the db + minio stacks' published ingress ports on the swarm host.
+#
+# Target: Portainer local endpoint on cubex (3-node Swarm). PROD only.
+# Deploy with a stack env that supplies every ${VAR:?...} below — see
+# deploy/jorgecuadros-app.env.example for the full list.
+#
+# Statefulness: the API keeps uploaded Access files (ingest) and DB backups on
+# named volumes, which are node-local. So the API is pinned to the same node as
+# the db/minio stacks (node label jorgecuadros_db == true) — a reschedule would
+# otherwise start against empty ingest/backup volumes. The web tier is
+# stateless and floats freely.
+#
+# The web image is NOT URL-baked: the browser's API origin is injected at
+# runtime from API_ORIGIN (see apps/web/src/app/layout.tsx), so this same image
+# works for any deployment — set the URL here, not at build time.
+
+version: "3.8"
+
+services:
+  api:
+    image: git.mancinas.io/rmancinas/jorgecuadros-api:${APP_TAG:-latest}
+    environment:
+      DATABASE_URL: ${DATABASE_URL:?DATABASE_URL must be set}
+      SESSION_SECRET: ${SESSION_SECRET:?SESSION_SECRET must be set}
+      # CORS: the public origin the browser loads the web app from.
+      WEB_ORIGIN: ${WEB_ORIGIN:?WEB_ORIGIN must be set}
+      PORT: "3001"
+      INGEST_DIR: /data/ingest
+      BACKUP_DIR: /data/backups
+      MIGRATION_ENV: prod
+      # Object storage — internal endpoint the API (server-side) uses to reach
+      # the minio stack. Not browser-facing (downloads proxy through the API).
+      S3_ENDPOINT: ${S3_ENDPOINT:?S3_ENDPOINT must be set}
+      S3_BUCKET: ${S3_BUCKET:-jorgecuadros-documents}
+      MINIO_ROOT_USER: ${MINIO_ROOT_USER:?MINIO_ROOT_USER must be set}
+      MINIO_ROOT_PASSWORD: ${MINIO_ROOT_PASSWORD:?MINIO_ROOT_PASSWORD must be set}
+    ports:
+      - target: 3001
+        published: ${API_PORT:-3001}
+        protocol: tcp
+        mode: ingress
+    volumes:
+      - ingest_data:/data/ingest
+      - backup_data:/data/backups
+    deploy:
+      replicas: 1
+      placement:
+        constraints:
+          - node.labels.jorgecuadros_db == true
+      restart_policy:
+        condition: any
+      update_config:
+        order: stop-first
+    healthcheck:
+      test: ["CMD-SHELL", "wget -qO- http://localhost:3001/health || exit 1"]
+      interval: 15s
+      timeout: 5s
+      retries: 10
+      start_period: 30s
+
+  web:
+    image: git.mancinas.io/rmancinas/jorgecuadros-web:${APP_TAG:-latest}
+    environment:
+      # Public API URL the browser calls (injected at runtime, see layout.tsx).
+      API_ORIGIN: ${API_ORIGIN:?API_ORIGIN must be set}
+    ports:
+      - target: 3000
+        published: ${WEB_PORT:-3000}
+        protocol: tcp
+        mode: ingress
+    depends_on:
+      - api
+    deploy:
+      replicas: 1
+      restart_policy:
+        condition: any
+      update_config:
+        order: start-first
+    healthcheck:
+      test: ["CMD-SHELL", "wget -qO- http://localhost:3000/ >/dev/null 2>&1 || exit 1"]
+      interval: 15s
+      timeout: 5s
+      retries: 10
+      start_period: 30s
+
+volumes:
+  ingest_data:
+  backup_data:

dev.sh

@@ -0,0 +1,50 @@
+#!/usr/bin/env bash
+#
+# Start the development servers (API + web).
+# Runs both in parallel and shuts both down on Ctrl-C.
+#
+set -euo pipefail
+
+cd "$(dirname "$0")"
+
+WEB_PORT=4500
+API_PORT=4501
+
+api_pid=""
+web_pid=""
+
+# Free a port by killing whatever is listening on it (stale dev servers).
+free_port() {
+  local port="$1"
+  local pids
+  pids="$(lsof -tiTCP:"$port" -sTCP:LISTEN 2>/dev/null || true)"
+  if [ -n "$pids" ]; then
+    echo "Freeing port $port (killing: $pids)"
+    kill $pids 2>/dev/null || true
+    sleep 1
+  fi
+}
+
+# Kill the child servers once, on Ctrl-C or exit.
+cleanup() {
+  trap - EXIT INT TERM
+  echo ""
+  echo "Shutting down dev servers..."
+  [ -n "$api_pid" ] && kill "$api_pid" 2>/dev/null || true
+  [ -n "$web_pid" ] && kill "$web_pid" 2>/dev/null || true
+}
+trap cleanup EXIT INT TERM
+
+free_port "$API_PORT"
+free_port "$WEB_PORT"
+
+echo "Starting API  -> http://localhost:$API_PORT"
+pnpm --filter @jorgecuadros/api start:dev &
+api_pid=$!
+
+echo "Starting web  -> http://localhost:$WEB_PORT"
+pnpm --filter @jorgecuadros/web dev &
+web_pid=$!
+
+# Wait for both. Ctrl-C fires the trap, which kills them.
+wait

docker-compose.yml

@@ -18,6 +18,25 @@ services:
       timeout: 5s
       retries: 10
 
+  minio:
+    image: minio/minio:RELEASE.2024-10-13T13-34-11Z
+    restart: unless-stopped
+    command: server /data --console-address ":9001"
+    environment:
+      MINIO_ROOT_USER: ${MINIO_ROOT_USER:-jc_minio}
+      MINIO_ROOT_PASSWORD: ${MINIO_ROOT_PASSWORD:-jc_minio_dev}
+    ports:
+      - "9000:9000"
+      - "9001:9001"
+    volumes:
+      - minio_data:/data
+    healthcheck:
+      test: ["CMD-SHELL", "mc ready local || curl -f http://localhost:9000/minio/health/live || exit 1"]
+      interval: 10s
+      timeout: 5s
+      retries: 12
+      start_period: 20s
+
   api:
     build:
       context: .
@@ -26,6 +45,8 @@ services:
     depends_on:
       mysql:
         condition: service_healthy
+      minio:
+        condition: service_healthy
     environment:
       DATABASE_URL: mysql://jorgecuadros:jorgecuadros@mysql:3306/jorgecuadros
       SESSION_SECRET: ${SESSION_SECRET:?SESSION_SECRET must be set}
@@ -34,6 +55,10 @@ services:
       INGEST_DIR: /data/ingest
       BACKUP_DIR: /data/backups
       MIGRATION_ENV: dev
+      S3_ENDPOINT: http://minio:9000
+      S3_BUCKET: jorgecuadros-documents
+      MINIO_ROOT_USER: ${MINIO_ROOT_USER:-jc_minio}
+      MINIO_ROOT_PASSWORD: ${MINIO_ROOT_PASSWORD:-jc_minio_dev}
     volumes:
       - ingest_data:/data/ingest
       - backup_data:/data/backups
@@ -56,3 +81,4 @@ volumes:
   mysql_data:
   ingest_data:
   backup_data:
+  minio_data:

docs/RENEWAL_NOTICES.md

@@ -0,0 +1,139 @@
+# Insurance Renewal Notices ("Atlas" reports)
+
+Staff refer to this report in the UI as "the Atlas report", but **Atlas
+isn't a report — it's a carrier**: `ATLAS, S.A.` is one of the insurance
+companies (`COMP` column) SEGUROS brokers policies for, alongside
+`QUALITAS, S.A.` and others. The legacy frontend (`SEGUROS 16.mdb`) never
+parameterized carrier or coverage tier in its renewal-notice report — it
+cloned the entire report + query chain once per carrier per coverage
+variant instead. This doc explains that clone pattern and the underlying
+workflow so the new platform can replace ~40 cloned Access objects with
+one parameterized feature.
+
+## Why this needed extra tooling
+
+`objects.json`/`LEGACY_DATABASES_OBJECTS.md` (see `migration/catalog_objects.py`)
+only capture report *names* — DAO's catalog interface doesn't expose a
+report's `RecordSource` or control layout, only the full Access object
+model does, and that model refused to load here
+(`"The Visual Basic for Applications project in the database is corrupt"`,
+a common failure mode for old .mdb files opened in a newer Access build).
+
+The workaround: `Application.SaveAsText(acReport, name, path)` exports a
+report's complete design as plain text without touching the VBA project.
+The raw (binary-blob-stripped) exports for the ATLAS renewal reports are
+committed in [`migration/legacy_report_defs/`](../migration/legacy_report_defs/):
+
+- `AMPL_R_RENEW_X_MES_NEW_ATLAS_13.txt` — Auto/Amplia (full coverage)
+- `AMPL_RENEW_X_MES_NEW_ATLAS_2013.txt` — Auto/Amplia, alternate batch
+- `RC_RENEW_X_MES_NEW_ATLAS_13.txt` — Auto/RC (liability only)
+- `RCR_RENEW_X_MES_NEWATLAS_2013.txt` — Auto/RC, renewal-of-renewal variant
+- `LIC_RENEW_X_VENCE_ATLAS_2013.txt` — Driver's-license insurance
+
+(`PrtDevMode`/`PrtMip`/`OleData`/`GUID` binary properties — printer
+settings and object GUIDs, no business meaning — were stripped so the
+files are readable text instead of multi-hundred-KB hex dumps.)
+
+## The report chain
+
+Each report is bound to a query that layers 2–3 other queries, filtered to
+one carrier, with two typed parameters staff fill in every run:
+
+```
+Report: AMPL R RENEW X MES NEW ATLAS 13
+  RecordSource -> Query: AMPL R RENEW CALC ATLAS 13
+    FROM [AMPL R CALC VIG], [AMPL R MENS]     (in-force calc view + installment schedule)
+    WHERE COMP = "ATLAS, S.A."
+      AND DatePart("m",[HASTA]) = [TECLEE MES DE VENCIMIENTO (1 A 12)]   -- typed param
+      AND DatePart("yyyy",[HASTA]) = [TECLEE AÑO DE VENCIMIENTO (1999)]  -- typed param
+```
+
+```
+Report: LIC RENEW X VENCE ATLAS 2013
+  RecordSource -> Query of the SAME NAME (query and report share a name)
+    FROM [LIC MENS], LIC INNER JOIN DATGRAL ... INNER JOIN [VIGENT CASA] ...
+    WHERE DatePart("m",[hasta]) = [TECLEE MES DE VENCIMIENTO 1 A 12]
+      AND DatePart("yyyy",[hasta]) = [TECLE AÑO VENCIMIENTO (1999)]
+      AND LIC.COMP = "ATLAS, S.A."
+```
+
+Staff pick a line of business, type the expiry month + year, and the
+report prints one notice per matching policy for that carrier that month.
+On screen the report is captioned **"AVISO DE RENOVACION"** (auto lines)
+or **"R E N E W A L   N O T I C E"** (license-insurance line). Every page
+prints the notice **twice** (identical top-half/bottom-half sections) —
+one copy to mail, one for the office file.
+
+## The multi-notice (reminder) workflow
+
+Renewal reminders escalate through **three generations**, each its own
+report clone, with a matching `CONTROL ...` companion report (a
+send/checklist log):
+
+| Generation | Report suffix | Control/log report |
+|---|---|---|
+| 1st notice | `RENEW` / (bare) | `CONTROL <LOB> RENEW X MES` |
+| 2nd notice | `RENEW2` | `CONTROL <LOB> RENEW2 X MES` (or `X MES` sibling) |
+| 3rd notice | `RENEW3` | `CONTROL <LOB> RENEW3 X MES` |
+
+This pattern repeats per line of business: `AMPL`/`AMPL R` (auto full
+coverage), `RC`/`RC R` (auto liability), `LIC` (driver's license), `RCR`,
+`MF`/`MF2`/`MF3` (home/multi-risk), `MCA2`, `ME`, `INCEN` (fire) — none of
+it is visible from the table schema alone, only from the report/query
+names (see `docs/LEGACY_DATABASES_OBJECTS.md`, "What the Reports actually
+reveal").
+
+## What's hardcoded vs. what's real policy data
+
+The extracted designs show the letter body mixes two very different kinds
+of content:
+
+1. **Per-policy data**, pulled live from the query: customer id, policy
+   number, vehicle (make/model/body/engine), expiry date.
+2. **Static label text baked into the report design**, re-typed by hand
+   every time a batch was cloned for a new rate or carrier — e.g. (from
+   `AMPL_R_RENEW_X_MES_NEW_ATLAS_13.txt`):
+   - `"COLLISION DEDUCTIBLE $ 500.00 Dls.  THEFT DEDUCTIBLE $ 1000.00 Dls. ..."`
+   - `"New Renewal annual Premium $ 365.25 Dls."`
+   - `"Total Annual Premium $ 405.25 Dls"`
+   - the whole CSL/medical-coverage recommendation and rental-car upsell
+     paragraphs
+
+None of those dollar figures are formulas — they're literal text, which is
+*why* there are so many near-duplicate reports: a new coverage tier or
+rate meant cloning the whole report and hand-editing the labels, rather
+than changing a parameter.
+
+The underlying **data these figures should come from already exists** on
+the source tables and is preserved (unmapped-but-captured) in
+`Policy.coveragesJson` after migration — confirmed against
+`docs/LEGACY_DATABASES.md`'s table appendix:
+
+| Legacy column | Sanitized `coveragesJson` key | Meaning |
+|---|---|---|
+| `COBERTURA` | `cobertura` | Coverage days/territory tier (30/40/50/365) |
+| `CSL LIMITE` | `csl_limite` | Combined single limit (liability) |
+| `GASTOS MEDICO` | `gastos_medico` | Medical coverage amount |
+| `SERVICIO ADICIONAL` | `servicio_adicional` (LICENCIAS: `servicio_adiconal`, a source typo) | Add-on service flag |
+| `PROPIEDADES` | `propiedades` | Property-damage coverage amount |
+| `PERSONAS` | `personas` | Per-person liability amount |
+
+(`Policy.netPremium`/`total`/`currency` are already first-class columns —
+see `packages/database/prisma/schema.prisma`.)
+
+**Migration implication:** a rebuilt renewal notice should render these
+from data (one parameterized template), not from report design text. See
+`RenewalNotice` in `schema.prisma` and the `aviso-renovacion` entry in
+`apps/api/src/reports/reports.registry.ts` for the first cut at this.
+
+## Caveats
+
+- Only the ATLAS variants were extracted verbatim; the QUALITAS and
+  "generic" (no-carrier-suffix) clones weren't pulled but are presumed
+  structurally identical modulo the `COMP` filter and hardcoded figures.
+- `coveragesJson` key names above are derived from
+  `migration/extract.py`'s `sanitize_column_name` (lowercase,
+  non-alphanumeric → `_`) applied to the *source* column names in
+  `docs/LEGACY_DATABASES.md`, not verified against a live migrated
+  database (no staged output was present in this environment). Confirm
+  against real data before wiring a template to these keys.

migration/prune_empty_customers.py

@@ -50,11 +50,21 @@ def main() -> None:
     ap.add_argument("--env", default="dev", help="target environment (reads deploy/.env.<env>)")
     ap.add_argument("--dry-run", action="store_true",
                     help="report and write the audit CSV, but delete nothing")
+    ap.add_argument("--sync", action="store_true",
+                    help="only prune legacy-owned empties; never touch manually-added "
+                         "customers (those with no customer_legacy_refs row)")
     args = ap.parse_args()
 
     conn = connect(args.env)
     cur = conn.cursor()
-    print(f"[prune] target env: {args.env}")
+    print(f"[prune] target env: {args.env}{' (sync: legacy-owned only)' if args.sync else ''}")
+
+    # In sync mode a manually-added customer (no legacy ref) with no records yet
+    # is a legitimate new row, not Access-era dead weight — so restrict the prune
+    # to customers that carry a legacy ref.
+    where = EMPTY_WHERE + (
+        "\nAND EXISTS (SELECT 1 FROM customer_legacy_refs lr WHERE lr.customerId = c.id)"
+        if args.sync else "")
 
     cur.execute("SELECT COUNT(*) FROM customers")
     before = cur.fetchone()[0]
@@ -67,7 +77,7 @@ def main() -> None:
                             ORDER BY r.sourceSystem SEPARATOR ' | ')
         FROM customers c
         LEFT JOIN customer_legacy_refs r ON r.customerId = c.id
-        WHERE {EMPTY_WHERE}
+        WHERE {where}
         GROUP BY c.id
         ORDER BY c.nameMissing, c.name
     """)
@@ -86,10 +96,17 @@ def main() -> None:
     if args.dry_run:
         print(f"  dry run — {len(rows)} would be pruned, nothing deleted")
     else:
-        cur.execute(f"DELETE r FROM customer_legacy_refs r JOIN customers c ON c.id = r.customerId "
+        # Delete by the exact id set selected above (which already carries the
-                    f"WHERE {EMPTY_WHERE}")
+        # sync guard). Deleting via the id list avoids referencing the delete
+        # target table inside its own WHERE (MySQL error 1093) and keeps refs +
+        # customers on the same set regardless of delete order.
+        ids = [r[0] for r in rows]
+        refs_deleted = deleted = 0
+        if ids:
+            fmt = ",".join(["%s"] * len(ids))
+            cur.execute(f"DELETE FROM customer_legacy_refs WHERE customerId IN ({fmt})", ids)
             refs_deleted = cur.rowcount
-        cur.execute(f"DELETE c FROM customers c WHERE {EMPTY_WHERE}")
+            cur.execute(f"DELETE FROM customers WHERE id IN ({fmt})", ids)
             deleted = cur.rowcount
         conn.commit()
         print(f"  deleted {deleted} customers, {refs_deleted} legacy refs")

migration/run_all.py

@@ -53,6 +53,9 @@ SYNC_STEPS = [
     "transform_properties.py",
     "transform_policies.py",
     "transform_transactions.py",
+    # Manual-safe prune: drops legacy-owned empties that the customer upsert
+    # re-creates from Parquet, but leaves manually-added customers alone.
+    "prune_empty_customers.py",
     "transform_bank.py",
 ]
 

migration/transform_bank.py

@@ -134,7 +134,7 @@ def main():
     print(f"  skipped (unparseable date): {skip_date}")
     print(f"  -> bank_transactions       : {count('bank_transactions')}")
     for src, n, tot in by_src:
-        print(f"       {src:10} {n:6}  sum {tot}")
+        print(f"       {(src or '(manual)'):10} {n:6}  sum {tot}")
     print(f"     net balance movement    : {net}")
     print(f"  -> business_line_categories: {count('business_line_categories')}")
     print("  validation: OK")

migration/transform_customers.py

@@ -293,18 +293,33 @@ def main() -> None:
         refs.append((str(uuid.uuid4()), cust_id, "insurance", "DATGRAL", ins_id))
 
     placeholders = ",".join(["%s"] * len(_CUST_COLS))
+    remap: dict[str, str] = {}  # in-memory customer id -> stable (DB) id
     if sync_mode:
-        existing = {}
+        # Resolve each in-memory customer to a stable id: if ANY of its legacy
-        cur.execute("SELECT id,sourceSystem,sourceTable,legacyId,customerId FROM customer_legacy_refs")
+        # refs already exists in the DB, reuse that customer's id (keeps PKs
-        for rid, system, table, legacy, customer_id in cur.fetchall():
+        # stable and preserves manual edits). `customers` and `refs` are
-            existing[(system, table, legacy)] = (rid, customer_id)
+        # different-length, differently-ordered lists — merged identities add a
-        for rec, ref in zip(customers, refs):
+        # ref without a customer — so refs are grouped by their owning customer,
-            key = (ref[2], ref[3], ref[4])
+        # never positionally zipped (the old zip mispaired almost every row).
-            customer_id = existing.get(key, (None, rec["id"]))[1]
+        cur.execute("SELECT sourceSystem,sourceTable,legacyId,customerId FROM customer_legacy_refs")
-            rec["id"] = customer_id
+        ref_existing = {(sy, tb, lg): cid for sy, tb, lg, cid in cur.fetchall()}
+        refs_by_cust: dict[str, list] = {}
+        for ref in refs:  # ref = (refId, custInMemId, system, table, legacyId)
+            refs_by_cust.setdefault(ref[1], []).append(ref)
+        for rec in customers:
+            stable = None
+            for ref in refs_by_cust.get(rec["id"], []):
+                cid = ref_existing.get((ref[2], ref[3], ref[4]))
+                if cid:
+                    stable = cid
+                    break
+            remap[rec["id"]] = stable or rec["id"]
+        for rec in customers:
+            rec["id"] = remap[rec["id"]]
             cur.execute(f"INSERT INTO customers ({','.join(f'`{c}`' for c in _CUST_COLS)}) VALUES ({placeholders}) ON DUPLICATE KEY UPDATE name=VALUES(name),nameSource=VALUES(nameSource),nameMissing=VALUES(nameMissing),addressLine1=VALUES(addressLine1),addressLine2=VALUES(addressLine2),city=VALUES(city),state=VALUES(state),zipCode=VALUES(zipCode),country=VALUES(country),phone=VALUES(phone),mobile=VALUES(mobile),fax=VALUES(fax),email=VALUES(email),notes=VALUES(notes),identificationType=VALUES(identificationType),identificationNumber=VALUES(identificationNumber),identificationExpiration=VALUES(identificationExpiration),customerSince=VALUES(customerSince),status=VALUES(status),feeAmount=VALUES(feeAmount),updatedAt=VALUES(updatedAt)", tuple(rec[c] for c in _CUST_COLS))
-            ref = (existing.get(key, (ref[0], customer_id))[0], customer_id, *ref[2:])
+        for ref in refs:
-            cur.execute("INSERT INTO customer_legacy_refs (id,customerId,sourceSystem,sourceTable,legacyId) VALUES (%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE customerId=VALUES(customerId)", ref)
+            cur.execute("INSERT INTO customer_legacy_refs (id,customerId,sourceSystem,sourceTable,legacyId) VALUES (%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE customerId=VALUES(customerId)",
+                        (ref[0], remap[ref[1]], ref[2], ref[3], ref[4]))
     else:
         cur.executemany(
             f"INSERT INTO customers ({','.join(f'`{c}`' for c in _CUST_COLS)}) VALUES ({placeholders})",
@@ -317,7 +332,10 @@ def main() -> None:
 
     # Enrich linked customers with insurance-only ID-doc fields, and fill any
     # contact fields the utilities master left empty (COALESCE keeps master's).
+    # In sync mode the enrich targets carry in-memory ids, so map them to the
+    # stable DB ids resolved above (identity map in full mode).
     for cust_id, ir in enrich:
+        cust_id = remap.get(cust_id, cust_id)
         cur.execute(
             "UPDATE customers SET "
             "identificationType   = COALESCE(identificationType, %s), "
@@ -366,6 +384,9 @@ def main() -> None:
         print(f"     from {src:16} : {n}")
     print(f"     of which via the linked insurance record : {from_ins_side}")
     print(f"     still {NO_NAME} : {still_unnamed}")
+    if not sync_mode:
+        # Full-load invariants only: sync upserts into an already-loaded (and
+        # pruned) table, so these exact counts don't hold.
         assert n_cust == len(util) + new_ins, "customer count mismatch"
         assert n_refs == len(util) + len(ins), "legacy ref count mismatch"
     print("  validation: OK")

migration/transform_policies.py

@@ -37,7 +37,7 @@ from pathlib import Path
 import pandas as pd
 
 from dbenv import connect, env_arg
-from sync import parse_mode
+from sync import parse_mode, existing_ids, delete_missing
 
 STG = Path(__file__).parent / "output" / "stg_seguros"
 LEGACY_DB = "SEGUROS 16_be"
@@ -180,6 +180,14 @@ def main():
     c.execute("SELECT legacyId, customerId FROM customer_legacy_refs WHERE sourceSystem='insurance'")
     cust = {r[0]: r[1] for r in c.fetchall()}
 
+    # Sync mode reuses each legacy policy's existing id (keyed by provenance) so
+    # its PK is stable and every child row built below points at the right
+    # parent. New legacy policies fall through to a fresh uuid.
+    existing_pol = existing_ids(
+        c, "policies", ("legacySourceDb", "legacySourceTable", "legacyId"),
+        "WHERE legacyId IS NOT NULL") if sync_mode else {}
+    pol_keys: set = set()
+
     policies, insts, vehicles, drivers = [], [], [], []
     polno_to_id = {}          # policy number -> a policyId (for BENEF/DATOS linking)
     providers, ptypes = set(), set()
@@ -198,7 +206,10 @@ def main():
             if not cid:
                 skipped += 1
                 continue
-            pid = str(uuid.uuid4())
+            legacy_pid = str(int(row["_row_num"]))
+            pkey = (LEGACY_DB, table, legacy_pid)
+            pol_keys.add(pkey)
+            pid = existing_pol.get(pkey) or str(uuid.uuid4())
             comp = s(row.get(F.get("comp", ""), None)) if F.get("comp") else None
             if comp:
                 providers.add(comp)
@@ -316,42 +327,74 @@ def main():
                        dt(r["fecha_cheque"]), s(r["num_cheque"]),
                        1 if truthy(r["concluido"]) else 0, s(r["resolucion"])))
 
+    pol_cols = ("id,policyNumber,customerId,policyTypeId,insuranceProviderId,agentName,policyDate,"
+                "policyFrom,policyTo,netPremium,policyFee,commission,total,currency,observations,"
+                "coveragesJson,liquidated,liquidationNumber,liquidationDate,legacySourceDb,"
+                "legacySourceTable,legacyId,updatedAt")
+    ph = ",".join(["%s"] * 23)
+    pol_upsert = (
+        f"INSERT INTO policies ({pol_cols}) VALUES ({ph}) ON DUPLICATE KEY UPDATE "
+        "customerId=VALUES(customerId),policyNumber=VALUES(policyNumber),policyTypeId=VALUES(policyTypeId),"
+        "insuranceProviderId=VALUES(insuranceProviderId),agentName=VALUES(agentName),policyDate=VALUES(policyDate),"
+        "policyFrom=VALUES(policyFrom),policyTo=VALUES(policyTo),netPremium=VALUES(netPremium),policyFee=VALUES(policyFee),"
+        "commission=VALUES(commission),total=VALUES(total),currency=VALUES(currency),observations=VALUES(observations),"
+        "coveragesJson=VALUES(coveragesJson),liquidated=VALUES(liquidated),liquidationNumber=VALUES(liquidationNumber),"
+        "liquidationDate=VALUES(liquidationDate),updatedAt=VALUES(updatedAt),archivedAt=NULL")
+
     if sync_mode:
-        c.execute("SELECT id,name FROM policy_types")
+        # policy_types / providers: upsert by their name unique, keep ids stable.
+        c.execute("SELECT name,id FROM policy_types")
         ptype_ids = dict(c.fetchall())
         for n in ptypes:
             ptype_ids.setdefault(n, str(uuid.uuid4()))
         c.executemany("INSERT INTO policy_types (id,name) VALUES (%s,%s) ON DUPLICATE KEY UPDATE name=VALUES(name)", [(i, n) for n, i in ptype_ids.items()])
-        c.execute("SELECT id,name FROM insurance_providers")
+        c.execute("SELECT name,id FROM insurance_providers")
         prov_ids = dict(c.fetchall())
         for n in providers:
             prov_ids.setdefault(n, str(uuid.uuid4()))
         c.executemany("INSERT INTO insurance_providers (id,name) VALUES (%s,%s) ON DUPLICATE KEY UPDATE name=VALUES(name)", [(i, n) for n, i in prov_ids.items()])
-        for p in policies:
+
-            p = list(p); p[3] = ptype_ids[p[3]]; p[4] = prov_ids.get(p[4])
+        # Adjusters carry no provenance key, so they can't be upserted by one.
-            c.execute(f"INSERT INTO policies ({pol_cols}) VALUES ({','.join(['%s'] * 23)}) ON DUPLICATE KEY UPDATE customerId=VALUES(customerId),policyNumber=VALUES(policyNumber),policyTypeId=VALUES(policyTypeId),insuranceProviderId=VALUES(insuranceProviderId),agentName=VALUES(agentName),policyDate=VALUES(policyDate),policyFrom=VALUES(policyFrom),policyTo=VALUES(policyTo),netPremium=VALUES(netPremium),policyFee=VALUES(policyFee),commission=VALUES(commission),total=VALUES(total),currency=VALUES(currency),observations=VALUES(observations),coveragesJson=VALUES(coveragesJson),liquidated=VALUES(liquidated),liquidationNumber=VALUES(liquidationNumber),liquidationDate=VALUES(liquidationDate),updatedAt=VALUES(updatedAt),archivedAt=NULL", tuple(p))
+        # Resolve claims against the adjusters already in the DB (manual + prior
+        # loads), inserting only names not present yet — keeps manual adjusters
+        # and every claim's adjusterId FK valid.
+        c.execute("SELECT id,name FROM adjusters")
+        db_adj = {(nm or "").upper(): i for i, nm in c.fetchall()}
+        adj_id_name = {aid: (nm or "").upper() for aid, comp, city, nm, tel, bp in adj_rows}
+        new_adj = []
+        for aid, comp, city, nm, tel, bp in adj_rows:
+            if nm and nm.upper() not in db_adj:
+                db_adj[nm.upper()] = aid
+                new_adj.append((aid, comp, city, nm, tel, bp))
+        if new_adj:
+            c.executemany("INSERT INTO adjusters (id,company,city,name,phone,beeper) VALUES (%s,%s,%s,%s,%s,%s)", new_adj)
+        claims = [(cl[0], cl[1], *cl[2:6], db_adj.get(adj_id_name.get(cl[6])) if cl[6] else None, *cl[7:]) for cl in claims]
+
+        # Rebuild every child of a legacy-owned policy before re-inserting the
+        # children below (manual rows survive: vehicles by their own null
+        # provenance, the rest by their parent policy's null provenance).
+        c.execute("DELETE FROM vehicles WHERE legacyId IS NOT NULL")
+        for tbl in ("policy_payment_installments", "insured_drivers", "policy_beneficiaries", "claims"):
+            c.execute(f"DELETE ch FROM {tbl} ch JOIN policies p ON p.id=ch.policyId WHERE p.legacyId IS NOT NULL")
+
+        pol_rows = [tuple([p[0], p[1], p[2], ptype_ids.get(p[3]), prov_ids.get(p[4]), *p[5:]]) for p in policies]
+        c.executemany(pol_upsert, pol_rows)
+        # Drop legacy policies that vanished from source (children already gone).
+        delete_missing(c, "policies", ("legacySourceDb", "legacySourceTable", "legacyId"), pol_keys, "WHERE legacyId IS NOT NULL")
     else:
+        c.execute("SET FOREIGN_KEY_CHECKS=0")
+        for t in ("policy_payment_installments", "vehicles", "insured_drivers",
+                  "policy_beneficiaries", "claims", "adjusters",
+                  "policies", "policy_types", "insurance_providers"):
+            c.execute(f"TRUNCATE TABLE {t}")
+        c.execute("SET FOREIGN_KEY_CHECKS=1")
         ptype_ids = {n: str(uuid.uuid4()) for n in ptypes}
         c.executemany("INSERT INTO policy_types (id,name) VALUES (%s,%s)", [(i, n) for n, i in ptype_ids.items()])
         prov_ids = {n: str(uuid.uuid4()) for n in providers}
         c.executemany("INSERT INTO insurance_providers (id,name) VALUES (%s,%s)", [(i, n) for n, i in prov_ids.items()])
         c.executemany("INSERT INTO adjusters (id,company,city,name,phone,beeper) VALUES (%s,%s,%s,%s,%s,%s)", adj_rows)
-
+        pol_rows = [tuple([p[0], p[1], p[2], ptype_ids.get(p[3]), prov_ids.get(p[4]), *p[5:]]) for p in policies]
-    pol_cols = ("id,policyNumber,customerId,policyTypeId,insuranceProviderId,agentName,policyDate,"
+        c.executemany(f"INSERT INTO policies ({pol_cols}) VALUES ({ph})", pol_rows)
-                "policyFrom,policyTo,netPremium,policyFee,commission,total,currency,observations,"
-                "coveragesJson,liquidated,liquidationNumber,liquidationDate,legacySourceDb,"
-                "legacySourceTable,legacyId,updatedAt")
-    if not sync_mode:
-        fixed = []
-        for p in policies:
-            p = list(p)
-            p[3] = ptype_ids.get(p[3])
-            p[4] = prov_ids.get(p[4])
-            fixed.append(tuple(p))
-        ph = ",".join(["%s"] * 23)
-        c.executemany(f"INSERT INTO policies ({pol_cols}) VALUES ({ph})", fixed)
-    else:
-        c.executemany("INSERT INTO policies (id,policyNumber,customerId,policyTypeId,insuranceProviderId,agentName,policyDate,policyFrom,policyTo,netPremium,policyFee,commission,total,currency,observations,coveragesJson,liquidated,liquidationNumber,liquidationDate,legacySourceDb,legacySourceTable,legacyId,updatedAt) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE customerId=VALUES(customerId),policyNumber=VALUES(policyNumber),policyTypeId=VALUES(policyTypeId),insuranceProviderId=VALUES(insuranceProviderId),agentName=VALUES(agentName),policyDate=VALUES(policyDate),policyFrom=VALUES(policyFrom),policyTo=VALUES(policyTo),netPremium=VALUES(netPremium),policyFee=VALUES(policyFee),commission=VALUES(commission),total=VALUES(total),currency=VALUES(currency),observations=VALUES(observations),coveragesJson=VALUES(coveragesJson),liquidated=VALUES(liquidated),liquidationNumber=VALUES(liquidationNumber),liquidationDate=VALUES(liquidationDate),updatedAt=VALUES(updatedAt),archivedAt=NULL", [tuple([p[0],p[1],p[2],ptype_ids.get(p[3]),prov_ids.get(p[4]),*p[5:]]) for p in policies])
 
 
 

migration/transform_properties.py

@@ -112,6 +112,12 @@ def main():
                 "WHERE sourceSystem='utilities' AND sourceTable='DATGRAL'")
     cust_map = {r[0]: r[1] for r in cur.fetchall()}
 
+    # Sync reuses each legacy property's existing id (keyed by provenance) so its
+    # PK is stable AND the services/trust rows built below point at the right
+    # parent. New legacy rows fall through to a fresh uuid.
+    existing_prop = existing_ids(cur, "properties", ("legacySourceTable", "legacyId"),
+                                 "WHERE legacyId IS NOT NULL") if sync_mode else {}
+
     dm = load("datmex")
     pf = load("profile")
     # PROFILE flags by join key (best-effort; key nearly unique in PROFILE)
@@ -133,7 +139,7 @@ def main():
 
         legacy_id = str(int(row["_row_num"]))
         prop_keys.add(("DATMEX", legacy_id))
-        pid = str(uuid.uuid4())
+        pid = existing_prop.get(("DATMEX", legacy_id)) or str(uuid.uuid4())
         addr2_parts = []
         for lbl, col in (("CASA", "casa"), ("MZ", "manzana"), ("LOTE", "lote")):
             if s_keep0(row[col]):
@@ -206,16 +212,14 @@ def main():
 
     # Fresh rebuild (children first), or additive upsert for legacy-owned rows.
     if sync_mode:
-        existing = existing_ids(cur, "properties", ("legacySourceTable", "legacyId"), "WHERE legacyId IS NOT NULL")
+        # Children first (scoped to legacy-owned rows so manual rows survive),
-        for row in props:
+        # then upsert properties (ids already stable), then drop legacy rows
-            key = (row[8], row[9])
+        # gone from source, then re-insert the rebuilt children.
-            if key in existing:
-                row = list(row); row[0] = existing[key]
-            cur.execute(
-                "INSERT INTO properties (id,customerId,addressLine1,addressLine2,phone1,phone2,phone3,zone,legacySourceTable,legacyId) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE customerId=VALUES(customerId),addressLine1=VALUES(addressLine1),addressLine2=VALUES(addressLine2),phone1=VALUES(phone1),phone2=VALUES(phone2),phone3=VALUES(phone3),zone=VALUES(zone),archivedAt=NULL", tuple(row))
-        delete_missing(cur, "properties", ("legacySourceTable", "legacyId"), prop_keys, "WHERE legacyId IS NOT NULL")
         cur.execute("DELETE ps FROM property_services ps JOIN properties p ON p.id=ps.propertyId WHERE p.legacyId IS NOT NULL")
         cur.execute("DELETE ta FROM trust_accounts ta JOIN properties p ON p.id=ta.propertyId WHERE p.legacyId IS NOT NULL")
+        cur.executemany(
+            "INSERT INTO properties (id,customerId,addressLine1,addressLine2,phone1,phone2,phone3,zone,legacySourceTable,legacyId) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE customerId=VALUES(customerId),addressLine1=VALUES(addressLine1),addressLine2=VALUES(addressLine2),phone1=VALUES(phone1),phone2=VALUES(phone2),phone3=VALUES(phone3),zone=VALUES(zone),archivedAt=NULL", props)
+        delete_missing(cur, "properties", ("legacySourceTable", "legacyId"), prop_keys, "WHERE legacyId IS NOT NULL")
         cur.executemany("INSERT INTO property_services (id,propertyId,kind,accountNumber,meterNumber,route,dueDay,active,notes) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)", services)
         cur.executemany("INSERT INTO trust_accounts (id,propertyId,bankName,trustNumber,bankFee,dueDate1,dueDate2) VALUES (%s,%s,%s,%s,%s,%s,%s)", trusts)
     else:
@@ -246,7 +250,8 @@ def main():
         print(f"       {k:14} {n}")
     print(f"  -> trust_accounts      : {n_t}")
     print(f"  orphan properties (bad customer FK): {orphans}")
-    assert n_p == len(props) and orphans == 0, "property load invariant failed"
+    # n_p == len(props) is a full-load invariant; sync keeps manual rows too.
+    assert (sync_mode or n_p == len(props)) and orphans == 0, "property load invariant failed"
     print("  validation: OK")
     conn.close()
 

migration/transform_transactions.py

@@ -227,7 +227,23 @@ def main():
     efectivo_like("stg_seguros", "efectivo", "INSURANCE", ins_cust, "SEGUROS 16_be", "EFECTIVO")
 
     if sync_mode:
-        c.executemany("INSERT INTO transactions (id,customerId,domain,typeId,transactionDate,period,reference,amount,currency,exchangeRate,checkNumber,message,outstanding,legacySourceDb,legacySourceTable,legacyId) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE customerId=VALUES(customerId),domain=VALUES(domain),typeId=VALUES(typeId),transactionDate=VALUES(transactionDate),period=VALUES(period),reference=VALUES(reference),amount=VALUES(amount),currency=VALUES(currency),checkNumber=VALUES(checkNumber),message=VALUES(message),updatedAt=NOW(),voidedAt=NULL", tx)
+        # Transaction types are rebuilt with fresh uuids each run; resolve them
+        # against the rows already in the DB by English name (inserting any that
+        # are new) and remap each tx's typeId onto the persisted id so the FK to
+        # type_transactions holds. exchange_rates isn't referenced by tx, so it
+        # is left untouched in sync.
+        c.execute("SELECT id,nameEn FROM type_transactions")
+        db_types = {(nm or "").upper(): i for i, nm in c.fetchall()}
+        fresh_name = {tid: (en or "").upper() for tid, en, es, active in type_rows}
+        new_types = []
+        for tid, en, es, active in type_rows:
+            if (en or "").upper() not in db_types:
+                db_types[(en or "").upper()] = tid
+                new_types.append((tid, en, es, active))
+        if new_types:
+            c.executemany("INSERT INTO type_transactions (id,nameEn,nameEs,isService) VALUES (%s,%s,%s,%s)", new_types)
+        tx = [(t[0], t[1], t[2], (db_types.get(fresh_name.get(t[3])) if t[3] else None), *t[4:]) for t in tx]
+        c.executemany("INSERT INTO transactions (id,customerId,domain,typeId,transactionDate,period,reference,amount,currency,exchangeRate,checkNumber,message,outstanding,legacySourceDb,legacySourceTable,legacyId) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE customerId=VALUES(customerId),domain=VALUES(domain),typeId=VALUES(typeId),transactionDate=VALUES(transactionDate),period=VALUES(period),reference=VALUES(reference),amount=VALUES(amount),currency=VALUES(currency),checkNumber=VALUES(checkNumber),message=VALUES(message),voidedAt=NULL", tx)
     else:
         c.execute("SET FOREIGN_KEY_CHECKS=0")
         for t in ("transactions", "type_transactions", "exchange_rates"):
@@ -255,7 +271,7 @@ def main():
     print(f"  -> transactions        : {count('transactions')}")
     print(f"     by domain           : {dict(by_dom)}")
     for src, n in by_src:
-        print(f"       {src:16} {n}")
+        print(f"       {(src or '(manual)'):16} {n}")
     print(f"  -> type_transactions   : {count('type_transactions')}")
     print(f"  -> exchange_rates      : {count('exchange_rates')}")
     print(f"  orphan transactions (bad customer FK): {orphans}")

packages/database/prisma/schema.prisma

@@ -181,12 +181,42 @@ model Policy {
   claims         Claim[]
   documents      PolicyDocument[]
   properties     Property[]
+  renewalNotices RenewalNotice[]
 
   @@unique([legacySourceDb, legacySourceTable, legacyId])
   @@index([policyNumber])
   @@map("policies")
 }
 
+enum RenewalNoticeChannel {
+  MAIL
+  EMAIL
+}
+
+/// Replaces the legacy `CONTROL <ramo> RENEW[2/3] X MES` reports — a
+/// per-batch printed checklist of who'd been sent which reminder. One row
+/// per notice generation actually sent for a policy, so "who got a 1st/2nd/
+/// 3rd notice and when" is a query instead of a paper trail. See
+/// docs/RENEWAL_NOTICES.md for the legacy report chain this replaces.
+model RenewalNotice {
+  id         String               @id @default(uuid())
+  policyId   String
+  policy     Policy               @relation(fields: [policyId], references: [id])
+  // 1 = first notice (bare RENEW), 2 = RENEW2, 3 = RENEW3 in the legacy naming.
+  generation Int
+  channel    RenewalNoticeChannel @default(MAIL)
+  sentAt     DateTime?
+  sentById   String?
+  notes      String?              @db.Text
+  createdAt  DateTime             @default(now())
+
+  // One row per generation per policy — matches the legacy's 1st/2nd/3rd
+  // notice cadence; re-running the same generation for a policy updates it
+  // rather than duplicating a log entry.
+  @@unique([policyId, generation])
+  @@map("renewal_notices")
+}
+
 /// Unpivots the 4 hardcoded payment-installment columns found on every
 /// legacy policy table (1ER PAGO/FECHA PAGO/NO CHEQUE, ...2, ...3, ...4).
 model PolicyPaymentInstallment {
@@ -221,10 +251,12 @@ model Vehicle {
   vinNumber         String?
   stateCode         String?
   notes             String? @db.Text
+  // One legacy policy row can carry up to 3 vehicles, so they share a
+  // legacyId (the source row number) — provenance is NOT unique per vehicle.
+  // Sync rebuilds legacy vehicles by scoped delete + reinsert instead of upsert.
   legacySourceTable String?
   legacyId          String?
 
-  @@unique([legacySourceTable, legacyId])
   @@map("vehicles")
 }