#!/usr/bin/env python3 """Colorado SB 26-189 and Claude at work: the notices, requests and records a company would keep, and the dates checked, in DuckDB. python3 colorado_sb26189_claude_check.py --as-of 2026-10-31 --request-days 45 --first-use-on 2026-01-01 --ids-sequential yes # clean generated records python3 colorado_sb26189_claude_check.py --plant --as-of 2026-10-31 --request-days 45 --first-use-on 2026-01-01 --ids-sequential yes # planted faults python3 colorado_sb26189_claude_check.py --as-of 2026-10-31 --first-use-on 2026-01-01 --ids-sequential yes # no request deadline set: that check fails python3 colorado_sb26189_claude_check.py --strict ... # exit 1 when an input check failed or a decision, request, record month or # missing id number needs follow-up; FOR LEGAL TO CHECK uses alone do not, # they are a prompt for Legal python3 colorado_sb26189_claude_check.py --work-dir some/folder # where generated files and results are written Builds four lists, each printed with its filters, row count and a SHA-256 of its rows, and each written as a CSV: use sort every row of your Claude use-case register: is it an employment use, and does it touch people in Colorado as you record it. An employment use, or an empty or unknown answer, is FOR LEGAL TO CHECK (reason: SB 26-189). A use is never called covered and never called exempt. decisions every employment decision where Claude's output was used: is a notice recorded before the decision (6-1-1704(1)), and for an adverse decision, was a plain-language description sent within 30 days after making the decision (6-1-1704(3)) requests every request for personal data, correction or human review: answered, and within the deadline YOU set records whether the decision log has a record in every finished month of the window (a month still running on the as-of date is not counted as a gap), and whether its id numbers have gaps The window is the later of "3 years before the as-of date" and "the date you began using Claude in employment decisions". Input checks run first (unreadable dates, answers in no list, duplicate ids, unset settings); a failed check marks the lists INCOMPLETE. The act (SB 26-189, Colorado Session Laws 2026, Ch. 131) takes effect January 1, 2027 and applies to consequential decisions made on or after that date (Section 5); decisions dated earlier are counted on an [info] line. This is NOT legal advice and NOT a compliance test. Legal decides whether the act reaches a use. The act's text is not fetched by this script. The generated records are NOT a real organization's data. """ import csv import datetime import hashlib import pathlib import random import re import sys import duckdb # ---- CHANGE THESE FOR YOUR OWN DATA --------------------------------------------------------- REGISTER_FILE = None # your Claude use-case register (CSV with a header row); None = generate DECISIONS_FILE = None # your log of employment decisions where Claude's output was used (CSV with a header row); None = generate REQUESTS_FILE = None # your log of requests for data, correction or human review (CSV with a header row); None = generate # the column of YOUR files that holds each field. Dates must be written YYYY-MM-DD. REG_COLS = {"use_case_id": "use_case_id", "decision": "decision", "colorado": "colorado_people", "created_on": "created_on"} DEC_COLS = {"decision_id": "decision_id", "use_case_id": "use_case_id", "decided_on": "decided_on", "outcome": "outcome", "notice_on": "notice_sent_on", "description_on": "description_sent_on", "created_on": "created_on"} REQ_COLS = {"request_id": "request_id", "kind": "kind", "received_on": "received_on", "answered_on": "answered_on", "created_on": "created_on"} # YOUR answers (lower case, no spaces at the ends) -> the meaning the script gives them. An answer in your files that is in # none of these lists is counted and fails an input check; it is never guessed. DECISION_WORDS = {"hiring": "employment", "promotion": "employment", "pay": "employment", "discipline": "employment", "termination": "employment", "other employment decision": "employment", "neither": "neither"} COLORADO_WORDS = {"yes": "yes", "no": "no"} # does the use touch people in Colorado, as your counsel defines it OUTCOME_WORDS = {"adverse": "adverse", "not adverse": "not adverse"} KIND_WORDS = {"personal data": "personal data", "correction": "correction", "human review": "human review"} # SETTINGS WITH NO DEFAULT: an unset one fails its check, because a check that could not run is not a pass. REQUEST_DAYS = None # the number of days you will allow to answer a request. 6-1-1705 states none (it points to 6-1-1306); your counsel sets it. FIRST_USE_ON = None # YYYY-MM-DD: the day you began using Claude in employment decisions AS_OF = None # YYYY-MM-DD: the day the check is for; None = today KNOWN_GAP_MONTHS = [] # months (YYYY-MM) with no decision records that you have explained, for example ["2026-03"] PUBLIC_NOTICE_SINCE = None # YYYY-MM-DD: the day you began keeping a prominent public notice at points of consumer interaction. # 6-1-1704(2) says that meets the notice duty; a decision on or after it with no notice of its own is # then not flagged. None: you rely on notices sent per decision, and each must be in the log. IDS_ARE_SEQUENTIAL = None # True: ids end in a number that goes up by one (D-001, D-002); a missing number is reported. False: ids come # from a shared counter or are not numbered, so gaps are not checked. None: not set, and that check fails. # ---------------------------------------------------------------------------------------------- DESCRIPTION_DAYS = 30 # 6-1-1704(3): "within thirty days after making the decision", a plain-language description of the decision and the technology's role RETENTION_YEARS = 3 # 6-1-1703: records kept "for not less than three years after the date of a consequential decision" APPLIES_FROM = "2027-01-01" # Section 5(3): the act applies to consequential decisions made on or after January 1, 2027 MISSING_LISTED = 10000 # missing ids written to records_kept.csv (the console shows the first 12 and the count) SOURCES = [("SB 26-189, session law (Ch. 131)", "https://leg.colorado.gov/laws/session-laws/SB26-189/131/download"), ("SB 26-189, bill page", "https://leg.colorado.gov/bills/sb26-189")] def qcol(name): return '"' + name.replace('"', '""') + '"' def strict_date(col): """YYYY-MM-DD only: '8/5/26' and '2026/09/24' are not dates here, and neither is '2026-02-30'.""" c = f"trim(coalesce({qcol(col)}, ''))" return (f"CASE WHEN regexp_full_match({c}, '\\d{{4}}-\\d{{2}}-\\d{{2}}') THEN try_strptime({c}, '%Y-%m-%d')::DATE END", f"{c} <> ''") def years_before(day, years): try: return day.replace(year=day.year - years) except ValueError: # 29 February return day.replace(year=day.year - years, day=28) def month_start(day): return day.replace(day=1) def next_month(day): return (day.replace(day=28) + datetime.timedelta(days=4)).replace(day=1) def generate(mode, gen_dir): """Seeded stand-in for an organization's records. mode: clean or plant.""" plant = mode == "plant" rnd = random.Random(226) gen_dir.mkdir(parents=True, exist_ok=True) as_of = datetime.date(2026, 10, 31) iso = lambda d: d.isoformat() if d else "" reg = [["UC-201", "Summarize resumes for recruiters", "hiring", "yes"], ["UC-202", "Rank resumes against a job description", "hiring", "yes"], ["UC-203", "Draft job postings", "neither", "yes"], ["UC-204", "Suggest a promotion shortlist", "promotion", "yes"], ["UC-205", "Answer staff questions about HR policy", "neither", "yes"], ["UC-206", "Draft performance review summaries", "pay", "yes"], ["UC-207", "Screen applicants for a role open only outside Colorado", "hiring", "no"]] if plant: reg += [["UC-208", "Score interview notes", "hiring", ""], # planted: Colorado not recorded ["uc-209", " Flag attendance problems ", "Discipline", " Yes "], # planted: other case and spaces; still read ["UC-210", "Pick finalists", "selection", "yes"], # planted: an answer in no list ["UC-202", "Rank resumes against a job description", "hiring", "yes"], # planted: duplicate id ["", "Chat helper for recruiters", "", ""]] # planted: no id, no answers with open(gen_dir / "register.csv", "w", newline="") as f: w = csv.writer(f) w.writerow(["use_case_id", "use_case", "decision", "colorado_people", "created_on"]) for r in reg: w.writerow([r[0], r[1], r[2], r[3], "2026-01-02"]) # 12 decisions in each month from January to October 2026, numbered in date order decisions, n = [], 0 for month in range(1, 11): for k in range(12): n += 1 decided = datetime.date(2026, month, 1) + datetime.timedelta(days=min(27, k * 2 + rnd.randint(0, 1))) adverse = rnd.random() < 0.3 notice = decided - datetime.timedelta(days=rnd.randint(0, 3)) desc = decided + datetime.timedelta(days=rnd.randint(3, 25)) if adverse else None if desc and desc > as_of: desc = None # not sent yet; still inside its 30 days in the clean run decisions.append([f"D-{n:03d}", rnd.choice(["UC-201", "UC-202", "UC-204", "UC-206"]), iso(decided), "adverse" if adverse else "not adverse", iso(notice), iso(desc), iso(decided)]) if plant: def row(did): return next(r for r in decisions if r[0] == did) row("D-050")[4] = "" # planted: no notice recorded r = row("D-051"); r[3], r[5] = "adverse", "" # planted: adverse, no description, 30 days long gone r = row("D-052"); r[3], r[5] = "adverse", iso(datetime.date.fromisoformat(r[2]) + datetime.timedelta(days=41)) # planted: sent on day 41 row("D-053")[2] = "6/12/26" # planted: a date that does not read as YYYY-MM-DD row("D-054")[3] = "denied" # planted: an outcome in no list row("D-055")[6] = "2027-01-01" # planted: a record dated after the as-of date decisions.append(list(row("D-056"))) # planted: a duplicated decision id decisions = [r for r in decisions if not r[2].startswith("2026-03") and r[0] != "D-090"] # planted: all of March gone, and one more row row("D-101")[1] = "UC-999" # planted: a use that is not on the register decisions.append(["", "UC-201", "2026-06-15", "not adverse", "2026-06-14", "", "2026-06-15"]) # planted: a decision with no id with open(gen_dir / "decisions.csv", "w", newline="") as f: w = csv.writer(f) w.writerow(["decision_id", "use_case_id", "decided_on", "outcome", "notice_sent_on", "description_sent_on", "created_on"]) w.writerows(decisions) requests = [] for i in range(1, 31): received = datetime.date(2026, 2, 1) + datetime.timedelta(days=int(i * 8.5)) answered = received + datetime.timedelta(days=rnd.randint(5, 40)) requests.append([f"R-{i:03d}", rnd.choice(["personal data", "correction", "human review"]), iso(received), iso(answered) if answered <= as_of else "", iso(received)]) if plant: requests[3][3] = "" # planted: R-004, never answered requests[5][3] = iso(datetime.date.fromisoformat(requests[5][2]) + datetime.timedelta(days=60)) # planted: answered after 60 days requests[7][3] = iso(datetime.date.fromisoformat(requests[7][2]) - datetime.timedelta(days=2)) # planted: answered before it was received requests[9][1] = "appeal" # planted: a kind in no list requests.append(list(requests[10])) # planted: a duplicated request id requests.append(["", "correction", "2026-05-01", "2026-05-10", "2026-05-01"]) # planted: a request with no id with open(gen_dir / "requests.csv", "w", newline="") as f: w = csv.writer(f) w.writerow(["request_id", "kind", "received_on", "answered_on", "created_on"]) w.writerows(requests) return str(gen_dir / "register.csv"), str(gen_dir / "decisions.csv"), str(gen_dir / "requests.csv") def fingerprint(rows): body = "\n".join("|".join("" if v is None else str(v) for v in r) for r in rows) return hashlib.sha256(body.encode()).hexdigest() def population(out_dir, name, control, filters, rows, header, complete): out_dir.mkdir(parents=True, exist_ok=True) with open(out_dir / f"{name}.csv", "w", newline="") as f: w = csv.writer(f) w.writerow(header) w.writerows(rows) print(f"\n== {control}: {name}" + ("" if complete else " ** INCOMPLETE: see input checks **")) print(f"filters : {filters}") print(f"rows : {len(rows)}") print(f"sha256 : {fingerprint(rows)}") def flag(argv, name): """The value after a --flag, or None; stops with a message if it is missing.""" if name not in argv: return None at = argv.index(name) + 1 if at >= len(argv) or argv[at].startswith("--"): sys.exit(f"usage: {name} needs a value after it") return argv[at] def read_day(text): if text is None or not re.fullmatch(r"\d{4}-\d{2}-\d{2}", text.strip()): return None try: return datetime.date.fromisoformat(text.strip()) except ValueError: return None def id_gaps(ids, limit): """ids: id strings as the log writes them. Groups by prefix ignoring case, and returns (count of missing numbers, up to `limit` missing ids written the way the log writes them: its own prefix and zero padding).""" by_prefix = {} for i in ids: m = re.fullmatch(r"(.*?)(\d+)", i) if m: g = by_prefix.setdefault(m.group(1).lower(), {"shown": m.group(1), "width": len(m.group(2)), "nums": set()}) g["nums"].add(int(m.group(2))) count, listed = 0, [] for _, g in sorted(by_prefix.items()): nums = g["nums"] count += max(nums) - min(nums) + 1 - len(nums) k = min(nums) while len(listed) < limit and k <= max(nums): if k not in nums: listed.append(f"{g['shown']}{k:0{g['width']}d}") k += 1 return count, listed def main(): argv = sys.argv strict = "--strict" in argv mode = "plant" if "--plant" in argv else "clean" base = pathlib.Path(flag(argv, "--work-dir") or ".") out_dir, gen_dir = base / "evidence-out", base / "generated" as_of_text = flag(argv, "--as-of") or AS_OF days_text = flag(argv, "--request-days") or REQUEST_DAYS first_text = flag(argv, "--first-use-on") or FIRST_USE_ON pub_text = flag(argv, "--public-notice-since") or PUBLIC_NOTICE_SINCE seq_text = flag(argv, "--ids-sequential") ids_seq = {"yes": True, "no": False}.get(seq_text.lower()) if seq_text else IDS_ARE_SEQUENTIAL if ids_seq not in (True, False, None): sys.exit("IDS_ARE_SEQUENTIAL must be True, False or None (not set)") for name, words in [("DECISION_WORDS", DECISION_WORDS), ("COLORADO_WORDS", COLORADO_WORDS), ("OUTCOME_WORDS", OUTCOME_WORDS), ("KIND_WORDS", KIND_WORDS)]: for k in words: if k != k.strip().lower(): sys.exit(f"{name}: the answer {k!r} must be lower case with no spaces at the ends") for v in DECISION_WORDS.values(): if v not in ("employment", "neither"): sys.exit(f"DECISION_WORDS: {v!r} must be employment or neither") for v in COLORADO_WORDS.values(): if v not in ("yes", "no"): sys.exit(f"COLORADO_WORDS: {v!r} must be yes or no") for v in OUTCOME_WORDS.values(): if v not in ("adverse", "not adverse"): sys.exit(f"OUTCOME_WORDS: {v!r} must be adverse or not adverse") for v in KIND_WORDS.values(): if not (isinstance(v, str) and v.strip()): sys.exit(f"KIND_WORDS: {v!r} must be a name") files = [REGISTER_FILE, DECISIONS_FILE, REQUESTS_FILE] if all(f is None for f in files): reg_file, dec_file, req_file = generate(mode, gen_dir) print(f"input : GENERATED records (not a real organization), seed 226, mode={mode}") elif any(f is None for f in files): sys.exit("set REGISTER_FILE, DECISIONS_FILE and REQUESTS_FILE together") else: reg_file, dec_file, req_file = files print("input : " + ", ".join(files)) for label, url in SOURCES: print(f"source : {label}, {url} (not fetched by this script)") # ---- settings: an unset or unreadable one is a failed check, never a quiet default as_of = read_day(as_of_text) if as_of_text else datetime.date.today() if as_of is None: sys.exit(f"the as-of date {as_of_text!r} is not written as YYYY-MM-DD, for example 2026-10-31") first_use = read_day(first_text) if first_text else None public_since = read_day(pub_text) if pub_text else None try: req_days = int(str(days_text)) if days_text is not None else None if req_days is not None and req_days <= 0: req_days = None except ValueError: req_days = None gap_months = [m for m in KNOWN_GAP_MONTHS if re.fullmatch(r"\d{4}-(0[1-9]|1[0-2])", str(m))] print(f"as of : {as_of} request deadline: {str(req_days) + ' days' if req_days else 'NOT SET'} first use: {first_use or 'NOT SET'}" f" explained gap months: {', '.join(gap_months) if gap_months else 'none'}") con = duckdb.connect() # every column your settings name must exist in your files; a wrong name stops the run with the names it did find for label, path, cols in [("register", reg_file, REG_COLS), ("decision log", dec_file, DEC_COLS), ("request log", req_file, REQ_COLS)]: have = {r[0].lower() for r in con.execute("DESCRIBE SELECT * FROM read_csv(?, header=true, all_varchar=true)", [path]).fetchall()} lost = [c for c in cols.values() if c.lower() not in have] if lost: sys.exit(f"{label}: no column named {', '.join(lost)} in {path}; its columns are {', '.join(sorted(have))}") for table, words in [("decision_map", DECISION_WORDS), ("colorado_map", COLORADO_WORDS), ("outcome_map", OUTCOME_WORDS), ("kind_map", KIND_WORDS)]: con.execute(f"CREATE TABLE {table}(word VARCHAR, meaning VARCHAR)") con.executemany(f"INSERT INTO {table} VALUES (?, ?)", [(k, str(v)) for k, v in words.items()]) word = lambda c: f"lower(trim(coalesce({qcol(c)}, '')))" ident = lambda c: f"nullif(lower(trim(coalesce({qcol(c)}, ''))), '')" shown = lambda c: f"nullif(trim(coalesce({qcol(c)}, '')), '')" def dates(cols, names): parts = [] for n in names: parsed, given = strict_date(cols[n]) parts.append(f"{parsed} AS {n}, {given} AS {n}_given") return ", ".join(parts) con.execute(f"""CREATE TABLE register AS SELECT row_number() OVER () AS row_no, {ident(REG_COLS['use_case_id'])} AS use_case_id, {shown(REG_COLS['use_case_id'])} AS id_shown, {word(REG_COLS['decision'])} AS decision_word, {word(REG_COLS['colorado'])} AS colorado_word, {dates(REG_COLS, ['created_on'])} FROM read_csv(?, header=true, all_varchar=true)""", [reg_file]) con.execute(f"""CREATE TABLE decisions AS SELECT row_number() OVER () AS row_no, {ident(DEC_COLS['decision_id'])} AS decision_id, {shown(DEC_COLS['decision_id'])} AS id_shown, {ident(DEC_COLS['use_case_id'])} AS use_case_id, {word(DEC_COLS['outcome'])} AS outcome_word, {dates(DEC_COLS, ['decided_on', 'notice_on', 'description_on', 'created_on'])} FROM read_csv(?, header=true, all_varchar=true)""", [dec_file]) con.execute(f"""CREATE TABLE requests AS SELECT row_number() OVER () AS row_no, {ident(REQ_COLS['request_id'])} AS request_id, {shown(REQ_COLS['request_id'])} AS id_shown, {word(REQ_COLS['kind'])} AS kind_word, {dates(REQ_COLS, ['received_on', 'answered_on', 'created_on'])} FROM read_csv(?, header=true, all_varchar=true)""", [req_file]) q = lambda s, *p: con.execute(s, list(p)).fetchone()[0] n_reg, n_dec, n_req = (q(f"SELECT count(*) FROM {t}") for t in ("register", "decisions", "requests")) # ---- input checks: each says which planted fault trips it unread = lambda tbl, col, m: q(f"SELECT count(*) FROM {tbl} WHERE {col} NOT IN (SELECT word FROM {m})") dup = lambda tbl, col: q(f"SELECT count(*) FROM (SELECT {col} FROM {tbl} WHERE {col} IS NOT NULL GROUP BY 1 HAVING count(*) > 1)") bad = lambda tbl, c: f"({c}_given AND {c} IS NULL)" after = lambda tbl, c: q(f"SELECT count(*) FROM {tbl} WHERE {c} > ?::DATE", str(as_of)) unreadable = (q(f"SELECT count(*) FROM decisions WHERE {bad('decisions', 'decided_on')} OR NOT decided_on_given OR {bad('decisions', 'notice_on')}" f" OR {bad('decisions', 'description_on')} OR {bad('decisions', 'created_on')} OR NOT created_on_given") + q(f"SELECT count(*) FROM requests WHERE {bad('requests', 'received_on')} OR NOT received_on_given" f" OR {bad('requests', 'answered_on')} OR {bad('requests', 'created_on')} OR NOT created_on_given") + q(f"SELECT count(*) FROM register WHERE {bad('register', 'created_on')} OR NOT created_on_given")) dated_after = (sum(after("decisions", c) for c in ("decided_on", "notice_on", "description_on", "created_on")) + sum(after("requests", c) for c in ("received_on", "answered_on", "created_on")) + after("register", "created_on")) checks = [ ("as-of date not written as YYYY-MM-DD", 0 if as_of else 1, "an as-of setting such as 31/10/2026"), ("request deadline not set, or not a whole number of days above zero", 0 if req_days else 1, "no --request-days and REQUEST_DAYS left as None; 6-1-1705 states no deadline, so you set one"), ("ids-are-sequential setting not set (True or False)", 0 if ids_seq in (True, False) else 1, "IDS_ARE_SEQUENTIAL left as None"), ("public-notice date not written as YYYY-MM-DD", 1 if pub_text and public_since is None else 0, "a PUBLIC_NOTICE_SINCE such as 1/1/27"), ("first-use date not set, or not written as YYYY-MM-DD", 0 if first_use else 1, "FIRST_USE_ON left as None"), ("explained gap months not written as YYYY-MM", len(KNOWN_GAP_MONTHS) - len(gap_months), "a KNOWN_GAP_MONTHS entry such as 3/2026"), ("register ids listed more than once (ignoring case)", dup("register", "use_case_id"), "a duplicated register row"), ("register rows with no use case id", q("SELECT count(*) FROM register WHERE use_case_id IS NULL"), "a register row with no id"), ("register answers in no list (decision, Colorado)", unread("register", "decision_word", "decision_map") + unread("register", "colorado_word", "colorado_map"), "an answer such as 'selection' that is not one of your words (an empty answer counts)"), ("decision ids listed more than once (ignoring case)", dup("decisions", "decision_id"), "a duplicated decision row"), ("decision rows with no decision id", q("SELECT count(*) FROM decisions WHERE decision_id IS NULL"), "a decision row with no id"), ("decisions naming a use that is not on the register", q("SELECT count(*) FROM decisions WHERE use_case_id IS NULL OR use_case_id NOT IN (SELECT use_case_id FROM register WHERE use_case_id IS NOT NULL)"), "a decision that names UC-999"), ("decision outcomes in no list", unread("decisions", "outcome_word", "outcome_map"), "an outcome such as 'denied'"), ("request ids listed more than once (ignoring case)", dup("requests", "request_id"), "a duplicated request row"), ("request rows with no request id", q("SELECT count(*) FROM requests WHERE request_id IS NULL"), "a request row with no id"), ("request kinds in no list", unread("requests", "kind_word", "kind_map"), "a kind such as 'appeal'"), ("dates missing or not written as YYYY-MM-DD (a blank notice or description is not counted: it is a finding)", unreadable, "a date such as 6/12/26, or a decision with no date"), ("dates after the as-of date", dated_after, "a record dated 2027-01-01"), ("descriptions dated before their decision, or answers before their request", q("SELECT count(*) FROM decisions WHERE description_on < decided_on") + q("SELECT count(*) FROM requests WHERE answered_on < received_on"), "a request answered two days before it was received"), ] print(f"\n== Input checks (register rows: {n_reg}; decision rows: {n_dec}; request rows: {n_req})") ok = True for label, n, fault in checks: ok &= n == 0 print(f"[{'ok ' if n == 0 else 'FAIL'}] {label}: {n}" + ("" if n == 0 else f" (trips on: {fault})")) # a word list entry that matches nothing in your files is reported, never silently ignored for tbl, col, src, w in [("register", "decision_word", "decision", "decision_map"), ("register", "colorado_word", "Colorado", "colorado_map"), ("decisions", "outcome_word", "outcome", "outcome_map"), ("requests", "kind_word", "request kind", "kind_map")]: dead = [r[0] for r in con.execute(f"SELECT word FROM {w} WHERE word NOT IN (SELECT {col} FROM {tbl}) ORDER BY 1").fetchall()] if dead: print(f"[info] {src} answers in your word list that no row uses: " + "; ".join(dead)) # ---- 1. the use sort con.execute("""CREATE TABLE sorted_uses AS SELECT r.row_no, coalesce(r.id_shown, '-') AS use_case_id, CASE WHEN d.meaning IS NULL THEN 'FOR LEGAL TO CHECK' WHEN d.meaning = 'neither' THEN 'NOT AN EMPLOYMENT USE AS RECORDED' WHEN c.meaning IS NULL THEN 'FOR LEGAL TO CHECK' WHEN c.meaning = 'no' THEN 'NO COLORADO PEOPLE AS RECORDED' ELSE 'FOR LEGAL TO CHECK' END AS status, CASE WHEN d.meaning IS NULL THEN 'decision type not recorded or not recognized' WHEN d.meaning = 'neither' THEN '' WHEN c.meaning IS NULL THEN 'Colorado not recorded or not recognized' WHEN c.meaning = 'no' THEN '' ELSE 'employment use that touches Colorado as recorded; SB 26-189' END AS reason FROM register r LEFT JOIN decision_map d ON d.word = r.decision_word LEFT JOIN colorado_map c ON c.word = r.colorado_word""") r_uc = con.execute("SELECT row_no, use_case_id, status, reason FROM sorted_uses ORDER BY row_no").fetchall() population(out_dir, "use_sort", "SB 26-189: the use sort", "every register row, none dropped; first applicable status; an empty or unrecognized answer is FOR LEGAL TO CHECK, never outside", r_uc, ["row_no", "use_case_id", "status", "reason"], ok) for r in r_uc: print(" " + " | ".join(str(v) for v in r)) counts = {k: sum(1 for r in r_uc if r[2] == k) for k in sorted({r[2] for r in r_uc})} print(" " + "; ".join(f"{k}: {v}" for k, v in counts.items())) print(" This sorts uses by what the register says. It does not say the act reaches a use or does not: Legal decides.") # ---- 2. decisions: notice, and the description due within 30 days of an adverse outcome rows = con.execute("""SELECT d.row_no, coalesce(d.id_shown, '-'), o.meaning, d.decided_on, d.notice_on_given, d.notice_on, d.description_on_given, d.description_on FROM decisions d LEFT JOIN outcome_map o ON o.word = d.outcome_word ORDER BY d.row_no""").fetchall() r_dec, follow_dec, open_dec = [], 0, 0 for row_no, did, outcome, decided, n_given, notice, s_given, sent in rows: hard, soft = [], [] if outcome is None: hard.append("outcome not recorded or not recognized") if decided is None: hard.append("decision date not read") else: if not n_given and public_since is not None and decided >= public_since: pass # 6-1-1704(2): the public notice you keep meets the duty elif not n_given: hard.append("NO NOTICE RECORDED") elif notice is None: hard.append("notice date not read") elif notice > decided: hard.append(f"NOTICE DATED {(notice - decided).days} DAYS AFTER THE DECISION") if outcome == "adverse": due = decided + datetime.timedelta(days=DESCRIPTION_DAYS) if not s_given: (hard if as_of > due else soft).append( f"NO DESCRIPTION, {DESCRIPTION_DAYS} DAYS PASSED (due {due})" if as_of > due else f"description due {due}") elif sent is None: hard.append("description date not read") elif sent > due: hard.append(f"DESCRIPTION SENT {(sent - decided).days} DAYS AFTER THE DECISION") status = "FOR FOLLOW-UP" if hard else ("OPEN, INSIDE 30 DAYS" if soft else "NO FLAG") follow_dec += status == "FOR FOLLOW-UP" open_dec += status.startswith("OPEN") r_dec.append((row_no, did, status, "; ".join(hard + soft))) population(out_dir, "decision_review", "SB 26-189: notice and the 30-day description", f"every decision row; notice before the decision (6-1-1704(1)); public notice kept since: {public_since or 'NOT SET (each decision needs its own)'}; " f"adverse = outcome mapped to adverse; description due {DESCRIPTION_DAYS} days after the decision date (6-1-1704(3)); " "a blank description is a finding, an unreadable date is not guessed", r_dec, ["row_no", "decision_id", "status", "reason"], ok) for r in r_dec: if r[2] != "NO FLAG": print(" " + " | ".join(str(v) for v in r)) dcounts = {k: sum(1 for r in r_dec if r[2] == k) for k in sorted({r[2] for r in r_dec})} print(" " + "; ".join(f"{k}: {v}" for k, v in dcounts.items())) print(" A flag says what the log records against the 30 days and the notice. It does not say the act was or was not followed.") # ---- 3. requests: answered, and within the deadline you set rows = con.execute("""SELECT q.row_no, coalesce(q.id_shown, '-'), k.meaning, q.received_on, q.answered_on_given, q.answered_on FROM requests q LEFT JOIN kind_map k ON k.word = q.kind_word ORDER BY q.row_no""").fetchall() r_req, follow_req, open_req = [], 0, 0 for row_no, rid, kind, received, a_given, answered in rows: hard, soft = [], [] if req_days is None: hard.append("DEADLINE NOT SET") if kind is None: hard.append("kind not recorded or not recognized") if received is None: hard.append("received date not read") elif req_days is not None: due = received + datetime.timedelta(days=req_days) if not a_given: (hard if as_of > due else soft).append( f"NOT ANSWERED, DEADLINE PASSED (due {due})" if as_of > due else f"open, due {due}") elif answered is None: hard.append("answer date not read") elif answered > due: hard.append(f"ANSWERED {(answered - received).days} DAYS AFTER RECEIPT (deadline {req_days})") status = "FOR FOLLOW-UP" if hard else ("OPEN, INSIDE DEADLINE" if soft else "NO FLAG") follow_req += status == "FOR FOLLOW-UP" open_req += status.startswith("OPEN") r_req.append((row_no, rid, status, "; ".join(hard + soft))) population(out_dir, "request_review", "SB 26-189: requests answered", f"every request row; deadline = {str(req_days) + ' days' if req_days else 'NOT SET'} from receipt, a number YOU set (6-1-1705 states none); " "a blank answer date is a finding", r_req, ["row_no", "request_id", "status", "reason"], ok) for r in r_req: if r[2] != "NO FLAG": print(" " + " | ".join(str(v) for v in r)) rcounts = {k: sum(1 for r in r_req if r[2] == k) for k in sorted({r[2] for r in r_req})} print(" " + "; ".join(f"{k}: {v}" for k, v in rcounts.items())) # ---- 4. records: a decision record in every month of the window, and no missing id numbers gaps_found = 0 # every gap of any kind, for --strict month_gaps, id_missing = 0, {"decision": 0, "request": 0} rec_rows = [] if first_use is None: window_start = None print("\n== SB 26-189: records kept ** NOT RUN: first-use date not set **") gaps_found += 1 else: window_start = max(years_before(as_of, RETENTION_YEARS), first_use) have = {r[0]: r[1] for r in con.execute( "SELECT strftime(decided_on, '%Y-%m'), count(*) FROM decisions WHERE decided_on IS NOT NULL GROUP BY 1").fetchall()} m, months = month_start(window_start), [] while m <= as_of: months.append(m.strftime("%Y-%m")) m = next_month(m) running = as_of.strftime("%Y-%m") if next_month(month_start(as_of)) - datetime.timedelta(days=1) != as_of else None partial = window_start.strftime("%Y-%m") if window_start.day != 1 else None for mo in months: c = have.get(mo, 0) if c: status = "RECORDS" elif mo == running: status = "MONTH NOT OVER, NO RECORDS YET" elif mo == partial: status = "PART MONTH AT WINDOW START, NO RECORDS" else: status = "NO RECORDS, EXPLAINED" if mo in gap_months else "NO RECORDS IN THIS MONTH" gaps_found += status == "NO RECORDS IN THIS MONTH" month_gaps += status == "NO RECORDS IN THIS MONTH" rec_rows.append(("month", mo, c, status)) if ids_seq: for log, tbl in [("decision", "decisions"), ("request", "requests")]: ids = [r[0] for r in con.execute(f"SELECT id_shown FROM {tbl} WHERE id_shown IS NOT NULL").fetchall()] n_missing, listed = id_gaps(ids, MISSING_LISTED) gaps_found += n_missing id_missing[log] = n_missing status = "NO MISSING NUMBERS" if not n_missing else "MISSING: " + ", ".join(listed) + ( f" (and {n_missing - len(listed)} more)" if n_missing > len(listed) else "") rec_rows.append((f"{log} ids", f"{len(ids)} ids read", n_missing, status)) older = q("SELECT count(*) FROM decisions WHERE decided_on < ?::DATE", str(window_start)) rec_rows.append(("older than the window", str(window_start), older, "NONE OLDER" if not older else "OLDER RECORDS: keep or not as you choose; the window is the minimum")) population(out_dir, "records_kept", "SB 26-189: records kept for at least 3 years", f"window {window_start} to {as_of} (the later of {RETENTION_YEARS} years before the as-of date and your first-use date); one record needed in each month " f"unless explained (a month still running on the as-of date, or a part month at the window start, is not a gap); " f"id numbers {'checked for gaps' if ids_seq else 'NOT checked (IDS_ARE_SEQUENTIAL is ' + str(ids_seq) + ')'}", rec_rows, ["what", "which", "count", "status"], ok) for r in rec_rows: if r[3] not in ("RECORDS", "NO MISSING NUMBERS", "NONE OLDER"): shown_status = r[3] if r[3].startswith("MISSING: "): parts = r[3][len("MISSING: "):].split(" (and ")[0].split(", ") shown_status = "MISSING: " + ", ".join(parts[:12]) + (f" ... ({r[2]} in all; every one is in records_kept.csv)" if r[2] > 12 else "") print(" " + " | ".join(str(v) for v in r[:3]) + " | " + shown_status) print(f" months in the window: {len(months)}; months with no record: {sum(1 for r in rec_rows if r[3] == 'NO RECORDS IN THIS MONTH')}") print(" A month with no record may be real (no decisions) or a deleted log: the script cannot tell which. Explain it in KNOWN_GAP_MONTHS or find the records.") before = q("SELECT count(*) FROM decisions WHERE decided_on < ?::DATE", APPLIES_FROM) print(f"\n[info] decisions dated before {APPLIES_FROM}, when the act begins to apply (Section 5(3)): {before} of {n_dec}") uses_legal = sum(1 for r in r_uc if r[2] == "FOR LEGAL TO CHECK") print("\n== Result") print(f"input checks : {'all passed' if ok else 'FAILED, the lists above are not complete'}") print(f"for Legal : {uses_legal} use(s) marked FOR LEGAL TO CHECK") print(f"decisions : {follow_dec} for follow-up, {open_dec} open inside 30 days") print(f"requests : {follow_req} for follow-up, {open_req} open inside the deadline") if first_use is None: print("records : NOT RUN (first-use date not set)") else: ids_part = (f"{id_missing['decision']} missing decision id(s), {id_missing['request']} missing request id(s)" if ids_seq else "id numbers not checked") print(f"records : {month_gaps} month(s) with no decision record, {ids_part}") print("This is not legal advice and not a compliance test. Legal decides what the act requires.") # --strict: FOR LEGAL TO CHECK uses are a prompt for Legal, not a failure, so they never set the exit code return 1 if strict and (not ok or follow_dec or follow_req or gaps_found) else 0 if __name__ == "__main__": sys.exit(main())