Lien waivers, notices to owner, and a spreadsheet that ran on cell colors

On a construction job, the subcontractors and suppliers who could file a lien send a notice to owner. Before the general contractor pays one of them for a draw, it needs a signed lien waiver back. Miss one and the owner can end up paying for the same work twice. At a commercial real estate developer's construction arm, that whole process lived in one legacy Excel workbook per job and in the head of the accounts payable lead.

When we opened the workbook, the real problem was in the formatting. Status was encoded as a letter plus a cell fill color, and the same letter meant two different things depending on whether the cell was orange or yellow. A blank cell shaded mint green meant "release due." That was the single most important state in the sheet, the one that should trigger follow-up, and it was invisible to anything but a human eye. Seven states, two dimensions, no documentation.

The paper arrived as photos and scans: notices from lienors, the recorded notice of commencement for each job, signed waivers coming back. Someone retyped all of it.

What we built

A Next.js app on Supabase and Vercel that replaces the workbook.

• Document intake by photo or upload. Claude reads notices to owner, recorded notices of commencement, and signed waivers. Extraction runs through a tool schema, so the model cannot return prose where a date belongs.
• Matching against the recorded notice. Each notice is checked against the job's notice of commencement. Parcel number, property address, and owner are hard fails. Contractor name, owner address, legal description, and lender are soft flags. A transposition-aware string distance plus company-suffix normalization tells a typo apart from a different company.
• A draw grid that looks like the old sheet. The seven states are now real stored states, but the grid renders the original colors so nobody has to relearn anything. The Excel export reproduces the original layout cell for cell.
• Waiver generation from the firm's own templates, notary block intact, as a Word file.
• A contractor database that imports from the accounting system's CSV export and handles the real duplicate cases: one supplier filing three notices, trade names, lower-tier companies working under someone else.

The first version was built and seeded with a real job the same day as discovery. On the first test, the model read a recorded notice of commencement (a scan with no text layer), got every field right, and transcribed a misspelling of the contractor's own name exactly as printed while flagging it as low confidence. That is the designed behavior. A system that reads legal documents should never quietly correct them.

Then we rebuilt it around how AP actually works

A good first version is still a draft. The real spec came from a recorded walkthrough in which the AP lead went step by step through the job. The biggest change: follow-up starts at payment, not at the notice.

Tiers, read from the sheet's fonts. On the AP lead's sheet, a direct subcontractor is bold and anyone working under that sub is italic beneath it. Every row now carries a tier and a parent, set automatically from the "under an order given by" line on the notice and overridable with a click. To seed existing jobs, we read tiers from the workbook's actual font flags rather than its indentation. That caught a supplier that was italic but never indented, which belonged under a different sub than its position suggested.

Notice of commencement first, then the job. Starting a job means dropping in the recorded notice, which fills the job in.

A closest-match job picker. A notice with one detail off now shows ranked candidate jobs with the reasons for each (matching parcel, matching owner, same plat book) instead of one silent guess.

Payments generate waivers. AP records a payment per sub per draw by scanning the check, scanning the printed virtual check it already cuts for ACH, or typing it in. The waiver generates from the payment and goes out with the check copy attached, the way AP did it by hand.

No waiver, no payment, enforced by the server. The rule as the AP lead put it: "You can't pay them until they have the waiver back." Marking a payment paid before its signed waiver returns is refused by the API, not just hidden in the interface. When the signed copy comes back, it is scanned, matched to the waiver that went out, and logging it fills the grid and releases the payment in one step.

Insurance the way AP tracks it. A certificate of insurance on its own carries no weight. The app tracks the certificate, the additional insured endorsement, and the waiver of subrogation where the contract requires it, per direct sub. A lapsed policy triggers a request for a renewed endorsement too, because endorsements belong to a policy term.

Skipped on purpose. An AI chat about "how the job is going" was on the early list. The AP lead pointed out that the app tracks paperwork, not job progress, and it was dropped. The best scope decisions in this build came from the person who does the work.

Small things that mattered

• Invite only, enforced in the database. No invite row, no account. There is no service role key anywhere in the project, so nothing can bypass row-level security. We checked it by pulling the public key out of the browser bundle and confirming every table came back empty.
• Two people, two cells, no overwrites. Saves write only the rows that changed, so two people editing different cells never clobber each other.
• Errors in plain words. If document reading is down, staff see that the item needs to be typed in by hand and who to tell, not an API error code.
• Questions surfaced, not decided. Discovery turned up discrepancies in the firm's own paperwork, including two different company addresses across the recorded notice and the waiver templates. Each one is shown in the app for a person to settle, because each one ends up printed on a legal document.
• The legend follows reality. The AP lead later clarified that yellow means a signed partial release was received, not sent to the owner. The legend changed. The stored state did not, so no existing data moved.

What changed

Document reading is confirmed working in production. Notices, notices of commencement, and signed waivers come in as photos instead of retyped rows, and the workbook's color code is now data the app can act on. The release-due state that used to be a mint-green blank is a trigger. The rule that a sub does not get paid without a waiver is enforced by the server instead of by memory.

What other operators can take from this

• Decode the spreadsheet before you replace it. The business logic is often hiding in the formatting.
• Keep the familiar view. The grid looks like the old sheet on purpose, so nobody has to relearn the job to use the new tool.
• Record a walkthrough with the person who does the job. It is a better spec than any requirements document.
• Put the rules that protect money in the server. An interface can be worked around. An API that refuses cannot.

If your AP team is still tracking lien releases in a spreadsheet, book a Systems Audit.