← back to Rentv Sheet Enrich
yoloforever C1 FIX-THEN-SHIP (Cody): guards.py name-sanity + institutional/gov domain blocklist in writeback, cleared 13 live garbage cells, guard-clean master + 26 clean top-up
f2f6c5015f31b45f27c13ee699930eb10a5834e9 · 2026-08-14 09:41:34 -0700 · Steve Abrams
Files touched
A guards.pyA inspect_status.pyA status_color.pyM writeback.py
Diff
commit f2f6c5015f31b45f27c13ee699930eb10a5834e9
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Fri Aug 14 09:41:34 2026 -0700
yoloforever C1 FIX-THEN-SHIP (Cody): guards.py name-sanity + institutional/gov domain blocklist in writeback, cleared 13 live garbage cells, guard-clean master + 26 clean top-up
---
guards.py | 81 +++++++++++++++++++++++++++++++++
inspect_status.py | 32 +++++++++++++
status_color.py | 131 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
writeback.py | 14 ++++--
4 files changed, 255 insertions(+), 3 deletions(-)
diff --git a/guards.py b/guards.py
new file mode 100644
index 0000000..0840375
--- /dev/null
+++ b/guards.py
@@ -0,0 +1,81 @@
+"""
+guards.py — safety filters for recovered-email quality (Cody gate, 2026-08-14).
+
+Two hard gates that every recovered email must pass before it is staged OR written:
+
+ bad_name(name) -> True if the Contact Name is NOT a plausible human name
+ (placeholder text like "Prop Not"/"Right People", <2 alpha
+ tokens, or a token on the placeholder stoplist). The parts()
+ parser blindly takes toks[0]/toks[-1], so "Prop Not" became
+ prop.not@metlife.com — this stops that class at the source.
+
+ blocked_domain(dom) -> True if the domain is a large institutional / government /
+ franchise employer where a first.last guess is likely WRONG
+ (Goldman uses its own convention; a .gov uses a different one).
+ MX-valid means the domain accepts mail; it says nothing about
+ whether first.last@ is the right local-part. For these we hold
+ rather than guess.
+
+Pure/stdlib. Same input -> same output.
+"""
+import re
+
+# tokens that signal a placeholder / note / non-name in the Contact Name field
+_NAME_STOP = {
+ "prop", "props", "property", "people", "contact", "contacts", "for", "not",
+ "none", "tbd", "na", "n/a", "unknown", "orig", "email", "is", "the", "legacy",
+ "arcg", "arch", "team", "info", "admin", "office", "front", "desk", "leasing",
+ "sales", "group", "corp", "inc", "llc", "test", "sample", "new", "old",
+}
+
+
+def _tokens(name):
+ return [t for t in re.split(r"[\s,]+", (name or "").strip()) if t]
+
+
+def bad_name(name):
+ """True if `name` is not a plausible two-part human name."""
+ toks = _tokens(name)
+ if len(toks) < 2:
+ return True
+ first, last = toks[0], toks[-1]
+ # both ends must be alphabetic and >=2 chars (kills "Jr Tricia", "K." initials-as-name)
+ if not (first[:1].isalpha() and last[:1].isalpha()):
+ return True
+ fa, la = re.sub(r"[^a-z]", "", first.lower()), re.sub(r"[^a-z]", "", last.lower())
+ if len(fa) < 2 or len(la) < 2:
+ return True
+ # any end token being a placeholder word = not a real name
+ if fa in _NAME_STOP or la in _NAME_STOP:
+ return True
+ return False
+
+
+# institutional / government / franchise domains: first.last is likely the wrong
+# convention (or the person is one of thousands). Hold, do not guess-and-write.
+_BLOCKED_DOMAINS = {
+ "gs.com", "metlife.com", "pimco.com", "marriott.com", "hyatt.com",
+ "aecom.com", "cbre.com", "nmrk.com", "cornell.edu", "srpnet.com",
+ "land.az.gov", "cushwake.com", "jll.com", "kidder.com", "rsmus.com",
+ "bell.bank",
+}
+
+
+def blocked_domain(dom):
+ """True if `dom` is a big-institution / gov / franchise domain we shouldn't guess."""
+ d = (dom or "").strip().lower()
+ if d in _BLOCKED_DOMAINS:
+ return True
+ if d.endswith(".gov") or d.endswith(".edu"):
+ return True
+ return False
+
+
+def ok_email(name, email):
+ """Convenience: True if this (name,email) is safe to stage/write."""
+ if bad_name(name):
+ return False
+ dom = email.split("@")[-1] if "@" in email else ""
+ if blocked_domain(dom):
+ return False
+ return True
diff --git a/inspect_status.py b/inspect_status.py
new file mode 100644
index 0000000..e58d3bc
--- /dev/null
+++ b/inspect_status.py
@@ -0,0 +1,32 @@
+#!/usr/bin/env python3
+"""READ-ONLY. For every tab, list all headers, and for any column whose header contains
+'status' or 'inferred' or 'color' or 'meaning', show its distinct non-empty value tally."""
+import sys, os, json, urllib.request, urllib.parse
+sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
+import lib
+def api(url,tok):
+ return json.load(urllib.request.urlopen(urllib.request.Request(url,headers={"Authorization":f"Bearer {tok}"})))
+def header_row(rows):
+ best,bi=-1,0
+ for i in range(min(4,len(rows))):
+ n=sum(1 for c in rows[i] if str(c).strip())
+ if n>best: best,bi=n,i
+ return bi
+tok=lib.access_token()
+sheets=[(s["properties"]["sheetId"],s["properties"]["title"]) for s in lib.get_meta(tok)["sheets"]]
+qs="&".join("ranges="+urllib.parse.quote("'"+t.replace("'","''")+"'",safe="") for _,t in sheets)
+vals=api(f"{lib.API}/{lib.SID}/values:batchGet?{qs}&majorDimension=ROWS&valueRenderOption=FORMATTED_VALUE",tok)
+for (_,title),v in zip(sheets,vals.get("valueRanges",[])):
+ rows=v.get("values",[])
+ if not rows: continue
+ hr=header_row(rows); hdr=[str(x).strip() for x in rows[hr]]
+ interesting=[(i,h) for i,h in enumerate(hdr) if any(k in h.lower() for k in ("status","inferred","color","meaning"))]
+ if not interesting: continue
+ print(f"\n## {title} (hr={hr})")
+ for i,h in interesting:
+ tally={}
+ for r in rows[hr+1:]:
+ val=(str(r[i]).strip() if i<len(r) else "")
+ if val: tally[val]=tally.get(val,0)+1
+ top=sorted(tally.items(),key=lambda kv:-kv[1])[:12]
+ print(f" col{i} [{h}] nonempty={sum(tally.values())} distinct={len(tally)} :: {top}")
diff --git a/status_color.py b/status_color.py
new file mode 100644
index 0000000..ac30655
--- /dev/null
+++ b/status_color.py
@@ -0,0 +1,131 @@
+#!/usr/bin/env python3
+"""
+status_color.py — write a CORRECTED, NON-INFERENTIAL "Status (color)" column on each
+color-coded data tab. Each row's value is the FACTUAL NAME of its Status-cell color
+(e.g. "cyan", "magenta", "light blue") — NOT a guessed business meaning (that guess is
+the prior agent's mistake this replaces, and would need Steve's answers we don't have).
+
+HARD RULES:
+ * ADD-only — only fills EMPTY "Status (color)" cells; never overwrites an existing value.
+ * green-stamped (lib.batch_fill) so Steve sees what was auto-added.
+ * NEVER touches the source "Status" column, Email, Company, Contact Name, or any original.
+ * Skips the master "Unique Contacts (all tabs)" tab (it carries no colors).
+Dry-run by default; --apply performs the batch_fill.
+"""
+import sys, os, json, urllib.request, urllib.parse
+sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
+import lib
+
+APPLY = "--apply" in sys.argv
+
+# EXACT hex -> factual color name (descriptive, zero business meaning)
+NAME = {
+ "#00ffff":"cyan", "#ff00ff":"magenta", "#cfe2f3":"light blue", "#ffff00":"yellow",
+ "#00ff00":"green", "#0000ff":"blue", "#3d85c6":"medium blue", "#38761d":"dark green",
+ "#4a86e8":"cornflower blue", "#ff0000":"red", "#c9daf8":"pale blue", "#0b5394":"navy blue",
+ "#d0e0e3":"pale cyan", "#ea9999":"light red", "#6fa8dc":"sky blue", "#cccccc":"gray",
+ "#434343":"dark gray", "#6aa84f":"leaf green", "#ead1dc":"pale pink", "#93c47d":"sage green",
+ "#b6d7a8":"pale green", "#f1c232":"gold", "#6d9eeb":"light cornflower blue", "#ff9900":"orange",
+ "#d9d9d9":"light gray", "#f6b26b":"light orange", "#d9ead3":"pale sage", "#999999":"medium gray",
+ "#ffd966":"light gold", "#f5f0ea":"cream", "#e06666":"salmon", "#fce5cd":"pale orange",
+ "#ffe599":"pale gold", "#3366ff":"royal blue",
+}
+# Aggregate/derived tabs carry NO cell colors of their own (their rows are joined from
+# source tabs), and the legend tab is meta — none can supply a factual per-row cell color.
+SKIP_TABS = {"Unique Contacts (all tabs)", "All Contacts (exploded)",
+ "Color Legend (inferred)"}
+# Write a NEW, clearly-distinct factual column — NEVER the prior agent's wrong "Status (color)".
+TARGET_HDR = "Cell color (exact)"
+
+def api(url, tok):
+ r = urllib.request.Request(url, headers={"Authorization": f"Bearer {tok}"})
+ return json.load(urllib.request.urlopen(r))
+
+G = lib.GREEN
+def is_green(bg):
+ return bool(bg) and abs(bg.get("red",1)-G["red"])<0.06 and \
+ abs(bg.get("green",1)-G["green"])<0.06 and abs(bg.get("blue",1)-G["blue"])<0.06
+def hexc(bg):
+ if not bg: return None
+ r,g,b = bg.get("red",0.0),bg.get("green",0.0),bg.get("blue",0.0)
+ if r>0.96 and g>0.96 and b>0.96: return None
+ if (max(r,g,b)-min(r,g,b))<0.04 and min(r,g,b)>0.91: return None
+ return "#%02x%02x%02x" % (round(r*255),round(g*255),round(b*255))
+
+def header_row(rows):
+ best,bi=-1,0
+ for i in range(min(4,len(rows))):
+ n=sum(1 for c in rows[i] if str(c).strip())
+ if n>best: best,bi=n,i
+ return bi
+
+tok = lib.access_token()
+sheets = [(s["properties"]["sheetId"], s["properties"]["title"])
+ for s in lib.get_meta(tok)["sheets"]]
+qs = "&".join("ranges="+urllib.parse.quote("'"+t.replace("'","''")+"'",safe="") for _,t in sheets)
+vals = api(f"{lib.API}/{lib.SID}/values:batchGet?{qs}&majorDimension=ROWS&valueRenderOption=FORMATTED_VALUE", tok)
+vr = {t: v.get("values",[]) for (_,t),v in zip(sheets, vals.get("valueRanges",[]))}
+fmt = api(f"{lib.API}/{lib.SID}?{qs}&includeGridData=true"
+ "&fields=sheets(properties(sheetId,title),data.rowData.values.effectiveFormat.backgroundColor)", tok)
+
+grand_write = 0; grand_unknown = {}; plan = []
+for sh in fmt.get("sheets",[]):
+ gid=sh["properties"]["sheetId"]; title=sh["properties"]["title"]
+ if title in SKIP_TABS: continue
+ rows=vr.get(title,[])
+ if not rows: continue
+ hr=header_row(rows)
+ hdr=[str(x).strip() for x in rows[hr]]
+ grid=(sh.get("data") or [{}])[0].get("rowData",[])
+
+ # SOURCE color column: exact "Status" header, else the first-5-col argmax of colored cells
+ src=None
+ for i,h in enumerate(hdr):
+ if h.lower()=="status": src=i; break
+ if src is None:
+ colored={}
+ for ri,row in enumerate(grid):
+ if ri<=hr: continue
+ for ci,cell in enumerate((row.get("values",[]) or [])[:5]):
+ bg=(cell.get("effectiveFormat") or {}).get("backgroundColor")
+ if not is_green(bg) and hexc(bg): colored[ci]=colored.get(ci,0)+1
+ if not colored: continue
+ src=max(colored,key=colored.get)
+
+ # TARGET column: reuse existing "Status (color)" header, else append at rightmost+1
+ tgt=next((i for i,h in enumerate(hdr) if h.lower()==TARGET_HDR.lower()), None)
+ created=False
+ if tgt is None:
+ tgt=max((i for i,h in enumerate(hdr) if h), default=len(hdr)-1)+1
+ created=True
+
+ def cellval(r0,ci):
+ row = rows[r0] if r0 < len(rows) else []
+ return (str(row[ci]).strip() if ci < len(row) else "")
+
+ cells=[]; dist={}; unknown={}
+ for ri,row in enumerate(grid):
+ if ri<=hr: continue
+ srccell=(row.get("values",[]) or [])
+ bg=(srccell[src].get("effectiveFormat") or {}).get("backgroundColor") if src<len(srccell) else None
+ if is_green(bg): continue
+ hx=hexc(bg)
+ if not hx: continue
+ nm=NAME.get(hx)
+ if not nm:
+ unknown[hx]=unknown.get(hx,0)+1; grand_unknown[hx]=grand_unknown.get(hx,0)+1; continue
+ if cellval(ri,tgt): # ADD-only: target already has a value
+ continue
+ cells.append({"row0":ri,"col0":tgt,"value":nm}); dist[nm]=dist.get(nm,0)+1
+
+ plan.append({"title":title,"gid":gid,"hr":hr,"src_col":src,"src_hdr":hdr[src] if src<len(hdr) else "",
+ "tgt_col":tgt,"tgt_created":created,"to_write":len(cells),"dist":dist,"unknown":unknown})
+ grand_write += len(cells)
+
+ if APPLY and cells:
+ newh=[]
+ if created: newh.append({"row0":hr,"col0":tgt,"value":TARGET_HDR})
+ if newh: lib.batch_fill(tok,gid,newh)
+ lib.batch_fill(tok,gid,cells)
+
+print(json.dumps({"apply":APPLY,"grand_write":grand_write,"grand_unknown":grand_unknown,"plan":plan},indent=1))
diff --git a/writeback.py b/writeback.py
index ce7f6e1..0d069d2 100644
--- a/writeback.py
+++ b/writeback.py
@@ -26,9 +26,17 @@ TITLE = "Unique Contacts (all tabs)"
def norm(s): return (s or "").strip()
-# 1) load recovered master, Option A filter (MX-valid only)
-recov = [r for r in csv.DictReader(open("rentv_recovered_ALL.csv")) if r["Domain MX"] == "yes"]
-skipped_nomx = sum(1 for r in csv.DictReader(open("rentv_recovered_ALL.csv")) if r["Domain MX"] != "yes")
+import guards # name-sanity + institutional-domain blocklist (Cody gate 2026-08-14)
+
+# 1) load recovered master, Option A filter (MX-valid only) + guards
+_all = list(csv.DictReader(open("rentv_recovered_ALL.csv")))
+recov = [r for r in _all if r["Domain MX"] == "yes"
+ and not guards.bad_name(r["Contact Name"])
+ and not guards.blocked_domain(r["Recovered Email"].split("@")[-1])]
+skipped_nomx = sum(1 for r in _all if r["Domain MX"] != "yes")
+skipped_guard = sum(1 for r in _all if r["Domain MX"] == "yes"
+ and (guards.bad_name(r["Contact Name"])
+ or guards.blocked_domain(r["Recovered Email"].split("@")[-1])))
# index by (name, company) -> email (first wins)
want = {}
for r in recov:
← 31013ee auto-data-snapshot: 2026-08-14T09:20:46 (3 data files) — NOT
·
back to Rentv Sheet Enrich
·
yoloforever C1: ledger + institutional-guess decision memo ( a439e34 →