UCC Lien Lapse Leads: Refinance Window + Secured-Party Rollup

UCC lapse leads for equipment-finance, MCA, factoring and working-capital brokers. A UCC-1 financing statement lapses five years after filing unless the lender files a continuation. The 90 days before that lapse is the refinance / re-solicit window: the borrower's existing equipment loan, lease or line is maturing, the incumbent lender either continues the lien or lets it go, and a competing lender who reaches the business first gets the renewal. Every UCC product on the Store today is a debtor search or a new-filings dump; this actor is the only one that sells the date-driven trigger -- and it computes it correctly.

Sources are the official state open-data portals (Socrata, keyless, no anti-bot): Colorado (filing, debtor, secured-party and collateral tables), Connecticut (UCC Lien Filings) and Oregon (UCC Secured Parties List). Price: $0.02 per row ($20 / 1,000) at the free tier, tiered discounts on paid plans. Default prefill (CO, 90 days, 40 rows) costs $0.80.

Why the obvious query is wrong, and what this actor does instead

On every one of these portals the initial-filing row is never updated when the lien is terminated. On Colorado's wffy-3uut the UCC-1 row keeps terminationflag=false forever; only the later "UCC amendment-termination" row carries true. Connecticut's lien_status still reads "Active" on a lien with a TERMINATION row. Measured on the first 16 Colorado financing statements lapsing in the 90 days from 2026-09-26, seven carried a termination row -- roughly 44% of the naive candidate set are liens that were already paid off. A lender calling those "leads" is calling businesses that already refinanced.

So this actor:

1. Queries every transaction whose current lapse date falls in the window (the lapse date is propagated to every row of the statement when a continuation is filed, which is what makes the windowed read whole),
2. Re-reads each financing statement in full (initial filing, every amendment, every continuation, any termination) before judging it,
3. Excludes any statement with a terminating row, and any whose lapse has been extended past the window, and reports how many it excluded on every row (state_terminated_excluded, state_lapse_extended_excluded),
4. Joins the current debtor and secured party of record (an assignment or name change on an amendment supersedes the original -- SunTrust's lien reads Truist), the collateral description where the portal publishes one, and the Colorado SOS entity id of an organisation debtor,
5. Stamps a per-state outcome on every row, so a state that did not answer can never look like a state with no lapsing liens.

Modes

lapse-window (prefill) -- one row per UCC-1 financing statement whose current lapse date falls between the window start (today, or sinceDate) and start + windowDays. Sorted soonest-lapse first. Filter by securedPartyQuery (the incumbent lender), debtorCity, or county where published.

secured-party-rollup -- initial UCC-1 filings per secured party x calendar quarter over the trailing windowDays (or since sinceDate), with each lender's share of the state's filings and rank in the quarter. Who is lending equipment money in Colorado this quarter, and how much market are they taking. Sorted largest lender first.

State coverage (stamped on every row as state_coverage)

State	Coverage	Source	What you get	Refresh
CO	full	data.colorado.gov wffy-3uut (2.6M filing rows) + 8upq-58vz debtors + ap62-sav4 secured parties + 4am6-w6u4 collateral	debtor (+ SOS entity id, org type), secured party, collateral text and categories, continuations, termination check	daily
CT	full	data.ct.gov xfev-8smz UCC Lien Filings (848k rows; OFS / OFS - LEASE filings only, statutory tax liens excluded)	debtor, secured party, continuations, termination check	daily
OR	partial	data.oregon.gov 2kf7-i54h UCC Secured Parties List (220k rows)	lapse date, secured party, link to the filing image; no debtor table is published, so debtor fields are null with debtor_basis = not_published_by_source	monthly snapshot of active liens
NY, PA, DE, TX, WA	not_available	discovery API checked 2026-09-26	requested explicitly, the state is reported on the run as not_available with the reason -- never an empty success	--

Nothing here is a county-level filing record: none of the three portals publishes a county on the filing, so county is null with a stated basis (Colorado carries one only on farm-product collateral records). Use debtorCity.

Example input

{
  "states": ["CO"],
  "mode": "lapse-window",
  "windowDays": 90,
  "maxResults": 40
}

Equipment-finance broker: liens held by a competitor lapsing next quarter -- {"states":["CO","CT"],"mode":"lapse-window","windowDays":120,"securedPartyQuery":"Kubota Credit","maxResults":500}. Market read: {"states":["CT"],"mode":"secured-party-rollup","windowDays":180,"maxResults":100}. Lapses in a future quarter: {"states":["CO"],"sinceDate":"2027-01-01","windowDays":90}.

Honesty contract

• A state that cannot answer is disclosed, never silently dropped. Every row carries run_states_requested / run_states_ok / run_states_failed / run_states_failed_detail and run_complete. If no requested state can answer, the run fails and bills nothing.
• A live drift gate runs per state before any billable row -- row-count floor, freshness, required columns, closed vocabulary, a pinned positive canary (a known 2011 Colorado financing statement must reproduce), a negative control (an impossible id must return 0 rows), and a check that the lapse filter is actually being applied. A portal that is merely unreachable is reported as unavailable; only a parsed wrong answer is drift. Either excludes the state.
• null means not published or not checked; false and 0 mean checked. Oregon's continuation_count and state_terminated_excluded are null because Oregon publishes neither a document type nor a termination flag.
• Every _basis column says where a value came from (active records, latest filing row, fallback to inactive records, not published).
• A filter the state's data cannot express is a refusal for that state, reported on the run -- an Oregon leg with debtorCity does not silently return every Oregon lien.
• sinceDate must be YYYY-MM-DD; slash and month-name forms are rejected without billing rather than guessed.
• The run cap maxResults is global across states; when more matched, every row carries result_truncated = true.

Output fields

Field	Meaning
record_kind	lapse_lead (one UCC-1 financing statement lapsing in the window) or secured_party_rollup (one lender x quarter cell).
state	Two-letter state whose registry produced the row.
state_coverage	full = debtor + secured party + lapse + termination check; partial = lapse + secured party only (OR publishes no debtor table).
coverage_note	Why coverage is partial, when it is; null on full-coverage states.
master_document_id	The master document / lien number every transaction on the statement shares (CO masterdocumentid, CT id_lien_flng_nbr, OR base file number).
lapse_date	CURRENT lapse date of the financing statement (YYYY-MM-DD) -- the max across every transaction on it, so a continuation is honoured.
days_to_lapse	Calendar days from the run date to lapse_date. Negative only when sinceDate placed the window in the past.
lapse_window_start	First day of the requested lapse window.
lapse_window_end	Last day of the requested lapse window.
initial_filing_date	Date the original UCC-1 was filed.
initial_filing_date_basis	initial_filing_row, or earliest_transaction_row_no_initial_row_published when the portal returned no initial-filing row for the statement.
years_since_initial_filing	Years from initial_filing_date to the run date, one decimal.
continuation_count	Number of continuation statements filed on the master document. null on OR (document type not published).
last_continuation_date	Date of the most recent continuation, if any.
amendment_count	Non-continuation, non-termination amendments on the statement (assignments, party changes).
last_amendment_date	Date of the most recent amendment, if any.
transaction_count	Total filing rows on the statement that were checked.
termination_check	How termination was ruled out: all_transactions_on_master_document_checked (CO), all_filing_rows_on_lien_checked (CT), or not_published_by_source_list_holds_active_liens_only (OR).
lapse_date_consistent	true when every transaction row on the statement carried the same lapse date; false when they disagreed (max was used).
transaction_ids	Comma-separated portal ids of every transaction row on the statement (CO fileid, CT id_ucc_flng_nbr, OR file numbers).
filing_record_url	Link to the filing image at the state registry (OR only).
debtor_name	Primary debtor (organisation name, or "Last, First").
debtor_type	organization or individual.
debtor_names_all	Every distinct debtor name on the statement, pipe-separated.
debtor_count	Distinct debtors; null when none published.
debtor_address	Primary debtor street address.
debtor_city	Primary debtor city.
debtor_state	Primary debtor state.
debtor_zip	Primary debtor ZIP.
debtor_sos_entity_id	CO Secretary of State entity id of an organisation debtor -- joins to sos-registry-monitor / kyb-company-verifier. null elsewhere.
debtor_organization_type	Organisation type as published (CO).
debtor_jurisdiction	Organisation jurisdiction as published (CO).
debtor_basis	active_records, no_active_record_fallback_to_inactive, latest_filing_row_naming_a_debtor (CT), none_published, or not_published_by_source (OR).
secured_party_name	Primary secured party (lender) currently of record.
secured_party_names_all	Every distinct secured-party name of record, pipe-separated.
secured_party_count	Distinct secured parties; null when none published.
secured_party_address	Street address of the primary secured party.
secured_party_city	City of the primary secured party.
secured_party_state	State of the primary secured party.
secured_party_zip	ZIP of the primary secured party.
secured_party_basis	active_records, no_active_record_fallback_to_inactive, latest_filing_row (CT), latest_secured_party_row (OR), or none_published.
collateral_summary	Collateral description text as filed (CO collateral table), trimmed to 400 chars; null where the portal publishes none.
collateral_categories	Distinct collateral category codes as published (CO), excluding the literal UNKNOWN placeholder.
collateral_is_farm_product	true when any collateral record is flagged as farm products (CO).
collateral_basis	active_collateral_records, no_active_record_fallback_to_inactive, none_published, or not_published_by_source.
county	County where published. Only CO farm-product collateral records carry one; null otherwise.
county_basis	collateral_record, collateral_record_only, not_published_on_filing_record, or not_published_by_source.
secured_party_name_key	Normalised (upper-case, punctuation-stripped) name used to fold spellings together.
quarter	Calendar quarter of the initial filing, e.g. 2026-Q3.
initial_filings	Initial UCC-1 filings naming this secured party in the quarter, inside the window.
state_initial_filings_in_quarter	Denominator: all initial UCC-1 filings in the state in that quarter inside the window.
share_of_state_filings_pct	initial_filings / state_initial_filings_in_quarter x 100, two decimals.
rank_in_quarter	Rank of this secured party by filings within the quarter, over every lender (not just the emitted rows).
filing_window_start	First accepted date counted.
filing_window_end	Last accepted date counted.
rollup_basis	Exactly what one count means for this state, in words.
result_truncated	true when more rows matched than maxResults allowed.
state_candidates_judged	Financing statements in the window that were fully read and judged for this state before the cap was reached.
state_terminated_excluded	Statements in the window EXCLUDED because a termination row exists (the naive query would have sold them as leads). null on OR.
state_lapse_extended_excluded	Statements excluded because their current lapse date, read across all rows, falls after the window.
state_filtered_out	Leads dropped by securedPartyQuery / debtorCity / county.
state_scan_truncated	true if the candidate scan hit its page ceiling before the window was exhausted.
state_filings_in_window	Initial UCC-1 filings counted for the state.
state_filings_without_secured_party	CO filings whose secured-party record was not published (denominator only). null elsewhere.
data_as_of	Newest filing date the portal served at run time.
run_mode	lapse-window or secured-party-rollup.
run_states_requested	Comma-separated states asked for.
run_states_ok	States that passed the live gate and answered.
run_states_failed	States that did not answer, drifted, refused a filter, or publish no UCC dataset.
run_states_failed_detail	Per-state reason, including state_coverage=not_available disclosures.
run_complete	true only when every requested state answered.
run_window_start	Window start used by the run.
run_window_end	Window end used by the run.
drift_gate_status	Per answering state: verified or verified_degraded (a corroborating probe could not complete).

Pricing and cost

$0.02 per row at the free tier ($20 / 1,000), Bronze -20%, Silver -30%, Gold -45%, Platinum -60%, Diamond -70%. A lapse-window run bills one row per financing statement emitted; a rollup bills one row per lender x quarter. A run that fails its gate, or is refused on input, bills nothing. Compare a $250-415 commercial UCC search report or a $0.10-0.15 per-record lien search that returns every lien with no lapse logic.

Related actors

• sos-registry-monitor -- new business formations across CO, CT, NY, OR, PA, DE (the Colorado debtor's debtor_sos_entity_id joins to it).
• kyb-company-verifier -- verify the debtor entity before you call.
• city-business-license-leads, liquor-license-new-openings-tracker -- other "just changed state" lead feeds.

Use as an MCP tool

Callable from Claude, Cursor and any MCP client through mcp.apify.com -- ask "which equipment liens in Colorado lapse in the next 60 days" and the agent fills states, windowDays and securedPartyQuery. Billing is unchanged when called as a tool.

FAQ

Why does a lien "lapsing in 2026" have an initial filing date of 2011? Continuations. Each one extends the lapse five years and the portal propagates the new date to every row of the statement; continuation_count and last_continuation_date show the history.

Can a lead be a lien that was already paid off? Not from CO or CT: any terminating row on the statement excludes it, and the count of exclusions is on your rows. Oregon publishes no termination flag; its list holds active liens only as of the monthly snapshot, and the row says so.

Why is Oregon "partial"? Oregon publishes secured parties with lapse dates but no debtor table. You get the lender, the lapse date and a link to the filing image where the debtor is named.

Does the collateral description tell me the equipment? Where filed. Colorado publishes the collateral text as filed ("KUBOTA L3560HSTC ... 4WD HST CAB TRACTOR"); many filings say only "all assets". CT and OR publish none.