The office books customers surname-first ("WAGONER, PAMELA") and carriers
print them given-name-first ("PAMELA DENISE WAGONER"), so the review screen
made staff retype a name the parser had already read. Comparing normalized
token sets makes the two orderings the same thing.
Only on the zero-hit path, where the policy number found nothing and a human
has to pick a customer anyway. The suggestions are written to a new
`customerSuggestions` column rather than `matchCandidates`, which the review
screen reads as policy-number hits, and they never set `matchedCustomerId` or
`confident` — matching on `Policy.policyNumber` is unchanged.
Two tiers, drawn where the real book has cliffs: EXACT (identical token sets)
and PARTIAL (containment, >=2 shared tokens, surname present). Of 1536
customers, 1487 have a distinct token set, so EXACT cross-person collisions
are ~0; loosen to surname + first given name and 131 (8.5%) collide, and 185
surnames are shared by 524 customers, which is why one token is never enough
and the surname must be printed explicitly. Replaying every book row as a
carrier would print it: 97.9% top-ranked correct, 1.2% a different row, all
but two of those the same human on a duplicate or variant row.
Normalization folds accents (OCR's MUNOZ reaches the book's MUÑOZ), drops
initials, Spanish particles, JR/S.A. DE C.V., and any token with a digit —
ANA prints the phone hard against the name as `Ph.3102001538`. Names over 8
tokens or 80 characters are refused outright, because GMX's especificación
has no field labels and the parser has handed its whole first page over as
`insuredName`.
Not used for utility statements: there the registrant genuinely is not the
customer, so the same trick would be wrong rather than noisy.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The parsers are written against `pdftotext -layout`, and every policy-ocr
fixture is a verbatim excerpt of it. The runtime does not use it: it reads
`-bbox-layout` and rebuilds the page from word boxes, and that rebuild
collapsed all white space — no blank lines between blocks, one space
between columns. White space is the only thing marking a cell boundary on
these borderless forms, so the fixtures could not see any of it.
What it cost, on the GMX PVL especificación and the ANA driver's policy:
- `espectBlock` walks a wrapped cell until a blank line. With no blank
line it ran to the end of the page, so the insured's name came back as
the entire first page of the specification.
- `INSURED\s{2,}` and its siblings matched nothing, and the phone that
shares the name cell rode along with it ("PAMELA DENISE WAGONER
Ph.3102001538"), which matches no customer.
- `parseAnaDriverCoverages` splits SUM INSURED from PREMIUM by the
header's own column offsets. Without offsets, every premium was filed
as a sum insured.
So `toVisualRows` now emits a blank line where the reader sees one (a
vertical gap over 1.6 line heights — the two populations measure 0.3-1.1
and 2.1+, so the threshold sits in empty space) and pads each word to its
own column, using one space wherever words merely follow each other so
rounding drift cannot sprinkle false cell boundaries through prose.
Two independent guards, so neither failure can come back silently: the
ANA phone splits on a single space, and the especificación's cell walk is
capped at the one wrap the longest cell on that document actually uses.
Verified against the real PDFs: the especificación reads "EMMER .
KATHLEEN" with all 18 coverages named (they were "(sin nombre)"), and the
ten born-digital gas invoices parse byte-identically to before. The
scanned statements are untouched — they come through tesseract, not this
path.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The BACKLOG claimed this was blocked on incomplete `policy_types` rows.
Querying the dev database says otherwise: AUTO (1316 policies) and LICENCIAS
(306) are both live and healthy, so ANA's two faces were never blocked at
all. Three separate things had been conflated.
What the parser now emits is a NAME, not an id -- it is a pure function over
text and must not reach for the database:
ANA AUTOMOBILE -> AUTO
ANA DRIVER'S POLICY -> LICENCIAS
GMX (both documents) -> MULT
`resolveLookups()` turns that into a foreign key at confirm, and does the
same for the carrier off the parser's provider code. It resolves, never
creates: a missing `policy_types` row means a human deleted it, and silently
recreating it would undo that with no record. An explicit `policyTypeId` /
`insuranceProviderId` on the confirm payload always wins.
GMX is MULT rather than INCENDIO because the caratula's own header reads
"Multiple Policy / Home" and the especificación is "PVL Hogar" -- one product,
two artifacts. MULT is the live row carrying 769 of them; INCENDIO is fire-only
and no policy in the book has ever used it.
The parser's provider code is not the carrier's row name, so PROVIDER_ROW_NAME
maps ANA onto "ANA SEGUROS", which is where the office's 738 ANA policies
already are.
--- the actual defect underneath -----------------------------------------
`policies.policyTypeId`, `policies.insuranceProviderId` and
`claims.adjusterId` are all ON DELETE SET NULL, and the lookups screen deleted
unconditionally. So deleting a lookup row returned 200 and silently blanked
the field on every row referencing it -- no error, nothing in the UI. That is
how M_EMPR disappeared and left 5 policies with no ramo, found months later
only by querying.
All three deletes now refuse while the row is in use, naming it and the count
("El tipo de póliza «M_EMPR» está en uso por 5 póliza(s)"). The schema-level
`onDelete: Restrict` the spec once recommended is deliberately not used: a raw
FK error is not something the operator can act on.
`20260815160000_policy_type_repair` cleans up what already happened:
- restores M_EMPR and re-points its 5 policies, scoped to
`policyTypeId IS NULL AND legacySourceTable = 'm_empr'` so it can never
claim a policy blanked for some other reason
- merges the duplicate "ANA" carrier (1 policy) into "ANA SEGUROS" (738).
OCR is about to start assigning the carrier automatically and two rows
would keep splitting the book. Written as joins, not subqueries, so both
statements are no-ops when either row is absent -- a subquery form would
resolve to NULL and blank the carrier off every ANA policy.
- does NOT restore INCENDIO. It is the other row the migration would have
produced, but the legacy INCENDIO table has 1 row that never loaded, so
the type has zero policies and restoring it would only put a dead option
in the type picker.
Verified by running the repair against the real broken dev data inside a
transaction and rolling back: 5 orphans -> 0, ANA/ANA SEGUROS -> one row with
739, and a second run in the same transaction changes nothing. The DDL half
matches `prisma migrate diff` exactly.
186 tests pass.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
`prisma migrate deploy` ran in one place only: a workflow step on the Gitea
runner, which has to reach the target host's MySQL on 3306 directly. Two
paths went around it:
- `skip_migrate=true`, the documented answer for when the runner cannot
reach 3306, left the schema a release behind with nothing to catch it.
The mismatch surfaced later as a column-not-found at runtime rather than
as a failed deploy.
- A container brought back by `restart: unless-stopped` after a host
reboot, or a stack re-applied by hand in Portainer, never runs the
workflow at all.
docker/api-entrypoint.sh becomes the api image's ENTRYPOINT: migrate, then
exec node. If the migration fails the container exits non-zero and the API
never listens — serving against a schema that does not match the code is
worse than being down, because the failures are partial and silent (a write
to a missing column breaks one feature while the rest looks healthy).
This does not replace the workflow step and is not a substitute for it. That
step still runs FIRST, while the old code is serving, which is the order
expand/contract migrations are designed around. `migrate deploy` is
idempotent, so on the normal path the container's run is a no-op query.
Behaviour:
RUN_MIGRATIONS=false skip and start anyway; plumbed through both app
stack files, for a schema moved by hand
DATABASE_URL unset refuse to start, and say why
P1001 (unreachable) retry, default 20 x 3s -- a cold db container, and
galactus's MagicDNS lookup right after a reboot
anything else exit at once; retrying a broken migration only
delays the same error. P3005 prints the
`migrate resolve --applied 0000_init` hint the
workflow step already printed.
Only P1001 retries, so a genuinely broken migration is not buried under a
minute of noise.
Both stacks are replicas: 1 and must stay so for an unrelated reason (the
servicios email sweep has no DB lock). The old comment claiming migrations
must not run per-container because "N replicas would race" is dropped: they
would not corrupt anything, since Prisma takes a database advisory lock and
the losers find nothing pending -- they would only each pay the wait.
The prisma CLI is already in the runtime layer (the image copies
/repo/node_modules wholesale), but which of the two plausible .bin paths
carries it is an implementation detail of pnpm's hoisted linker, so the
entrypoint accepts either and the Dockerfile asserts one exists at BUILD
time. A missing CLI breaks the image build, not a production boot.
Verified by running the entrypoint against stubbed prisma binaries: clean
run, P3005, P1001-to-exhaustion, P1001-then-recovery, RUN_MIGRATIONS=false,
missing DATABASE_URL, missing CLI.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
A.N.A. is the Rosarito office's tourist auto book and the second carrier
the policy OCR pipeline reads. It ships two unrelated faces, and the split
is different from GMX's: GMX ships two documents about one policy, A.N.A.
ships two products.
AUTOMOBILE (SPECIAL POLICY FOR TOURISTS) insures a car; vehicle table,
9 numbered sections, one
LIMIT OF LIABILITY column
DRIVER'S POLICY (the office: "licencia") insures up to 5 named drivers;
no vehicle at all, 6 unnumbered
sections in a different order,
SUM INSURED + PREMIUM columns
The four automobile products the office sells (amplia / responsabilidad
civil, annual / by-the-day) are the same layout with different numbers, so
they get one parser rather than four.
These are born-digital portal PDFs, so pdftotext -layout returns exact
columns and the driver's-policy parser uses that: its two value columns
print the same shape (100,000.00 usd. / 18.70 usd.) with no per-row label,
so horizontal position is the only thing separating them. The split comes
from the header's own offsets, not a constant, because they shift between
products; when it can't be read every amount is reported as a sum insured
and the reviewer is told, rather than half the premiums being filed as
coverage limits.
Three things the layout will punish a naive read for:
- Each PDF prints its face two or three times (ORIGINAL, AGENT COPY, then
a receipt and three travel cards) and the pipeline concatenates every
page before parsing. The coverage walk is bounded to the first copy and
the driver list to the first POLICY HOLDER block. Unbounded, the licencia
returns the same person three times, which reads as a three-driver policy
rather than as a bug.
- The money row is read positionally off its header. An unused DISCOUNT
prints as a bare "-", so "find the six amounts" shifts every value one
column left on a discounted policy.
- Two five-digit numbers sit in the header band and only one is the agent
clave; the agent's street address is "BENITO JUAREZ 25 No.50 INT 38",
three lines above the No. cell holding the policy number.
Sections 6-8 print a PREMIUM where the others print a limit, so
ParsedCoverage gains an optional `premium` (GMX never fills it) and the
review table a column: $40 is what legal aid cost, not a $40 liability
limit. Exclusions follow the GMX rule and go in the risk label with a null
amount -- which matters more here, since a responsabilidad-civil policy
prints 0.00 for material damage and the two are identical on the page.
Also in this change:
- coveragePeriodDays is parsed and written. A.N.A. sells 3- and 4-day
policies; Policy.coveragePeriodDays defaults to 365, so a weekend policy
left at the default sits in the renewals window a year out. Derived from
the dates, cross-checked against the printed DAYS cell, disagreement
noted not resolved.
- Vehicles and named drivers are parsed, shown read-only in review, and
written as Vehicle / InsuredDriver rows on confirm, skipping any already
on the policy (VIN then plate; licence then name). The case that forces
the skip is confirming a renewal onto an existing policy. Nothing is ever
updated or deleted -- a changed plate lands as a second row for a human.
- Batch.provider is set from what the parsers actually claimed instead of
being hardcoded "GMX", so a mixed upload is labelled as mixed and the
header can never contradict its own documents. PolicyDocument.documentType
follows the same rule (was hardcoded GMX_POLICY).
- matchNote becomes TEXT. It was VARCHAR(191) and the note trail was sliced
to 190 chars, which cut the tail notes -- the "could not read X" ones.
- The policy detail page renders an array coveragesJson as a table. Both
shapes have always been possible there, but the object renderer was the
only one, so an OCR-confirmed policy showed a row per array index labelled
"0", "1", "2" with [object Object] as the value. ANA makes that routine.
GMX is untouched behaviourally; its two parsers now spread a shared empty
base instead of listing every null field. 29 new parser cases against
verbatim pdftotext output of three real ANA PDFs, 53 in the suite.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Confirmed with Luz, who handles GMX policies at the office: the three
fields the especificación does not carry are entered manually. The review
screen already supports it — all three are editable and `postPremium`
enables off the typed premium, so no code change was needed.
The parser's note said "esos datos están en la carátula de la póliza",
which now sends the reviewer looking for the wrong document. It says
"captúrelos a mano" instead, and names the consequence of leaving the
vigencia blank: `Policy.policyTo` is nullable and the renewals window
filters `policyTo: { gte, lte }`, so a policy confirmed without one never
matches and never gets a renewal notice — silently, permanently, with
nothing downstream erroring.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
GMX ships two unrelated documents for the same policy and the office
downloads both from the same portal. The parser only knew the English
caratula, so a `…-CondicionesParticulares.pdf` parsed to an almost
entirely empty row — including the policy number, which the matcher needs.
`parseGmx` becomes a dispatcher over `parseGmxCaratula` (unchanged
behaviour) and the new `parseGmxEspecificacion`. Both still report
`provider: "GMX"`: the matcher keys on the policy number alone and must
not care which artifact was uploaded.
The especificación has no tables. Coverages are found by anchoring on
`Límite … Responsabilidad:` and walking backwards for the heading, where a
heading is a short line *preceded by a blank line* — length alone cannot
tell one from the wrapped tail of the paragraph above it, and without that
condition coverages get named after the last word of the preceding prose.
Also fixed, both pre-existing:
- The policy number's group widths are not the same across the two
families (`007-037-…-0000-02` vs `07-037-…-00000-01`). The pinned-width
regex is replaced by a shape, so both read.
- The caratula's ZIP fallback pushed a note saying it had read the ZIP
from the address, then never assigned it.
Verified against the full ten-page real document: all 17 coverages,
amounts, deductibles and the excluded earthquake section match what is
printed. 24 parser tests (was 8), four of them regressions for ways this
layout can silently attach the *wrong* value rather than none.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Two leftovers from making the browser derive the API origin. The stack env still
injected API_ORIGIN from a repo secret, which pinned the origin again on every
deploy and would have re-broken an https front door with mixed active content.
Drop it from both env_data blocks; the secret stays, now purely as the URL the
verify step probes.
That verify step was also about to break on its own: WEB_ORIGIN is a
comma-separated CORS list now, and `curl "$WEB_ORIGIN/version"` on a list
retries thirty times and fails a deploy whose app is perfectly healthy. Probe
the first entry, so keep the runner-reachable origin first in the secret.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The swarm stack still hard-failed on an unset API_ORIGIN, and both the env
template and the README told the reader to pin it — the exact habit the derived
origin was meant to end. Make it an optional override everywhere, and say that
WEB_ORIGIN is now a list.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The browser hard-required API_ORIGIN, so every move of the server — tailnet
today, the 192.168.1.0 office LAN later, a temporary demo domain in between —
meant editing the deploy env and redeploying. Worse, an http:// API origin on a
page served over TLS is blocked outright as mixed active content, which is what
broke the demo on https://jorgecuadros.freakma.com.
The browser now derives the origin from window.location the way a PHP app
would: same host on port 3001 over plain HTTP, or the same-origin /api path
under https (the reverse proxy strips the prefix). API_ORIGIN survives as an
optional override for a deployment that genuinely splits the two hosts, and SSR
still reads process.env because a derived origin is browser-only.
WEB_ORIGIN becomes a comma-separated list to match: one deployment is now
reached under several origins, and a credentialed fetch from an unlisted one
gets no CORS headers and fails.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Every field the replication card showed was self-reported by the replica, and
the two most reassuring ones lie in the same failure. Seconds_Behind_Source
reads 0 when the I/O thread is disconnected — with no incoming event there is
nothing to measure staleness against — and Replica_IO_Running only says the
network thread is alive, not that it is receiving.
Two checks that ask the master instead:
- GTID drift, folded into the polled status. GTID_SUBTRACT(master, replica)
counts transactions the master executed that the replica has not, so a silent
disconnect shows up as a number that climbs instead of a lag that stays 0.
It also isolates transactions carried under the replica's OWN server UUID —
writes that exist nowhere on the master. There are currently 518 of them,
residue of the seed dump load; inert while log_replica_updates is off, and a
real divergence the day anyone promotes that box.
- A full row-by-row comparison behind a button, over the eight tables
my.jorgecuadros.com reads. GTIDs prove the replica applied everything the
master sent; they say nothing about rows changed here by another route, which
is the one failure the rest of the card cannot see.
The comparison hashes CONVERT(col USING binary), not CAST(col AS CHAR). CAST
transcodes into the connection character set, and the two servers do not agree
on it: the client inside the master's container negotiates latin1, the replica's
utf8mb4. Every accented character in a Mexican name, street or note then hashes
differently and the tool reports a permanent mismatch on exactly the tables that
hold free text. Caught by building it and running it — customers.name gave
3344437324815 against 3339150372121 under CAST, and 3339150372121 on both under
CONVERT. All eight tables now match byte for byte.
Verify is POST and audited despite reading nothing: it full-scans both servers,
so a prefetch or a refresh must not be able to start one.
Tests cover the GTID interval arithmetic, which is inclusive at both ends and
easy to get wrong by one in the direction that hides a gap.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
A full run_all.py pass truncates and rebuilds every table it owns from the
Access extract. That was harmless while the platform was a read-only mirror --
every row came from the extract, so wiping and rebuilding lost nothing. It
stopped being harmless once the platform started minting rows Access has never
heard of: allocated portal NUMids, customers created in the staff UI,
OCR-captured policies, app-booked ledger rows, uploaded documents.
REIMPORT is a button in /operaciones, so that was one click away.
native_guard.py counts what only exists here and exits 3; run_all.py runs it
before the first truncate and stops. Detecting an allocated NUMid needs the
staged Parquet -- the customer holds an ordinary-looking (utilities, DATGRAL,
'1172') ref, so "customer has no refs" cannot see it and only comparing against
the extract can. Missing staging is therefore treated as blocking rather than
as "nothing to protect".
The guard does not teach full mode to preserve anything: --sync already upserts
legacy rows against the existing refs and leaves the rest alone, and rebuilding
that inside full mode would re-implement it. --force-full (checkbox in the
REIMPORT confirm, recorded in the audit log) deletes them deliberately.
Verified against dev: clean before, exit 3 listing utilities/1172 with a
synthetic ref present, clean again after removing it.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Customers created in the staff UI had no NUMid and so could not log in to
my.jorgecuadros.com at all: the id is a CustomerLegacyRef row, not a column,
and create() deliberately writes none.
Allocation is a staff action (POST /customers/:id/portal-access, MANAGER)
rather than part of create, because insurance is expected to move to the
platform before utilities and an insurance-only customer has no reason to
spend a utilities id.
The audit that decides which ids are reusable took three passes. "Owns no
rows" matches nobody -- migration gave all 1,171 NUMids a property and a
transaction. "No transaction in N years" also matches nobody -- every customer
carries a synthetic Jan-1 opening-balance row, so everyone looks active this
year. Subtracting that row is what makes dormancy measurable, and it leaves 4
never-used ids and 10 dormant ones on dev. Two further traps are encoded in the
queries: insurance/DATGRAL is a separate id space that reuses the sourceTable
name and runs past 4,000, and ACCOUNT CANCELED is a transaction line type, not
an account state -- all 8 customers carrying it have current-year activity.
Recycling ships switched off (numid.recycleEmpty, default false). Every
reusable id still exists in Access DATGRAL, and a --sync run reassigns refs
with ON DUPLICATE KEY UPDATE customerId, so an id recycled before the utilities
cutover is silently handed back to its Access owner.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
BALANCE FORWARD rows are not movements. Access materialized one per
customer per year, dated Jan 1, holding the closing balance of everything
before it — that is what let the portal keep each year in its own table and
still show a correct running balance from one year's rows.
The platform imported those rows AND the real pre-cutover history they
summarize, and every balance aggregate summed the lot. NUMid 501 read
-10,469.29 on the receivables worklist against -14,065.29 on the customer's
own statement and on the legacy portal; the gap was two cash receipts from
2009 and 2012 that the 2026 opening balance had already absorbed.
The scale settles what it is: summed the old way the whole book came to
+20,605,447.86 MXN — the office owing its customers 20.6 million pesos.
Floored, it is -56,855.90. A receivables ledger cannot be 20M in credit.
Adds BALANCE_FLOOR_JOIN + NOT_SUPERSEDED and applies them to balances()
(page and count queries, which must agree), to stats()'s per-currency and
per-domain figures, and to the owing/in-credit split. The four stats()
aggregates moved from Prisma groupBy to raw SQL because groupBy cannot
express a per-customer floor.
statement() takes the same floor as a scalar, which is also what stops
FEE ANUAL and fee15 leaking in. Those are not in
STATEMENT_EXCLUDED_SOURCE_TABLES — that list reproduces legacy's
DATOS2-only `datosfreak` — and they were putting 2,092 pre-cutover fee rows
across 1,062 customers into the statement, skewing it by -5,129,764 against
the number those customers have been quoted for years. Dating rather than
source is the right test: a fee row *after* the opening balance is a real
charge and still counts.
movements() is deliberately left alone. It is a browser over captured rows
— "how much water did we capture in April" — and staff need the historical
rows visible, so it keeps totalling everything, the same asymmetry
NOT_OUTSTANDING already has.
stats() now separates the two questions it was mixing: movements,
ledgerCustomers, crossLineCustomers and the date range stay unfloored
inventory; everything under byCurrency/byDomain is a balance and is floored.
BillingService had no tests. Adds 13 covering the floor's failure modes —
it fails silently, so MIN-vs-MAX, `>` vs `>=`, the NULL branch for customers
with no opening balance, and the join/predicate alias pairing are each
pinned, plus the 501 arithmetic as a regression.
Verified through the real service against the live ledger: balances() and
statement() both return -14,065.29 for 501, matching the portal.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
datosfreak's NOPAGO is the legacy "still owed" flag, and the website reads
it directly — account.statement.php splits the statement on NOPAGO = 0 vs
NOPAGO = 1 and renders the latter as "Outstanding Bills Requiring
Attention". transform_transactions.py hardcoded 0, so all 40,421 rows came
across settled and that section renders empty for anyone served off the
platform. Not a missing column: a missing section, with no error.
Only the three DATOS2-shaped tables carry the flag (76 rows set in datos2,
0 in FEE ANUAL and fee15); the EFECTIVO/FM3 cash streams have no such
column and keep the 0 default. Sync mode gets outstanding=VALUES(...) too,
so an additive sync corrects rows already loaded rather than leaving them
settled forever.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The deploy was a job inside build.yml gated by
`if: startsWith(github.ref, 'refs/tags/v')`. Gitea draws every job into the
run graph before it evaluates that `if`, so an ordinary push to master showed
a pending "Deploy to galactus" — indistinguishable from prod being about to be
redeployed off an unreleased commit, and the only safe reaction is to cancel
the run, which takes the images down with it.
The gate itself was never wrong (no deploy-galactus run has ever been created
from a branch ref), but a guarantee you cannot see is not much of a guarantee.
`on: push: tags: ["v*"]` in a workflow of its own makes it structural: the
deploy cannot appear on a master build because the workflow does not exist
there.
It replaces `needs: build` by polling the Actions API for the build.yml run at
this tag and requiring it green, so both images are still known to be in the
registry before anything is pulled. AUTO_DEPLOY_GALACTUS still cuts the chain.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
SYNC ran `run_all.py --sync` without `--stage`, so it depended on staged
Parquet under migration/output. That directory is part of the image, not a
volume, so any redeploy wiped it and the job died on the first transform:
FileNotFoundError: '/repo/migration/output/stg_utilities/datgral.parquet'
Re-staging is also what makes the job's own label true — without it a sync
would replay whatever upload staged last, not the files currently in the
ingest folder.
Staging now counts as a numbered step when it runs, so the Operaciones
progress bar moves during the slowest phase instead of sitting empty.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Cutting a release then had one manual step left: watch build.yml and
dispatch "Deploy to galactus" by hand with the version. Chain it.
build.yml gains a `deploy` job, `needs: build` and gated on
refs/tags/v*, that dispatches deploy-galactus.yml against the tag with
tag=<version> scope=app bootstrap=false skip_migrate=false. `needs`
waits for both matrix legs, so api and web are both in the registry
before prod pulls either — deploy-galactus.yml only pulls, and a
half-pushed pair leaves prod running one new image and one old one.
A dispatch rather than a `workflow_run:` trigger (which Gitea has
supported since 1.24) because deploy-galactus.yml reads
github.event.inputs.* in ten places; under workflow_run all of them are
empty strings, so the deploy would run with no tag. The dispatch keeps
that workflow's contract intact and keeps it hand-runnable, which is
how rollbacks work.
The dispatch is confirmed the same way release.yml confirms the build
started: snapshot the existing deploy-galactus run ids first, then
require a new one to appear. An accepted dispatch that creates no run
is the failure mode that cost v1.0.3 its images, and a plain "is there
a deploy run" check would be satisfied by the previous release.
Kill switch: repo variable AUTO_DEPLOY_GALACTUS=false prints the manual
command instead of deploying. Needs the existing RELEASE_TOKEN secret;
preflight fails loudly and names the manual command if it is unset.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Seconds_Behind_Source cannot answer "is it moving?". While the SQL thread
works through one large transaction the lag counter holds still — often at
0 — even though the replica is not caught up. The relay backlog does move,
and it comes out of the SHOW REPLICA STATUS the panel already runs, so this
costs no extra query and no connection to the source.
Adds applyProgress(), which reads Source_Log_File / Read_Source_Log_Pos vs
Relay_Source_Log_File / Exec_Source_Log_Pos and reports the fetched-but-not-
applied byte delta plus a percentage. Both positions are source binlog
coordinates, so they are only comparable while the two threads are on the
same file; across files the delta is meaningless (positions restart at ~4 in
each new file) and is reported as null rather than as a huge negative number.
The percentage deliberately stops at 99.99 while any backlog remains —
binlog positions are large enough that a real backlog of a few KB rounds to
100% and would render a lagging replica as caught up.
Not folded into `healthy`: a non-zero backlog is the normal state of a
working replica between fetch and apply, so alarming on it would cry wolf.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
A REIMPORT takes ~110 seconds and, until now, showed only a scrolling log —
there was no way to tell "halfway" from "wedged", which mattered the day one
actually did wedge.
run_all.py emits "[paso i/N] name" before each step and the API derives
progress from the job log. Emitting the marker from the Python rather than
having the UI count STEPS itself means the step count is stated in exactly
one place; adding a step cannot desync the display. Progress is derived, not
stored, for the same reason: the log is already the record of what happened,
and a separate counter could contradict it, which is precisely the confusion
a progress display exists to remove.
While RUNNING, step i is IN PROGRESS rather than finished, so only i-1 count
as done. Counting i would show 100% while the final step was still working —
and the final step (blob_extract) is the slowest, so the bar would sit at
"100%" for the longest stretch of the job.
BACKUP and RESTORE are a single mysqldump with no steps and deliberately
render no bar; a fabricated percentage would be worse than none. The safety
backup that precedes a REIMPORT is likewise named explicitly instead of
showing 0%, which reads as stuck.
Pinned by job-progress.spec.ts, including the literal line run_all.py emits,
so a change to the Python format fails a test rather than silently blanking
the panel.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The panel reported "Error SQL: Replicate_Ignore_Server_Ids:" against a
replica that was healthy — both threads running, zero lag.
`\s` matches newlines in JavaScript, so `^\s*NAME:\s*(.*)$` let the `\s*`
after the colon walk past an EMPTY field's line break and capture the
following line. Last_SQL_Error is blank on a healthy replica and
Replicate_Ignore_Server_Ids happens to be printed immediately after it, so
the blank error field returned the next field's name as its value. Every
empty field was affected; the visible damage was that a healthy replica
rendered as broken, which is the worst direction for a health panel to fail.
Fixed with `[^\S\n]` — horizontal whitespace only — on both sides of the
field name.
Extracted as replicaField() and pinned by replication.spec.ts against the
verbatim output of the live replica, keeping the empty Last_SQL_Error
adjacent to Replicate_Ignore_Server_Ids because that exact adjacency is what
broke. Also covers the literal "NULL" lag surviving as a distinct value from
empty, and a field name that is a suffix of another (Last_Error vs
Last_SQL_Error) not matching the wrong line.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Ops jobs run as a child of the API process, so no job can outlive it. When a
deploy landed 110 seconds into a REIMPORT, the child died and nothing was
left to finalize the row — it stayed RUNNING forever. Because startJob()
refuses to start while any RUNNING row exists, that one interrupted job
wedged the panel permanently with no way out from the UI; recovering it took
a manual UPDATE against the production database.
A fresh boot is proof that nothing survived, so this is unconditional rather
than filtered on age: "started recently" does not imply "still alive" here.
Rows are updated one at a time rather than with updateMany so the reason can
be APPENDED to the log. A job whose log simply stops mid-step with no
explanation is what made the first occurrence hard to diagnose.
Failure to reconcile is logged and swallowed: a wedged panel is bad, an API
that will not boot is worse.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
my.jorgecuadros.com serves customer balances from the Oracle VPS replica.
A replica whose SQL thread has stopped does not error — it keeps answering,
with data frozen at the moment it stopped — so nothing on the customer site
looks wrong and the only signal is a customer complaining about a stale
balance. This puts the failure somewhere a human sees it.
Deliberately does not trust the two fields an operator reaches for first.
Replica_IO_Running reports Yes while the SQL thread is stopped, because the
network thread keeps downloading binlog it will never apply; verified by
stopping SQL_THREAD and watching IO stay Yes. Seconds_Behind_Source reads
NULL whenever EITHER thread is down, so the card renders "sin dato" rather
than "0 s" — showing zero there would report an outage as perfect health.
The problem string is resolved most-specific-first for the same reason.
Shells out to the mysql client because the API has no MySQL driver and the
image already ships one. --ssl is required (the replica sets
require_secure_transport); --ssl-verify-server-cert=0 is deliberate and is
NOT the trade-off the website makes: this hop never leaves Tailscale and the
replica's firewall admits only this host, so WireGuard authenticates the
peer, whereas the DreamHost leg crosses the public internet and pins the CA.
The account behind it holds REPLICATION CLIENT and nothing else — it cannot
read a single row. REPLICA_DB_* unset is a supported state and renders "no
configurada", which is correct in dev and before cutover.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The EFECTIVO ledgers have no type column — in Access the transaction type
is implied by which table a row lives in — so unlike DATOS2 there was no
string to map and typeId came out NULL on all 13,496 rows.
That is not just a blank label. handleGetAccountDetails in
my.jorgecuadros.com identifies payments by matching TYPEOFTRX against
('PAYMENT THANK YOU', 'PAYPAL', 'CASH DEPOSIT', 'CHECK DEPOSIT') to reset
the running balance in mode=current. An unlabelled payment is not
recognised, so the balance silently diverges from legacy — 285 rows across
129 customers in the current year alone.
"CASH DEPOSIT" is measured, not chosen: matching the unlabelled rows to the
live site on (NUMid, date, amount) resolves unanimously to that label —
66/66 in the current-year `datosfreak` and 100/100 in the prior-year `2025`
table, the only two periods the site allowlists.
The FM3 fee streams (EFECTIVO FM3 627, CHEQUE FM3 157) have the same
missing-type problem and are deliberately left NULL: every row predates both
exposed periods, so nothing can be matched against a legacy label and none
can reach a customer. Guessing "CHECK DEPOSIT" there would feed the
payment-detection list on no evidence.
type_id_for(None) returns None, so call sites without a label are unchanged.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
my.jorgecuadros.com reads customer data from the Oracle VPS replica, and a
replica that has silently stopped applying serves stale balances rather
than erroring — so "is it replicating" needed an answer that is not a
human squinting at SHOW REPLICA STATUS.
Runs entirely against the replica over ssh, so it needs no credentials for
the galactus master, and exits non-zero on failure so it can be driven from
cron or a monitor.
It deliberately does not trust the two fields an operator reaches for first.
Replica_IO_Running reports Yes while the SQL thread is stopped, because the
network thread is still downloading binlog it will never apply — verified by
stopping SQL_THREAD and watching IO stay Yes. Seconds_Behind_Source reads 0
both when there is nothing to apply and when nothing is connected. The
trustworthy signal is GTID_SUBTRACT(Retrieved, Executed): binlog fetched but
not applied.
NULL lag means either thread is down, so it is reported as "not applying"
rather than blamed on a specific thread — the thread fields above already
say which, and guessing there produced a wrong diagnosis.
Uses sed rather than `head -n1`; on this machine `head` resolves to LWP's
HTTP head(1), which mangles the pipeline instead of failing.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Two fields the customer-facing site reads were being dropped on the way in
from Access.
transform_transactions.py mapped DATOS2's type string through the Access
`TYPE OF TRX` table and stored NULL on a miss. That table is a stale
pick-list rather than a constraint — staff free-text straight into DATOS2 —
so 78 distinct values covering 3,939 rows never resolved, including
BALANCE FORWARD (1,188) and ANNUAL FEE (1,116). Nothing else on
`transactions` carries the type text, so those rows lost their label
outright and rendered blank. Now mints a type_transactions row from the
literal string when the lookup lacks it; nameEs stays NULL since only the
lookup has translations.
transform_customers.py never carried DATGRAL.TIPO, leaving
customers.minimumBalance empty on every row despite the column existing.
TIPO is the minimum-balance threshold (100/200/300/500; 1,017 of 1,172
customers carry one), not an account type as the name suggests — the
customer app shows it as `minBalance`. Added to the insert list and to the
ON DUPLICATE KEY UPDATE clause, without which --sync would silently skip
it on existing rows.
Both land on the next `run_all.py --sync` reload.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Five documents asserted the SES_* secrets were unset in Gitea. They now
exist, so all five are corrected rather than leaving the claim to rot in
whichever one a reader opens first.
Replaces the blocker with the two things creating the secrets does NOT
establish, since both fail in ways that look identical to a missing
config: SES_FROM must be a verified identity in SES_REGION, and the
account must be out of the SES sandbox — in sandbox SES only delivers to
verified recipients, so a sweep across 815 policyholders would fail
almost every send while the configuration reads as correct.
Recommends running the first sweep with debug on, which diverts every
recipient and, on the pólizas side, leaves the avisos pending so a failed
test consumes nothing.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Open work was spread across six documents: PLAN's per-step status,
RESUME §6, two specs' collected open questions, and the "Not built"
sections of the two OCR docs. Nothing tracked the two live data defects
except a paragraph inside INSURANCE_FEATURES_SPEC, and nothing at all
recorded that master is 14 commits and 5 migrations past the last tag.
Compiled by reading those six, then checking each claim against the code
and the dev database rather than trusting the prose — which is how the
dead-table finding surfaced and how both insurance defects were confirmed
still open.
Leads with the ship blocker: SES_* is unset in Gitea while the pólizas
sweep defaults to enabled at 06:00, so deploying current master gives a
nightly sweep that fails every run. Set the secrets or disable the
schedule before cutting v1.0.7.
Findings not previously written down anywhere:
- policy_types still holds only AUTO/LICENCIAS/MULT and 5 policies still
have a NULL policyTypeId; policyTypeId is still `String?` with Prisma's
default SetNull, so the spec's recommended Restrict was never applied.
- EmailTemplate / EmailCampaign / EmailLog have zero references in
apps/api/src or apps/web/src. Scaffolded for step 10's "email
campaigns"; notificaciones shipped against email_notification_log
instead. Either wire them or drop them.
- Customer.customerNumber does not exist, so recycling is not merely
unbuilt but unstarted at the schema level.
Linked from PLAN.md and README so it is findable from either entry point.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Gives receipt capture the same treatment policy OCR just got: a doc that
records what is in the code, separate from the spec that records what was
designed. RECEIPT_CAPTURE_SPEC.md §2 had accumulated three BUILT notes
totalling ~120 lines of findings, which is the right place for the
evidence but the wrong place to look up how the matcher picks a column.
docs/STATEMENT_OCR.md covers the pipeline, the OCR seam and its
text-layer-first rule, all eight parsers and the ordering constraints
between them, the matcher's two governing rules and the scopedRefField
table, confirm-through-BillingService, the learning write-back, and the
API surface.
Weight goes to the things that are load-bearing and invisible from the
code shape: brand detection must run to completion before layout because
Tijuana bills predial and zona federal off the same treasury header;
scopedRefField is exported because three call sites must agree or a
reference gets learned into a column nothing searches; FEDERAL_ZONE's
accountNumber holds a peso amount, so it fails the null-guards as well
as the lookup; a misread `$` is the dangerous failure, not a missing one.
Also records that CFE/CESPT/Telnor have no unit suite — they predate the
gas/predial extension and were only verified end to end.
Cross-linked from the spec, POLICY_OCR.md, PLAN.md, README and RESUME.md.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Policy OCR shipped 2026-08-01 (5e9cb12) and was documented nowhere. It is
not in INSURANCE_FEATURES_SPEC.md because it did not come from that
meeting — it came out of building the utility statement OCR pipeline in
RECEIPT_CAPTURE_SPEC.md §2 and noticing the same shape fits carrier
policy PDFs. A reader had no way to find that lineage.
New docs/POLICY_OCR.md covers it end to end, with weight on the three
things that are not obvious from the statement side:
- **One PDF = one policy.** Statements arrive bundled one customer per
page, so there a page is a document. A GMX certificate is one policy
across two pages, so the pages are concatenated and the parser runs
once per file — which is why `pageNumber` is a file ordinal and
`storageKey` is the source PDF, not a page image.
- **The GMX certificate carries no premium at all** — it lives on a
separate recibo PDF. Hence the null-preserving confirm and the
double-gated ledger write.
- **OcrModule was extracted out of StatementsModule to make this
possible**, and that was blocking rather than cosmetic.
Cross-referenced from RECEIPT_CAPTURE_SPEC.md §2 (where it came from),
INSURANCE_FEATURES_SPEC.md (which never proposed it, and whose §4 carrier
API it partly overlaps), PLAN.md step 11, README and RESUME.md.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The docs still described the state before the last five commits: the
insurance spec called for a `@Cron` literal and a manual mark-as-sent
mutation, PLAN.md had step 12 as "NOT STARTED", and README's module and
route lists predated seven modules.
- MASS_EMAIL_NOTIFICATIONS.md: new "Send flags", "API surface" and
"Scheduled runs" sections; "Cron (future)" removed — it exists. The
flags table says which flags apply where, and why a debug renewal send
must skip both the RenewalNotice row and `lastSuccessfulAt`.
- INSURANCE_FEATURES_SPEC.md: §1 BUILT note listing the three places the
build diverged from the spec; §1.1 and §1.4 marked superseded in place
rather than deleted, so the reasoning stays readable.
- PLAN.md: step 12 renewal emails DONE with the divergences; status
paragraph rewritten.
- README.md: current module/route lists, plus a "Scheduled jobs" section —
a reader cloning this repo had no way to know the API sends mail on a
timer.
- DEPLOY_AND_MIGRATIONS.md: the cadence lives in app_settings and survives
an image rollback, and the servicios sweep has no multi-replica lock.
- RESUME.md: session record for the whole notificaciones arc.
- RENEWAL_NOTICES.md: pointer that this is the legacy record, not what
shipped.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The "Flags del envío" panel lived inside the Servicios tab and only
governed the four bulk jobs. The pólizas half had no debug at all, so
there was no way to test a renewal notice without mailing a real
customer. The panel now lives in the /notificaciones shell above the
tabs and both halves read it.
`debug` on the renewal path diverts to the same override inbox as the
servicios jobs and deliberately does NOT write the `RenewalNotice` row
or advance the sweep's `lastSuccessfulAt` — the customer was not
notified, so nothing may gate the letter they are still owed.
`ignoreDayRestriction` and `useEmailLimit` stay estado-de-cuenta-only
and are labelled as such.
Both automatic sweeps are now operator-editable. The renewal cadence
was a `@Cron("0 6 * * *")` literal and servicios had no automatic run
at all; both now resolve through `NotificationScheduleService`, which
stores the cadence in `app_settings` and reinstalls the cron job on
save — no redeploy, no restart. Defaults preserve current behaviour:
pólizas 06:00 daily, servicios off. A scheduled run never inherits the
UI flags; it always sends for real.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
NOTIFICATION_ADMIN_EMAILS made "add Beto to the summaries" a redeploy —
the wrong unit of work for a list that changes when office staff change.
Adds `app_settings`, a key/value table for the configuration staff must
be able to change without a deploy, and `SettingsService`, which resolves
every key db -> env -> default and reports which of the three a value
came from. That ladder is what makes the move safe: a deployment behaves
exactly as before until somebody saves in the UI, and the screen can say
"this is still coming from the deployment" rather than implying somebody
chose it.
- new ability `setting:manage` (ADMIN) — deliberately above
`notification:send`, since redirecting the audit summaries is how
someone would quietly stop them being read
- GET/PUT /notifications/settings/admin-emails; read is open to any
logged-in user so the UI can display the list, write is gated
- resolved per job, not cached at boot, or we would reintroduce exactly
the restart-to-apply behaviour being removed
- a saved empty list means "nobody" and does NOT fall through to the env,
or clearing the field would keep mailing the people just removed
Credentials stay in env — see the model doc for where the line is drawn.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
The stack env is assembled from Gitea repo secrets by the deploy
workflows' `env_data` block — there is no .env file on the host for the
app stack. SES was in neither, so `MailService` came up unconfigured on
every deployment and, with NODE_ENV=production killing the stdout dev
fallback, every notification and renewal aviso failed.
Wire SES_REGION / SES_FROM / SES_FROM_NAME / SES_ACCESS_KEY /
SES_SECRET_KEY / SES_CONFIGURATION_SET / NOTIFICATION_ADMIN_EMAILS
through both galactus and cubex. No `_GALACTUS` suffix: one SES identity
serves every deployment.
Kept out of the required-secrets preflight — mail is not needed to boot,
and failing a deploy over it would be wrong. Preflight warns instead,
since the failure is otherwise invisible until someone clicks "Ejecutar".
Also corrects the comments added in the previous commit, which claimed
these belonged in a host env file rather than in CI secrets.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Renewal avisos left behind only a `RenewalNotice` row, whose sole job is
gating: a row with `sentAt` drops the policy off the pending list. It
cannot represent a failed send or a customer with no address, so the
Pólizas tab had no "Registro de envíos" to show and a sent notice simply
vanished from the list.
Renewals now write `email_notification_log` — the same table the four
bulk jobs write — as `RENEWAL_NOTICE` / `POLICIES`, with rows for
failures and no-email skips too. `RenewalNotice` keeps its gating role
unchanged; the two are complementary, not redundant.
- extend `EmailNotificationType` (+RENEWAL_NOTICE) and
`EmailNotificationServicio` (+POLICIES); `level` now carries the aviso
generation on renewal rows, so every reader must branch on the type
first (`notificationLevelLabel()` is the one place that lives)
- backfill emailed notices (`channel = 'EMAIL'`) into the log; MAIL-channel
rows are legacy printed letters and are deliberately left out
- extract `NotificationLogService`/`NotificationLogModule` as the single
writer, so a feature that sends mail records it without pulling the
bulk-job pipelines into its module
- `GET /notifications/log` and `/stats` take a comma-separated `servicio`
list; each tab reads its own slice. This also fixes the "Omitidos"
view, which mapped to no filter at all and showed every row
- share one `NotificationLogPanel` between both tabs
- pass SES_* / NOTIFICATION_ADMIN_EMAILS through the galactus compose,
which was missing them entirely — mail is runtime config, not a CI
secret, and the prod image sets NODE_ENV=production so a blank config
fails loudly instead of falling back to stdout
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
gitea-actions
rmancinasandClaude Opus 5