Add scripts/dedupe-corridor-shells.sql + hide 119 noisy seed rows

1acea14490d2011545daed0fddf1ddd5d7ca96dd · 2026-05-07 09:27:50 -0700 · Steve Abrams

Hides booth-renter personal-name LLCs sharing addresses with proper
storefronts (52 rows) and the talent-agency production-shell LLCs that
share creative-vertical office suites (67 rows). Visible directory
shrinks from 1,938 → 1,819 active rows. Reversible: status='hidden'
not deletion. Run script standalone after any future corridor reseed.

Files touched

A scripts/dedupe-corridor-shells.sql

Diff

commit 1acea14490d2011545daed0fddf1ddd5d7ca96dd
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu May 7 09:27:50 2026 -0700

    Add scripts/dedupe-corridor-shells.sql + hide 119 noisy seed rows
    
    Hides booth-renter personal-name LLCs sharing addresses with proper
    storefronts (52 rows) and the talent-agency production-shell LLCs that
    share creative-vertical office suites (67 rows). Visible directory
    shrinks from 1,938 → 1,819 active rows. Reversible: status='hidden'
    not deletion. Run script standalone after any future corridor reseed.
---
 scripts/dedupe-corridor-shells.sql | 65 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 65 insertions(+)

diff --git a/scripts/dedupe-corridor-shells.sql b/scripts/dedupe-corridor-shells.sql
new file mode 100644
index 0000000..1d8996a
--- /dev/null
+++ b/scripts/dedupe-corridor-shells.sql
@@ -0,0 +1,65 @@
+-- scripts/dedupe-corridor-shells.sql
+--
+-- Hide noisy public_records seed rows after the corridor seed has run.
+-- Reversible: sets status='hidden' rather than deleting. Re-running the
+-- seed never resurrects hidden rows because slug-conflict makes the
+-- INSERT a no-op (ON CONFLICT (slug) DO NOTHING).
+--
+-- Two patterns this targets:
+--
+--   1. Personal-name LLCs sharing an address with a proper-business-name
+--      peer. These are typically booth-renters at salons, tax preparers
+--      registered to a coworking suite, or owner LLCs at the same address
+--      as the actual storefront. The proper-business-name peer is "the
+--      business"; the personal-name row is noise.
+--
+--   2. Creative vertical addresses with 4+ entries. These are talent-agency
+--      / production-coworking suites (e.g. 15260 Ventura Blvd #1040 holds
+--      24 entertainment-industry shell LLCs). Keep the oldest row at each
+--      so the address is still represented; hide the rest.
+--
+-- Run: psql -d ventura_claw_leads -f scripts/dedupe-corridor-shells.sql
+
+BEGIN;
+
+WITH personal AS (
+  SELECT id
+    FROM businesses
+   WHERE source='public_records' AND status='active'
+     AND business_name ~ '^[A-Z][a-z]+ ([A-Z]\.? )?[A-Z][a-z]+(?:-[A-Z][a-z]+)?( [A-Z][a-z]+)?$'
+     AND business_name !~* '\m(Inc|Llc|Co|Corp|Salon|Spa|Hair|Nail|Bar|Cafe|Kitchen|Studio|Boutique|Yoga|Pilates|Dance|Pet|Photo|Florist|Cleaner|Detail|Tint|Wash|Restaurant|Bakery|Pizza|Burger|Taco|Sushi|Bistro|Bagel|Coffee|Tea|Juice|Deli|Catering|Brow|Lash|Skin)\M'
+),
+peers AS (
+  SELECT vertical, lower(regexp_replace(street, '\s+', ' ', 'g')) AS norm_street, city
+    FROM businesses
+   WHERE status='active'
+     AND business_name !~ '^[A-Z][a-z]+ ([A-Z]\.? )?[A-Z][a-z]+(?:-[A-Z][a-z]+)?( [A-Z][a-z]+)?$'
+   GROUP BY 1,2,3
+)
+UPDATE businesses b
+   SET status='hidden'
+  FROM personal p
+ WHERE b.id = p.id
+   AND EXISTS (
+     SELECT 1 FROM peers
+      WHERE peers.vertical=b.vertical
+        AND peers.norm_street=lower(regexp_replace(b.street, '\s+', ' ', 'g'))
+        AND peers.city=b.city
+   );
+
+WITH crowded AS (
+  SELECT lower(regexp_replace(street, '\s+', ' ', 'g')) AS norm_street, city,
+         array_agg(id ORDER BY id) AS ids
+    FROM businesses
+   WHERE source='public_records' AND vertical='creative' AND status='active' AND street IS NOT NULL
+   GROUP BY 1,2 HAVING COUNT(*) >= 4
+)
+UPDATE businesses b
+   SET status='hidden'
+  FROM crowded c
+ WHERE b.vertical='creative'
+   AND lower(regexp_replace(b.street, '\s+', ' ', 'g')) = c.norm_street
+   AND b.city = c.city
+   AND b.id <> c.ids[1];
+
+COMMIT;

← f62acb4 add in-process 120s cache for /api/businesses.geo to prevent · back to Ventura Claw Leads · Per-vertical SEO meta uses real total + city names; visible c64097d →