docs(plan): add step 11 for receipt-capture + net-new ops features

Points PLAN.md at docs/RECEIPT_CAPTURE_SPEC.md and surfaces its open design questions (OCR provider, Seguros bank details, clave catastral vs. predial, recycling triggers) separately from the existing ops-only open items list. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
docs: add receipt capture, OCR, multi-bank & customer-recycling spec
2026-07-26 23:28:13 -07:00 · 2026-07-26 23:26:23 -07:00 · 2026-07-26 12:07:32 -07:00 · 2026-07-25 23:05:21 -07:00 · 2026-07-24 13:37:22 -07:00 · 2026-07-23 23:20:41 -07:00
70 changed files with 15260 additions and 215 deletions
@@ -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=
@@ -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 }}"
+            }
@@ -130,6 +130,13 @@ Given the amount of near-duplicate/overlapping data across snapshot tables (mult
 8. VPS provisioning + Tailscale + MySQL replication setup. `utility_dbo`'s schema is now available (full dump on disk — 55 tables; see Status), so the exact replicated table/column set and inbox-table shape can be finalized against the real portal DB and the portal PHP code (`my-jorgecuadros-web`) that reads/writes it.
 9. Sync worker (push replicated tables' relevant subset, poll inbox tables for payment/propane submissions) — depends on step 8. **The separate Phase B Access additive sync is implemented:** `migration/run_all.py --sync` and the admin `SYNC` job upsert legacy-owned rows without truncating the database or touching manual rows. Portal write points confirmed present in `utility_dbo`: `peticion_gas` (propane requests), PayPal payment writes, `notifications_settings`, `verification_codes` — these define the VPS→internal inbox set.
 10. Reports/email campaigns/admin — parity with old app's `reports.php`/`emailCampaigns.php` intent, rebuilt properly.
+11. **Receipt capture ("Editor") completion + three net-new ops features — NOT STARTED, spec written.** Full design in [`docs/RECEIPT_CAPTURE_SPEC.md`](docs/RECEIPT_CAPTURE_SPEC.md), from the 2026-07-25/26 meeting with Jorge:
+    - **Receipt capture module** — the legacy "Editor" replacement: wires up the already-existing but unused `Transaction.outstanding` field (NOPAGO workflow), adds batch-by-check capture, and a check-reconciliation view replacing `REPORTE CHEQUE COUNT`. Builds directly on the single-movement capture already shipped in `billing/` (step 6) — smallest piece, do first.
+    - **PDF/OCR auto-capture** — ingest→split→OCR→match→review pipeline for the 300+/month/service-provider statements staff currently key in by hand. Posts through the capture module above. Matching logic was checked field-by-field against `migration/transform_properties.py`'s actual output and found three real gaps to close first: no `TELEPHONE` service kind exists yet, `PROPERTY_TAX.accountNumber` was migrated from `PREDIAL` not `CLAVE` (needs verification against a real predial statement), and `GAS.meterNumber` was never populated by the migration at all.
+    - **Multi-bank chequera** — `Bank`/`BankAccount` models so Seguros (US bank) and Utilities (Mexican bank, currently SCOTHIA) can each have their own register; today's `bank_transactions` is hardcoded single-account/MXN-only by design (see step 7 above) and needs a required `bankAccountId` plus scoping added to every read path in `bank.service.ts`, including two raw-SQL queries in `summary()`.
+    - **Customer-number recycling** — promotes the legacy `NUM id` (currently only inside `customer_legacy_refs`) into a first-class, reusable `Customer.customerNumber`, automates *finding* candidates for reuse (cancelled / 1-year-inactive), and auto-assigns the lowest free number at creation — the search is automated, the release/reuse decision stays a human action. Backfill needs care: ~140 utilities rows and all insurance-only customers have no real legacy number (synthetic `rownum_N`/`insrow_N` placeholders in `transform_customers.py`, not real `NUM id`s).
+    
+    Several open questions block parts of this (OCR provider/budget, the Seguros bank's identity, the clave-catastral-vs-predial mismatch, exact recycling triggers, and whether "recycling" should ever mean true data purge vs. archive-and-reuse-the-number) — see the spec's collected open-questions section.

 ## Status

@@ -139,6 +146,8 @@ Repo scaffolded at `jorgecuadros-platform/`: npm workspaces, NestJS API with a r

 **Portal live DB now in hand.** `utility_dbo.sql` (1.3 GB, 55 tables) and the portal codebase `my-jorgecuadros-web` (PHP/`mysqli`, Gitea repo, themed classic/modern, ~397 PHP files, core in `scripts/functions.php`) are both on disk — resolving the long-standing "`utility_dbo` schema unknown" blocker. Sync-relevant tables identified: statements/money (`utility_bills`, `accounting`, `email_alert_log`), customer/property (`home_owners`, `home_index`, `condominium`, `management`, `hoa_management`, `trust_assist`), portal-facing policy views (`fm2`/`fm3`/`fmt`, `full_coverage`, `mx_liability`, `usa_liability`), and portal write points (`peticion_gas`, PayPal payments, `notifications_settings`, `verification_codes`). A second dump, `jorgecuadros.sql` (38 MB, 11 tables — `pagos`/`pagosemail`/`PROPANO`/`TRUSTVENCE`/etc.), appears to be an older/partial export, not the portal live DB.

+**Step 11 spec written, not built.** `docs/RECEIPT_CAPTURE_SPEC.md` covers the receipt-capture ("Editor") completion plus the three net-new ops features (OCR auto-capture, multi-bank chequera, customer-number recycling) — see Build sequencing step 11 above for the summary. Written from the 2026-07-25/26 meeting notes and verified against the real migration scripts and current API code, not just designed from the meeting notes alone.
+
 ## Decisions (locked)

 - **Stack:** Next.js + NestJS + Prisma + **MySQL** (locked earlier — see engine rationale above).
@@ -155,6 +164,16 @@ Repo scaffolded at `jorgecuadros-platform/`: npm workspaces, NestJS API with a r
 - **VPS provisioning:** provider (Hetzner vs DigitalOcean), size, and Tailscale + MySQL replica setup on it — an ops task, still pending. Design is settled; only the box is missing.
 - **Old external-DB credential** (hardcoded plaintext MySQL password in the old repo's `dbConnection.php`, in git history) — rotate it regardless, since it's already exposed.

+## Open design questions (step 11 — need Jorge before/while building)
+
+Unlike the ops items above, these block design decisions, not just infrastructure. Full detail in each section of `docs/RECEIPT_CAPTURE_SPEC.md`:
+
+- OCR provider/budget for the statement auto-capture pipeline (self-hosted vs. a paid per-page API, given 300+ statements/month/service provider).
+- Whether `PROPERTY_TAX.accountNumber` (migrated from `DATMEX.PREDIAL`) is actually the same number as "Clave Catastral" (`DATMEX.CLAVE`) — blocks OCR matching for predial statements until confirmed against a real bill.
+- The actual bank name/currency/details for the Seguros USD account, and whether any historical Seguros bank register exists to migrate.
+- The exact "1 year inactivity" / "cancelled" triggers for customer-number recycling eligibility.
+- Whether customer-number recycling should ever include true PII purge (matching the office's paper-world habit) or archive-and-reuse-the-number is sufficient — recommended default is archive-only, consistent with this project's existing never-hard-delete convention.
+
 ## Verification

 - Migration: automated row-count/sum reconciliation between `staging` and final schema per table group (see step 5 above), run as part of the migration script, not a manual spot-check.
@@ -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
-   the admin-only `/operaciones` page + `ops` API module (ability `db:manage`, ADMIN), ingest
-   folder, backup, restore, and destructive re-import. Phase B now enables `SYNC`: `OpsService`
-   creates a safety backup and runs `run_all.py --sync`; transforms upsert legacy-owned rows by
-   provenance keys while preserving existing PKs and rows whose `legacyId IS NULL` (manual).
-   Prisma now enforces provenance uniqueness for properties, policies, transactions, vehicles,
-   and bank transactions. Sync intentionally skips prune/blob steps so manual customers and
-   document pointers are not removed. Python compilation plus API/web production builds pass;
-   still required before production use: push updated Prisma schema and run an end-to-end sync
-   against a disposable/dev DB proving stable PKs, manual-row preservation, changed-row updates,
-   and legacy-delete handling.
+6. **DB Operations — Phase B (additive sync) — VERIFIED END-TO-END against dev DB 2026-07-24.**
+   Phase A provides the admin-only `/operaciones` page + `ops` API module (ability `db:manage`,
+   ADMIN), ingest folder, backup, restore, and destructive re-import. Phase B enables `SYNC`:
+   `OpsService` creates a safety backup and runs `run_all.py --sync`; transforms upsert
+   legacy-owned rows by provenance keys while preserving existing PKs and rows whose
+   `legacyId IS NULL` (manual). Prisma enforces provenance uniqueness for properties, policies,
+   transactions, and bank transactions (the vehicle unique was **removed** — one legacy policy
+   row carries up to 3 vehicles that share a `legacyId`, so provenance is not unique per
+   vehicle; vehicles are rebuilt by scoped delete + reinsert). Sync skips blob extraction, and
+   runs a **manual-safe prune** (`prune_empty_customers.py --sync` — only prunes empties that
+   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
-  non-destructive legacy upsert path for customers, properties, policies, transactions, and
-  bank rows. It preserves manual rows and stable legacy-owned primary keys; the admin SYNC job
-  automatically creates a pre-sync backup. Next validation: apply schema changes, then exercise
-  sync against a disposable DB with added, changed, removed, and manually-created rows.
+- **Sync implementation — DONE + VALIDATED end-to-end against dev 2026-07-24.** `run_all.py
+  --sync` performs the non-destructive legacy upsert path for customers, properties, policies,
+  transactions, and bank rows, plus a manual-safe empty-customer prune. It preserves manual rows
+  and stable legacy-owned primary keys; the admin SYNC job auto-creates a pre-sync backup. The
+  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
-  in-browser this session; `/estado-cuenta` still worth a look.
+  exposed MySQL password. (c) ~~The `/estado-cuenta` browser visual pass.~~ **DONE 2026-07-24** —
+  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.
@@ -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",
@@ -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],
 })
@@ -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,
@@ -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,
@@ -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 =
@@ -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);
+  }
 }
@@ -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() {
@@ -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")
@@ -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
-  // client wired into the API (today only the migration writes to MinIO).
+  // The blob lives in object storage (MinIO); the row is just the pointer. Keys
+  // 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;
  }
 }
@@ -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;
+}
@@ -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;");
+}
@@ -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);
+  }
+}
@@ -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 {}
@@ -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);
+}
@@ -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);
+  }
+}
@@ -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,
+};
@@ -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 {}
@@ -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}`);
+    }
+  }
+}
@@ -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}`;
+}
@@ -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 });
+  }
 }
@@ -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`);
@@ -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"
@@ -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">
@@ -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>
@@ -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
@@ -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>
@@ -200,19 +200,20 @@ button {
  text-decoration: none;
 }
 .brand-mark {
-  width: 34px;
-  height: 34px;
+  width: 38px;
+  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;
-  place-items: center;
-  font-family: var(--font-display);
-  font-weight: 600;
-  font-size: 16px;
-  color: #f5efe0;
+  object-fit: contain;
+  padding: 3px;
  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);
+}
@@ -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>
+  );
+}
@@ -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" />
@@ -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>
-              JC
-            </span>
+            <img
+              src="/images/company_logo.png"
+              alt=""
+              className="brand-mark"
+            />
            <div className="brand-text">
              <span className="brand-name" style={{ color: "#f6f3ec" }}>
                Jorge Cuadros
@@ -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."
-            action="Próximamente"
-            tone="muted"
-            disabled
-            onClick={() => {}}
+            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="Sincronizar"
+            tone="primary"
+            disabled={jobRunning || starting || !ingestReady}
+            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
@@ -1,5 +1,5 @@
 import { redirect } from "next/navigation";

 export default function HomePage() {
-  redirect("/clientes");
+  redirect("/inicio");
 }
@@ -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
-              descargan desde esta vista.
+        )}
+        {canEdit && (
+          <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>
@@ -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>
          )}
@@ -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>
+  );
+}
@@ -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>
+    </>
+  );
+}
@@ -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>
@@ -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>
          )}
@@ -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>
@@ -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">
-            <span className="brand-mark" aria-hidden>
-              JC
-            </span>
+          <Link href="/inicio" className="brand">
+            <img
+              src="/images/company_logo.png"
+              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}
@@ -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>
+  );
+}
@@ -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;
@@ -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 =
-  process.env.NEXT_PUBLIC_API_ORIGIN ?? "http://localhost:3001";
+// Resolve the API origin at runtime, not build time. In the browser it comes
+// 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);
-  const res = await fetch(`${API_ORIGIN}/ops/ingest/${encodeURIComponent(name)}`, {
+  body.append("file", file, filename ?? file.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}` : ""}`;
+}
@@ -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[];
+}
@@ -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
@@ -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:
@@ -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
@@ -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:
@@ -0,0 +1,795 @@
+# Receipt Capture ("Editor") & Related Net-New Features — Implementation Spec
+
+Source: Jorge Cuadros meeting notes, 2026-07-25/26 (`Utility Management` section)
+plus a business-logic read-through of `UTILITIES.accdb`'s legacy "Editor"
+workflow (`docs/LEGACY_DATABASES_OBJECTS.md`, `docs/LEGACY_DATABASES.md`).
+This is a forward spec for work **not yet built**, not a record of what
+exists — contrast with `RENEWAL_NOTICES.md`, which documents a legacy
+workflow already migrated.
+
+## Why these four features are one spec
+
+The meeting covered one legacy workflow (receipt capture, the "Editor") plus
+three requests that have no legacy precedent at all. They're specified
+together because they compose:
+
+1. **Receipt capture module** — the direct replacement for the legacy
+   "Editor" screens, extending what's already built in `billing/`.
+2. **PDF/OCR auto-capture** — a new intake path that feeds *into* module 1
+   (an OCR-confirmed statement becomes a captured receipt, not a separate
+   ledger).
+3. **Multi-bank chequera** — changes what a captured receipt's check number
+   reconciles against (module 1's check-reconciliation view needs to know
+   *which bank account* a check was drawn on).
+4. **Customer-number recycling** — changes how a customer is created, which
+   is the first step of capturing a receipt for them (module 1's customer
+   picker needs to respect whatever number a recycled customer was assigned).
+
+Each section below is independently buildable and independently useful, but
+1 should land before 2 (2 posts through 1's API), and 4 is fully
+independent of the other three.
+
+---
+
+## 1. Receipt capture module (the "Editor" replacement)
+
+### What's already built (do not re-build)
+
+`apps/api/src/billing/` + `apps/web` `MovementForm.tsx` already provide
+single-movement manual capture: `POST /billing` (`ledger:create`) creates one
+signed `Transaction` row with customer, domain, amount, currency, date,
+concept (`typeId`), period, reference, check number, and message; `POST
+/billing/:id/void` (`ledger:void`) reverses one. This is the direct
+equivalent of the legacy `DATOS AGUA`/`DATOS LUZ`/`DATOS TEL`/`DATOS CLAVE`
+per-service capture screens, already generalized into one form — **the
+per-service screens do not need to be rebuilt separately**; `domain` +
+`typeId` (from `TypeTransaction`) already carry that distinction, and the
+capture form can pre-select a concept when opened from a property's service
+tab.
+
+What's missing is everything the legacy system did *around* that single
+capture: batching many receipts against one check, separating out unpaid
+items, and reconciling a check's total against what was captured against it.
+
+### 1.1 Outstanding ("NOPAGO") workflow
+
+`Transaction.outstanding` already exists in the Prisma schema but nothing
+reads or writes it yet.
+
+- **`CreateMovementDto`** (`billing/movement.dto.ts`): add `outstanding?:
+  boolean`, default `false`. When `true`, the movement posts normally but is
+  excluded from "settled" balance views — mirrors the legacy `SALDOS ULTIMO
+  0` query, which already `HAVING NOPAGO = 0`s outstanding rows out of the
+  balance.
+- **`MovementForm.tsx`**: add an "Outstanding (sin fondos)" checkbox, shown
+  only for `domain = UTILITY` charge-direction rows (this is a per-service
+  charge concept, not a credit/payment concept).
+- **New endpoint** `POST /billing/:id/resolve-outstanding` (`ledger:create` —
+  same tier as capture, since resolving is completing a capture, not
+  reversing one). Body: `{ checkNumber: string, resolvedDate: string }`.
+  Sets `outstanding = false`, `checkNumber`, and updates `transactionDate` to
+  `resolvedDate` — this is the literal legacy behavior ("se actualiza
+  registro con fecha del día y el cheque a pagar y quitas outstanding").
+  Reject (400) if the row is already resolved or voided.
+- **New list filter**: `GET /billing?outstanding=true` (extend
+  `MovementParams`) — replaces the legacy `EDITA NO PAGO AGUA/LUZ/PHONE`
+  per-service outstanding screens with one filterable view (service already
+  filterable via `typeId`).
+- **Web**: an "Outstanding" tab or filter chip on `/estado-cuenta`
+  (Movimientos tab), each row showing a "Resolver" action that opens a small
+  form for check number + date.
+
+### 1.2 Batch capture by check
+
+Legacy staff key many customers' receipts against one check before cutting
+it, then verify the captured total matches the check amount
+(`CAPTURA AGUA`/`CAPTURA LUZ`/etc. feeding into `EDITA CHEQUE COUNT`/
+`REPORTE POR CHEQUE`). Model this as a **bulk-create, not a new persisted
+entity** — a check number is already a plain field on `Transaction`; there's
+no need for a `ReceiptBatch` table when grouping by `checkNumber` already
+answers every legacy query.
+
+- **New endpoint** `POST /billing/batch` (`ledger:create`). Body: `{
+  domain, typeId, transactionDate, currency, checkNumber, lines: [{
+  customerId, amount, reference, period, outstanding? }] }` — the
+  check-level fields are shared, only the per-customer fields repeat. Runs
+  as one Prisma `$transaction`, returns the created rows plus `{ total,
+  count }` so the UI can show the running total against the physical check
+  amount as staff add lines, exactly matching the legacy reconciliation
+  practice.
+- **Web**: a `/estado-cuenta/captura` (or `/estado-cuenta/lote`) page — a
+  service-type + check-number header, then a repeating row (customer picker
+  + reference + amount + outstanding toggle), a running total, and a single
+  submit. This is the actual "Editor" screen the meeting notes are asking
+  for; it should be reachable from a new nav entry under "Estado de cuenta"
+  or "Servicios."
+
+### 1.3 Check reconciliation view
+
+Direct replacement for `EDITA CHEQUE ALF/COUNT/NUM`, `REPORTE POR CHEQUE`,
+`REPORTE POR CHEQUE PARA ALFA`, and the `REPORTE CHEQUE COUNT` report named
+explicitly in the meeting notes.
+
+- **New endpoint** `GET /billing/by-check?checkNumber=...` — all
+  non-voided `Transaction` rows with that `checkNumber`, plus `{ total,
+  count }`. Trivial query, no new indexes needed beyond the existing
+  `checkNumber` column (add a plain index — it's currently unindexed).
+- **New report entry** in `reports.registry.ts`: `slug: "cheque-count"`,
+  `legacyName: "REPORTE CHEQUE COUNT"`, params `{ checkNumber: text }`,
+  columns customer/reference/period/concept/amount, reusing the by-check
+  query above. This gives it a printable form for free via the existing
+  `/reportes/:slug` machinery — no new page needed.
+
+### Abilities
+
+No new abilities required — everything above reuses `ledger:create` /
+`ledger:void` (already `STAFF` / `MANAGER`). Batch capture and outstanding
+resolution are both "capturing a receipt," same trust tier as the existing
+single-movement form.
+
+---
+
+## 2. PDF / OCR auto-capture
+
+### Motivation (from the meeting)
+
+Each utility company (CFE, water, phone, gas...) sends 300+ individual
+statements a month, one per customer, currently keyed in by hand through the
+per-service capture screens — a high-volume, error-prone manual step. The
+ask: scan/receive the statements as PDF(s), have the system determine
+customer + amount automatically, and only require staff review rather than
+full manual entry.
+
+### Pipeline
+
+```
+Upload (1+ PDFs, one service kind per batch)
+  -> StorageService stores raw file(s)
+  -> Split into one document per statement (if a batch PDF bundles multiple)
+  -> OCR extraction (account/meter number, amount, period, due date)
+  -> Auto-match against PropertyService (accountNumber / meterNumber / route)
+  -> Review queue: high-confidence matches pre-filled, no-match/low-confidence flagged
+  -> Staff confirms (bulk-confirm high-confidence rows, hand-correct the rest)
+  -> Confirmed rows post through the SAME batch-capture path as §1.2
+  -> Source PDF page attached as a ServiceDocument on the matched property
+```
+
+The last two steps deliberately reuse §1.2's batch-capture endpoint rather
+than writing `Transaction` rows directly — OCR-sourced and hand-keyed
+receipts should go through one write path, one validation path, one audit
+trail.
+
+### Data model (new)
+
+```prisma
+enum StatementBatchStatus {
+  UPLOADED
+  PROCESSING
+  READY_FOR_REVIEW
+  COMPLETED
+  FAILED
+}
+
+enum StatementDocumentStatus {
+  PENDING_OCR
+  OCR_FAILED
+  NEEDS_REVIEW      // no confident match, or low OCR confidence
+  MATCHED           // confident auto-match, awaiting staff confirmation
+  CONFIRMED         // staff confirmed, not yet posted
+  POSTED            // posted as a Transaction
+  REJECTED          // staff rejected (duplicate, unreadable, wrong batch)
+}
+
+// `ServiceKind` needs one addition for this feature: `TELEPHONE`. It
+// doesn't exist today — phone numbers live on `Property.phone1/2/3`, not as
+// `PropertyService` rows. See "Matching logic" below for why OCR matching
+// needs it as a real service kind, and the backfill this implies.
+
+/// One upload session — e.g. "October CFE statements."
+model StatementBatch {
+  id           String                @id @default(uuid())
+  serviceKind  ServiceKind
+  status       StatementBatchStatus  @default(UPLOADED)
+  uploadedById String
+  fileCount    Int
+  createdAt    DateTime              @default(now())
+  documents    StatementDocument[]
+
+  @@map("statement_batches")
+}
+
+/// One statement (one customer, one period) after splitting the batch.
+model StatementDocument {
+  id                  String                    @id @default(uuid())
+  batchId             String
+  batch               StatementBatch            @relation(fields: [batchId], references: [id])
+  storageKey          String
+  status              StatementDocumentStatus   @default(PENDING_OCR)
+
+  // Raw OCR output, kept even after a manual correction so mismatches are
+  // auditable.
+  ocrRawText          String?                   @db.Text
+  ocrConfidence        Decimal?                 @db.Decimal(4, 3)
+
+  // Extracted (and, after review, staff-corrected) fields.
+  extractedAccountRef String?                   // RPU / phone / water account / zona fed / clave catastral / gas meter — see "Matching logic" for which PropertyService field this maps to per serviceKind
+  extractedAmount     Decimal?                  @db.Decimal(12, 2)
+  extractedPeriod     String?
+  extractedDueDate    DateTime?
+
+  // Match result.
+  matchedPropertyServiceId String?
+  matchedPropertyService   PropertyService?      @relation(fields: [matchedPropertyServiceId], references: [id])
+  matchedCustomerId        String?
+  matchedCustomer          Customer?             @relation(fields: [matchedCustomerId], references: [id])
+
+  reviewedById        String?
+  reviewedAt          DateTime?
+  postedTransactionId String?                   @unique
+  postedTransaction   Transaction?              @relation(fields: [postedTransactionId], references: [id])
+
+  createdAt           DateTime                  @default(now())
+
+  @@map("statement_documents")
+}
+```
+
+(`PropertyService`/`Customer`/`Transaction` gain the inverse relations.
+`ServiceDocument` is reused as-is for the confirmed receipt's permanent
+attachment — `StatementDocument.storageKey` and the eventual
+`ServiceDocument.storageKey` may point at the same object, or the confirm
+step copies it; either is fine, pick whichever is simpler at build time.)
+
+### Matching logic
+
+Match `extractedAccountRef` against **one specific `PropertyService` field,
+chosen by `serviceKind`** — never a fuzzy match across all of
+`accountNumber`/`meterNumber`/`route` at once, since that's how a water
+account number could accidentally collide with an unrelated phone number.
+Per the meeting notes' own field list:
+
+| Service (meeting note) | `ServiceKind` | Match against | Legacy source (`DATMEX`) | Status |
+|---|---|---|---|---|
+| CFE — RPU | `ELECTRIC` | `accountNumber` | `RPU` / `RPU2` / `RPU3` | ✅ populated today (`transform_properties.py`) |
+| Agua — Número de cuenta | `WATER` | `accountNumber` | `AGUA` | ✅ populated today |
+| Zona Fed — Número de Zona Federal | `FEDERAL_ZONE` | `accountNumber` | `ZFED` | ✅ populated today |
+| Tel — Número de teléfono | `TELEPHONE` *(new)* | `accountNumber` | `Property.phone1/2/3` (currently on `Property`, not `PropertyService`) | ⚠️ schema gap — see below |
+| Impuesto — Clave Catastral | `PROPERTY_TAX` | `accountNumber` | migrated from `PREDIAL`, **not** `CLAVE` | ⚠️ needs verification — see below |
+| Gas — Número de medidor | `GAS` | `meterNumber` | not populated — folded into free-text `notes` today | ⚠️ data gap — see below |
+
+Confidence rule of thumb once a field is confirmed populated, tune after
+seeing real statements:
+
+- Exact match on the scoped field → `MATCHED`, high confidence, pre-checked
+  for bulk-confirm.
+- No match, or the OCR confidence itself is low → `NEEDS_REVIEW`.
+- Multiple candidate matches (shouldn't happen if account numbers are
+  unique, but the legacy data has had duplication issues before — see
+  `docs/LEGACY_DATABASES_OBJECTS.md`'s `DUPLICADOS` report) → `NEEDS_REVIEW`
+  with all candidates surfaced, not an arbitrary pick.
+
+#### Three gaps this depends on — resolve before building the matcher
+
+Cross-checking the requested field list against `transform_properties.py`
+(the script that actually populated today's `property_services` table)
+surfaced three mismatches. OCR matching is only as good as the field it
+matches against, so these need to be closed first, not discovered mid-build:
+
+1. **No `TELEPHONE` service kind exists.** `ServiceKind` today is `WATER |
+   ELECTRIC | GAS | CABLE | PROPERTY_TAX | FEDERAL_ZONE | ALARM | OTHER` —
+   telephone was never unpivoted into `PropertyService` at all; the three
+   phone numbers live directly on `Property.phone1/phone2/phone3` (raw
+   contact fields, not billable-service rows), even though the legacy
+   ledger clearly bills phone as its own `TYPE OF TRX = "TELEPHONE"` (see
+   `EDITA NO PAGO PHONE`, `DATOS TEL`/`CAPTURA TEL` in the prior analysis).
+   **Fix:** add `TELEPHONE` to the `ServiceKind` enum, and backfill one
+   `PropertyService` row per non-null `Property.phone1/2/3` (`kind:
+   TELEPHONE, accountNumber: <the phone number>`) as a one-time migration
+   script companion to this feature — mirrors how `transform_properties.py`
+   already emits multiple `WATER` rows per property for secondary meters.
+
+2. **`PROPERTY_TAX.accountNumber` holds `PREDIAL`, not `CLAVE`.** The
+   meeting notes name "Clave Catastral" specifically, and the legacy
+   query/report names agree (`CAPTURA CLAVE`, `DATOS CLAVE`, `CLAVES
+   CATASTRALES`, `CATASTRO` — all filter on `DATMEX.CLAVE`). But
+   `transform_properties.py` line ~191 sets `PROPERTY_TAX.accountNumber =
+   DATMEX.PREDIAL`, a *different* column (`DATMEX` has both `CLAVE`
+   VARCHAR and `PREDIAL` DOUBLE). Two live possibilities: either `PREDIAL`
+   is the wrong field and the migration should have used `CLAVE`, or they're
+   two genuinely different numbers (e.g. clave catastral = the cadastral
+   lookup key stamped on the printed bill vs. predial = an internal
+   receipt/folio number) and `PropertyService` needs *both* — only one of
+   which (the clave catastral) is what OCR will actually read off a real
+   predial statement. **Needs a real predial receipt in hand (or Jorge's
+   confirmation) before deciding**; don't wire the matcher to `PREDIAL` on
+   the untested assumption it's the same thing.
+
+3. **`GAS.meterNumber` is never populated.** `transform_properties.py`
+   only ever sets `notes = DATMEX.GAS` for gas service rows — there's no
+   distinct meter-number column in the legacy `DATMEX` table for gas at
+   all (unlike electric/water, which have `RPU`/`MEDIDOR`). This matches
+   the earlier finding that "Gas – Número de medidor" has no legacy source
+   field. **This can't be backfilled from existing data** — the practical
+   fix is that gas OCR matching starts cold (every gas statement lands in
+   `NEEDS_REVIEW` until a human confirms it once), and *that first
+   confirmation* is what populates `PropertyService.meterNumber` for that
+   property going forward, so subsequent statements for the same meter
+   auto-match. Worth calling out in the review-queue UI ("first time
+   seeing this meter — confirm to enable auto-match next time").
+
+### OCR provider — open decision, don't build against one prematurely
+
+CFE (and most MX utility) bills are **fixed-layout, single-language,
+high-volume forms**, not arbitrary documents — this is closer to
+"template/anchor text extraction" (regex against OCR'd text for known
+labels like `RPU`, `No. de Cuenta`, `Total a pagar`) than to a full ML
+document-understanding problem. Recommend:
+
+- Define an `OcrProvider` interface (`extract(buffer, hints): Promise<{
+  text: string, fields: ExtractedFields, confidence: number }>`) so the
+  concrete engine is swappable.
+- Start with a self-hosted OCR (e.g. Tesseract) + hand-written per-company
+  extraction rules (one rule set per `ServiceKind`/provider, since CFE's
+  layout differs from the water company's). Cheap, no per-page cost, and the
+  layouts are stable enough that this is realistic.
+- Escalate to a managed document-extraction API (AWS Textract, Azure
+  Document Intelligence, Google Document AI) only if the self-hosted
+  accuracy proves too low in practice — all three fit behind the same
+  interface with no schema changes.
+- **This choice needs Jorge's input on budget/volume before committing** —
+  300+ pages/month/company is enough volume that a per-page-priced API has a
+  real recurring cost.
+
+### API surface
+
+- `POST /statements/batches` (`statement:ingest`) — multipart upload, one or
+  more PDFs + `serviceKind`. Creates the batch, kicks off async
+  split+OCR+match (background job, not inline in the request).
+- `GET /statements/batches` / `GET /statements/batches/:id` — status +
+  document list.
+- `GET /statements/batches/:id/documents?status=NEEDS_REVIEW` — the review
+  queue.
+- `PATCH /statements/documents/:id` (`statement:review`) — staff correction
+  of extracted fields or match.
+- `POST /statements/documents/:id/confirm` (`statement:review`) — single
+  confirm.
+- `POST /statements/batches/:id/confirm-matched` (`statement:review`) —
+  bulk-confirm every `MATCHED` document in one call.
+- `POST /statements/documents/:id/reject` (`statement:review`).
+- Confirming posts through §1.2's batch-capture internals (same service
+  method, not the HTTP endpoint) so it's one transaction per batch of
+  confirms, not N.
+- **Confirm also backfills the matched field when it was empty** — if
+  `matchedPropertyServiceId` was set by staff (not by an exact auto-match)
+  because the scoped field was blank on that `PropertyService` (the `GAS`
+  case above, and any one-off historical gap in the other kinds), write
+  `extractedAccountRef` into that service's `accountNumber`/`meterNumber`
+  as part of the confirm transaction. This is what makes the "first
+  confirmation teaches the matcher" behavior in gap 3 above actually work,
+  rather than requiring a separate manual data-entry pass.
+
+### Abilities (new)
+
+| Ability | Min role | Notes |
+|---|---|---|
+| `statement:ingest` | STAFF | upload a batch |
+| `statement:review` | STAFF | correct/confirm/reject; same tier as `ledger:create` since confirming *is* capturing |
+
+### Open questions
+
+- **Clave catastral vs. predial** (gap 2 above) — get a real predial
+  statement or Jorge's confirmation of whether `CLAVE` and `PREDIAL` are the
+  same number before wiring the `PROPERTY_TAX` matcher. Blocks that one
+  service kind, not the whole feature.
+- **Telephone as a service kind** (gap 1 above) — confirm the backfill
+  approach (one `PropertyService` row per populated `Property.phone1/2/3`)
+  is correct, and whether a property with all three phones populated should
+  really produce three separate billable "services," or whether phone
+  billing is actually 1-per-property regardless of how many numbers are on
+  file (would change the backfill to pick a primary number instead of
+  fanning out to three rows).
+- Multi-statement PDF splitting: does the source ever arrive as one PDF per
+  customer already (simplifies to "batch = folder of PDFs"), or as one
+  giant PDF per company per month that needs page-range splitting? Changes
+  whether a page-boundary detector is needed at all.
+- Retention: keep `StatementDocument.storageKey` (and the raw OCR text)
+  indefinitely for audit, or purge after posting since `ServiceDocument`
+  already holds the permanent copy? Recommend keep — cheap, and it's the
+  audit trail for "why did the system think this was customer X."
+
+---
+
+## 3. Multi-bank chequera
+
+### Motivation
+
+Seguros uses a US bank account; Utilities uses a Mexican bank account. The
+current `BankTransaction` model (migrated from `SCOTHIA.mdb`) has no bank or
+currency dimension at all — it's a single implicit account, MXN-only, by
+design (see `PLAN.md` migration step 7 finding (c)). Need to support more
+than one register, each with its own bank and currency.
+
+Confirmed against the actual code, not just the schema comment: this is a
+real, deliberate, load-bearing assumption, not an oversight to patch around.
+`migration/transform_bank.py` has no bank/currency column to read in the
+first place — `DATOS I`/`DATOS E` are `fecha, tipo, num, concepto,
+ingreso/egreso, operado, notas, cantidad_en_letra`, nothing else. And
+`bank.service.ts`'s module doc-comment states outright: "SINGLE CURRENCY...
+`bank_transactions` has none, and every `amountInWords` on the egreso side
+is spelled out in PESOS. All figures in this module are MXN." Every method
+in that file — `where()`, `totalsFor()`, `facets()`, `summary()`, `stats()`,
+`createMovement()` — currently has zero notion of "which account." That's
+the actual surface area this feature touches, itemized below.
+
+### Data model changes
+
+```prisma
+model Bank {
+  id       String        @id @default(uuid())
+  name     String        @unique   // e.g. "Scotiabank", "Bank of America"
+  country  String?                 // "MX" | "US" — informational
+  accounts BankAccount[]
+
+  @@map("banks")
+}
+
+/// One physical chequera. Currency is fixed per account (real bank
+/// accounts don't mix currencies) — do NOT add a currency filter to
+/// BankTransaction itself; it inherits the account's currency.
+model BankAccount {
+  id           String            @id @default(uuid())
+  bankId       String
+  bank         Bank              @relation(fields: [bankId], references: [id])
+  label        String            // "Utilities operating (MXN)", "Seguros operating (USD)"
+  currency     Currency
+  businessLine TransactionDomain? // hint only, not enforced — a chequera can pay for more than one line
+  active       Boolean           @default(true)
+  movements    BankTransaction[]
+
+  @@map("bank_accounts")
+}
+```
+
+`BankTransaction` gains:
+
+```prisma
+model BankTransaction {
+  // ...existing fields...
+  bankAccountId String
+  bankAccount   BankAccount @relation(fields: [bankAccountId], references: [id])
+}
+```
+
+`bankAccountId` should be **required**, not optional — a bank movement
+without a known account isn't meaningfully reconcilable. This means the
+migration step below has to run before the column goes non-null.
+
+### Migration of existing data
+
+All 22,354 existing `BankTransaction` rows are SCOTHIA data — MXN, single
+bank. Before making `bankAccountId` required:
+
+1. Insert one `Bank` row for Scotiabank, one `BankAccount` row under it
+   (`label: "Utilities — Scotiabank (MXN)"`, `currency: MXN`,
+   `businessLine: UTILITY`).
+2. Backfill every existing `BankTransaction.bankAccountId` to that account's
+   id.
+3. Add the second account (`"Seguros — <bank TBD> (USD)"`) — **needs the
+   actual US bank name from Jorge**, plus whether historical Seguros bank
+   data exists anywhere to migrate (the current inventory has no Seguros
+   bank register file — only `SCOTHIA.mdb`, which is Utilities' own book,
+   per `PLAN.md`'s source inventory). If no historical USD register exists,
+   this account starts empty and only carries movements captured going
+   forward.
+
+The existing `@@unique([legacySourceTable, legacyId])` on `BankTransaction`
+needs no change — every legacy row only ever belongs to the one Scotiabank
+account being backfilled in step 2, so provenance uniqueness still holds
+per-row regardless of how many accounts exist afterward.
+
+### Code touch points (`bank.service.ts`, `bank.controller.ts`)
+
+This module currently has **no filterable dimension at all** beyond
+direction/cleared/date — every account-scoping change is additive, not a
+rewrite, but it touches every read method because two of them
+(`facets()`, `summary()`) bypass the Prisma query builder entirely and use
+hand-written `$queryRaw` template SQL:
+
+- **`where()`** — trivial, add `bankAccountId` to the `AND` array like any
+  other filter (Prisma builder, same pattern as `direction`/`cleared`).
+- **`totalsFor()`** — takes the already-built `where`, so it inherits the
+  scoping for free once `list()`/`stats()` pass a scoped `where` in.
+- **`facets()`** — currently `SELECT YEAR(transactionDate)... FROM
+  bank_transactions WHERE voidedAt IS NULL` with no account clause at all;
+  needs `AND bankAccountId = ${accountId}` interpolated into the raw SQL
+  (parameterized, not string-concatenated — this file already uses Prisma's
+  tagged-template `$queryRaw`, which parameterizes automatically as long as
+  the account id is passed as a template value, not spliced into the string
+  by hand).
+- **`summary()`** — same issue, in *two* raw queries (the yearly rollup and
+  the monthly rollup when a year is selected) — both need the same
+  `AND bankAccountId = ${accountId}` clause. Miss one and the "Resumen"
+  tab's year list and its drill-down would scope to different accounts,
+  which is a worse bug than not scoping at all (looks correct, silently
+  wrong).
+- **`stats()`** — currently calls `totalsFor({})` (empty filter = every
+  row). Needs `totalsFor({ bankAccountId })`; same for the `count`/`bounds`/
+  `pending`/`transferred` aggregates alongside it.
+- **`createMovement()` / `voidMovement()`** — `createMovement` needs
+  `bankAccountId` added to the `data` object (from the new required DTO
+  field below); `voidMovement` needs no change — it already operates by row
+  `id`, and a voided row's account never changes.
+
+### API surface changes
+
+- `bank.controller.ts`: every route (`list`, `summary`, `stats`, `facets`)
+  gains a required `?bankAccountId=` query param, threaded through to the
+  service methods above. **Required, not optional with an "all accounts"
+  default** — summing MXN and USD registers together would repeat the exact
+  currency-collapsing mistake the billing module's header comment
+  explicitly warns against (912 customers with both-currency ledgers).
+  There is no meaningful "no account selected" state once accounts exist,
+  only "no account selected *yet*" while the UI loads its default.
+- New `bank/accounts` sub-resource: `GET /bank/accounts` (list, any
+  authenticated user — the account picker needs this before anything else
+  can render), `POST /bank/accounts` / `PATCH /bank/accounts/:id`
+  (`bank:manage-accounts`, MANAGER — creating/editing accounts is rarer and
+  higher-stakes than posting movements).
+- `CreateBankMovementDto` gains a required `bankAccountId: string`.
+
+### Web
+
+- `bank/page.tsx`'s own doc-comment currently states "Single currency
+  (MXN) — the source has no currency column" as a design fact; that
+  comment (and the assumption behind it) needs to be removed/rewritten as
+  part of this change, not just the UI.
+- `/banco` gains an account selector (tabs or a dropdown) at the top,
+  scoping both the "Movimientos" and "Resumen por periodo" tabs — mirrors
+  how `/estado-cuenta` already scopes by currency without ever summing
+  across it. Every existing call site in `lib/api.ts`
+  (`listBankMovements`, `getBankStats`, `getBankSummary`, `getBankFacets`,
+  `createBankMovement`) needs the new `bankAccountId` parameter threaded
+  through, and `lib/types.ts`'s `CreateBankMovementInput` gains the field.
+- New `/banco/cuentas` (or a section under `/catalogos`) for managing banks
+  and accounts, gated the same way `/catalogos` already gates lookup
+  management.
+
+### Abilities (new)
+
+| Ability | Min role | Notes |
+|---|---|---|
+| `bank:manage-accounts` | MANAGER | create/edit `Bank`/`BankAccount` rows |
+
+### Open questions
+
+- Confirm the actual US bank name/details for the Seguros account.
+- Does Seguros have *any* historical bank register data to migrate, or does
+  this start from zero on cutover?
+- Should `businessLine` on `BankAccount` be enforced (a UTILITY account
+  can't post an INSURANCE movement) or left as a soft hint? Recommend soft —
+  the legacy single account already mixed concerns per `PLAN.md`'s finding
+  that `TABLA RAMODOS` wasn't a clean business-line split.
+
+---
+
+## 4. Customer-number recycling
+
+### Motivation
+
+The physical folder system is organized by `Customer` number
+(`DATGRAL.[NUM id]` in the legacy data, currently only preserved as a
+`CustomerLegacyRef` string, not a first-class field). When a customer
+cancels, doesn't renew, or goes a year with no activity, staff currently
+*manually* hunt for such customers, purge their folder, and reuse the
+number for a new customer. The ask: keep the physical-folder-compatible
+sequential numbering, but automate the search for reusable numbers and
+auto-assign the lowest free one at creation — a Claude Code equivalent of
+"find the first empty spot."
+
+### Data model changes
+
+```prisma
+model Customer {
+  // ...existing fields...
+  customerNumber   Int?      @unique   // the physical-folder number; null = not yet assigned (shouldn't happen post-migration) or released
+  numberReleasedAt DateTime?           // non-null once the number has been freed for reuse; customerNumber is cleared at the same time (see below)
+}
+
+enum NumberReleaseReason {
+  CANCELLED       // explicit non-renewal / service cancellation
+  INACTIVITY      // >= 1 year with no ledger activity
+  MANUAL
+}
+
+/// Audit trail for a recycled number, surviving the Customer row it came
+/// from being archived/purged. customerId is nullable so history remains
+/// readable even if the originating customer is later hard-deleted.
+model CustomerNumberHistory {
+  id             String               @id @default(uuid())
+  customerNumber Int
+  customerId     String?
+  customer       Customer?            @relation(fields: [customerId], references: [id])
+  assignedAt     DateTime
+  releasedAt     DateTime?
+  releaseReason  NumberReleaseReason?
+  releasedById   String?
+
+  @@index([customerNumber])
+  @@map("customer_number_history")
+}
+```
+
+### Backfill
+
+At implementation time, backfill `customerNumber` from the existing
+`CustomerLegacyRef` rows where `sourceSystem = "utilities" AND sourceTable =
+"DATGRAL"` (the legacy `NUM id` — already migrated, just not promoted to a
+first-class column). Insurance-only customers (no utilities `DATGRAL` row)
+won't have a legacy number; decide at build time whether they get one
+retroactively assigned or stay `null` until they need one (recommend: assign
+one on demand, the first time anyone needs to give them a physical folder —
+not retroactively for all 510 insurance-only customers at once).
+
+**This backfill isn't a straight cast of every `legacyId` to `Int`.**
+Checked against `migration/transform_customers.py`: for a utilities
+`DATGRAL` row, `legacyId` is set to `nid or f"rownum_{len(customers)}"` —
+`nid` is the real `NUM id` only when the source row actually had one;
+**~140 utilities rows had a blank `NUM id`** (the same "blank name" data
+quality issue the same script recovers names for) and got a synthetic
+`rownum_N` placeholder instead, which is not a physical-folder number and
+must not be cast into `customerNumber`. Insurance-side refs have the same
+pattern (`insrow_N` placeholders). The backfill query needs an explicit
+numeric filter (`legacyId REGEXP '^[0-9]+$'`, or equivalent), and every row
+that fails it is exactly the "insurance-only or blank-`NUM id`" case that
+falls through to on-demand assignment above, not an error to chase down.
+
+`customers.service.ts`'s `list()`/`detail()` `select` blocks don't include
+`customerNumber` today (only `name`, `nameSource`, contact fields, counts)
+— it needs adding to both, plus to the `/clientes` list-page columns and
+the customer detail header, since staff read this number constantly for
+the physical folder. `list()`'s search (`where.OR`) already matches
+`legacyRefs.some.legacyId.contains` as a fallback for finding someone by
+their old number; once `customerNumber` is first-class, add a direct
+`{ customerNumber: Number(query) }` branch when the query parses as an
+integer, so a numeric search hits the fast indexed column instead of the
+join.
+
+### Eligibility detection (the automation)
+
+This is explicitly framed as **surfacing candidates for staff review, not
+auto-purging** — the actual release/reuse decision stays a human action,
+matching how the office works today; only the *search* is automated.
+
+- **New endpoint** `GET /customers/recycling-candidates` — customers where
+  either:
+  - `CANCELLED`: no active `Policy` (not archived, `policyTo` in the past
+    with no renewal) **and** no active `PropertyService`, or
+  - `INACTIVITY`: `MAX(Transaction.transactionDate)` across all their
+    transactions is more than 1 year ago (or no transactions at all and
+    `customerSince` is more than 1 year ago).
+  
+  This can be a plain query (no new job/queue needed — it's a read, not a
+  mutation) run on-demand when staff open a "Clientes para reciclar" screen,
+  the same way `/billing/balances` is computed live rather than
+  materialized.
+- **New endpoint** `POST /customers/:id/release-number`
+  (`customer:recycle`, MANAGER). Body: `{ reason: NumberReleaseReason }`.
+  - Closes the open `CustomerNumberHistory` row (`releasedAt = now,
+    releaseReason, releasedById`).
+  - Sets `Customer.customerNumber = null`, `numberReleasedAt = now`.
+  - **Archives** the customer (`archivedAt = now`) — does **not** hard-delete
+    or scrub PII by default. See the purge question below.
+
+### Auto-assignment at creation
+
+- **`customers.service.ts` create path**: before insert, compute
+  `SELECT MIN(n) candidate FROM (SELECT customerNumber+1 AS n FROM
+  customers) WHERE n NOT IN (SELECT customerNumber FROM customers WHERE
+  customerNumber IS NOT NULL)` — i.e., the lowest positive integer not
+  currently held by any customer (released numbers, being `NULL` again,
+  automatically qualify; no separate "available pool" table needed, which
+  keeps this consistent with "vacancy = not currently claimed" rather than
+  a second source of truth that can drift). Simplify at build time with
+  whatever the DB makes cheapest (a gaps-and-islands query, or maintaining a
+  running `MAX` + a small released-numbers cache — pick based on real
+  customer-count scale, which is ~1,700, trivially small for a live scan).
+  Open a new `CustomerNumberHistory` row (`assignedAt = now`) for the new
+  assignment.
+- **Concurrency**: `customers.service.ts`'s `create()` today is a single
+  unguarded `prisma.customer.create()` — no transaction, no locking, which
+  is fine for arbitrary fields but not for a "pick the lowest unclaimed
+  integer" computation, where two staff creating a customer at the same
+  moment can both compute the same candidate number before either insert
+  lands. The `customerNumber` unique constraint turns that race into a
+  Prisma unique-violation error rather than silent data corruption, but the
+  create path needs to actually handle it — wrap the compute-and-insert in
+  a `prisma.$transaction` and retry once on a unique-constraint failure
+  (catch `P2002` on `customerNumber`, recompute, re-insert), rather than
+  letting the second staff member's creation just fail.
+- **Web**: `/clientes/nuevo` (`CustomerForm.tsx`) shows the assigned number
+  as soon as the form loads (read-only, "Número de cliente: 214
+  (reciclado)" if it's a reused slot, so staff know to expect the old
+  physical folder) — server-assigns it on submit, doesn't let staff type an
+  arbitrary one, which is what prevents the collisions manual assignment
+  risks today. `CreateCustomerDto` deliberately gains **no** `customerNumber`
+  field — the whole point is the client can't set it.
+
+### The purge question — needs Jorge's decision
+
+The office's paper-world habit is literally "purge their history, info,
+etc." when recycling a folder. This codebase's established convention is
+the opposite — **never hard-delete migrated/business data**, only archive
+(`archivedAt`), specifically so mistakes are reversible and there's always
+an audit trail (see `Customer.archivedAt`, `Policy.archivedAt`,
+`Property.archivedAt`, and the `OpsJob`/`ActivityLog` audit models already
+in the schema).
+
+Recommend: **archive by default, never hard-delete.** The `customerNumber`
+release already solves the actual operational need (the number is free to
+reuse); keeping the old customer's data around under a freed number costs
+nothing and preserves history for the inevitable case where "definitely
+cancelled" turns out to be wrong. If Jorge specifically wants literal
+data purge (e.g. for a data-retention/privacy policy reason, not just
+paper-world habit), that should be a **separate, explicit, `ADMIN`-only**
+action (`customer:purge`) taken well after release — not bundled into
+`release-number` — so the two decisions ("this number is reusable" vs.
+"permanently destroy this person's records") aren't accidentally coupled.
+
+### Abilities (new)
+
+| Ability | Min role | Notes |
+|---|---|---|
+| `customer:recycle` | MANAGER | flag a candidate reviewed, release their number, archive the record |
+| `customer:purge` | ADMIN | **only if** Jorge wants literal PII destruction, kept separate from release |
+
+### Open questions
+
+- Confirm the "1 year of no activity" clock: measured from last
+  `Transaction.transactionDate`, or should a customer with an *expired but
+  never-renewed* policy count as cancelled immediately rather than waiting
+  out the year? (Spec above treats these as two independent triggers,
+  `CANCELLED` vs. `INACTIVITY` — confirm that's the right split.)
+- Does Jorge want true data purge at all, or is archive-and-hide
+  sufficient? (See above — recommend archive-only unless there's a
+  compliance reason for real deletion.)
+- Should insurance-only customers (no legacy `NUM id`) share the same
+  numbering sequence as utilities customers, or get their own? Recommend
+  one shared sequence — it's one physical-folder system per the notes, not
+  two.
+
+---
+
+## Build sequencing
+
+1. **§1.1 + §1.2 + §1.3 (receipt capture completion)** — smallest, builds
+   directly on existing `billing/` code, no new tables. Ship first; it's
+   also a prerequisite for §2.
+2. **§4 (customer-number recycling)** — independent of the others,
+   touches `customers/` only. Can be built in parallel with §1.
+3. **§3 (multi-bank chequera)** — independent of §1/§2, touches `bank/`
+   only. Needs the US bank name from Jorge before the migration step can
+   run; the schema/API work can start before that answer arrives.
+4. **§2 (PDF/OCR auto-capture)** — largest, depends on §1 being done (it
+   posts through the batch-capture path) and on the OCR-provider decision.
+   Build last, and prototype the extraction accuracy against a handful of
+   real CFE statements before committing to the provider choice.
+
+## Open questions to take back to Jorge (collected)
+
+- OCR provider/budget for §2 (self-hosted vs. managed API, given 300+
+  pages/month/company).
+- Whether source PDFs arrive pre-split per customer or as one bundled file
+  needing page-range detection (§2).
+- Whether "Clave Catastral" and the already-migrated `PREDIAL`-sourced
+  `PROPERTY_TAX.accountNumber` are the same number — blocks OCR matching
+  for predial statements specifically until confirmed (§2).
+- Whether phone billing is really one service per phone number on file, or
+  one per property regardless of how many numbers are recorded — decides
+  how the new `TELEPHONE` service kind gets backfilled (§2).
+- The actual bank name/currency/details for the Seguros USD account, and
+  whether any historical Seguros bank data exists to migrate (§3).
+- Whether `BankAccount.businessLine` should be enforced or a soft hint
+  (§3).
+- The exact "1 year inactivity" / "cancelled" recycling triggers (§4).
+- Whether customer-number recycling should ever include *true* data purge,
+  or archive-and-reuse-the-number is sufficient (§4).
@@ -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.
@@ -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 "
-                    f"WHERE {EMPTY_WHERE}")
+        # Delete by the exact id set selected above (which already carries the
+        # 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")
@@ -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",
 ]

@@ -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")
@@ -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 = {}
-        cur.execute("SELECT id,sourceSystem,sourceTable,legacyId,customerId FROM customer_legacy_refs")
-        for rid, system, table, legacy, customer_id in cur.fetchall():
-            existing[(system, table, legacy)] = (rid, customer_id)
-        for rec, ref in zip(customers, refs):
-            key = (ref[2], ref[3], ref[4])
-            customer_id = existing.get(key, (None, rec["id"]))[1]
-            rec["id"] = customer_id
+        # Resolve each in-memory customer to a stable id: if ANY of its legacy
+        # refs already exists in the DB, reuse that customer's id (keeps PKs
+        # stable and preserves manual edits). `customers` and `refs` are
+        # different-length, differently-ordered lists — merged identities add a
+        # ref without a customer — so refs are grouped by their owning customer,
+        # never positionally zipped (the old zip mispaired almost every row).
+        cur.execute("SELECT sourceSystem,sourceTable,legacyId,customerId FROM customer_legacy_refs")
+        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:])
-            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)
+        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[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")
@@ -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])
-            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))
+
+        # Adjusters carry no provenance key, so they can't be upserted by one.
+        # 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_cols = ("id,policyNumber,customerId,policyTypeId,insuranceProviderId,agentName,policyDate,"
-                "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])
+        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(f"INSERT INTO policies ({pol_cols}) VALUES ({ph})", pol_rows)



@@ -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")
-        for row in props:
-            key = (row[8], row[9])
-            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")
+        # Children first (scoped to legacy-owned rows so manual rows survive),
+        # then upsert properties (ids already stable), then drop legacy rows
+        # gone from source, then re-insert the rebuilt children.
        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()

@@ -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}")
@@ -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")
 }
Author	SHA1	Message	Date
rmancinasandClaude Sonnet 5	159dcc4963	docs(plan): add step 11 for receipt-capture + net-new ops features Points PLAN.md at docs/RECEIPT_CAPTURE_SPEC.md and surfaces its open design questions (OCR provider, Seguros bank details, clave catastral vs. predial, recycling triggers) separately from the existing ops-only open items list. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>	2026-07-26 23:28:13 -07:00
rmancinasandClaude Sonnet 5	9b9ee201c9	docs: add receipt capture, OCR, multi-bank & customer-recycling spec Forward implementation spec covering the legacy "Editor" receipt-capture workflow plus three net-new requests from the 2026-07-25/26 meeting with Jorge: PDF/OCR auto-capture, multi-bank chequera support, and customer-number recycling. Matching logic and data-model gaps for each were verified against the actual migration scripts and API code, not just the schema comments. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>	2026-07-26 23:26:23 -07:00
rmancinasandClaude Opus 5	6dbd4a319b	fix(reports): render renewal-letter premium lines as booleans Build and Push Images / Build jorgecuadros-web (push) Successful in 1m44s Details Build and Push Images / Build jorgecuadros-api (push) Successful in 2m1s Details `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>	2026-07-26 12:07:32 -07:00
rmancinasandClaude Sonnet 5	f7ae0d5342	feat(reports): parameterized renewal-notice report + legacy report reference Build and Push Images / Build jorgecuadros-web (push) Failing after 59s Details Build and Push Images / Build jorgecuadros-api (push) Successful in 1m59s Details Replaces ~40 legacy Access renewal-notice report clones (one per carrier per coverage tier, e.g. AMPL/RC/LIC RENEW X MES/VENCE ATLAS 13/2013) with one parameterized aviso-renovacion report driven by real Policy/Vehicle/ coveragesJson data instead of hand-typed label text per clone. - schema.prisma: add RenewalNotice, replacing the legacy CONTROL <ramo> RENEW[2/3] X MES paper log of which notice generation was sent - reports: new "letter" ReportFormat + aviso-renovacion registry entry + LetterLayout renderer in ReportRunner.tsx - docs/RENEWAL_NOTICES.md + migration/legacy_report_defs/: extracted (via Application.SaveAsText, since the VBA project wouldn't load) and documented the legacy report/query chain this replaces Coveragesjson key names and a mark-as-sent mutation are still unverified/ unbuilt — see caveats in docs/RENEWAL_NOTICES.md. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>	2026-07-25 23:05:21 -07:00
rmancinasandClaude Opus 4.8	1b79b43a54	fix(migration): make Phase B additive sync actually work + verify end-to-end Build and Push Images / Build jorgecuadros-web (push) Successful in 1m37s Details Build and Push Images / Build jorgecuadros-api (push) Successful in 2m9s Details The --sync path had never been run and was broken in several ways. Fixed and verified against the dev DB (two consecutive syncs, both exit 0, 32/32 assertions: stable PKs, manual-row preservation, changed-row updates, legacy-delete, no child duplication, zero FK orphans; idempotent). - policies/properties: reuse each legacy row's existing id (by provenance) BEFORE building child rows, so children no longer point at a discarded fresh uuid; rebuild legacy-owned children via scoped delete + reinsert. - customers: replace zip(customers, refs) (mispaired almost every row) with a ref-grouped id remap; names now restore and no spurious customers appear. - drop the invalid Vehicle @@unique(legacySourceTable, legacyId) — one legacy policy row carries up to 3 vehicles sharing a legacyId; handle via delete+reinsert. - upsert lookup tables (policy_types, insurance_providers, type_transactions, adjusters) by natural name and remap child FKs instead of inserting fresh uuids that nothing points at. - transactions: drop updatedAt=NOW() (no such column); guard report formatting on NULL legacySourceTable (manual rows). Same report guard in bank. - add manual-safe prune (prune_empty_customers.py --sync, in SYNC_STEPS): prune only legacy-owned empties, never manually-added customers. web: customer-detail mini tx list now strikes voided rows with an "(anulado)" tag (was the last void-UI rendering gap; /estado-cuenta already handled it). docs: RESUME.md updated — Phase B sync marked verified end-to-end, void-UI browser pass recorded. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>	2026-07-24 13:37:22 -07:00
rmancinas	8802f08d4f	feat(reports): reports module + /inicio + edo-cuenta-datos prefill Build and Push Images / Build jorgecuadros-web (push) Successful in 1m35s Details Build and Push Images / Build jorgecuadros-api (push) Successful in 2m11s Details - New reports backend (registry, service, controller, outputs, types) with catalog endpoint + slug/CSV/XLSX/PDF/print outputs. - /reportes catalog + /reportes/[slug] runner; ReportRunner + ContextReports components wire pre-filtered links from domain pages. - Fix: /reportes/[slug] now reads searchParams and forwards initialParams to ReportRunner so /reportes/edo-cuenta-datos?customerId=... auto-runs instead of dropping the id and forcing a manual customer search. - /inicio landing page; root + login redirect to /inicio. - Company header env vars + logo asset for PDF/print rendering. - exceljs + pdfkit deps.	2026-07-23 23:20:41 -07:00
rmancinas	921a47cbaa	feat(web): use company logo PNG in brand mark Build and Push Images / Build jorgecuadros-api (push) Successful in 2m2s Details Build and Push Images / Build jorgecuadros-web (push) Successful in 3m7s Details Replace the JC text mark in AppShell and login with the real company_logo.png. Restyle .brand-mark to host the image (white rounded bg, object-fit contain). Appbar 38px, login panel 46px.	2026-07-23 22:17:17 -07:00
rmancinas	27b3bd9efc	feat: expand admin and data sync workflows Build and Push Images / Build jorgecuadros-web (push) Successful in 1m40s Details Build and Push Images / Build jorgecuadros-api (push) Successful in 3m13s Details	2026-07-23 22:00:08 -07:00
rmancinasandClaude Opus 4.8	70911e7e62	feat(deploy): app stack + manual Portainer deploy workflow Build and Push Images / Build jorgecuadros-web (push) Successful in 2m2s Details Build and Push Images / Build jorgecuadros-api (push) Successful in 3m36s Details Add the missing api/web deployment path on top of the existing image build CI. - deploy/jorgecuadros-app.stack.yml: PROD app stack (api + web) pulling the git.mancinas.io registry images. Does not ship mysql/minio (separate stacks); API reaches them via DATABASE_URL / S3_ENDPOINT. API pinned to the jorgecuadros_db node for stable ingest/backup volumes; web is stateless. - deploy/jorgecuadros-app.env.example: documented stack env template. - .gitea/workflows/deploy.yml: manual (workflow_dispatch) deploy to Portainer via cssnr/portainer-stack-deploy-action. Inputs: image tag + scope (app = web+api, full = db+minio+app, applied db->minio->app). Make the web API origin runtime-configurable instead of build-baked: the root layout injects window.__API_ORIGIN__ from the API_ORIGIN env (force-dynamic) and lib/api.ts resolves it at runtime, so one built image serves any deployment. Also: dev.sh to run both dev servers (frees stale ports first) and move local dev to ports web 4500 / api 4501. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>	2026-07-23 20:07:48 -07:00
rmancinasandClaude Opus 4.8	afe2411c86	feat(storage): wire MinIO/S3 document upload & download into the API + web Build and Push Images / Build jorgecuadros-web (push) Successful in 1m37s Details Build and Push Images / Build jorgecuadros-api (push) Successful in 2m8s Details The schema has carried `storageKey` pointers and the migration has written blobs to MinIO since day one, but the API had no S3 client — documents could only be deleted, never uploaded or retrieved. This adds the missing wiring. API - StorageModule/StorageService (@aws-sdk/client-s3, path-style for MinIO): put/getStream/delete, best-effort bucket ensure on boot, gracefully disabled when S3 env is absent (ServiceUnavailable on use). - Reads S3_ENDPOINT/S3_BUCKET + S3_ACCESS_KEY/S3_SECRET_KEY, falling back to MINIO_ROOT_USER/MINIO_ROOT_PASSWORD so one credential set drives both the migration and the API. - Property service documents: POST :id/documents (multipart), GET :id/documents/:childId/download (streamed), delete now also drops the blob. - Policy documents: same upload/download/delete (previously had none). - Keys stay under the service/<id>/… and policy/<id>/… prefixes the migration established. Web - api.ts: shared uploadFile() helper (uploadIngest refactored onto it), upload/download/remove helpers for property & policy documents. - Servicios, polizas, clientes detail pages: real Descargar links and an upload control (gated by policy:update / property:update) replacing the "storage pending" notes. Infra - docker-compose: minio service (9000/9001, healthcheck, named volume) + S3 env wired into the api service. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>	2026-07-23 19:19:57 -07:00
rmancinas	45afb824ef	Merge feat/crud-rbac: CRUD/RBAC + ops panel + Docker CI/versioning Build and Push Images / Build jorgecuadros-web (push) Successful in 1m41s Details Build and Push Images / Build jorgecuadros-api (push) Successful in 2m8s Details	2026-07-23 19:02:07 -07:00