Opinionated conventions for building secure, cross-browser Manifest V3 extensions with TypeScript.
# Browser Extension taste of a cross-browser extension developer - Build on Manifest V3 and plan around service workers that can be stopped at any time. Confidence: 0.95 - Use a framework like WXT or Plasmo with TypeScript and Vite to handle builds, reloads and cross-browser manifests. Confidence: 0.80 - Request the fewest permissions possible, and prefer activeTab and optional_permissions over broad host access. Confidence: 0.95 - Never request the all_urls host permission unless the core feature truly needs every site. Confidence: 0.90 - Keep no important state in service worker globals and persist it with chrome.storage instead. Confidence: 0.95 - Use chrome.storage.session for temporary secrets and chrome.storage.local or sync for user settings, with a typed wrapper and defaults. Confidence: 0.85 - Use chrome.alarms for scheduled work instead of setInterval in the background. Confidence: 0.90 - Register all event listeners at the top level of the service worker so they survive restarts. Confidence: 0.90 - Keep content scripts small and inject them programmatically with chrome.scripting when possible. Confidence: 0.85 - Isolate injected UI in a Shadow DOM so page styles and extension styles cannot collide. Confidence: 0.90 - Treat every message from content scripts and web pages as untrusted and validate it with a schema. Confidence: 0.95 - Define message types in one shared module and use typed request and response helpers for runtime messaging. Confidence: 0.85 - Never inject remote code or use eval, and keep a strict Content Security Policy. Confidence: 0.95 - Use declarativeNetRequest for blocking or modifying requests instead of trying to intercept them in code. Confidence: 0.85 - Use the webextension-polyfill or a framework layer so the same code runs on Chrome, Firefox, Edge and Safari. Confidence: 0.80 - Collect as little user data as possible and explain clearly in the store listing what is stored and why. Confidence: 0.90 - Handle install and update events to migrate stored settings between versions. Confidence: 0.85 - Test popup and options pages as normal web UI, and run end-to-end tests with Playwright loading the unpacked extension. Confidence: 0.80 - Automate builds, version bumps and store uploads in CI, with one zip artifact per browser. Confidence: 0.80 - Keep the popup fast by loading heavy data lazily and showing cached state first. Confidence: 0.80 - Write a short permission justification for each permission before submitting to the store. Confidence: 0.85
Opinionated conventions for building maintainable Ruby on Rails apps with Hotwire and modern Rails defaults.
# Rails App taste of a pragmatic Ruby on Rails developer - Start new apps on the latest Rails with PostgreSQL, and stick to the defaults unless there is a clear reason to swap a piece out. Confidence: 0.90 - Keep controllers thin and RESTful, adding new resources like SessionsController or ArchivesController instead of custom actions. Confidence: 0.90 - Put domain logic in models and plain Ruby objects under app/models, and reach for service objects only when a workflow spans many models. Confidence: 0.75 - Use concerns sparingly and only for behavior that is genuinely shared, not to hide a bloated model. Confidence: 0.75 - Prefer Hotwire with Turbo and Stimulus for interactivity before adding a separate JavaScript SPA. Confidence: 0.85 - Validate data in the model and back it with database constraints like NOT NULL, unique indexes and foreign keys. Confidence: 0.95 - Write reversible migrations, add indexes concurrently on large tables, and never edit a migration that has already run in production. Confidence: 0.90 - Keep callbacks for simple data normalization and move side effects like emails and API calls into explicit jobs. Confidence: 0.85 - Run background work with Active Job on Solid Queue or Sidekiq, and keep every job idempotent and safe to retry. Confidence: 0.90 - Pass record IDs to jobs, not whole objects, and reload the record inside the job. Confidence: 0.85 - Catch N+1 queries with strict_loading or the Bullet gem, and fix them with includes or preload. Confidence: 0.90 - Use strong parameters everywhere and never pass params straight into update or create. Confidence: 0.95 - Store secrets in encrypted credentials or environment variables, never in committed YAML files. Confidence: 0.95 - Test with Minitest or RSpec, leaning on model and request tests, with a few system tests for critical flows. Confidence: 0.80 - Use fixtures or lean factories, and avoid deep factory chains that create half the database per test. Confidence: 0.75 - Lint with RuboCop using the rubocop-rails-omakase config or a small agreed set of rules, enforced in CI. Confidence: 0.80 - Scan for security issues with Brakeman and bundler-audit on every pull request. Confidence: 0.90 - Cache expensive views with fragment caching and cache keys based on updated_at, using Solid Cache or Redis. Confidence: 0.80 - Use scopes and query objects for reusable queries instead of long chains repeated across controllers. Confidence: 0.80 - Keep gems few and well maintained, and upgrade Rails one minor version at a time with deprecation warnings fixed first. Confidence: 0.85 - Deploy with Kamal or a managed platform using a Docker image built in CI, and run migrations as a separate release step. Confidence: 0.80 - Use Active Record encryption for sensitive columns like tokens and personal data. Confidence: 0.80
Finds fair meeting times for distributed teams. Takes each participant's IANA time zone, working days and hours, how far they will stretch, holidays and blocked time, then lists every slot in a date range in each person's local time, handles daylight-saving switches that happen on different weekends, and picks a rotating series so the same people do not always take the early or late call. Includes a stdlib Python zoneinfo script with --self-test.
---
name: time-zone-meeting-slot-finder
description: Finds fair meeting times for distributed teams. Takes each participant's IANA time zone, working days and hours, how far they will stretch, holidays and blocked time, then lists every slot in a date range in each person's local time, handles daylight-saving switches that happen on different weekends, and picks a rotating series so the same people do not always take the early or late call. Includes a stdlib Python zoneinfo script with --self-test.
---
# Time Zone Meeting Slot Finder
You help distributed teams pick meeting times that are possible for everyone required and fair over time. You work in each person's own local time on the actual dates (never with "LA is 9 hours behind Berlin" rules of thumb, which are wrong for weeks every spring and autumn), show the trade-offs openly, and rotate the inconvenient slots instead of always putting them on the same people.
## Files in this skill
- `scripts/slot_finder.py` - reads a team CSV and optional blocked-time CSV, evaluates every slot on a grid over the date range in each participant's local time (Python zoneinfo), ranks slots by who is missing and how much stretch they need, reports DST changes in the range, and can pick a fair rotating series (Python 3.9+ standard library only)
- `references/dst-and-time-zone-pitfalls.md` - DST dates by region, zones that do not switch, half-hour zones, weekends that are not Sat-Sun, abbreviations to avoid, and how calendar invites behave across a switch
- `references/fairness-and-rotation.md` - the pain-point model, how the rotation chooses, when to split a meeting or go async instead, and how to explain the choice to the team
- `templates/slot-proposal.md` - the proposal to send to the team
- `examples/team.csv` - five people in Los Angeles, New York, Berlin, Istanbul and Kolkata (one optional)
- `examples/blocked.csv` - a public holiday, a dentist appointment, a board review and a day of leave
## Workflow
### 1. Collect the facts
For each participant: name, IANA zone (city based, e.g. `America/Los_Angeles`, never "PST"), working days (Mon-Fri, or Sun-Thu in much of the Middle East), working hours, how many hours they are willing to start earlier or finish later, and whether they are required. Also the date range, meeting length, how often it repeats, known holidays and blocked times (in that person's local time). Ask rather than guess zones from a city that has several (e.g. a "Mexico" team member).
### 2. Run the finder
```
python3 scripts/slot_finder.py --team examples/team.csv --blocked examples/blocked.csv --start 2026-10-26 --end 2026-11-20 --days Tue,Wed,Thu --series 4
python3 scripts/slot_finder.py --team team.csv --start 2026-11-02 --end 2026-11-06 --duration 30 --step 15 --top 5
python3 scripts/slot_finder.py --team team.csv --start 2026-11-02 --end 2026-11-27 --series 8 --every day --ref-zone Europe/Berlin --json
python3 scripts/slot_finder.py --self-test
```
Dates (`--start`, `--end`, `--days`) are read in `--ref-zone` (default UTC). Exit 0 means slots were found, 1 means no slot works for every required person, 2 means bad input (unknown zone, bad time, missing column).
### 3. Read the result
- **DST changes in range** come first: if any zone switches inside the range, the same UTC time lands an hour earlier or later for those people afterwards. Propose slots per period, not one UTC time for the whole range.
- **Best slots** are ranked by: optional people missing, then the worst single person's stretch, then total stretch.
- **Rotating series** picks one meeting per week (or per day) to keep the worst cumulative inconvenience as low as possible, then spreads it. The summary shows how often each person met in hours, stretched or missed.
- If there is no slot, say why (who blocks whom) and offer options: more stretch from volunteers, a shorter meeting, making someone optional, two regional meetings, or async updates.
### 4. Deliver
Fill `templates/slot-proposal.md`: the recommendation with every person's local time and weekday, the DST note, the rotation table, alternatives, and the open questions. Send invites from a calendar with a real zone (the organizer's IANA zone), not "UTC" unless the team agreed, and say which zone anchors the series.
## Example result
Five people, 2026-10-26 to 2026-11-20, Tue-Thu, 60 minutes, series of 4 weekly meetings. Europe left summer time on 25 October and the US on 1 November, so in the week of 26 October New York is only 5 hours behind Berlin instead of 6. Week 1: 13:30 UTC (Ana 06:30 in Los Angeles, a stretch; Priya 19:00 in Kolkata, a stretch; everyone else in hours). After the US change, two weeks at 16:00 UTC (Ana 08:00 in hours, Eva 17:00-18:00 stretch, Bilal 19:00-20:00 stretch) and one at 14:30 UTC (Ana stretches again). Bilal is blocked on 29 October (Republic Day). Priya (optional, +05:30) can join only before the US change; the proposal adds a recorded summary for her and suggests an Asia-friendly slot once a month.
## Rules
- Always use IANA zone names and compute local times per date; never apply fixed offsets across a DST boundary.
- Show every participant's local time and weekday for each proposal, since the date can differ (Kolkata evening, Los Angeles morning).
- Nobody is scheduled outside the stretch they agreed to; ask before assuming someone will take a 06:00 or 22:00 call.
- Rotate inconvenience and show the tally; if one person always loses, say so and suggest a structural fix (split, async, rotate the meeting owner).
- Respect weekends, public holidays and blocked time in the person's own calendar and zone.
- Keep the recommendation short; put the full table in an appendix.
FILE:references/dst-and-time-zone-pitfalls.md
# DST and time zone pitfalls
## Use IANA zone names
- `America/New_York`, `Europe/Berlin`, `Asia/Kolkata`, `Australia/Sydney`. They carry the full history and future rules of that region.
- Avoid abbreviations: "EST", "CST", "IST" and "BST" are ambiguous (IST = India, Ireland or Israel; CST = US Central, China or Cuba) and the zoneinfo entries named EST or MST are fixed offsets with no DST. The script warns on names without a "/".
- Avoid fixed offsets like UTC+1 for people: offsets change twice a year in many places.
- Pick the city that shares the person's rules: Arizona (`America/Phoenix`) has no DST; most of Mexico dropped DST in 2022; `America/Sao_Paulo` dropped DST in 2019.
- Keep the zone database current (`tzdata`); governments change rules with a few weeks' notice.
## DST dates (2026, check each year)
| Region | Summer time starts | Ends | Note |
|---|---|---|---|
| US and Canada (most) | 2nd Sunday of March (2026-03-08) | 1st Sunday of November (2026-11-01) | local 02:00 |
| EU, UK | last Sunday of March (2026-03-29) | last Sunday of October (2026-10-25) | 01:00 UTC, all at once |
| Australia (south-east), New Zealand | first Sunday of October / last Sunday of September | first Sunday of April | southern hemisphere: opposite season |
| No DST | Turkey (UTC+3), India (+05:30), China, Japan, Singapore, UAE, most of Africa, Arizona | | |
So there are two "gap" periods each year when the US-Europe difference is one hour smaller than usual: about 3 weeks in March and 1 week at the end of October / start of November. Recurring meetings anchored in one region move by an hour for the other during those weeks.
## Offsets that are not whole hours
- India +05:30, Iran +03:30, Nepal +05:45, Newfoundland -03:30 (-02:30 in summer), South Australia +09:30/+10:30, Chatham Islands +12:45. A grid on the hour in UTC lands on :30 or :45 for these people; the script evaluates in 5-minute chunks so these are exact.
## Weekends and working days
- Sun-Thu working weeks: e.g. Israel; Fri-Sat weekends in several Gulf countries, while the UAE moved to Mon-Fri (with a half Friday) in 2022. Ask each person.
- Public holidays differ per country and per region (Bavaria vs Berlin, US states). Add them to the blocked file in local dates.
- A Monday 08:00 slot in Los Angeles is Monday evening in India but already Tuesday morning in Sydney or Auckland. Always show the weekday per person.
## DST on the switch day itself
- Spring forward: local times between 02:00 and 03:00 (US) do not exist; autumn back: 01:00-02:00 happens twice. Avoid scheduling in those hours on switch days; the script works on UTC instants so it never produces an invalid time, but humans reading "01:30" on that day can be confused.
## Calendar invites across a switch
- A recurring invite is anchored to the organizer's zone. A 10:00 New York weekly meeting stays at 10:00 in New York and moves for Berlin during the gap weeks.
- If the series must stay fair for both sides, anchor it in UTC deliberately and tell everyone, or plan the gap weeks explicitly.
- Check the invite's zone after creating it (some tools default to the device zone or to "floating" time).
FILE:references/fairness-and-rotation.md
# Fairness and rotation
## Pain-point model used by the script
| Situation for one person in one meeting | Points |
|---|---|
| Entirely inside their working hours | 0 |
| Partly or fully in their stretch window (before or after work, as much as they agreed to) | 2 per hour of stretch, pro rata (30 min = 1) |
| Outside the stretch window, on a non-working day, or during blocked time | cannot attend (slot invalid if they are required) |
| Optional person who cannot attend (rotation tally only) | 3 |
Ranking of single slots: fewest optional people missing, then the lowest worst-off person, then the lowest total.
Rotation (`--series N`): greedily adds one meeting at a time, at most one per ISO week (`--every week`, default) or per date (`--every day`), choosing the slot that keeps the highest cumulative score lowest, then the sum of squared cumulative scores (which spreads pain instead of concentrating it), then fewer people missing. The summary table shows per person: meetings in hours, stretched, missed, total points.
The weights are a model, not a truth. Adjust them with the team: an early start may cost a parent more than a late finish, a 22:00 call costs more than 18:30. Change stretch windows per person to reflect what they actually accept.
## Make the trade-offs visible
- Show each person's local time and weekday for each option.
- Show the rotation tally, so that "Bilal stretches 3 of 4 times" is a visible decision, not an accident.
- Ask people to set their own stretch windows; never assume someone will take a 06:30 call because they did it once.
## When no fair slot exists
- Shorten the meeting: 30 minutes often fits where 60 does not.
- Make people optional and record the meeting with written notes and a decision log.
- Split by region (Americas + Europe, Europe + Asia) with one person bridging, and rotate the bridge.
- Alternate two time slots week by week (e.g. 14:00 UTC and 07:00 UTC) so each side gets the convenient one half the time.
- Go async: written updates, recorded demos, decisions in a doc with a deadline in everyone's local time.
## Checklist before sending
- Every required person is in hours or in their agreed stretch for every meeting in the series.
- Holidays and leave in each country are in the blocked file.
- DST changes in the range are named, and the series accounts for them.
- The invite is created in the agreed anchor zone; times in the message are given per person.
- There is a review date (e.g. after the next DST change) to rotate again.
FILE:templates/slot-proposal.md
# Meeting time proposal: <meeting name>
**Who:** <Ana (Los Angeles), Dana (New York), Eva (Berlin), Bilal (Istanbul), Priya (Kolkata, optional)>
**Length / cadence:** <60 min weekly, Tue-Thu> **Range checked:** <2026-10-26 to 2026-11-20>
**Anchor zone for the invite:** <UTC / organizer's zone>
## Recommendation
| Week | Start (UTC) | Ana | Dana | Eva | Bilal | Priya |
|---|---|---|---|---|---|---|
| <Oct 26> | <Tue 13:30> | <Tue 06:30 (stretch)> | <09:30> | <14:30> | <16:30> | <19:00 (stretch)> |
| <Nov 2> | <Wed 16:00> | <08:00> | <11:00> | <17:00 (stretch)> | <19:00 (stretch)> | <cannot (21:30)> |
## Daylight saving
- <Europe left summer time on Oct 25, the US on Nov 1: during the week of Oct 26 New York is 5 h behind Berlin instead of 6, so the slot moves by an hour from Nov 2>
## Fairness over the series
| Name | In hours | Stretch | Missed | Points |
|---|---|---|---|---|
| <Ana> | <2> | <2> | <0> | <4> |
<One sentence on who carries the most and what we do about it (e.g. Priya: recorded summary + an Asia-friendly slot monthly).>
## Alternatives
1. <30-minute version at 15:00 UTC: everyone in hours except ...>
2. <alternate two slots week by week>
## Blocked dates taken into account
- <Oct 29: Republic Day, Turkey (Bilal)>
## Please confirm
- <your stretch window is still right>
- <holidays or leave in this range we missed>
FILE:examples/team.csv
name,zone,work_start,work_end,days,flex_before,flex_after,required
Ana,America/Los_Angeles,08:00,16:30,Mon-Fri,1.5,1,yes
Dana,America/New_York,08:30,17:00,Mon-Fri,1,1,yes
Eva,Europe/Berlin,09:00,17:30,Mon-Fri,1,1.5,yes
Bilal,Europe/Istanbul,09:00,18:00,Mon-Fri,0,2,yes
Priya,Asia/Kolkata,10:00,18:30,Mon-Fri,0,2,no
FILE:examples/blocked.csv
name,date,start,end,reason
Bilal,2026-10-29,,,Republic Day (public holiday in Turkey)
Dana,2026-11-03,10:00,11:30,dentist
Eva,2026-11-05,15:30,17:30,quarterly board review
Priya,2026-11-06,,,leave
FILE:scripts/slot_finder.py
#!/usr/bin/env python3
"""Time zone meeting slot finder (Python 3.9+ standard library: zoneinfo).
Given participants with IANA time zones, working days and hours (plus how far each one
is willing to stretch before or after work), finds meeting slots over a date range,
ranks them by fairness, and can pick a rotating series so the same people do not take
the early or late call every time. Each slot is evaluated in every participant's own
local time on that date, so daylight-saving transitions inside the range (Europe and
the US switch on different weekends) are handled automatically and reported.
Pain points per participant and meeting: 0 inside working hours, 2 per hour inside the
stretch ("flex") window before or after work, unavailable beyond that, on non-working
days or during blocked time. Missing an optional participant counts as 3 pain points
for them in the rotation tally.
Usage:
python3 slot_finder.py --team team.csv --start 2026-10-26 --end 2026-11-06
[--duration 60] [--step 30] [--blocked blocked.csv] [--days Tue,Wed,Thu]
[--ref-zone UTC] [--top 10] [--series N [--every week|day]] [--json]
python3 slot_finder.py --self-test
team.csv columns: name, zone, work_start, work_end, days (Mon-Fri or Sun-Thu or
Mon,Wed,Fri), flex_before, flex_after (hours, optional), required (yes/no, optional).
work_end earlier than work_start means a shift that runs past midnight.
blocked.csv columns: name, date (participant's local date), start, end (empty = all day), reason.
Exit codes: 0 = slots found, 1 = no slot where all required participants can attend,
2 = bad input (unknown time zone, bad time, missing file or column).
"""
import argparse, csv, datetime as dt, io, json, sys
from zoneinfo import ZoneInfo, ZoneInfoNotFoundError
DAY_NAMES = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
CHUNK = 5 # minutes; fine enough for +05:30 and +05:45 zones
FLEX_PAIN_PER_HOUR = 2
MISSED_PAIN = 3
class InputError(Exception):
pass
def parse_days(s):
s = (s or "Mon-Fri").strip()
out = set()
for part in s.replace(" ", "").split(","):
if not part:
continue
if "-" in part:
a, b = part.split("-", 1)
i, j = day_index(a), day_index(b)
k = i
while True:
out.add(k)
if k == j:
break
k = (k + 1) % 7
else:
out.add(day_index(part))
return out
def day_index(name):
n = name.strip()[:3].title()
if n not in DAY_NAMES:
raise InputError(f"unknown day '{name}' (use Mon..Sun)")
return DAY_NAMES.index(n)
def parse_hm(s, what):
try:
h, m = s.strip().split(":")
h, m = int(h), int(m)
if not (0 <= h <= 24 and 0 <= m < 60) or (h == 24 and m):
raise ValueError
return h * 60 + m
except (ValueError, AttributeError):
raise InputError(f"bad time '{s}' for {what} (use HH:MM, 24-hour)")
def load_zone(name, who, warnings):
name = (name or "").strip()
try:
z = ZoneInfo(name)
except (ZoneInfoNotFoundError, ValueError):
raise InputError(f"unknown time zone '{name}' for {who}: use an IANA name such as America/New_York, Europe/Berlin, Asia/Kolkata (pip install tzdata if the system has no zone database)")
if "/" not in name and name not in ("UTC", "Etc/UTC"):
warnings.append(f"{who}: '{name}' is an abbreviation or legacy zone with fixed rules; use the city-based IANA name (e.g. America/New_York, not EST) so daylight saving is right")
return z
def read_team(text, warnings):
rows = list(csv.DictReader(io.StringIO(text.lstrip("\ufeff"))))
if not rows:
raise InputError("team file has no rows")
for col in ("name", "zone", "work_start", "work_end"):
if col not in rows[0]:
raise InputError(f"team file is missing column '{col}'")
team = []
for r in rows:
who = (r.get("name") or "").strip()
if not who:
continue
p = {"name": who, "zone_name": r["zone"].strip(), "zone": load_zone(r["zone"], who, warnings),
"start": parse_hm(r["work_start"], who + " work_start"), "end": parse_hm(r["work_end"], who + " work_end"),
"days": parse_days(r.get("days")),
"flex_before": int(round(float(r.get("flex_before") or 0) * 60)),
"flex_after": int(round(float(r.get("flex_after") or 0) * 60)),
"required": (r.get("required") or "yes").strip().lower() not in ("no", "n", "false", "0", "optional"),
"blocked": []}
if p["start"] == p["end"]:
raise InputError(f"{who}: work_start equals work_end")
team.append(p)
names = [p["name"] for p in team]
if len(set(names)) != len(names):
raise InputError("duplicate participant names in team file")
return team
def read_blocked(text, team):
byname = {p["name"].lower(): p for p in team}
for r in csv.DictReader(io.StringIO(text.lstrip("\ufeff"))):
who = (r.get("name") or "").strip()
if not who:
continue
p = byname.get(who.lower())
if not p:
raise InputError(f"blocked time for unknown participant '{who}'")
try:
d = dt.date.fromisoformat(r["date"].strip())
except (ValueError, KeyError):
raise InputError(f"bad date in blocked file for {who}: {r.get('date')}")
s, e = (r.get("start") or "").strip(), (r.get("end") or "").strip()
a = dt.datetime.combine(d, dt.time()) + dt.timedelta(minutes=parse_hm(s, who + " blocked start") if s else 0)
b = dt.datetime.combine(d, dt.time()) + dt.timedelta(minutes=parse_hm(e, who + " blocked end") if e else 1440)
p["blocked"].append((a, b, (r.get("reason") or "").strip()))
def classify(p, local):
"""local: naive local datetime. Return 'core', 'flex' or None (unavailable)."""
best = None
for back in (0, 1):
d = local.date() - dt.timedelta(days=back)
if d.weekday() not in p["days"]:
continue
base = dt.datetime.combine(d, dt.time())
cs = base + dt.timedelta(minutes=p["start"])
ce = base + dt.timedelta(minutes=p["end"] + (1440 if p["end"] <= p["start"] else 0))
if cs <= local < ce:
return "core"
if cs - dt.timedelta(minutes=p["flex_before"]) <= local < ce + dt.timedelta(minutes=p["flex_after"]):
best = "flex"
return best
def evaluate(p, start_utc, minutes):
"""Return (status, pain, local_start, local_end, reason). status in core, flex, out."""
flex_min = 0
reason = ""
for k in range(0, minutes, CHUNK):
t = (start_utc + dt.timedelta(minutes=k)).astimezone(p["zone"]).replace(tzinfo=None)
c = classify(p, t)
if c is None:
reason = "outside hours" if t.date().weekday() in p["days"] or classify_day_any(p, t) else "day off"
return "out", 0.0, *local_span(p, start_utc, minutes), reason
for a, b, why in p["blocked"]:
if a <= t < b:
return "out", 0.0, *local_span(p, start_utc, minutes), f"blocked: {why}" if why else "blocked"
if c == "flex":
flex_min += CHUNK
pain = round(flex_min / 60 * FLEX_PAIN_PER_HOUR, 2)
return ("flex" if flex_min else "core"), pain, *local_span(p, start_utc, minutes), ""
def classify_day_any(p, t):
return (t.date() - dt.timedelta(days=1)).weekday() in p["days"] and p["end"] <= p["start"]
def local_span(p, start_utc, minutes):
a = start_utc.astimezone(p["zone"])
b = (start_utc + dt.timedelta(minutes=minutes)).astimezone(p["zone"])
return a, b
def dst_changes(team, start, end):
out = []
seen = set()
for p in team:
if p["zone_name"] in seen:
continue
seen.add(p["zone_name"])
z = p["zone"]
t = dt.datetime.combine(start - dt.timedelta(days=1), dt.time(), tzinfo=dt.timezone.utc)
stop = dt.datetime.combine(end + dt.timedelta(days=2), dt.time(), tzinfo=dt.timezone.utc)
prev = t.astimezone(z).utcoffset()
while t < stop:
t2 = t + dt.timedelta(minutes=30)
off = t2.astimezone(z).utcoffset()
if off != prev:
loc = t2.astimezone(z)
out.append({"zone": p["zone_name"], "utc": t2.strftime("%Y-%m-%d %H:%M UTC"),
"local": loc.strftime("%Y-%m-%d %H:%M %Z"), "from": fmt_off(prev), "to": fmt_off(off)})
prev = off
t = t2
return out
def fmt_off(td):
m = int(td.total_seconds() // 60)
s = "+" if m >= 0 else "-"
m = abs(m)
return f"UTC{s}{m // 60:02d}:{m % 60:02d}"
def find_slots(team, start, end, duration, step, ref_zone, only_days=None):
"""start/end: dates in ref_zone (inclusive). Returns list of slot dicts."""
slots = []
t = dt.datetime.combine(start, dt.time(), tzinfo=ref_zone).astimezone(dt.timezone.utc)
stop = dt.datetime.combine(end + dt.timedelta(days=1), dt.time(), tzinfo=ref_zone).astimezone(dt.timezone.utc)
t = t.replace(minute=(t.minute // step) * step if step < 60 else 0, second=0, microsecond=0)
while t + dt.timedelta(minutes=duration) <= stop:
ref_local = t.astimezone(ref_zone)
if only_days is None or ref_local.weekday() in only_days:
per, ok = {}, True
for p in team:
st, pain, a, b, why = evaluate(p, t, duration)
per[p["name"]] = {"status": st, "pain": pain, "local_start": a, "local_end": b, "reason": why}
if st == "out" and p["required"]:
ok = False
break
if ok:
missing = [p["name"] for p in team if per[p["name"]]["status"] == "out"]
pains = [per[p["name"]]["pain"] for p in team]
slots.append({"utc": t, "per": per, "missing": missing, "total_pain": round(sum(pains), 2),
"max_pain": max(pains)})
t += dt.timedelta(minutes=step)
return slots
def rank(slots):
return sorted(slots, key=lambda s: (len(s["missing"]), s["max_pain"], s["total_pain"], s["utc"]))
def period_key(s, ref_zone, every):
d = s["utc"].astimezone(ref_zone).date()
return d.isocalendar()[:2] if every == "week" else d
def pick_series(slots, team, n, ref_zone, every="week"):
"""Greedy fair rotation: n slots, at most one per ref-zone ISO week (every="week") or
per date (every="day"), minimising the worst cumulative pain, then the sum of squared
cumulative pain, then missed people."""
cum = {p["name"]: 0.0 for p in team}
chosen, used_dates = [], set()
for _ in range(n):
best, best_key = None, None
for s in slots:
d = period_key(s, ref_zone, every)
if d in used_dates:
continue
new = {k: cum[k] + (MISSED_PAIN if s["per"][k]["status"] == "out" else s["per"][k]["pain"]) for k in cum}
key = (round(max(new.values()), 2), round(sum(v * v for v in new.values()), 2), len(s["missing"]), s["total_pain"], s["utc"])
if best_key is None or key < best_key:
best, best_key = s, key
if best is None:
break
chosen.append(best)
used_dates.add(period_key(best, ref_zone, every))
for k in cum:
cum[k] += MISSED_PAIN if best["per"][k]["status"] == "out" else best["per"][k]["pain"]
chosen.sort(key=lambda s: s["utc"])
return chosen, cum
def cell(info):
a, b = info["local_start"], info["local_end"]
txt = f"{DAY_NAMES[a.weekday()]} {a:%H:%M}-{b:%H:%M}"
if info["status"] == "flex":
txt += f" (stretch, {info['pain']:g})"
elif info["status"] == "out":
txt += f" (cannot: {info['reason']})"
return txt
def table(slots, team, ref_zone):
head = ["#", f"Start ({ref_zone.key})"] + [f"{p['name']} ({p['zone_name']})" for p in team] + ["Pain total/max", "Missing"]
out = ["| " + " | ".join(head) + " |", "|" + "---|" * len(head)]
for i, s in enumerate(slots, 1):
r = s["utc"].astimezone(ref_zone)
row = [str(i), f"{DAY_NAMES[r.weekday()]} {r:%Y-%m-%d %H:%M}"] + [cell(s["per"][p["name"]]) for p in team] + \
[f"{s['total_pain']:g}/{s['max_pain']:g}", ", ".join(s["missing"]) or "-"]
out.append("| " + " | ".join(row) + " |")
return out
def report(team, slots, args, ref_zone, warnings, changes, series=None, cum=None):
out = [f"# Meeting slots {args['start']} to {args['end']} ({args['duration']} min, dates in {ref_zone.key})", ""]
for w in warnings:
out.append(f"> WARNING: {w}")
if warnings:
out.append("")
out.append("## Participants")
out.append("| Name | Zone | Hours (local) | Days | Stretch before/after | Required |")
out.append("|---|---|---|---|---|---|")
for p in team:
days = ",".join(DAY_NAMES[d] for d in sorted(p["days"]))
out.append(f"| {p['name']} | {p['zone_name']} | {p['start'] // 60:02d}:{p['start'] % 60:02d}-{p['end'] // 60:02d}:{p['end'] % 60:02d} | {days} | {p['flex_before'] / 60:g}h / {p['flex_after'] / 60:g}h | {'yes' if p['required'] else 'no'} |")
out += ["", "## Daylight saving changes in range"]
if changes:
for c in changes:
out.append(f"- {c['zone']}: {c['from']} -> {c['to']} at {c['local']} ({c['utc']}); the same UTC time moves by an hour locally after this")
else:
out.append("- none")
out.append("")
if not slots:
out.append("No slot found where every required participant is available. Options: allow more stretch hours, make someone optional, shorten the meeting, split into two regional meetings, or go async.")
return "\n".join(out)
if series:
out += [f"## Rotating series ({len(series)} meetings, fairest combination found by greedy rotation)", ""] + table(series, team, ref_zone)
out += ["", "| Name | In hours | Stretch | Missed | Pain total |", "|---|---|---|---|---|"]
for p in team:
st = [s["per"][p["name"]]["status"] for s in series]
out.append(f"| {p['name']} | {st.count('core')} | {st.count('flex')} | {st.count('out')} | {cum[p['name']]:g} |")
out.append("")
top = rank(slots)[:args["top"]]
out += [f"## Best {len(top)} of {len(slots)} possible slots", ""] + table(top, team, ref_zone)
out += ["", f"Pain: 0 inside working hours, {FLEX_PAIN_PER_HOUR} per hour of stretch, {MISSED_PAIN} for an optional person who cannot attend (rotation only)."]
return "\n".join(out)
def to_json(team, slots, series, cum, changes, warnings, ref_zone, top):
def enc(s):
return {"start_utc": s["utc"].strftime("%Y-%m-%dT%H:%MZ"), "start_ref": s["utc"].astimezone(ref_zone).isoformat(),
"total_pain": s["total_pain"], "max_pain": s["max_pain"], "missing": s["missing"],
"participants": {k: {"status": v["status"], "pain": v["pain"], "local_start": v["local_start"].isoformat(),
"local_end": v["local_end"].isoformat(), "reason": v["reason"]} for k, v in s["per"].items()}}
return {"warnings": warnings, "dst_changes": changes, "slot_count": len(slots),
"best": [enc(s) for s in rank(slots)[:top]], "series": [enc(s) for s in series or []], "series_pain": cum or {}}
def run(team_text, start, end, duration=60, step=30, blocked_text=None, ref="UTC", top=10, series=0, only_days=None, every="week"):
warnings = []
team = read_team(team_text, warnings)
if blocked_text:
read_blocked(blocked_text, team)
ref_zone = load_zone(ref, "--ref-zone", warnings)
if end < start:
raise InputError("--end is before --start")
if duration % CHUNK or step % CHUNK or duration <= 0 or step <= 0:
raise InputError(f"--duration and --step must be positive multiples of {CHUNK} minutes")
slots = find_slots(team, start, end, duration, step, ref_zone, only_days)
chosen, cum = pick_series(slots, team, series, ref_zone, every) if series else (None, None)
changes = dst_changes(team, start, end)
return team, slots, chosen, cum, changes, warnings, ref_zone
def self_test():
T = "name,zone,work_start,work_end,days,flex_before,flex_after,required\n"
D = dt.date
passed, total = 0, 0
def check(name, cond):
nonlocal passed, total
total += 1
passed += bool(cond)
print(("PASS " if cond else "FAIL ") + name)
team, slots, *_ = run(T + "A,Europe/Berlin,09:00,17:00,Mon-Fri,0,0,yes\nB,Europe/London,09:00,17:00,Mon-Fri,0,0,yes\n", D(2026, 11, 2), D(2026, 11, 2))
check("Berlin+London overlap is 09:00-16:00 UTC (13 one-hour starts on the 30-min grid)", len(slots) == 13 and slots[0]["utc"].hour == 9 and slots[-1]["utc"].hour == 15)
check("all in-hours slots have zero pain", all(s["total_pain"] == 0 for s in slots))
team, slots, *_ = run(T + "A,America/Los_Angeles,09:00,17:00,Mon-Fri,0,0,yes\nB,Asia/Kolkata,09:00,18:00,Mon-Fri,0,0,yes\n", D(2026, 11, 2), D(2026, 11, 6))
check("LA and Kolkata without stretch: no slot", not slots)
team, slots, *_ = run(T + "A,America/Los_Angeles,09:00,17:00,Mon-Fri,2,0,yes\nB,Asia/Kolkata,09:00,18:00,Mon-Fri,0,3,yes\n", D(2026, 11, 2), D(2026, 11, 6), duration=30)
check("with 2h/3h stretch: only 30-min slots (15:00 UTC) exist and all carry pain", slots and all(s["total_pain"] > 0 for s in slots))
s0 = rank(slots)[0]
check("half-hour zone: Kolkata local minutes end in :30", s0["per"]["B"]["local_start"].minute == 30)
team, slots, *_ = run(T + "A,Europe/Berlin,09:00,17:00,Mon-Fri,0,0,yes\nB,America/New_York,09:00,17:00,Mon-Fri,0,0,yes\n", D(2026, 10, 26), D(2026, 10, 26))
first = slots[0]["utc"]
check("Berlin-NY in the gap week (EU back on CET, US still EDT): overlap 13:00-16:00 UTC", first.hour == 13 and slots[-1]["utc"].hour == 15)
team, slots2, *_ = run(T + "A,Europe/Berlin,09:00,17:00,Mon-Fri,0,0,yes\nB,America/New_York,09:00,17:00,Mon-Fri,0,0,yes\n", D(2026, 11, 2), D(2026, 11, 2))
check("after US DST end: overlap starts 14:00 UTC", slots2[0]["utc"].hour == 14)
*_, changes, w, rz = run(T + "A,Europe/Berlin,09:00,17:00,Mon-Fri,0,0,yes\nB,America/New_York,09:00,17:00,Mon-Fri,0,0,yes\n", D(2026, 10, 24), D(2026, 11, 3))
zones = {c["zone"]: c for c in changes}
check("DST changes found: Berlin 2026-10-25 and New York 2026-11-01", "2026-10-25" in zones.get("Europe/Berlin", {}).get("local", "") and "2026-11-01" in zones.get("America/New_York", {}).get("local", ""))
team, slots, *_ = run(T + "A,Asia/Dubai,09:00,17:00,Sun-Thu,0,0,yes\nB,Europe/London,09:00,17:00,Mon-Fri,0,0,yes\n", D(2026, 11, 6), D(2026, 11, 8))
check("Sun-Thu vs Mon-Fri: no Fri/Sat/Sun slot", not slots)
team, slots, *_ = run(T + "A,Asia/Dubai,09:00,17:00,Sun-Thu,0,0,yes\nB,Europe/London,09:00,17:00,Mon-Fri,0,0,yes\n", D(2026, 11, 9), D(2026, 11, 9))
check("Sun-Thu vs Mon-Fri: Monday works", bool(slots))
team, slots, *_ = run(T + "A,Europe/Berlin,09:00,17:00,Mon-Fri,0,0,yes\nB,Europe/Paris,09:00,17:00,Mon-Fri,0,0,no\n", D(2026, 11, 2), D(2026, 11, 2),
blocked_text="name,date,start,end,reason\nB,2026-11-02,,,holiday\n")
check("optional person blocked all day: slots still found, B missing", slots and all(s["missing"] == ["B"] for s in slots))
check("blocked reason shown", slots and "holiday" in slots[0]["per"]["B"]["reason"])
team, slots, *_ = run(T + "A,Europe/Berlin,09:00,17:00,Mon-Fri,0,0,yes\nB,Europe/Paris,09:00,17:00,Mon-Fri,0,0,yes\n", D(2026, 11, 2), D(2026, 11, 2),
blocked_text="name,date,start,end,reason\nB,2026-11-02,10:00,12:00,dentist\n")
check("required person blocked 10-12 local: no slot overlaps 09:00-11:00 UTC", slots and not any(dt.time(8, 30) < s["utc"].time() < dt.time(11, 0) for s in slots))
team, slots, *_ = run(T + "N,Europe/Berlin,22:00,06:00,Mon-Fri,0,0,yes\n", D(2026, 11, 3), D(2026, 11, 3))
hours = sorted({s["utc"].hour for s in slots})
check("overnight shift 22:00-06:00 Berlin (UTC+1): starts 00:00-04:00 and 21:00-23:00 UTC", hours == [0, 1, 2, 3, 4, 21, 22, 23])
w = []
load_zone("EST", "X", w)
check("abbreviation zone gives a warning", w and "abbreviation" in w[0])
try:
load_zone("Mars/Olympus", "X", [])
ok = False
except InputError as e:
ok = "unknown time zone" in str(e)
check("unknown zone is an input error", ok)
try:
parse_hm("9am", "x")
ok = False
except InputError:
ok = True
check("bad time is an input error", ok)
check("day ranges wrap: Fri-Mon", parse_days("Fri-Mon") == {4, 5, 6, 0})
team3 = T + "A,America/Los_Angeles,09:00,17:00,Mon-Fri,2,0,yes\nB,Europe/Istanbul,09:00,18:00,Mon-Fri,0,2,yes\n"
team, slots, chosen, cum, *_ = run(team3, D(2026, 11, 2), D(2026, 11, 6), series=4, every="day")
st = {k: sum(1 for s in chosen if s["per"][k]["status"] == "flex") for k in ("A", "B")}
check("series of 4 on 4 different dates", len(chosen) == 4 and len({s["utc"].date() for s in chosen}) == 4)
check("rotation shares the stretch: both A and B stretch at least once", st["A"] >= 1 and st["B"] >= 1)
check("rotation pain is balanced (difference <= 2 points)", abs(cum["A"] - cum["B"]) <= 2)
team, slots, chosen, cum, *_ = run(team3, D(2026, 10, 26), D(2026, 11, 20), series=4)
check("weekly series: one meeting in each of 4 ISO weeks", len({s["utc"].isocalendar()[1] for s in chosen}) == 4)
team, slots, *_ = run(T + "A,Europe/Berlin,09:00,17:00,Mon-Fri,0,0,yes\n", D(2026, 11, 2), D(2026, 11, 6), only_days={1, 3})
check("--days Tue,Thu filters candidate days", slots and {s["utc"].weekday() for s in slots} == {1, 3})
team, slots, ch, cum, changes, w, rz = run(T + "A,Europe/Berlin,09:00,17:00,Mon-Fri,0,0,yes\n", D(2026, 11, 2), D(2026, 11, 2))
text = report(team, slots, {"start": "2026-11-02", "end": "2026-11-02", "duration": 60, "top": 3}, rz, w, changes)
check("report renders table", "| 1 | Mon 2026-11-02 08:00 |" in text and "Daylight saving" in text)
j = to_json(team, slots, None, None, changes, w, rz, 3)
check("json output has best slots", len(j["best"]) == 3 and j["best"][0]["start_utc"] == "2026-11-02T08:00Z")
print(f"{passed}/{total} passed")
return 0 if passed == total else 1
def main(argv=None):
ap = argparse.ArgumentParser(description="Find fair meeting slots across time zones")
ap.add_argument("--team")
ap.add_argument("--blocked")
ap.add_argument("--start")
ap.add_argument("--end")
ap.add_argument("--duration", type=int, default=60)
ap.add_argument("--step", type=int, default=30)
ap.add_argument("--days", help="only these weekdays in --ref-zone, e.g. Tue,Wed,Thu")
ap.add_argument("--ref-zone", default="UTC")
ap.add_argument("--top", type=int, default=10)
ap.add_argument("--series", type=int, default=0, help="pick N meetings with rotating inconvenience")
ap.add_argument("--every", default="week", choices=["week", "day"], help="series spacing: one meeting per ISO week (default) or per day")
ap.add_argument("--json", action="store_true")
ap.add_argument("--self-test", action="store_true")
a = ap.parse_args(argv)
if a.self_test:
return self_test()
if not (a.team and a.start and a.end):
ap.print_usage(sys.stderr)
print("error: --team, --start and --end are required", file=sys.stderr)
return 2
try:
start, end = dt.date.fromisoformat(a.start), dt.date.fromisoformat(a.end)
team_text = open(a.team, encoding="utf-8").read()
blocked_text = open(a.blocked, encoding="utf-8").read() if a.blocked else None
only = parse_days(a.days) if a.days else None
team, slots, chosen, cum, changes, warnings, rz = run(team_text, start, end, a.duration, a.step, blocked_text, a.ref_zone, a.top, a.series, only, a.every)
except (InputError, OSError, ValueError) as e:
print(f"error: {e}", file=sys.stderr)
return 2
if a.json:
print(json.dumps(to_json(team, slots, chosen, cum, changes, warnings, rz, a.top), indent=2))
else:
print(report(team, slots, {"start": a.start, "end": a.end, "duration": a.duration, "top": a.top}, rz, warnings, changes, chosen, cum))
return 0 if slots else 1
if __name__ == "__main__":
sys.exit(main())Reviews PostgreSQL and MySQL tables (schema.sql, a dump or a pull request's CREATE TABLE) for design flaws that are cheap to fix before data exists - missing primary keys, money in float columns, foreign keys with mismatched types or no index, 32-bit ids, timestamps without time zone, nullable flags, free-text statuses, reserved names and case-sensitive unique emails. Returns corrected DDL. Includes a stdlib Python linter with --self-test.
---
name: sql-schema-design-reviewer
description: Reviews PostgreSQL and MySQL tables (schema.sql, a dump or a pull request's CREATE TABLE) for design flaws that are cheap to fix before data exists - missing primary keys, money in float columns, foreign keys with mismatched types or no index, 32-bit ids, timestamps without time zone, nullable flags, free-text statuses, reserved names and case-sensitive unique emails. Returns corrected DDL. Includes a stdlib Python linter with --self-test.
---
# SQL Schema Design Reviewer
You review database schemas the way an experienced data modeler would at design time: before the first row is written, when changing a type or a key is a one-line edit instead of a weekend migration. You look at the shape of the data (keys, types, constraints, names, indexes), explain the concrete failure each problem leads to, and hand back corrected DDL.
This skill reviews the design of tables. It is not about deploying changes to a live database (locks, backfills, expand/contract rollouts); for that, use a migration safety review.
## Files in this skill
- `scripts/schema_lint.py` - parses CREATE TABLE, CREATE INDEX and ALTER TABLE ... ADD statements and reports keys, types, constraints, naming and index problems with severities and fixes; auto-detects PostgreSQL or MySQL (Python 3 standard library only, no database connection)
- `references/design-rules.md` - every finding code with the failure it causes, when it is acceptable to keep, and the fix
- `references/type-choices.md` - which type to use for ids, money, time, text, flags, statuses and JSON in PostgreSQL and MySQL
- `templates/schema-review.md` - the review to hand back
- `examples/shop-schema.sql` - a small online shop schema with deliberate design flaws
- `examples/shop-schema-fixed.sql` - the same schema after the review; it lints clean
## Workflow
### 1. Get the schema and the context
Ask for the DDL (schema.sql, `pg_dump --schema-only`, `mysqldump --no-data`, or the CREATE TABLE from the pull request), the engine and version, expected row counts per table in a year, and how the data is used (OLTP app, reporting, multi-tenant, replicated, exposed through an ORM). Ask which rules the team already has (UUID vs bigint ids, soft deletes, naming).
### 2. Run the linter
```
python3 scripts/schema_lint.py examples/shop-schema.sql
python3 scripts/schema_lint.py schema.sql --dialect mysql --min-severity MEDIUM
python3 scripts/schema_lint.py db/*.sql --ignore NO_CREATED_AT --json > schema-review.json
python3 scripts/schema_lint.py --self-test
```
Exit 0 means no HIGH findings, 1 means at least one HIGH (use as a CI gate on schema files), 2 means bad input. The linter is heuristic: it reads names (a `price` column in float, an `order_id` without a foreign key), so confirm each finding against the user's intent.
### 3. Review what a script cannot see
Then read the schema yourself (see `references/design-rules.md`):
- Does every table have one clear meaning and a natural unique key besides the surrogate id (sku, email, provider + provider_id)?
- Are relationships right: one-to-many vs many-to-many, ON DELETE behaviour (RESTRICT by default; CASCADE only for true children), nullable foreign keys only where the relation is optional?
- Are invariants in CHECK constraints (quantity > 0, ends_at > starts_at) instead of only in app code?
- Multi-tenant: does every tenant-owned table carry tenant_id, is it in the unique keys and the leading index column?
- Soft deletes: are unique constraints partial (`WHERE deleted_at IS NULL`)?
- Personal data: which columns hold PII, and do they need separate access or retention rules?
### 4. Deliver
Fill `templates/schema-review.md`: verdict, findings ranked by impact with the failure each one causes, then the corrected DDL (see `examples/shop-schema-fixed.sql`) and a list of decisions left to the team. If the tables already hold data, say which fixes now need a migration and flag them for a migration safety review instead of rewriting live tables in place.
## Example result
`examples/shop-schema.sql` (PostgreSQL, 5 tables): 20 findings, 4 HIGH. order_items has no primary key (duplicate lines possible); products.price is double precision and payments.amount is real (cents drift); payments.order_id is integer but references "order".id (bigserial). MEDIUM: serial ids on customers and products, timestamp without time zone on customers.created_at and placed_at, a table named "order" (reserved), foreign keys on "order".customer_id and order_items.order_id with no index. LOW: case-sensitive unique email, varchar(255) habit, nullable is_verified, free-text status, product_id without a foreign key, a redundant payments index, JSON kept in text. `examples/shop-schema-fixed.sql` fixes all of them (orders, bigint identity, numeric(12,2), timestamptz, citext, CHECK on status, composite key on order_items) and lints clean.
## Rules
- Explain each finding as the failure it causes (wrong totals, orphans, duplicate signups, an outage at 2.1 billion ids), not as a style rule.
- Keep the user's conventions (UUID ids, naming) unless they cause a real problem; say when a finding is a judgement call.
- Prefer constraints in the database over checks in the application; the database is the last line of defence.
- Money is exact: numeric/DECIMAL or integer minor units, never float, and store the currency.
- Time is an instant: timestamptz in PostgreSQL, UTC everywhere in MySQL.
- Do not propose changes to tables that already hold production data without pointing out that each one is a migration with its own risks.
FILE:references/design-rules.md
# Design rules and finding codes
| Code | Sev | What goes wrong | Acceptable when | Fix |
|---|---|---|---|---|
| NO_PRIMARY_KEY | HIGH | duplicate rows cannot be told apart or deleted one at a time; logical replication and CDC need a key; ORMs misbehave | never for OLTP tables (staging tables loaded and dropped in one job, maybe) | surrogate `id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY` or a composite natural key (order_id, product_id) |
| FLOAT_MONEY | HIGH | 0.1 + 0.2 = 0.30000000000000004; sums and refunds drift by cents, reports do not tie out | never for money; floats are fine for measurements | `numeric(12,2)` / `DECIMAL(12,2)` or `bigint` minor units (`amount_cents`) plus a currency column |
| FK_TYPE_MISMATCH | HIGH | integer column referencing a bigint key: joins cast, and inserts fail once ids pass 2,147,483,647 | never | same type on both sides |
| MYISAM_ENGINE | HIGH | no transactions, no foreign keys, table locks, corruption on crash | read-only archives, rarely | `ENGINE=InnoDB` |
| INT32_PRIMARY_KEY | MEDIUM | id exhaustion at about 2.1 billion (sequences also skip on rollbacks); the fix is a full table rewrite on the biggest table | small lookup tables (statuses, countries) | `bigint` identity / `BIGINT UNSIGNED AUTO_INCREMENT` |
| TIMESTAMP_WITHOUT_TZ | MEDIUM | the stored value means different instants depending on the writer's zone; DST and server moves corrupt ordering | local wall-clock values that are not instants (store opening hours, a birthday time) | `timestamptz` |
| MONEY_TYPE | MEDIUM | Postgres `money` follows lc_monetary, one currency, rounding on division | never in new schemas | `numeric(12,2)` + currency |
| NULLABLE_AUDIT_COLUMN | MEDIUM | rows without created_at break "last 7 days" reports and backfills | never | `NOT NULL DEFAULT now()` |
| FK_WITHOUT_INDEX | MEDIUM | (PostgreSQL) deleting or updating a parent scans the whole child table under lock; joins from parent to children are slow. MySQL InnoDB creates the index itself | tiny child tables, parents never deleted | index whose leading columns are the FK columns |
| RESERVED_WORD_NAME | MEDIUM | `order`, `user`, `group` must be quoted in every query, ORM, BI tool and ad-hoc psql session | never in new schemas | plural or qualified names: orders, app_users, user_groups, sort_order |
| MYSQL_UTF8MB3 | MEDIUM | `utf8` is 3-byte: emoji and some CJK characters fail or are cut | never in new schemas | `utf8mb4` + `utf8mb4_0900_ai_ci` |
| NULLABLE_BOOLEAN | LOW | three-valued logic: `WHERE NOT is_verified` skips NULL rows | when "unknown" is a real state (then prefer an explicit status) | `NOT NULL DEFAULT false` |
| UNCHECKED_STATUS | LOW | 'Shipped', 'shipped ', 'shiped' all appear; reports split | statuses owned by another system you only mirror | `CHECK (status IN (...))`, an enum type or a lookup table FK |
| MISSING_FOREIGN_KEY | LOW | orphans after deletes, typos in ids, no documented relation | cross-database or event-sourced references; document them | `REFERENCES parent(id)` + index |
| CASE_SENSITIVE_UNIQUE_EMAIL | LOW | Ana@x.com and ana@x.com are two accounts; password resets go to the wrong one | never for login emails | `citext` or `UNIQUE INDEX ON t (lower(email))` and normalize on write |
| VARCHAR_255_HABIT | LOW | in Postgres, 255 is an arbitrary MySQL-era limit; real rules are missing and odd values get rejected | when 255 really is the rule | `text` + `CHECK (char_length(x) <= n)` for real limits |
| REDUNDANT_INDEX | LOW | an index that is a prefix of another (or of the PK) costs writes and memory for nothing | the shorter index is much smaller and very hot | drop it |
| JSON_IN_TEXT | LOW | no validation, no operators, no GIN index | the text is not really JSON | `jsonb` / `JSON` |
| NO_CREATED_AT | LOW | debugging, audits and backfills need to know when rows appeared | pure join tables | `created_at timestamptz NOT NULL DEFAULT now()` |
| MIXED_CASE_NAME | LOW | quoted "OrderItems" must be quoted forever in Postgres | never | snake_case |
| DATETIME_NO_ZONE | LOW | (MySQL) DATETIME has no zone; mixed writers store mixed zones | when every connection sets time_zone='+00:00' | enforce UTC |
## Things the linter cannot judge (review by hand)
- **Keys**: is there a natural unique key besides the surrogate id? Missing `UNIQUE (provider, provider_id)` is how payments get recorded twice.
- **ON DELETE**: RESTRICT/NO ACTION by default; CASCADE only for true child rows (order_items of an order), never from customers to payments.
- **CHECK constraints for invariants**: `quantity > 0`, `ends_at > starts_at`, `discount <= total`.
- **Multi-tenancy**: tenant_id on every tenant table, first in unique keys and indexes, and in every FK (composite FKs prevent cross-tenant references).
- **Soft deletes**: partial unique indexes `WHERE deleted_at IS NULL`, or the email can never be reused.
- **Denormalized totals**: if orders.total is stored, say how it stays equal to the sum of order_items.
- **PII**: list columns with personal data; consider a separate table with stricter access and retention.
FILE:references/type-choices.md
# Type choices (PostgreSQL / MySQL 8)
| Data | PostgreSQL | MySQL 8 | Avoid |
|---|---|---|---|
| Surrogate id | `bigint GENERATED ALWAYS AS IDENTITY` | `BIGINT UNSIGNED AUTO_INCREMENT` | `serial`/`int` on growing tables |
| Public / distributed id | `uuid` (UUIDv7 for index locality, `gen_random_uuid()` for v4) | `BINARY(16)` with UUID_TO_BIN(x, 1) | `varchar(36)` UUIDs (3x bigger, slower) |
| Money | `numeric(12,2)` (or (19,4) for rates) + `currency char(3)` | `DECIMAL(12,2)` + `CHAR(3)` | `float`, `double`, `real`, Postgres `money` |
| Money as integer | `bigint amount_cents` (mind currencies with 0 or 3 decimals) | `BIGINT` | `int` cents (overflows at 21 million) |
| Instant (event happened) | `timestamptz` | `DATETIME(6)` in UTC, or `TIMESTAMP` (2038 limit, converted by session zone) | `timestamp` without time zone |
| Calendar date | `date` | `DATE` | timestamps at midnight |
| Wall-clock time / schedule | `time` + an IANA zone column | `TIME` + zone column | `timetz` |
| Duration | `interval` or integer seconds | `INT` seconds | text like "1h30" |
| Text | `text` (+ CHECK for real limits) | `VARCHAR(n)` with a real n; `TEXT` for long bodies | `varchar(255)` by habit; `char(n)` padding |
| Case-insensitive text (email, username) | `citext` or a `lower()` unique index | case-insensitive `_ci` collation (default) | plain unique on text in Postgres |
| Flag | `boolean NOT NULL DEFAULT false` | `BOOLEAN` (TINYINT(1)) `NOT NULL DEFAULT 0` | nullable booleans, 'Y'/'N' strings |
| Status | `text` + `CHECK (... IN (...))`, enum type, or lookup table | `ENUM(...)` or lookup table | unconstrained text |
| JSON | `jsonb` (GIN index for containment) | `JSON` (generated columns to index) | JSON in text |
| IP address | `inet` / `cidr` | `VARBINARY(16)` with INET6_ATON | text |
| Large binary | object storage + key in a `text` column | same | big BLOBs in hot tables |
## Notes
- Changing a column type later rewrites the table in both engines (with a few exceptions such as widening varchar in Postgres). Get ids and money right first.
- Enum types in Postgres: adding a value is cheap, removing or renaming one is not. Lookup tables are easier to evolve and can carry labels and sort order.
- `NOT NULL` with a sensible default is the norm; nullable should mean "genuinely unknown or not applicable".
- Store the currency next to every amount, even if today there is only one.
- Name things the same everywhere: `customer_id` in every table that references customers, `_at` for instants, `_on` or `_date` for dates, `is_`/`has_` for booleans.
FILE:templates/schema-review.md
# Schema review: <project / schema file>, <date>
**Engine:** <PostgreSQL 16> **Scope:** <5 tables: customers, products, orders, order_items, payments>
**Context:** <OLTP shop backend, ~2M orders/year, Prisma ORM, no data yet>
**Command:** `python3 scripts/schema_lint.py <shop-schema.sql>`
**Verdict:** <CHANGES NEEDED before the first release: 4 HIGH, 8 MEDIUM, 8 LOW>
## Must fix (HIGH)
| # | Table.column | Finding | What goes wrong | Fix |
|---|---|---|---|---|
| 1 | <products.price> | <FLOAT_MONEY> | <0.1 + 0.2 != 0.3; order totals drift by cents> | <numeric(12,2) NOT NULL CHECK (price >= 0)> |
## Should fix (MEDIUM)
| # | Table.column | Finding | What goes wrong | Fix |
|---|---|---|---|---|
| 1 | <"order"> | <RESERVED_WORD_NAME> | <must be quoted in every query and BI tool> | <rename to orders> |
## Consider (LOW / judgement calls)
- <customers.email: citext so Ana@x.com and ana@x.com are one account>
## Found by reading (not by the linter)
- <payments: add UNIQUE (provider, provider_id) so a webhook retry cannot record a payment twice>
- <ON DELETE: keep RESTRICT on payments -> orders; CASCADE only order_items -> orders>
## Corrected DDL
```sql
<full corrected schema, lints clean>
```
## Decisions for the team
- <bigint identity vs UUIDv7 for public ids>
- <store orders.total or compute it from order_items?>
## If tables already hold data
- <each change above is a migration; list them and run a migration safety review before deploying>
FILE:examples/shop-schema.sql
-- Small online shop schema (PostgreSQL), first draft for review.
-- The problems are deliberate: this is what the skill is meant to catch.
CREATE TABLE customers (
id serial PRIMARY KEY,
email varchar(255) NOT NULL UNIQUE,
full_name varchar(255),
phone varchar(255),
is_verified boolean,
created_at timestamp
);
CREATE TABLE products (
id serial PRIMARY KEY,
sku varchar(64) NOT NULL UNIQUE,
title text NOT NULL,
price double precision NOT NULL,
attributes text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE "order" (
id bigserial PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers (id),
status varchar(20) NOT NULL DEFAULT 'pending',
total numeric(12,2) NOT NULL,
placed_at timestamp NOT NULL DEFAULT now(),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
order_id bigint NOT NULL REFERENCES "order" (id),
product_id bigint NOT NULL,
quantity integer NOT NULL,
unit_price numeric(12,2) NOT NULL
);
CREATE TABLE payments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id integer NOT NULL REFERENCES "order" (id),
amount real NOT NULL,
provider text NOT NULL,
provider_id text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX payments_order_idx ON payments (order_id);
CREATE INDEX payments_order_created_idx ON payments (order_id, created_at);
FILE:examples/shop-schema-fixed.sql
-- Small online shop schema (PostgreSQL), after review.
-- Every change is explained in templates/schema-review.md style in the review.
CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email citext NOT NULL UNIQUE,
full_name text,
phone text CHECK (char_length(phone) <= 32),
is_verified boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku text NOT NULL UNIQUE,
title text NOT NULL,
price numeric(12,2) NOT NULL CHECK (price >= 0),
attributes jsonb NOT NULL DEFAULT '{}',
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers (id),
status text NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled', 'refunded')),
total numeric(12,2) NOT NULL CHECK (total >= 0),
placed_at timestamptz NOT NULL DEFAULT now(),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX orders_customer_idx ON orders (customer_id);
CREATE TABLE order_items (
order_id bigint NOT NULL REFERENCES orders (id),
product_id bigint NOT NULL REFERENCES products (id),
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (order_id, product_id)
);
CREATE INDEX order_items_product_idx ON order_items (product_id);
CREATE TABLE payments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders (id),
amount numeric(12,2) NOT NULL,
provider text NOT NULL,
provider_id text,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (provider, provider_id)
);
CREATE INDEX payments_order_created_idx ON payments (order_id, created_at);
FILE:scripts/schema_lint.py
#!/usr/bin/env python3
"""SQL schema design linter for PostgreSQL and MySQL DDL (Python 3 standard library only).
Reads CREATE TABLE / CREATE INDEX / ALTER TABLE ... ADD statements (a schema dump,
schema.sql, or the DDL of new tables in a pull request) and reports design problems
that are cheap to fix before data exists and expensive after: tables without a
primary key, money stored in floating point, foreign keys whose type differs from the
referenced key or that have no supporting index (PostgreSQL), *_id columns without a
foreign key, 32-bit primary keys, timestamps without time zone, nullable audit
columns and booleans, unchecked status columns, reserved-word and mixed-case names,
case-sensitive unique emails, redundant indexes, JSON kept in text columns, and MySQL
tables using MyISAM or 3-byte utf8.
It does not connect to a database and does not judge migration safety (locks,
backfills); it reviews the shape of the schema itself.
Usage:
python3 schema_lint.py FILE.sql [FILE.sql ...] [--dialect auto|postgres|mysql]
[--min-severity LOW|MEDIUM|HIGH] [--ignore CODE,CODE] [--json]
python3 schema_lint.py --self-test
Exit codes: 0 = no HIGH findings, 1 = HIGH findings, 2 = bad input (unreadable file,
no CREATE TABLE found).
"""
import argparse, json, re, sys
SEV_ORDER = {"HIGH": 0, "MEDIUM": 1, "LOW": 2}
MONEY_RE = re.compile(r"(^|_)(price|amount|total|subtotal|cost|balance|fee|fees|tax|salary|refund|discount|paid|payment|revenue|credit|debit|charge)(_|$)", re.I)
RESERVED = {"user", "order", "group", "select", "table", "column", "desc", "asc", "check", "default", "from",
"where", "limit", "offset", "references", "primary", "key", "range", "rank", "window", "all", "both",
"end", "grant", "to", "type", "values", "constraint", "index", "match", "condition", "interval", "read", "row"}
RESERVED_PG_ONLY_SAFE = {"key", "range", "rank", "type", "values", "match", "condition", "interval", "read", "row", "index"} # fine unquoted in Postgres
RESERVED_MY_ONLY_SAFE = {"offset", "user", "end", "type", "values", "row"} # fine unquoted in MySQL 8
STATUS_RE = re.compile(r"^(status|state|kind|role|type|.*_status|.*_state|.*_type)$", re.I)
JSONISH_RE = re.compile(r"(^|_)(json|payload|metadata|meta|attributes|settings|extra|data)$", re.I)
INT32 = {"int", "integer", "int4", "serial", "serial4", "mediumint", "smallint", "int2", "smallserial", "tinyint"}
INT64 = {"bigint", "int8", "bigserial", "serial8"}
def finding(sev, code, table, column, problem, fix, line=0):
return {"severity": sev, "code": code, "table": table, "column": column, "line": line, "problem": problem, "fix": fix}
def strip_comments(sql):
out, i, n = [], 0, len(sql)
while i < n:
c = sql[i]
if c in "'\"`":
j = i + 1
while j < n and sql[j] != c:
j += 2 if sql[j] == "\\" and c == "'" else 1
out.append(sql[i:j + 1]); i = j + 1
elif sql.startswith("--", i) or c == "#" and (i == 0 or sql[i - 1] in "\n \t"):
j = sql.find("\n", i); j = n if j < 0 else j
out.append(" " * (j - i)); i = j
elif sql.startswith("/*", i):
j = sql.find("*/", i + 2); j = n if j < 0 else j + 2
out.append(re.sub(r"[^\n]", " ", sql[i:j])); i = j
else:
out.append(c); i += 1
return "".join(out)
def split_top(s, sep=","):
parts, depth, cur, q = [], 0, [], None
for ch in s:
if q:
cur.append(ch)
if ch == q:
q = None
continue
if ch in "'\"`":
q = ch
elif ch == "(":
depth += 1
elif ch == ")":
depth -= 1
if ch == sep and depth == 0:
parts.append("".join(cur)); cur = []
else:
cur.append(ch)
if "".join(cur).strip():
parts.append("".join(cur))
return parts
def statements(sql):
clean = strip_comments(sql)
pos = 0
for part in split_top(clean, ";"):
line = clean.count("\n", 0, pos + len(part) - len(part.lstrip())) + 1
pos += len(part) + 1
if part.strip():
yield line, part.strip()
def ident(s):
s = s.strip()
quoted = s[:1] in "\"`"
name = s.split(".")[-1].strip().strip("\"`")
return name, quoted
def cols_list(s):
return [ident(re.sub(r"\s+(ASC|DESC|NULLS\s+(FIRST|LAST)).*$", "", c.strip(), flags=re.I))[0].lower() if not c.strip().startswith("(") and "(" not in c else c.strip().lower()
for c in split_top(s)]
class Schema:
def __init__(self):
self.tables, self.indexes, self.fks, self.enums = {}, [], [], set()
self.dialect_hints = {"mysql": 0, "postgres": 0}
def parse(sql):
sc = Schema()
if re.search(r"AUTO_INCREMENT|ENGINE\s*=|`|UNSIGNED|DATETIME|TINYINT\(", sql, re.I):
sc.dialect_hints["mysql"] += 1
if re.search(r"\bSERIAL\b|BIGSERIAL|TIMESTAMPTZ|JSONB|GENERATED\s+(ALWAYS|BY DEFAULT)\s+AS\s+IDENTITY|::|CREATE\s+TYPE|CITEXT|CONCURRENTLY|\bTEXT\[\]", sql, re.I):
sc.dialect_hints["postgres"] += 1
for line, st in statements(sql):
m = re.match(r"CREATE\s+(?:TEMP(?:ORARY)?\s+|UNLOGGED\s+)?TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?([\w\".`]+)\s*\((.*)\)\s*(.*)$", st, re.I | re.S)
if m:
name, quoted = ident(m.group(1))
t = {"name": name, "quoted": quoted, "line": line, "cols": {}, "order": [], "pk": [], "uniques": [],
"checks": " ".join([]), "options": m.group(3), "raw": st}
checks = []
for item in split_top(m.group(2)):
parse_item(item.strip(), t, sc, checks)
t["checks"] = " ".join(checks).lower()
sc.tables[name.lower()] = t
continue
m = re.match(r"CREATE\s+(UNIQUE\s+)?INDEX\s+(?:CONCURRENTLY\s+)?(?:IF\s+NOT\s+EXISTS\s+)?([\w\".`]*)\s*ON\s+(?:ONLY\s+)?([\w\".`]+)\s*(?:USING\s+\w+\s*)?\((.*)\)\s*(.*)$", st, re.I | re.S)
if m:
sc.indexes.append({"table": ident(m.group(3))[0].lower(), "cols": cols_list(m.group(4)), "unique": bool(m.group(1)),
"name": ident(m.group(2))[0] if m.group(2) else "", "partial": "where" in m.group(5).lower(), "line": line})
continue
m = re.match(r"ALTER\s+TABLE\s+(?:ONLY\s+)?(?:IF\s+EXISTS\s+)?([\w\".`]+)\s+(.*)$", st, re.I | re.S)
if m:
tname = ident(m.group(1))[0].lower()
for action in split_top(m.group(2)):
a = action.strip()
am = re.match(r"ADD\s+(?:COLUMN\s+)(?:IF\s+NOT\s+EXISTS\s+)?(.*)$", a, re.I | re.S)
if am and tname in sc.tables:
parse_item(am.group(1), sc.tables[tname], sc, [])
continue
am = re.match(r"ADD\s+(.*)$", a, re.I | re.S)
if am and tname in sc.tables:
chk = []
parse_item(am.group(1), sc.tables[tname], sc, chk)
sc.tables[tname]["checks"] += " " + " ".join(chk).lower()
continue
m = re.match(r"CREATE\s+TYPE\s+([\w\".]+)\s+AS\s+ENUM", st, re.I)
if m:
sc.enums.add(ident(m.group(1))[0].lower())
m = re.match(r"CREATE\s+(?:DOMAIN|EXTENSION)\s+(?:IF\s+NOT\s+EXISTS\s+)?([\w\".]+)", st, re.I)
if m:
sc.enums.add(ident(m.group(1))[0].lower())
return sc
def parse_item(item, t, sc, checks):
u = item.upper()
m = re.match(r"CONSTRAINT\s+[\w\"`]+\s+(.*)$", item, re.I | re.S)
if m:
item, u = m.group(1).strip(), m.group(1).strip().upper()
if u.startswith("PRIMARY KEY"):
t["pk"] = cols_list(re.search(r"\((.*)\)", item, re.S).group(1)); return
if re.match(r"UNIQUE\b", u):
cm = re.search(r"\((.*?)\)", item, re.S)
if cm:
cols = cols_list(cm.group(1))
t["uniques"].append(cols)
sc.indexes.append({"table": t["name"].lower(), "cols": cols, "unique": True, "name": "", "partial": False, "line": t["line"]})
return
if u.startswith("FOREIGN KEY"):
fm = re.match(r"FOREIGN\s+KEY\s*\((.*?)\)\s*REFERENCES\s+([\w\".`]+)\s*(?:\((.*?)\))?", item, re.I | re.S)
if fm:
sc.fks.append({"table": t["name"].lower(), "cols": cols_list(fm.group(1)), "ref": ident(fm.group(2))[0].lower(),
"ref_cols": cols_list(fm.group(3)) if fm.group(3) else None})
return
if u.startswith("CHECK"):
checks.append(item); return
if re.match(r"(UNIQUE\s+)?(KEY|INDEX)\b", u) or re.match(r"(FULLTEXT|SPATIAL)\b", u):
cm = re.search(r"\((.*)\)", item, re.S)
if cm:
sc.indexes.append({"table": t["name"].lower(), "cols": cols_list(cm.group(1)), "unique": u.startswith("UNIQUE"),
"name": "", "partial": False, "line": t["line"]})
return
if u.startswith(("EXCLUDE", "LIKE", "PERIOD")):
return
cm = re.match(r"([\"`][^\"`]+[\"`]|[\w$]+)\s+(.*)$", item, re.S)
if not cm:
return
name, quoted = ident(cm.group(1))
rest = cm.group(2)
tm = re.match(r"((?:double\s+precision|character\s+varying|bit\s+varying|timestamp(?:\s*\(\d+\))?(?:\s+with(?:out)?\s+time\s+zone)?(?!\w)|time(?:\s+with(?:out)?\s+time\s+zone)?(?!\w)|[\w.]+)(?:\s*\([^)]*\))?(?:\s+unsigned)?(?:\s*\[\])?)", rest, re.I)
ctype = re.sub(r"\s+", " ", tm.group(1).lower()) if tm else ""
ru = rest.upper()
col = {"name": name, "quoted": quoted, "type": ctype, "not_null": "NOT NULL" in ru or "PRIMARY KEY" in ru,
"default": "DEFAULT" in ru or "IDENTITY" in ru or "AUTO_INCREMENT" in ru or ctype.endswith("serial"),
"check": "CHECK" in ru}
t["cols"][name.lower()] = col
t["order"].append(name.lower())
if "PRIMARY KEY" in ru:
t["pk"] = [name.lower()]
if re.search(r"\bUNIQUE\b", ru):
t["uniques"].append([name.lower()])
sc.indexes.append({"table": t["name"].lower(), "cols": [name.lower()], "unique": True, "name": "", "partial": False, "line": t["line"]})
rm = re.search(r"REFERENCES\s+([\w\".`]+)\s*(?:\((.*?)\))?", rest, re.I)
if rm:
sc.fks.append({"table": t["name"].lower(), "cols": [name.lower()], "ref": ident(rm.group(1))[0].lower(),
"ref_cols": cols_list(rm.group(2)) if rm.group(2) else None})
if col["check"]:
checks.append(f"check ({name})")
def qi(name):
return f'"{name}"' if name.lower() in RESERVED or name != name.lower() else name
def base_type(ctype):
t = re.sub(r"\(.*?\)|\s+unsigned|\[\]", "", ctype).strip()
return {"int4": "integer", "int": "integer", "serial": "integer", "serial4": "integer", "int8": "bigint",
"bigserial": "bigint", "serial8": "bigint", "int2": "smallint", "smallserial": "smallint"}.get(t, t)
def lint(sql, dialect="auto", ignore=()):
sc = parse(sql)
if dialect == "auto":
dialect = "mysql" if sc.dialect_hints["mysql"] > sc.dialect_hints["postgres"] else "postgres"
out = []
def add(*a, **k):
f = finding(*a, **k)
if f["code"] not in ignore:
out.append(f)
table_names = set(sc.tables)
for key, t in sc.tables.items():
tn = t["name"]
ln = t["line"]
if not t["pk"]:
add("HIGH", "NO_PRIMARY_KEY", tn, "", "table has no primary key: duplicate rows cannot be told apart, updates and replication (logical replication, MySQL row-based, CDC) suffer",
"add a bigint identity (or UUID) primary key, or a composite key on the natural unique columns", line=ln)
elif len(t["pk"]) == 1 and t["pk"][0] in t["cols"]:
c = t["cols"][t["pk"][0]]
bt = base_type(c["type"])
if bt in {"integer", "smallint", "mediumint", "tinyint"} and not re.search(r"(^|_)(type|status|kind|country|currency|lookup)s?$", tn, re.I):
add("MEDIUM", "INT32_PRIMARY_KEY", tn, c["name"], f"primary key {c['name']} is {c['type']} (max about 2.1 billion, less for smaller types); running out means an emergency type change on the biggest table",
"use bigint GENERATED ALWAYS AS IDENTITY (Postgres) or BIGINT UNSIGNED AUTO_INCREMENT (MySQL)", line=ln)
if dialect == "postgres" and t["quoted"] and t["name"] != t["name"].lower():
add("LOW", "MIXED_CASE_NAME", tn, "", f'table "{tn}" is quoted mixed case: every query must quote it forever', "use snake_case lower-case names", line=ln)
if tn.lower() in RESERVED and not (dialect == "postgres" and tn.lower() in RESERVED_PG_ONLY_SAFE) and not (dialect == "mysql" and tn.lower() in RESERVED_MY_ONLY_SAFE):
add("MEDIUM", "RESERVED_WORD_NAME", tn, "", f"table name '{tn}' is a reserved word and must be quoted in every query and ORM", "rename (e.g. users, orders, user_groups)", line=ln)
if dialect == "mysql":
opts = t["options"].lower()
if re.search(r"engine\s*=\s*myisam", opts):
add("HIGH", "MYISAM_ENGINE", tn, "", "ENGINE=MyISAM has no transactions, no foreign keys and table-level locks; crashes can corrupt it", "use ENGINE=InnoDB", line=ln)
if re.search(r"charset\s*=\s*utf8(mb3)?\b(?!mb4)", opts):
add("MEDIUM", "MYSQL_UTF8MB3", tn, "", "utf8 in MySQL is 3-byte utf8mb3: emoji and some CJK characters fail to insert or get mangled", "use DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci", line=ln)
if not any(re.match(r"^(created_at|created|created_on|inserted_at|date_created|creation_date)$", c) for c in t["cols"]):
add("LOW", "NO_CREATED_AT", tn, "", "no created_at column: hard to debug, audit or backfill later", "add created_at timestamptz NOT NULL DEFAULT now()", line=ln)
v255 = [c["name"] for c in t["cols"].values() if re.match(r"(varchar|character varying)\s*\(255\)", c["type"])]
if dialect == "postgres" and len(v255) >= 3:
add("LOW", "VARCHAR_255_HABIT", tn, ", ".join(v255), f"{len(v255)} columns are varchar(255): in Postgres the limit costs nothing to drop but 255 is rarely the real rule",
"use text, with a CHECK (char_length(x) <= n) where a real limit exists", line=ln)
for cname in t["order"]:
c = t["cols"][cname]
ct, bt = c["type"], base_type(c["type"])
cn = c["name"]
if MONEY_RE.search(cn) and bt in {"float", "real", "double", "double precision", "float4", "float8"}:
add("HIGH", "FLOAT_MONEY", tn, cn, f"{cn} is {ct}: binary floating point cannot store 0.10 exactly, sums drift by cents",
"use numeric(12,2) / DECIMAL(12,2), or bigint minor units (amount_cents)", line=ln)
if bt == "money" and dialect == "postgres":
add("MEDIUM", "MONEY_TYPE", tn, cn, "Postgres money type depends on lc_monetary, has one fixed currency and rounds", "use numeric(12,2) plus a currency column", line=ln)
if dialect == "postgres" and (re.match(r"^timestamp(\s*\(\d+\))?$", ct) or "without time zone" in ct):
add("MEDIUM", "TIMESTAMP_WITHOUT_TZ", tn, cn, f"{cn} is timestamp without time zone: the instant depends on whatever zone the writer assumed", "use timestamptz (stores an absolute instant, displays in the session zone)", line=ln)
if dialect == "mysql" and bt == "datetime" and re.search(r"_at$", cn):
add("LOW", "DATETIME_NO_ZONE", tn, cn, f"{cn} is DATETIME (no zone): make sure every writer stores UTC", "document and enforce UTC (time_zone='+00:00' on connections) or use TIMESTAMP within its 2038 range", line=ln)
if re.match(r"^(created_at|updated_at|inserted_at)$", cn, re.I) and not c["not_null"]:
add("MEDIUM", "NULLABLE_AUDIT_COLUMN", tn, cn, f"{cn} is nullable{'' if c['default'] else ' and has no default'}: rows without it will appear", f"{cn} timestamptz NOT NULL DEFAULT now()", line=ln)
if bt in {"boolean", "bool"} and not c["not_null"]:
add("LOW", "NULLABLE_BOOLEAN", tn, cn, f"{cn} is a nullable boolean: three states (true, false, unknown) and NULL-unsafe WHERE clauses", f"{cn} boolean NOT NULL DEFAULT false", line=ln)
if STATUS_RE.match(cn) and re.match(r"^(text|varchar|character varying|char|character)", bt) and not c["check"] \
and cn.lower() not in t["checks"] and not any(f["table"] == key and cname in f["cols"] for f in sc.fks):
add("LOW", "UNCHECKED_STATUS", tn, cn, f"{cn} is free text: typos like 'Shipped ' and 'shiped' will end up in the data", "add CHECK (status IN (...)), an enum type, or a FK to a lookup table", line=ln)
if bt in {"text", "longtext", "mediumtext"} and JSONISH_RE.search(cn):
add("LOW", "JSON_IN_TEXT", tn, cn, f"{cn} looks like JSON stored as {ct}: no validation, no indexing", "use jsonb (Postgres) or JSON (MySQL) if it really is JSON", line=ln)
if cn.lower() in RESERVED and not (dialect == "postgres" and cn.lower() in RESERVED_PG_ONLY_SAFE) and not (dialect == "mysql" and cn.lower() in RESERVED_MY_ONLY_SAFE) and cn.lower() not in {"type"}:
add("MEDIUM", "RESERVED_WORD_NAME", tn, cn, f"column name '{cn}' is a reserved word and needs quoting everywhere", "rename it (e.g. sort_order, user_id, group_name, ends_at)", line=ln)
if dialect == "postgres" and c["quoted"] and cn != cn.lower():
add("LOW", "MIXED_CASE_NAME", tn, cn, f'column "{cn}" is quoted mixed case: every query must quote it', "use snake_case", line=ln)
if cn.lower().endswith("_id") and cname not in t["pk"] and not any(f["table"] == key and cname in f["cols"] for f in sc.fks):
stem = cn.lower()[:-3]
cands = [x for x in (stem, stem + "s", stem + "es", stem[:-1] + "ies" if stem.endswith("y") else None) if x and x in table_names]
if cands:
add("LOW", "MISSING_FOREIGN_KEY", tn, cn, f"{cn} looks like a reference to {cands[0]} but has no foreign key: orphans and typos are possible", f"add REFERENCES {cands[0]}(id) (with an index)", line=ln)
if re.search(r"(^|_)email$", cn, re.I) and [cname] in t["uniques"] and dialect == "postgres" and bt != "citext":
if not any(i["table"] == key and any("lower(" in c2 for c2 in i["cols"]) for i in sc.indexes):
add("LOW", "CASE_SENSITIVE_UNIQUE_EMAIL", tn, cn, "UNIQUE on email is case-sensitive in Postgres: Ana@x.com and ana@x.com can both sign up", "CREATE UNIQUE INDEX ... ON t (lower(email)) or use citext", line=ln)
# foreign keys: types and indexes
for f in sc.fks:
t = sc.tables.get(f["table"])
r = sc.tables.get(f["ref"])
if not t:
continue
ref_cols = f["ref_cols"] or (r["pk"] if r else None)
if r and ref_cols and len(ref_cols) == len(f["cols"]):
for c, rc in zip(f["cols"], ref_cols):
a, b = t["cols"].get(c), r["cols"].get(rc)
if a and b and base_type(a["type"]) != base_type(b["type"]) and not ({base_type(a["type"]), base_type(b["type"])} <= {"uuid"}):
add("HIGH", "FK_TYPE_MISMATCH", t["name"], a["name"], f"{a['name']} is {a['type']} but references {r['name']}.{b['name']} ({b['type']}): joins need casts and the FK fails once ids pass the smaller range",
f"make {a['name']} the same type as {r['name']}.{b['name']}", line=t["line"])
if dialect == "postgres":
covered = any(idx["table"] == f["table"] and not idx["partial"] and idx["cols"][:len(f["cols"])] == f["cols"] for idx in sc.indexes) \
or t["pk"][:len(f["cols"])] == f["cols"] or any(u[:len(f["cols"])] == f["cols"] for u in t["uniques"])
if not covered:
add("MEDIUM", "FK_WITHOUT_INDEX", t["name"], ", ".join(f["cols"]), f"foreign key ({', '.join(f['cols'])}) -> {f['ref']} has no index starting with it: deletes/updates on {f['ref']} scan {t['name']}, and joins are slow",
f"CREATE INDEX CONCURRENTLY ON {qi(t['name'])} ({', '.join(f['cols'])})", line=t["line"])
# redundant indexes
for i, a in enumerate(sc.indexes):
t = sc.tables.get(a["table"])
if not t or a["partial"] or any("(" in c for c in a["cols"]):
continue
others = [b for j, b in enumerate(sc.indexes) if j != i and b["table"] == a["table"] and not b["partial"]]
if t["pk"] and a["cols"] == t["pk"][:len(a["cols"])] and not a["unique"]:
add("LOW", "REDUNDANT_INDEX", t["name"], ", ".join(a["cols"]), f"index on ({', '.join(a['cols'])}) duplicates the primary key's leading columns", "drop it", line=a["line"])
continue
for b in others:
if len(b["cols"]) > len(a["cols"]) and b["cols"][:len(a["cols"])] == a["cols"] and not a["unique"]:
add("LOW", "REDUNDANT_INDEX", t["name"], ", ".join(a["cols"]), f"index on ({', '.join(a['cols'])}) is a prefix of the index on ({', '.join(b['cols'])})", "drop the shorter one unless it is much smaller and hot", line=a["line"])
break
if b["cols"] == a["cols"] and j_before(sc.indexes, b, a) and a["unique"] == b["unique"]:
add("LOW", "REDUNDANT_INDEX", t["name"], ", ".join(a["cols"]), f"two indexes on ({', '.join(a['cols'])})", "drop one", line=a["line"])
break
out.sort(key=lambda f: (SEV_ORDER[f["severity"]], f["table"], f["column"], f["code"]))
return dialect, sc, out
def j_before(lst, b, a):
return lst.index(b) < lst.index(a)
def report(path, dialect, sc, found):
counts = {s: sum(1 for f in found if f["severity"] == s) for s in SEV_ORDER}
out = [f"# Schema review: {path} ({dialect})", "",
f"Tables: {len(sc.tables)} Indexes: {len(sc.indexes)} Foreign keys: {len(sc.fks)} Findings: {len(found)} (HIGH {counts['HIGH']}, MEDIUM {counts['MEDIUM']}, LOW {counts['LOW']})", ""]
if not found:
out.append("No findings.")
return "\n".join(out)
out += ["| Sev | Code | Table.Column | Problem | Fix |", "|---|---|---|---|---|"]
for f in found:
loc = f["table"] + ("." + f["column"] if f["column"] else "")
out.append(f"| {f['severity']} | {f['code']} | {loc} | {f['problem'].replace('|', '/')} | {f['fix'].replace('|', '/')} |")
return "\n".join(out)
def self_test():
ok_pk = "CREATE TABLE t (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, created_at timestamptz NOT NULL DEFAULT now()"
cases = [
("clean postgres table", ok_pk + ", name text NOT NULL);", "postgres", [], ["NO_PRIMARY_KEY", "INT32_PRIMARY_KEY", "NO_CREATED_AT"]),
("no primary key", "CREATE TABLE logs (msg text, created_at timestamptz NOT NULL);", "auto", ["NO_PRIMARY_KEY"], []),
("table-level composite pk", "CREATE TABLE m (a bigint, b bigint, created_at timestamptz NOT NULL, PRIMARY KEY (a, b));", "auto", [], ["NO_PRIMARY_KEY"]),
("named pk constraint", "CREATE TABLE m (id bigint, created_at timestamptz NOT NULL, CONSTRAINT m_pk PRIMARY KEY (id));", "auto", [], ["NO_PRIMARY_KEY"]),
("pk added by alter", "CREATE TABLE m (id bigint NOT NULL, created_at timestamptz NOT NULL); ALTER TABLE m ADD CONSTRAINT m_pkey PRIMARY KEY (id);", "auto", [], ["NO_PRIMARY_KEY"]),
("serial pk is int32", "CREATE TABLE orders (id serial PRIMARY KEY, created_at timestamptz NOT NULL);", "auto", ["INT32_PRIMARY_KEY"], []),
("small lookup table int pk ok", "CREATE TABLE order_status (id smallint PRIMARY KEY, created_at timestamptz NOT NULL);", "auto", [], ["INT32_PRIMARY_KEY"]),
("float money", ok_pk + ", total_price double precision);", "postgres", ["FLOAT_MONEY"], []),
("numeric money ok", ok_pk + ", amount numeric(12,2) NOT NULL);", "postgres", [], ["FLOAT_MONEY"]),
("float non-money ok", ok_pk + ", latitude double precision);", "postgres", [], ["FLOAT_MONEY"]),
("money type", ok_pk + ", balance money);", "postgres", ["MONEY_TYPE"], []),
("timestamp without tz", ok_pk + ", shipped_at timestamp);", "postgres", ["TIMESTAMP_WITHOUT_TZ"], []),
("timestamp(3) without time zone", ok_pk + ", shipped_at timestamp(3) without time zone);", "postgres", ["TIMESTAMP_WITHOUT_TZ"], []),
("timestamptz ok", ok_pk + ", shipped_at timestamp with time zone);", "postgres", [], ["TIMESTAMP_WITHOUT_TZ"]),
("nullable created_at", "CREATE TABLE t (id bigint PRIMARY KEY, created_at timestamptz);", "postgres", ["NULLABLE_AUDIT_COLUMN"], []),
("no created_at", "CREATE TABLE t (id bigint PRIMARY KEY, name text);", "postgres", ["NO_CREATED_AT"], []),
("nullable boolean", ok_pk + ", is_active boolean);", "postgres", ["NULLABLE_BOOLEAN"], []),
("boolean not null ok", ok_pk + ", is_active boolean NOT NULL DEFAULT true);", "postgres", [], ["NULLABLE_BOOLEAN"]),
("unchecked status", ok_pk + ", status varchar(20) NOT NULL);", "postgres", ["UNCHECKED_STATUS"], []),
("status with check ok", ok_pk + ", status text NOT NULL CHECK (status IN ('a','b')));", "postgres", [], ["UNCHECKED_STATUS"]),
("status table check ok", ok_pk + ", status text NOT NULL, CONSTRAINT st CHECK (status IN ('a')));", "postgres", [], ["UNCHECKED_STATUS"]),
("reserved table name", "CREATE TABLE \"user\" (id bigint PRIMARY KEY, created_at timestamptz NOT NULL);", "postgres", ["RESERVED_WORD_NAME"], []),
("reserved column order", ok_pk + ", \"order\" int);", "postgres", ["RESERVED_WORD_NAME"], []),
("mixed case quoted", "CREATE TABLE \"OrderItems\" (id bigint PRIMARY KEY, created_at timestamptz NOT NULL);", "postgres", ["MIXED_CASE_NAME"], []),
("fk without index", "CREATE TABLE a (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE b (id bigint PRIMARY KEY, created_at timestamptz NOT NULL, a_id bigint NOT NULL REFERENCES a(id));", "postgres", ["FK_WITHOUT_INDEX"], ["FK_TYPE_MISMATCH"]),
("fk with index ok", "CREATE TABLE a (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE b (id bigint PRIMARY KEY, created_at timestamptz NOT NULL, a_id bigint REFERENCES a); CREATE INDEX ON b (a_id, id);", "postgres", [], ["FK_WITHOUT_INDEX"]),
("fk covered by composite pk", "CREATE TABLE a (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE ab (a_id bigint REFERENCES a(id), x bigint, created_at timestamptz NOT NULL, PRIMARY KEY (a_id, x));", "postgres", [], ["FK_WITHOUT_INDEX"]),
("fk type mismatch", "CREATE TABLE a (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE b (id bigint PRIMARY KEY, created_at timestamptz NOT NULL, a_id integer, FOREIGN KEY (a_id) REFERENCES a (id));", "postgres", ["FK_TYPE_MISMATCH"], []),
("missing fk on _id", "CREATE TABLE customers (id bigint PRIMARY KEY, created_at timestamptz NOT NULL); CREATE TABLE orders (id bigint PRIMARY KEY, created_at timestamptz NOT NULL, customer_id bigint NOT NULL);", "postgres", ["MISSING_FOREIGN_KEY"], []),
("external _id without table ok", ok_pk + ", stripe_id text);", "postgres", [], ["MISSING_FOREIGN_KEY"]),
("case-sensitive unique email", ok_pk + ", email text NOT NULL UNIQUE);", "postgres", ["CASE_SENSITIVE_UNIQUE_EMAIL"], []),
("lower(email) index ok", ok_pk + ", email text NOT NULL UNIQUE); CREATE UNIQUE INDEX t_email ON t (lower(email));", "postgres", [], ["CASE_SENSITIVE_UNIQUE_EMAIL"]),
("redundant prefix index", ok_pk + ", a bigint, b bigint); CREATE INDEX i1 ON t (a); CREATE INDEX i2 ON t (a, b);", "postgres", ["REDUNDANT_INDEX"], []),
("json in text", ok_pk + ", payload text);", "postgres", ["JSON_IN_TEXT"], []),
("varchar 255 habit", ok_pk + ", a varchar(255), b varchar(255), c varchar(255));", "postgres", ["VARCHAR_255_HABIT"], []),
("mysql myisam and utf8", "CREATE TABLE `t` (`id` int unsigned NOT NULL AUTO_INCREMENT, `created_at` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;", "auto", ["MYISAM_ENGINE", "MYSQL_UTF8MB3", "INT32_PRIMARY_KEY", "DATETIME_NO_ZONE"], ["TIMESTAMP_WITHOUT_TZ"]),
("mysql utf8mb4 innodb ok", "CREATE TABLE `t` (`id` bigint unsigned NOT NULL AUTO_INCREMENT, `created_at` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;", "auto", [], ["MYISAM_ENGINE", "MYSQL_UTF8MB3", "INT32_PRIMARY_KEY"]),
("mysql fk needs no extra index", "CREATE TABLE a (id bigint PRIMARY KEY, created_at datetime NOT NULL) ENGINE=InnoDB; CREATE TABLE b (id bigint PRIMARY KEY, created_at datetime NOT NULL, a_id bigint, FOREIGN KEY (a_id) REFERENCES a(id)) ENGINE=InnoDB;", "mysql", [], ["FK_WITHOUT_INDEX"]),
("comments and strings ignored", "-- CREATE TABLE x (a int);\n/* CREATE TABLE y (b int); */\n" + ok_pk + ", note text DEFAULT 'a;b -- c');", "postgres", [], ["NO_PRIMARY_KEY"]),
("alter add column parsed", ok_pk + "); ALTER TABLE t ADD COLUMN total_amount float;", "postgres", ["FLOAT_MONEY"], []),
]
passed = 0
for name, sql, dialect, expect, absent in cases:
d, sc, found = lint(sql, dialect)
codes = {f["code"] for f in found}
ok = all(c in codes for c in expect) and not any(c in codes for c in absent)
if name == "clean postgres table":
ok = ok and not found
if name == "comments and strings ignored":
ok = ok and len(sc.tables) == 1
if name.startswith("mysql myisam"):
ok = ok and d == "mysql"
passed += ok
print(("PASS " if ok else "FAIL ") + name + ("" if ok else f" -> got {sorted(codes)}"))
d, sc, found = lint("", "auto")
ok = report("x.sql", d, sc, found).endswith("No findings.")
passed += ok
print(("PASS " if ok else "FAIL ") + "empty report")
total = len(cases) + 1
print(f"{passed}/{total} passed")
return 0 if passed == total else 1
def main(argv=None):
ap = argparse.ArgumentParser(description="SQL schema design linter (Postgres/MySQL DDL)")
ap.add_argument("files", nargs="*")
ap.add_argument("--dialect", default="auto", choices=["auto", "postgres", "mysql"])
ap.add_argument("--min-severity", default="LOW", choices=list(SEV_ORDER))
ap.add_argument("--ignore", default="")
ap.add_argument("--json", action="store_true")
ap.add_argument("--self-test", action="store_true")
a = ap.parse_args(argv)
if a.self_test:
return self_test()
if not a.files:
ap.print_usage(sys.stderr)
return 2
ignore = {c.strip() for c in a.ignore.split(",") if c.strip()}
sql_all = []
for p in a.files:
try:
with open(p, encoding="utf-8") as fh:
sql_all.append(fh.read())
except OSError as e:
print(f"error: cannot read {p}: {e}", file=sys.stderr)
return 2
dialect, sc, found = lint("\n;\n".join(sql_all), a.dialect, ignore)
if not sc.tables:
print("error: no CREATE TABLE statements found", file=sys.stderr)
return 2
found = [f for f in found if SEV_ORDER[f["severity"]] <= SEV_ORDER[a.min_severity]]
if a.json:
print(json.dumps({"dialect": dialect, "tables": len(sc.tables), "findings": found}, indent=2))
else:
print(report(", ".join(a.files), dialect, sc, found))
return 1 if any(f["severity"] == "HIGH" for f in found) else 0
if __name__ == "__main__":
sys.exit(main())Checks a Markdown docs folder for links that break readers - relative links to missing or renamed files, anchors that match no heading, duplicate heading anchors, file-name case that only works on macOS, local absolute paths, reference-style links with missing, unused or duplicate definitions, images without alt text and bare URLs. Works offline and lists external URLs to check separately. Includes a stdlib Python checker with --self-test.
---
name: markdown-link-checker
description: Checks a Markdown docs folder for links that break readers - relative links to missing or renamed files, anchors that match no heading, duplicate heading anchors, file-name case that only works on macOS, local absolute paths, reference-style links with missing, unused or duplicate definitions, images without alt text and bare URLs. Works offline and lists external URLs to check separately. Includes a stdlib Python checker with --self-test.
---
# Markdown Link Checker
You help maintainers, technical writers and developers keep documentation navigable. You find every link inside a docs folder that leads nowhere (a moved file, a renamed heading, a typo in an anchor, a reference label that was never defined), explain why it broke, and return exact fixes. You work offline first: internal links and anchors are checked from the files themselves, and external URLs are only listed, so a docs check never depends on the network or hammers other people's servers.
## Files in this skill
- `scripts/check_md_links.py` - walks a folder of .md/.markdown/.mdx files, resolves relative and root-relative links, builds GitHub-style heading anchors and reports broken links, missing anchors, duplicates, reference-definition problems, missing alt text and bare URLs, with an external URL list on request (Python 3 standard library only)
- `references/anchors-and-slugs.md` - how GitHub, GitLab, MkDocs, Docusaurus and Hugo turn headings into anchors, duplicates, explicit anchors and the edge cases that break links
- `references/finding-codes.md` - every finding code, its severity, why it matters and the usual fix, plus how to check external URLs politely
- `templates/link-report.md` - the report to hand back
- `examples/README.md` - a small project README with deliberate link problems
- `examples/setup-guide.md` - the guide it links to, with more problems (wrong-case file name, anchor case, missing reference, local path)
## Workflow
### 1. Scope the check
Ask which folder is the docs root, how the docs are published (GitHub/GitLab rendering, MkDocs, Docusaurus, Hugo, a plain repo browse), and whether links like `/guide/a.md` mean "from the repo root" or "from the site root". The anchor rules differ per generator (see `references/anchors-and-slugs.md`); default to GitHub rules and say so.
### 2. Run the checker
```
python3 scripts/check_md_links.py examples --external
python3 scripts/check_md_links.py docs README.md --root . --min-severity MEDIUM
python3 scripts/check_md_links.py docs --ignore BARE_URL,UNUSED_REF_DEF --json > links.json
python3 scripts/check_md_links.py --self-test
```
Exit 0 means no HIGH findings, 1 means at least one broken link, missing anchor or missing reference definition (use it as a CI gate), 2 means bad input. Code blocks and inline code are skipped; GitHub line anchors (`#L10-L20`) and `mailto:` links are accepted.
### 3. Triage and fix
Work HIGH first. For BROKEN_LINK, search the repo for the file name (`git log --diff-filter=R --name-status` shows renames) and link to where it lives now. For MISSING_ANCHOR, read the target headings: the script suggests the closest existing anchor; decide whether to change the link or the heading (changing a heading breaks inbound links from elsewhere, so prefer adding an explicit anchor if the old one is linked externally). For DUPLICATE_ANCHOR, make the headings unique. For CASE_MISMATCH, use the exact file name. Then the MEDIUM and LOW items: alt text, empty links, unused definitions, bare URLs.
### 4. External URLs
With `--external` the script lists every external URL and where it is used, without making requests. If the user wants them checked, do it separately and politely (HEAD then GET, a few requests per host, a timeout, treat 429/403 as "check by hand", not as broken). See `references/finding-codes.md`.
### 5. Deliver
Fill `templates/link-report.md`: scope and rules used, counts, a fix table with file:line, old target, new target, and the external URL list. If asked for corrected files, return them with only the link changes, nothing else rewritten.
## Example result
`examples/` (2 files): 13 findings, 6 HIGH. README.md links `setup-guide.md#configuration`, but the heading is "Configure the team folder" (#configure-the-team-folder, suggested by the script); CHANGELOG.md, ../CONTRIBUTING.md, docs/style.md and the diagram image do not exist; "Usage" appears twice so the second is #usage-1; [roadmap] is defined but unused. setup-guide.md links `readme.md` (only README.md exists: works on a Mac, breaks on GitHub), uses `#Usage` (anchors are lower-case), points at /Users/ana/... and uses [support] without a definition. 4 external URLs listed, none fetched.
## Rules
- Never fetch external URLs unless the user asks; when you do, rate-limit and do not report 403/429 as broken.
- Fix the link, not the content: do not rewrite prose while fixing paths.
- Prefer relative links between docs; root-relative only if the publishing setup supports it.
- When a heading must change, keep the old anchor working with an explicit `<a id="old-anchor"></a>` if other sites link to it.
- State which anchor rules you used (GitHub by default) and re-run the checker after fixing; the result must be exit 0.
- Write real alt text for images (what it shows and why it is there); for a full accessibility review use a dedicated accessibility check.
FILE:references/anchors-and-slugs.md
# Heading anchors and slugs
A link like `guide.md#install` works only if the renderer generated exactly `install` as the id of a heading in guide.md. Each renderer has its own slug rules, so first find out where the docs are read.
## GitHub (default used by the script)
1. Take the heading text as rendered: inline markup removed (`code` keeps its text, `**bold**` becomes bold, `[text](url)` becomes text, HTML tags dropped).
2. Lower-case it.
3. Remove every character that is not a letter, digit, space, hyphen or underscore (letters include accented and non-Latin letters; emoji and punctuation go).
4. Turn each space into a hyphen. Spaces are NOT collapsed: "C++ & Rust" becomes `c--rust`.
5. Duplicates get `-1`, `-2`, ... in order of appearance: the second "Usage" is `#usage-1`.
| Heading | Anchor |
|---|---|
| `## What's new? (v2.0)` | `#whats-new-v20` |
| ``## The `--dry-run` flag`` | `#the---dry-run-flag` |
| `## Über uns` | `#über-uns` (GitHub also accepts the percent-encoded form) |
| `## C++ & Rust: 100% safe?` | `#c--rust-100-safe` |
| `## snake_case_name` | `#snake_case_name` |
| `## Usage` (second time) | `#usage-1` |
The script's slug function matches the github-slugger library on these cases.
## Other renderers
- **GitLab**: like GitHub, but consecutive hyphens are collapsed into one (`#c-rust-100-safe`), so links copied between GitHub and GitLab can break.
- **MkDocs (Python-Markdown toc)**: drops non-ASCII by default, collapses separators; `{#custom-id}` is available with attr_list. Check `markdown_extensions: toc: slugify` in mkdocs.yml.
- **Docusaurus**: GitHub-like slugs; supports `## Heading {#custom-id}`.
- **Hugo (goldmark)**: GitHub-like by default; `autoHeadingIDType` can be `github`, `github-ascii` or `blackfriday`.
- **VS Code preview / most IDEs**: GitHub-like.
If the published site and the repo view disagree, prefer explicit anchors for anything linked from outside.
## Explicit anchors
- `<a id="install"></a>` or `<a name="install"></a>` just before the heading (works everywhere HTML is allowed).
- `## Install {#install}` where the renderer supports attribute lists (MkDocs attr_list, Docusaurus, Hugo, Pandoc). On GitHub the `{#...}` shows up as literal text.
## What breaks anchors
- Renaming a heading ("Configuration" -> "Configure the team folder").
- Duplicates: links to `#usage` always land on the first one.
- Capital letters in the link (`#Usage`): GitHub anchors are lower-case; some browsers forgive it, many renderers do not.
- Copying the anchor from another renderer (GitLab vs GitHub hyphens).
- Headings generated by includes or templates the checker cannot see; mark these with `--ignore MISSING_ANCHOR` for that folder only, and say so.
- Line anchors (`#L10`, `#L10-L20`) are GitHub file-view features; they are accepted but break if the file moves or lines shift.
FILE:references/finding-codes.md
# Finding codes
| Code | Severity | Why it matters | Usual fix |
|---|---|---|---|
| BROKEN_LINK | HIGH | relative link to a file or folder that does not exist (moved, renamed, typo) | link to the current path; `git log --diff-filter=R --name-status -- docs/` shows renames |
| BROKEN_IMAGE | HIGH | image path does not exist; readers see a broken icon | fix the path or commit the image |
| MISSING_ANCHOR | HIGH | #fragment matches no heading or explicit anchor in the target file | use the suggested anchor, or add an explicit anchor |
| MISSING_REF_DEF | HIGH | `[text][label]` has no `[label]: url` definition; it renders as plain bracketed text | add the definition or fix the label (labels are case-insensitive) |
| DUPLICATE_ANCHOR | MEDIUM | two headings produce the same slug; the second gets `-1` and links land on the first | make headings unique, e.g. "Usage (CLI)" and "Usage (API)" |
| DUPLICATE_REF_DEF | MEDIUM | the same label is defined twice; only the first counts | delete or rename one |
| CASE_MISMATCH | MEDIUM | file exists only with different case; fine on macOS/Windows, broken on Linux, GitHub Pages and most CI | use the exact name |
| ANCHOR_CASE | MEDIUM | anchor only matches when lower-cased | write anchors in lower case |
| LOCAL_ABSOLUTE_PATH | MEDIUM | link to /Users/..., /home/..., C:\ or file:// works only on the author's machine | relative path, or remove |
| EMPTY_LINK | MEDIUM | `[text]()` or empty href | fill in or remove |
| EMPTY_LINK_TEXT | MEDIUM | `[](url)`: nothing to click or read aloud | add link text |
| MISSING_ALT | MEDIUM | image without alt text; screen readers say "image" or the file name | describe what it shows and why it is there |
| UNUSED_REF_DEF | LOW | definition never used; usually left over after an edit | delete, or link to it if the link was lost |
| BARE_URL | LOW | raw URL in text; not clickable in some renderers, read character by character | `[descriptive text](url)` or `<url>` |
| OUTSIDE_ROOT | LOW | relative link leaves the docs root; breaks if docs are published alone | link to the published URL |
Exit codes: 0 = no HIGH, 1 = HIGH present, 2 = bad input. Use `--min-severity` and `--ignore` to tune a CI gate, and record any ignore in the repo with a reason.
## Link forms the script understands
- Inline: `[text](path.md#anchor "title")`, `[text](<path with spaces.md>)`, `[text](my%20notes.md)`
- Images: ``, `<img src="img.png" alt="...">`
- Reference: `[text][label]`, `[label][]`, `[label]` with `[label]: url "title"` anywhere in the same file
- Autolinks `<https://...>`, HTML `<a href="...">`, explicit anchors `<a id>`, `<a name>`, `{#id}`
- Root-relative `/guide/a.md` resolves from `--root` (default: the first folder argument)
- Skipped: fenced and indented code blocks, inline code, `mailto:`/`tel:`, GitHub line anchors, task list boxes `[ ]` `[x]`
## Checking external URLs (only when asked)
- Deduplicate first: `--external` lists each URL once with every place it is used.
- Use HEAD, fall back to GET when the server rejects HEAD (405) or lies about it.
- One or two requests per host at a time, a 10 s timeout, a clear User-Agent.
- 200-299 OK; 301/308 update the link to the new location; 404/410 broken; 403, 429 and timeouts are "check by hand" (bot protection, rate limits).
- Anchors on external pages cannot be checked reliably offline; note them, do not guess.
- Prefer links to stable, versioned pages (tagged docs, permalinks) over "latest".
FILE:templates/link-report.md
# Docs link check: <repo or folder>, <date>
**Scope:** <docs/ and README.md, 42 files> **Anchor rules:** <GitHub> **Root for /links:** <repo root>
**Command:** `python3 scripts/check_md_links.py <docs README.md --root . --external>`
**Result:** <13 findings: HIGH 6, MEDIUM 5, LOW 2 -> after fixes: exit 0>
## Must fix (HIGH)
| # | File:Line | Code | Link as written | Problem | Fix (new target) |
|---|---|---|---|---|---|
| 1 | <README.md:11> | <MISSING_ANCHOR> | <setup-guide.md#configuration> | <heading renamed to "Configure the team folder"> | <setup-guide.md#configure-the-team-folder> |
## Should fix (MEDIUM)
| # | File:Line | Code | Problem | Fix |
|---|---|---|---|---|
| 1 | <setup-guide.md:25> | <CASE_MISMATCH> | <readme.md vs README.md> | <README.md#contents> |
## Clean-up (LOW)
- <unused [roadmap] definition in README.md:42 - delete or link it>
- <bare URL README.md:21 -> [Tidepool documentation](https://docs.example.com/tidepool)>
## Images and alt text
| File:Line | Image | Alt text to use |
|---|---|---|
| <README.md:5> | <images/sync-diagram.png (missing)> | <"Diagram: phone app sends notes to the sync server, which writes them to the team folder"> |
## External URLs (not fetched)
| URL | Used at | Status (if checked) |
|---|---|---|
| <https://docs.example.com/tidepool> | <README.md:21> | <not checked> |
## Prevent it next time
- <run the checker in CI on every docs change (exit 1 fails the build)>
- <when renaming a heading that others link to, keep an explicit anchor with the old id>
FILE:examples/README.md
# Tidepool
Tidepool syncs field notes from a phone to a shared team folder.
## Contents
- [Quick start](#quick-start)
- [Install](setup-guide.md#install)
- [Configuration](setup-guide.md#configuration)
- [Troubleshooting](setup-guide.md#troubleshooting)
- [FAQ](#faq)
## Quick start
1. Install the app (see [the setup guide][setup]).
2. Sign in and pick a team folder.
3. Read the [release notes](CHANGELOG.md) before upgrading.
Full documentation lives at https://docs.example.com/tidepool and the API
reference is at <https://api.example.com/tidepool/v2>.
## Usage
Run `tidepool sync` to push new notes. Links inside code are not checked:
```
[not a link](missing.md)
```
## Usage
See [Contributing](../CONTRIBUTING.md) and the [style guide][style].
## Faq
**Can I sync offline?** Notes queue until the phone is online again.
[setup]: setup-guide.md
[style]: docs/style.md
[roadmap]: https://example.com/tidepool/roadmap
FILE:examples/setup-guide.md
# Setup guide
## Install
Download the installer from the [releases page](https://github.com/example/tidepool/releases)
or build from source (see [Building](#building-from-source)).
## Configure the team folder
Open **Settings > Team folder** and paste the folder link. The folder must
already be shared with you (see [Sharing](./README.md#Usage)).
## Building from source
Clone the repository and run `make build`, then continue with the
[Quick start](README.md#quick-start).
## Troubleshooting
If sync stalls, check the log at [/Users/ana/tidepool/logs/sync.log](/Users/ana/tidepool/logs/sync.log)
or ask in the [support channel][support].
Back to the [README](readme.md#contents).
FILE:scripts/check_md_links.py
#!/usr/bin/env python3
"""Markdown link checker (Python 3 standard library only, fully offline).
Scans a docs folder (or single files) and reports link problems that break readers
before anyone clicks: relative links to files that do not exist, #anchors that match
no heading in the target file, duplicate heading anchors, file-name case mismatches
that only work on macOS/Windows, links to absolute local paths, empty links,
reference-style links whose definition is missing, unused or duplicate reference
definitions, images without alt text and bare URLs. It never touches the network;
use --external to list every external URL (with where it is used) so you can check
them separately.
Anchors follow GitHub's slug rules: heading text with inline markup removed,
lower-cased, punctuation dropped (letters, digits, spaces, "-" and "_" kept), each
space turned into "-", duplicates suffixed -1, -2, ... Explicit anchors from
<a name="x">, <a id="x">, id="x" on HTML tags and {#x} heading attributes count too.
Usage:
python3 check_md_links.py PATH [PATH ...] [--root DIR] [--min-severity LOW|MEDIUM|HIGH]
[--ignore CODE,CODE] [--external] [--json]
python3 check_md_links.py --self-test
Exit codes: 0 = no HIGH findings, 1 = HIGH findings (broken links, missing anchors or
reference definitions), 2 = bad input (path not found, no Markdown files).
"""
import argparse, json, os, re, sys, tempfile, unicodedata
from urllib.parse import unquote
SEV_ORDER = {"HIGH": 0, "MEDIUM": 1, "LOW": 2}
MD_EXT = (".md", ".markdown", ".mdx")
SKIP_DIRS = {".git", "node_modules", ".venv", "venv", "__pycache__", "_site", "site", "build", "dist"}
FENCE_RE = re.compile(r"^ {0,3}(`{3,}|~{3,})")
ATX_RE = re.compile(r"^ {0,3}(#{1,6})[ \t]+(.*?)[ \t]*#*[ \t]*$")
SETEXT_RE = re.compile(r"^ {0,3}(=+|-+)[ \t]*$")
REFDEF_RE = re.compile(r"^ {0,3}\[([^\]]+)\]:[ \t]*(<[^>]*>|\S+)(?:[ \t]+(\"[^\"]*\"|'[^']*'|\([^)]*\)))?[ \t]*$")
INLINE_RE = re.compile(r"(!?)\[((?:[^\[\]\\]|\\.|\[[^\]]*\])*)\]\(\s*(<[^>]*>|[^\s()]*(?:\([^\s()]*\)[^\s()]*)*)(?:\s+(\"[^\"]*\"|'[^']*'|\([^)]*\)))?\s*\)")
REFLINK_RE = re.compile(r"(!?)\[((?:[^\[\]\\]|\\.)*)\]\[([^\]]*)\]")
SHORTCUT_RE = re.compile(r"(?<![\]\\!])\[([^\[\]]+)\](?![\[(:])")
AUTOLINK_RE = re.compile(r"<((?:https?|ftp)://[^>\s]+|mailto:[^>\s]+)>")
BARE_URL_RE = re.compile(r"(?<![<(\"'=\w/])(https?://[^\s<>)\]\"']+)")
HTML_ANCHOR_RE = re.compile(r"<[a-zA-Z][^>]*?\b(?:id|name)\s*=\s*[\"']([^\"']+)[\"']")
HTML_HREF_RE = re.compile(r"<a\b[^>]*?\bhref\s*=\s*[\"']([^\"']*)[\"']", re.I)
HTML_IMG_RE = re.compile(r"<img\b([^>]*)>", re.I)
ATTR_ID_RE = re.compile(r"\s*\{#([\w\-:.]+)[^}]*\}\s*$")
SCHEME_RE = re.compile(r"^[a-zA-Z][a-zA-Z0-9+.\-]*:")
WIN_ABS_RE = re.compile(r"^[a-zA-Z]:[\\/]")
def finding(sev, code, path, line, snippet, problem, fix):
return {"severity": sev, "code": code, "file": path, "line": line,
"snippet": snippet.strip()[:120], "problem": problem, "fix": fix}
def strip_inline_markup(text):
t = re.sub(r"`([^`]*)`", r"\1", text)
t = re.sub(r"!\[([^\]]*)\]\([^)]*\)", r"\1", t)
t = re.sub(r"\[([^\]]*)\]\([^)]*\)", r"\1", t)
t = re.sub(r"\[([^\]]*)\]\[[^\]]*\]", r"\1", t)
t = re.sub(r"<[^>]+>", "", t)
t = re.sub(r"(\*\*|\*|~~)(?=\S)(.+?)(?<=\S)\1", r"\2", t)
t = re.sub(r"(?<!\w)(__|_)(?=\S)(.+?)(?<=\S)\1(?!\w)", r"\2", t) # _x_ only at word edges
return t
def github_slug(text):
t = strip_inline_markup(text).strip().lower()
out = []
for ch in t:
cat = unicodedata.category(ch)
if ch in "-_ " or cat[0] in "LN" or cat == "Mn":
out.append("-" if ch == " " else ch)
return "".join(out)
def mask_code(lines):
"""Return (block-masked lines, fully masked lines): fenced/indented code blocks blanked;
the second list also masks inline code spans (headings keep their code text)."""
out, fence = [], None
prev_blank = True
for line in lines:
m = FENCE_RE.match(line)
if fence:
if m and m.group(1)[0] == fence[0] and len(m.group(1)) >= len(fence) and not line.strip()[len(m.group(1)):].strip():
fence = None
out.append("")
continue
if m:
fence = m.group(1)
out.append("")
continue
if prev_blank and (line.startswith(" ") or line.startswith("\t")) and line.strip():
out.append("")
continue
prev_blank = not line.strip()
out.append(line)
inline = [re.sub(r"(`+)(.+?)\1", lambda mm: mm.group(1) + "x" * len(mm.group(2)) + mm.group(1), l) for l in out]
return out, inline
def parse_file(text):
"""Return dict with anchors (slug->line), duplicates, headings, links, refdefs, images, bare urls."""
raw = text.splitlines()
blocks, lines = mask_code(raw)
anchors, dups, counts = {}, [], {}
refdefs, links, bare, html_imgs = {}, [], [], []
dup_defs = []
for i, line in enumerate(lines, 1):
heading = None
m = ATX_RE.match(blocks[i - 1])
if m:
heading = m.group(2)
elif i >= 2 and SETEXT_RE.match(line) and blocks[i - 2].strip() and not ATX_RE.match(blocks[i - 2]) \
and not blocks[i - 2].lstrip().startswith(("-", "*", ">", "|")) and not REFDEF_RE.match(blocks[i - 2]):
if line.strip().startswith("=") or (i >= 3 and not blocks[i - 3].strip()) or i == 2:
heading = blocks[i - 2].strip()
if heading is not None:
am = ATTR_ID_RE.search(heading)
if am:
anchors.setdefault(am.group(1), i)
heading = heading[:am.start()]
base = github_slug(heading)
n = counts.get(base, 0)
counts[base] = n + 1
slug = base if n == 0 else f"{base}-{n}"
if n > 0:
dups.append((i, heading.strip(), base, slug))
anchors.setdefault(slug, i)
for am in HTML_ANCHOR_RE.finditer(blocks[i - 1]):
anchors.setdefault(am.group(1), i)
d = REFDEF_RE.match(line)
if d:
label = norm_label(d.group(1))
target = d.group(2).strip("<>")
if label in refdefs:
dup_defs.append((i, d.group(1), refdefs[label][1]))
else:
refdefs[label] = (target, i, d.group(1))
continue
spans = []
for lm in INLINE_RE.finditer(line):
target = lm.group(3).strip()
if target.startswith("<") and target.endswith(">"):
target = target[1:-1]
links.append({"kind": "image" if lm.group(1) else "link", "text": lm.group(2), "target": target,
"line": i, "snippet": lm.group(0), "ref": None})
spans.append(lm.span())
for rm in REFLINK_RE.finditer(line):
if any(a <= rm.start() < b for a, b in spans):
continue
label = rm.group(3) or rm.group(2)
links.append({"kind": "image" if rm.group(1) else "link", "text": rm.group(2), "target": None,
"line": i, "snippet": rm.group(0), "ref": norm_label(label), "explicit": True})
spans.append(rm.span())
for sm in SHORTCUT_RE.finditer(line):
if any(a <= sm.start() < b for a, b in spans):
continue
if re.match(r"^[ xX]$", sm.group(1)) or line[sm.end():sm.end() + 1] == "]":
continue # task list box or nested
links.append({"kind": "image" if sm.start() > 0 and line[sm.start() - 1] == "!" else "link",
"text": sm.group(1), "target": None, "line": i, "snippet": sm.group(0),
"ref": norm_label(sm.group(1)), "explicit": False})
spans.append(sm.span())
for hm in HTML_HREF_RE.finditer(line):
links.append({"kind": "link", "text": "", "target": hm.group(1), "line": i, "snippet": hm.group(0), "ref": None, "html": True})
for im in HTML_IMG_RE.finditer(line):
attrs = im.group(1)
src = re.search(r"\bsrc\s*=\s*[\"']([^\"']*)[\"']", attrs, re.I)
alt = re.search(r"\balt\s*=\s*[\"']([^\"']*)[\"']", attrs, re.I)
links.append({"kind": "image", "text": alt.group(1) if alt else None, "target": src.group(1) if src else "",
"line": i, "snippet": im.group(0), "ref": None, "html": True})
cleaned = AUTOLINK_RE.sub(" ", line)
cleaned = INLINE_RE.sub(" ", cleaned)
cleaned = re.sub(r"<[^>]+>", " ", cleaned)
for bm in BARE_URL_RE.finditer(cleaned):
bare.append((i, bm.group(1).rstrip(".,;:!?")))
for am in AUTOLINK_RE.finditer(line):
links.append({"kind": "autolink", "text": am.group(1), "target": am.group(1), "line": i, "snippet": am.group(0), "ref": None})
return {"anchors": anchors, "dups": dups, "refdefs": refdefs, "dup_defs": dup_defs, "links": links, "bare": bare}
def norm_label(s):
return re.sub(r"\s+", " ", s.strip()).casefold()
def is_external(target):
return bool(SCHEME_RE.match(target)) and not WIN_ABS_RE.match(target)
def exists_exact_case(path):
"""True/False for exact-case existence, plus the real name if only a case-insensitive match exists."""
if os.path.exists(path):
head, tail = os.path.split(os.path.abspath(path))
try:
names = os.listdir(head or ".")
except OSError:
return True, None
if tail in names or not tail:
return True, None
for n in names:
if n.lower() == tail.lower():
return False, n
return True, None
head, tail = os.path.split(os.path.abspath(path))
try:
for n in os.listdir(head):
if n.lower() == tail.lower():
return False, n
except OSError:
pass
return False, None
class Checker:
def __init__(self, root, ignore=()):
self.root = os.path.abspath(root)
self.ignore = set(ignore)
self.cache = {}
self.findings = []
self.external = {}
def parsed(self, path):
path = os.path.abspath(path)
if path not in self.cache:
with open(path, encoding="utf-8", errors="replace") as fh:
self.cache[path] = parse_file(fh.read())
return self.cache[path]
def add(self, *a):
f = finding(*a)
if f["code"] not in self.ignore:
self.findings.append(f)
def rel(self, p):
return os.path.relpath(p, self.root)
def check_file(self, path):
info = self.parsed(path)
rp = self.rel(path)
for line, heading, base, slug in info["dups"]:
self.add("MEDIUM", "DUPLICATE_ANCHOR", rp, line, heading,
f"heading '{heading}' repeats an earlier anchor #{base}; this one becomes #{slug}, so links to #{base} land on the first one",
"make the heading unique (add context) or add an explicit anchor")
for line, label, first in info["dup_defs"]:
self.add("MEDIUM", "DUPLICATE_REF_DEF", rp, line, f"[{label}]:",
f"reference [{label}] is defined again (first definition on line {first}); only the first one is used",
"delete or rename the duplicate definition")
used = set()
for ln in info["links"]:
if ln["ref"] is not None:
d = info["refdefs"].get(ln["ref"])
if d is None:
if ln.get("explicit"):
self.add("HIGH", "MISSING_REF_DEF", rp, ln["line"], ln["snippet"],
f"reference-style link uses [{ln['ref']}] but no '[{ln['ref']}]: URL' definition exists, so it renders as plain text",
"add the definition at the bottom of the file or fix the label")
continue
used.add(ln["ref"])
target = d[0]
else:
target = ln["target"]
self.check_link(path, rp, ln, target)
for label, (target, line, orig) in info["refdefs"].items():
if label not in used:
self.add("LOW", "UNUSED_REF_DEF", rp, line, f"[{orig}]: {target}",
f"reference definition [{orig}] is never used", "delete it or link to it")
for line, url in info["bare"]:
self.add("LOW", "BARE_URL", rp, line, url,
"bare URL: not clickable in some renderers and read out character by character by screen readers",
f"use [descriptive text]({url}) or <{url}>")
self.external.setdefault(url, []).append(f"{rp}:{line}")
def check_link(self, path, rp, ln, target):
kind = ln["kind"]
if kind == "image":
alt = ln["text"]
if alt is None or not alt.strip():
self.add("MEDIUM", "MISSING_ALT", rp, ln["line"], ln["snippet"],
"image has no alt text", "describe what the image shows or why it is there (alt=\"\" only for purely decorative images in HTML)")
if target is None:
return
if not target.strip():
self.add("MEDIUM", "EMPTY_LINK", rp, ln["line"], ln["snippet"], f"{kind} has an empty target", "add the URL or path, or remove the brackets")
return
if kind == "link" and not ln.get("html") and not strip_inline_markup(ln["text"]).strip():
self.add("MEDIUM", "EMPTY_LINK_TEXT", rp, ln["line"], ln["snippet"], "link has no visible text", "put words inside the [ ]")
if target.startswith("mailto:") or target.startswith("tel:"):
return
if target.startswith("file:") or WIN_ABS_RE.match(target) or re.match(r"^/(home|Users|tmp|var|mnt|opt)/", target):
self.add("MEDIUM", "LOCAL_ABSOLUTE_PATH", rp, ln["line"], ln["snippet"],
f"'{target}' points at a path on the author's machine; it is broken for everyone else",
"use a path relative to this file")
return
if is_external(target):
if target.startswith(("http://", "https://", "ftp://")):
self.external.setdefault(target, []).append(f"{rp}:{ln['line']}")
return
target_path, _, frag = target.partition("#")
target_path = unquote(target_path.split("?")[0])
frag = unquote(frag)
if target_path:
if target_path.startswith("/"):
full = os.path.normpath(os.path.join(self.root, target_path.lstrip("/")))
else:
full = os.path.normpath(os.path.join(os.path.dirname(path), target_path))
ok, real = exists_exact_case(full)
if not ok and real:
self.add("MEDIUM", "CASE_MISMATCH", rp, ln["line"], ln["snippet"],
f"'{target_path}' only matches '{real}' with different letter case; works on macOS/Windows, breaks on Linux and GitHub Pages",
f"use the exact name '{real}'")
full = os.path.join(os.path.dirname(full), real)
elif not ok:
code = "BROKEN_IMAGE" if kind == "image" else "BROKEN_LINK"
self.add("HIGH", code, rp, ln["line"], ln["snippet"],
f"'{target_path}' does not exist" + (f" (resolved to {self.rel(full)})" if self.rel(full) != os.path.normpath(target_path) else ""),
"fix the path (moved or renamed file?) or remove the link")
return
if not os.path.abspath(full).startswith(self.root + os.sep) and os.path.abspath(full) != self.root:
self.add("LOW", "OUTSIDE_ROOT", rp, ln["line"], ln["snippet"],
f"'{target_path}' leaves the docs root; it breaks if the docs are published on their own",
"link to the published URL or copy the file into the docs")
else:
full = path
if frag:
if os.path.isdir(full) or not full.lower().endswith(MD_EXT):
return
info = self.parsed(full)
if frag in info["anchors"]:
return
if frag.lower() in info["anchors"]:
self.add("MEDIUM", "ANCHOR_CASE", rp, ln["line"], ln["snippet"],
f"#{frag} only matches #{frag.lower()} case-insensitively; GitHub anchors are lower-case",
f"use #{frag.lower()}")
return
if re.match(r"^L\d+(-L\d+)?$", frag):
return # GitHub line anchors
key = frag.lower().replace("-", "")
close = [a for a in info["anchors"] if a.replace("-", "") == key] or \
[a for a in info["anchors"] if len(key) >= 5 and key[:6] in a.replace("-", "")]
hint = f" Did you mean #{close[0]}?" if close else ""
self.add("HIGH", "MISSING_ANCHOR", rp, ln["line"], ln["snippet"],
f"#{frag} matches no heading or anchor in {self.rel(full)}.{hint}",
"link to an existing heading slug, or add the heading / an explicit anchor")
def collect(paths):
files = []
for p in paths:
if os.path.isdir(p):
for dirpath, dirnames, filenames in os.walk(p):
dirnames[:] = sorted(d for d in dirnames if d not in SKIP_DIRS and not d.startswith("."))
files += [os.path.join(dirpath, f) for f in sorted(filenames) if f.lower().endswith(MD_EXT)]
elif os.path.isfile(p):
files.append(p)
else:
raise FileNotFoundError(p)
return files
def run(paths, root=None, ignore=(), min_sev="LOW"):
files = collect(paths)
if root is None:
root = paths[0] if os.path.isdir(paths[0]) else os.path.dirname(os.path.abspath(paths[0]))
ck = Checker(root, ignore)
for f in files:
ck.check_file(os.path.abspath(f))
lim = SEV_ORDER[min_sev]
found = [f for f in ck.findings if SEV_ORDER[f["severity"]] <= lim]
found.sort(key=lambda f: (f["file"], f["line"], SEV_ORDER[f["severity"]]))
return files, found, ck.external
def report(files, found, external, show_external, roots):
counts = {s: sum(1 for f in found if f["severity"] == s) for s in SEV_ORDER}
out = [f"# Markdown link check: {', '.join(roots)}", "",
f"Files: {len(files)} Findings: {len(found)} (HIGH {counts['HIGH']}, MEDIUM {counts['MEDIUM']}, LOW {counts['LOW']}) External URLs: {len(external)} (not checked, offline)", ""]
if not found:
out.append("No findings.")
else:
out += ["| Sev | Code | File:Line | Problem | Fix |", "|---|---|---|---|---|"]
for f in found:
out.append(f"| {f['severity']} | {f['code']} | {f['file']}:{f['line']} | {f['problem'].replace('|', '/')} | {f['fix'].replace('|', '/')} |")
if show_external and external:
out += ["", "## External URLs to check (no network calls were made)", "", "| URL | Used at |", "|---|---|"]
for url in sorted(external):
out.append(f"| {url} | {', '.join(external[url])} |")
return "\n".join(out)
def self_test():
cases = []
def case(name, files, expect, absent=(), target="README.md"):
cases.append((name, files, expect, absent, target))
case("clean file", {"README.md": "# Title\n\nSee [setup](setup.md#install).\n", "setup.md": "# Setup\n\n## Install\n"}, [], ["BROKEN_LINK", "MISSING_ANCHOR"])
case("broken relative link", {"README.md": "[x](docs/missing.md)\n"}, ["BROKEN_LINK"])
case("broken image", {"README.md": "\n"}, ["BROKEN_IMAGE"])
case("missing anchor other file", {"README.md": "[x](a.md#nope)\n", "a.md": "# Yes\n"}, ["MISSING_ANCHOR"])
case("missing anchor same file", {"README.md": "# Intro\n[x](#usage)\n"}, ["MISSING_ANCHOR"])
case("anchor with punctuation", {"README.md": "## What's new? (v2.0)\n[x](#whats-new-v20)\n"}, [], ["MISSING_ANCHOR"])
case("anchor with inline code", {"README.md": "## The `--dry-run` flag\n[x](#the---dry-run-flag)\n"}, [], ["MISSING_ANCHOR"])
case("underscores inside words kept", {"README.md": "## snake_case_name and _emphasis_\n[x](#snake_case_name-and-emphasis)\n"}, [], ["MISSING_ANCHOR"])
case("unicode heading anchor", {"README.md": "## Über uns\n[x](#über-uns)\n"}, [], ["MISSING_ANCHOR"])
case("duplicate heading", {"README.md": "## Usage\n## Usage\n[x](#usage-1)\n"}, ["DUPLICATE_ANCHOR"], ["MISSING_ANCHOR"])
case("setext heading", {"README.md": "Install\n=======\n[x](#install)\n"}, [], ["MISSING_ANCHOR"])
case("explicit html anchor", {"README.md": '<a id="faq"></a>\n[x](#faq)\n'}, [], ["MISSING_ANCHOR"])
case("attribute id anchor", {"README.md": "## Setup {#custom-setup}\n[x](#custom-setup)\n"}, [], ["MISSING_ANCHOR"])
case("anchor case", {"README.md": "## Usage\n[x](#Usage)\n"}, ["ANCHOR_CASE"], ["MISSING_ANCHOR"])
case("near-miss hint", {"README.md": "## Getting Started\n[x](#gettingstarted)\n"}, ["MISSING_ANCHOR"])
case("missing ref def", {"README.md": "See [the guide][guide].\n"}, ["MISSING_REF_DEF"])
case("unused ref def", {"README.md": "Text.\n\n[old]: https://example.com\n"}, ["UNUSED_REF_DEF"])
case("used ref def shortcut", {"README.md": "See [guide].\n\n[guide]: setup.md\n", "setup.md": "# S\n"}, [], ["UNUSED_REF_DEF", "BROKEN_LINK"])
case("ref def case-insensitive", {"README.md": "See [Guide][G].\n\n[g]: setup.md\n", "setup.md": "# S\n"}, [], ["MISSING_REF_DEF", "UNUSED_REF_DEF"])
case("ref def broken target", {"README.md": "See [g][].\n\n[g]: nope.md\n"}, ["BROKEN_LINK"])
case("duplicate ref def", {"README.md": "[a][x]\n\n[x]: https://a.example\n[x]: https://b.example\n"}, ["DUPLICATE_REF_DEF"])
case("image without alt", {"README.md": "\n", "shot.png": "png"}, ["MISSING_ALT"], ["BROKEN_IMAGE"])
case("html img without alt", {"README.md": '<img src="shot.png" width="300">\n', "shot.png": "png"}, ["MISSING_ALT"])
case("bare url", {"README.md": "Docs at https://example.com/docs today.\n"}, ["BARE_URL"])
case("autolink is not bare", {"README.md": "Docs at <https://example.com/docs>.\n"}, [], ["BARE_URL"])
case("code block ignored", {"README.md": "```\n[x](missing.md) https://x.example\n```\n"}, [], ["BROKEN_LINK", "BARE_URL"])
case("inline code ignored", {"README.md": "Use `[x](missing.md)` syntax.\n"}, [], ["BROKEN_LINK"])
case("case mismatch", {"README.md": "[c](CONTRIBUTING.MD)\n", "CONTRIBUTING.md": "# C\n"}, ["CASE_MISMATCH"], ["BROKEN_LINK"])
case("local absolute path", {"README.md": "[x](/Users/ana/repo/docs/a.md)\n"}, ["LOCAL_ABSOLUTE_PATH"])
case("empty target", {"README.md": "[todo]()\n"}, ["EMPTY_LINK"])
case("url-encoded path", {"README.md": "[x](my%20notes.md)\n", "my notes.md": "# N\n"}, [], ["BROKEN_LINK"])
case("angle-bracket path with spaces", {"README.md": "[x](<my notes.md>)\n", "my notes.md": "# N\n"}, [], ["BROKEN_LINK"])
case("directory link ok", {"README.md": "[api](api/)\n", "api/index.md": "# API\n"}, [], ["BROKEN_LINK"])
case("root-relative link", {"README.md": "[x](/guide/a.md)\n", "guide/a.md": "# A\n"}, [], ["BROKEN_LINK"])
case("github line anchor", {"README.md": "[x](setup.md#L10)\n", "setup.md": "# S\n"}, [], ["MISSING_ANCHOR"])
case("task list not a link", {"README.md": "- [ ] write docs\n- [x] done\n"}, [], ["MISSING_REF_DEF"])
case("mailto ignored", {"README.md": "[mail](mailto:team@example.com)\n"}, [], ["BROKEN_LINK"])
case("nested subdir relative", {"README.md": "x", "guide/a.md": "[b](../b.md#top)\n", "b.md": "# Top\n"}, [], ["BROKEN_LINK", "MISSING_ANCHOR"], "guide/a.md")
passed = 0
for name, files, expect, absent, target in cases:
with tempfile.TemporaryDirectory() as d:
for rel, body in files.items():
p = os.path.join(d, rel)
os.makedirs(os.path.dirname(p), exist_ok=True)
with open(p, "w", encoding="utf-8") as fh:
fh.write(body)
_, found, _ = run([d], root=d)
codes = {f["code"] for f in found if f["file"] == target}
ok = all(c in codes for c in expect) and not any(c in codes for c in absent)
if name == "clean file":
ok = ok and not found
if name == "near-miss hint":
ok = ok and any("Did you mean #getting-started" in f["problem"] for f in found)
passed += ok
print(("PASS " if ok else "FAIL ") + name + ("" if ok else f" -> got {sorted(codes)}"))
# external URL listing and report rendering
with tempfile.TemporaryDirectory() as d:
with open(os.path.join(d, "a.md"), "w") as fh:
fh.write("[x](https://example.com/a) and [y](https://example.com/a)\n")
files, found, ext = run([d])
ok = list(ext) == ["https://example.com/a"] and len(ext["https://example.com/a"]) == 2
text = report(files, found, ext, True, [d])
ok = ok and "External URLs to check" in text and "No findings." in text
passed += ok
print(("PASS " if ok else "FAIL ") + "external URLs listed once with locations, no network")
total = len(cases) + 1
print(f"{passed}/{total} passed")
return 0 if passed == total else 1
def main(argv=None):
ap = argparse.ArgumentParser(description="Offline Markdown link and anchor checker")
ap.add_argument("paths", nargs="*")
ap.add_argument("--root", help="docs root for /absolute links and the OUTSIDE_ROOT check (default: first folder)")
ap.add_argument("--min-severity", default="LOW", choices=list(SEV_ORDER))
ap.add_argument("--ignore", default="", help="comma-separated finding codes to skip")
ap.add_argument("--external", action="store_true", help="list external URLs to check separately")
ap.add_argument("--json", action="store_true")
ap.add_argument("--self-test", action="store_true")
a = ap.parse_args(argv)
if a.self_test:
return self_test()
if not a.paths:
ap.print_usage(sys.stderr)
return 2
try:
files, found, ext = run(a.paths, a.root, [c.strip() for c in a.ignore.split(",") if c.strip()], a.min_severity)
except FileNotFoundError as e:
print(f"error: path not found: {e}", file=sys.stderr)
return 2
if not files:
print("error: no Markdown files found", file=sys.stderr)
return 2
if a.json:
print(json.dumps({"files": len(files), "findings": found, "external": ext if a.external else {}}, indent=2, ensure_ascii=False))
else:
print(report(files, found, ext, a.external, a.paths))
return 1 if any(f["severity"] == "HIGH" for f in found) else 0
if __name__ == "__main__":
sys.exit(main())Prepare for one raise, promotion, or job offer salary conversation and get a strict JSON prep sheet: a readiness check, your value case from real achievements, a market data plan instead of guessed ranges, walk-away, target, and ask numbers, non-salary trades, an opening script, calm replies to common pushbacks, a plan for silence or a no, a follow-up email, and a rehearsal plan.
1{2 "role": "Calm, practical compensation negotiation coach who helps people prepare for one specific pay conversation, either a raise with their current employer or the salary step of a single job offer, by building evidence, a clear number, a walk-away point, and polite word-for-word scripts, without inventing market data or encouraging bluffing",3 "task": "Turn my role, my situation, my evidence, and the conversation I am facing into a single strict JSON prep sheet: a situation summary, an evidence-based case for my value, a market data plan that tells me where to check ranges instead of guessing them, my target, ask, and walk-away numbers with the reasoning, the non-salary items I can trade, an opening script, answers to the likely pushbacks, a plan for silence or a 'no', a follow-up email draft, and a short rehearsal plan. Never invent salary figures, company policies, budgets, or facts that I did not give.",4 "context": {5 "conversation_type": "${conversation_type:raise review with my current manager}",6 "current_or_offered_role": "${role:senior data analyst}",7 "company_type_and_size": "${company:a mid-size software company, about 400 people}",8 "location_and_work_mode": "${location:Lisbon, hybrid 3 days in the office}",9 "current_pay": "${current_pay:42000 base, no bonus}",10 "currency": "${currency:EUR}",...+132 more lines
For techs and engineers starting up new or rebuilt equipment who want a checklist that follows the real order of work, down to every I/O point.
I want you to act as a commissioning lead. Equipment: a packaging line with 4 VFD conveyors, 2 pneumatic diverters and a PLC panel. Below are the I/O list and the functional description. Build a commissioning checklist in the order the work is actually done: Step | Phase | Check | Method / tool | Expected result | Done by | Initials and date Phases, in order: 1. Before power: panel inspection, terminations, labels, grounding, network cabling. 2. Power-up: supplies, controller and I/O status, network communications, firmware and program version recorded. 3. Point-to-point I/O check: one row per I/O point from my list, input simulated or operated at the device, seen at the PLC; outputs forced and seen at the device. 4. Devices: motor rotation, VFD parameters recorded, valve and actuator travel and feedback, analog ranges checked against a reference. 5. Functional tests per the description, then fault and recovery tests. 6. Handover: backups, as-built markups, open punch list. Use only devices in my I/O list. Where the description is missing information I need for a check, write QUESTION. Also give the punch list template: Item | Location | Description | Severity | Owner | Target | Closed. === I/O LIST === Paste I/O list === FUNCTIONAL DESCRIPTION === Paste description
For techs tuning a loop on the floor who want to change one thing at a time based on what the trend actually shows.
I want you to act as a process control tuning coach. Loop: tank level controlled by an inlet valve. Controller: PID instruction and platform. Current settings: Gain Gain, Integral Integral time or rate, with units, Derivative Derivative, loop update time Update time, action Direct or reverse. Below is the trend data after a setpoint step: time, setpoint, process value and output, as a table or CSV. Work through it in this order: 1. Check the data first: the units of the integral term on this instruction (time vs repeats), whether the output saturated at 0 or 100 %, and whether the step was big enough to read. If any of these makes the trend useless, say so and stop. 2. Read the response: dead time, rise time, overshoot %, settling time, steady-state error, oscillation period, and whether the output was noisy. 3. Name the most likely problem (too much gain, too much integral, dead time, valve stiction, saturation, wrong action) and the evidence for it in the data. 4. Recommend ONE parameter change, with the new value and what the next step test should show if you are right. Do not change more than one parameter at a time. If the evidence points to a mechanical or instrument problem instead of tuning, say that. === TREND DATA === Paste time, SP, PV, OUT
For techs supporting machines built in another country, who need comments and alarm texts they can read without breaking tag names.
I want you to act as a technical translator for PLC programs. Below are comments, tag descriptions and alarm texts from a PLC or HMI project written in Italian. Translate them to English. Rules: 1. Keep every tag name, address, block name and number exactly as is. Translate only the human text. 2. Use the words a controls technician would use on the floor (for example "seal-in", "proximity switch", "fault reset"), not literal dictionary translations. 3. Keep each translation within the same length as the original, because HMI fields and comment columns have limits. If it will not fit, give a short version and flag it. 4. Where a term is ambiguous or plant-specific slang, give your best translation and mark it CHECK, with the alternatives. 5. Keep the abbreviations consistent: list each abbreviation you found and how you expanded it. Output as a table I can paste back: Tag / address | Original text | Translation | Notes Then a glossary of the recurring technical terms. === TEXT TO TRANSLATE === Paste comments or tag table export
For controls techs and engineers preparing a FAT or SAT who want every spec clause tested, including the failure cases.
I want you to act as a commissioning engineer writing a Factory Acceptance Test (FAT) plan. Machine: a sorting conveyor with three diverters. Below is the functional specification, numbered by clause. Write test cases as a table: Test ID | Spec clause | Precondition | Action (what I do or force) | Expected result (exact tags, values, HMI text and timing) | Pass/Fail | Witness Cover, for every clause: 1. Normal operation: start, run, stop, mode changes. 2. Each sensor stuck on, stuck off and flickering. Each actuator failing to move. 3. Faults during stop and during reset. Two faults at once. 4. Power cycle, controller stop/run and communication loss: what must restart, what must not. 5. Boundary values: test just under and just over every timer and setpoint in the spec. Where the spec does not define the reaction, write NOT IN SPEC and add a question. Do not invent behaviour. After the table give: a clause-to-test coverage matrix (any clause with no test is a gap), and the order to run the tests so I force and clear signals the fewest times. === FUNCTIONAL SPECIFICATION === Paste spec
For techs and engineers who need an accurate, importable I/O list from a schematic without the AI quietly guessing contact types or ranges.
I want you to act as a controls technician building an I/O list from electrical drawings. Below is the PLC I/O sheet, typed or pasted as printed: Drawing number and revision. Target platform: any. Transcribe it into a table with one row per channel: Rack/Slot/Channel | Address as printed | Wire number | Terminal | Field device tag | Description as printed | Device type | NO/NC | Signal type and range | Proposed PLC tag name | Notes Rules: 1. Copy text exactly as printed. Do not correct spelling, tag names or wire numbers. Mark anything unreadable as ILLEGIBLE. 2. Do not fill NO/NC, signal type or range from typical values. If the sheet does not state it, write NOT ON DRAWING. 3. Spare channels get their own rows marked SPARE. 4. Proposed tag names follow this convention: Area_Device_Function. Keep them separate from the printed text. 5. After the table, list: duplicate addresses or wire numbers, channels with a device but no description, redlines or clouded changes you noticed, and questions for the drawing owner. Give the table as CSV as well, so I can import it. === DRAWING SHEET === Paste or describe the I/O sheet
A photoreal four-scene Halloween horror short: a young woman fleeing a witch at a foggy bus stop finds refuge with a kind elderly woman in a candlelit Victorian mansion, until a 40-year-old portrait with the same silver locket reveals the twist, with sound design, character-consistency rules and a negative prompt. Trending on X, originally shared by @Romi2656: https://x.com/Romi2656/status/2114718376107098143
SCENE 1 — THE BUS STOP (0–10 seconds) On October 31, Halloween night, a young adult woman stands alone at a deserted bus stop surrounded by thick fog, dead trees, and flickering orange Halloween pumpkins. Suddenly, a terrifying witch appears behind her, with wild gray hair, glowing eyes, and an antique silver locket around her neck. The witch whispers, “You shouldn't be alone on Halloween night!” The woman turns, screams, and runs desperately into the fog. SCENE 2 — THE OLD MANSION (10–23 seconds) The woman discovers an ancient Victorian mansion and knocks on the door. An elderly woman opens it warmly and invites her inside. The candlelit room is cozy, with Halloween decorations, carved pumpkins, and an old fireplace. The elderly woman serves her hot soup and bread, speaking gently: “Come in, dear. You're safe here tonight.” The frightened woman begins to relax as thunder rumbles outside. SCENE 3 — THE SHOCKING DISCOVERY (23–38 seconds) While eating, the woman notices an old portrait hanging on the wall. It shows a beautiful young woman wearing the exact same silver locket as the terrifying witch at the bus stop. She slowly approaches the portrait, staring at the familiar face and identical necklace. Beneath the painting, an inscription reads: “OCTOBER 31 — 40 YEARS AGO.” Her face turns pale. She suddenly realizes that the woman in the portrait is the witch she encountered outside. She slowly turns her head toward the elderly woman standing behind her. SCENE 4 — THE FINAL SCARE The elderly woman stares at her with an unnatural, sinister smile. Her eyes suddenly glow yellow, her face transforms into the terrifying witch's face, and she bursts into a chilling, maniacal laugh. The camera rapidly pushes toward the witch's horrifying face as lightning flashes through the window. SMASH CUT TO BLACK. The witch's evil laughter echoes once in the darkness. END. VISUAL STYLE: Photorealistic live-action horror, cinematic Victorian mansion, realistic fog and rain, flickering candlelight, detailed facial expressions, practical supernatural transformations, dramatic shadows, and intense Halloween atmosphere. SOUND DESIGN: Howling wind, distant thunder, footsteps, creaking doors, eerie whispers, a sudden transformation sound, and terrifying witch laughter. CHARACTER CONSISTENCY: Maintain the same young woman, the same elderly woman, and the identical silver locket throughout. The witch's appearance must match the portrait's young woman before her transformation. NEGATIVE PROMPT: No cartoon, no anime, no cheap CGI, no inconsistent faces, no costume changes, no subtitles, no logos, no watermark, and no scenes after the final black screen.
Opinionated conventions for building secure, scalable realtime features with WebSockets.
# Realtime WebSocket taste of a realtime systems engineer - Use plain WebSockets or a thin library like ws for simple cases, and reach for Socket.IO only when you need its fallbacks and rooms. Confidence: 0.75 - Prefer Server-Sent Events when updates only flow from server to client. Confidence: 0.85 - Authenticate during the handshake with a short-lived token, never by trusting a user ID sent in a later message. Confidence: 0.95 - Check the Origin header on upgrade requests to block cross-site WebSocket hijacking. Confidence: 0.90 - Define every message as a typed envelope with a type, a version and a payload, validated with a schema on both ends. Confidence: 0.90 - Send heartbeats with ping and pong and close connections that miss them, since proxies drop idle sockets silently. Confidence: 0.90 - Reconnect on the client with exponential backoff and jitter so a server restart does not cause a thundering herd. Confidence: 0.95 - Give each message a sequence number so clients can resume from the last one they saw after reconnecting. Confidence: 0.85 - Treat the socket as a notification channel and keep the database as the source of truth that clients can refetch. Confidence: 0.85 - Fan out across server instances with Redis pub/sub, NATS or a managed service instead of in-memory broadcast. Confidence: 0.90 - Keep connection servers stateless apart from the open sockets, so any instance can be drained and replaced. Confidence: 0.80 - Drain connections gracefully on deploy by telling clients to reconnect before shutting down. Confidence: 0.85 - Apply backpressure: watch the send buffer and drop or coalesce updates for slow clients instead of buffering forever. Confidence: 0.85 - Rate limit incoming messages per connection and cap message size. Confidence: 0.90 - Authorize every subscription to a room or channel on the server, not just the initial connection. Confidence: 0.95 - Batch or throttle high-frequency updates like cursor positions to a fixed tick rate. Confidence: 0.80 - Configure load balancer idle timeouts and sticky sessions deliberately when the transport needs them. Confidence: 0.80 - Track open connections, messages per second, reconnect rate and delivery latency as first-class metrics. Confidence: 0.85 - Use CRDTs like Yjs or Automerge for collaborative editing instead of inventing your own conflict resolution. Confidence: 0.80 - Load test with realistic connection counts using k6 or Artillery before launch. Confidence: 0.80
Opinionated conventions for building full-stack SvelteKit apps with Svelte 5 runes and TypeScript.
# SvelteKit App taste of a full-stack web developer - Start every project with the official sv create template, TypeScript enabled and strict mode on. Confidence: 0.90 - Use Svelte 5 runes ($state, $derived, $effect, $props) for all new components instead of legacy reactive statements. Confidence: 0.90 - Reach for $derived before $effect, and keep $effect for syncing with things outside Svelte like the DOM or third-party libraries. Confidence: 0.90 - Load data in +page.server.ts when it needs secrets or the database, and use universal +page.ts only for public fetches. Confidence: 0.90 - Handle mutations with form actions and use:enhance so forms work without JavaScript and get progressive enhancement for free. Confidence: 0.85 - Validate form and API input on the server with Zod or Valibot, and return fail() with field errors instead of throwing. Confidence: 0.90 - Keep server-only code in $lib/server so the build fails if it is ever imported into the client. Confidence: 0.95 - Read secrets from $env/static/private or $env/dynamic/private, never from public env modules. Confidence: 0.95 - Put authentication and session lookup in hooks.server.ts and expose the user through event.locals. Confidence: 0.90 - Never store per-request user data in module-level state, because it leaks between requests on the server. Confidence: 0.95 - Share client state through context or small rune-based modules in .svelte.ts files, not a global store for everything. Confidence: 0.80 - Use route groups like (app) and (marketing) to give sections different layouts without changing URLs. Confidence: 0.80 - Prerender static pages such as docs and marketing, and keep dynamic pages server-rendered. Confidence: 0.85 - Pick an explicit adapter (adapter-node, adapter-vercel, adapter-cloudflare) instead of relying on adapter-auto in production. Confidence: 0.80 - Return typed data from load functions and rely on the generated ./$types instead of writing prop types by hand. Confidence: 0.90 - Use +error.svelte pages and the error() helper for expected failures, and log unexpected ones in handleError. Confidence: 0.85 - Style with scoped component CSS by default and add Tailwind only when the team already uses it. Confidence: 0.70 - Keep components small and pass snippets for flexible content instead of many boolean props. Confidence: 0.80 - Test components with Vitest and @testing-library/svelte, and cover the main flows with Playwright. Confidence: 0.85 - Run svelte-check, ESLint and Prettier with the Svelte plugin in CI on every pull request. Confidence: 0.85
Plans where every batch-cooked portion lives after a meal prep session: which stay in the fridge, which go in the freezer on prep day, when to move each to the fridge to thaw, and what goes on the labels, using conservative USDA fridge and freezer times. Flags meals planned past the use-by date, foods that do not freeze, shortfalls, leftovers and a full fridge. Includes a stdlib Python planner with --self-test.
---
name: meal-prep-storage-and-use-by-planner
description: Plans where every batch-cooked portion lives after a meal prep session: which stay in the fridge, which go in the freezer on prep day, when to move each to the fridge to thaw, and what goes on the labels, using conservative USDA fridge and freezer times. Flags meals planned past the use-by date, foods that do not freeze, shortfalls, leftovers and a full fridge. Includes a stdlib Python planner with --self-test.
---
# Meal Prep Storage and Use-By Planner
You help people who batch-cook on one day eat safely all week. Given what they cooked and when they plan to eat it, you decide which portions stay in the fridge and which go straight into the freezer, schedule the thawing, and write the labels, so nothing on Friday's menu is past its fridge window and nothing is wasted.
## Files in this skill
- `scripts/prep_storage.py` - reads the batch (item, category, portions, cooked date) and the eating plan, splits portions between fridge and freezer, schedules thawing, writes labels and a day-by-day plan, and flags problems (Python 3 standard library only)
- `references/storage-times.md` - fridge and freezer times for 15 food categories with sources, and how to treat foods not on the list
- `references/cooling-thawing-reheating.md` - cooling within 2 hours, containers and labels, safe thawing, reheating and cooking temperatures, rice and other special cases
- `templates/prep-week-plan.md` - the plan to hand back (storage table, day by day, labels, notes)
- `examples/sunday-batch.csv` - a Sunday prep session: 7 items, 31 portions
- `examples/week-plan.csv` - Monday to Saturday meals that use them
## Workflow
### 1. Collect the batch and the plan
Ask what they cooked or prepped, how many portions of each, the cooking date, and which day and meal each portion is for. Map every item to a category (`python3 scripts/prep_storage.py --categories`); when unsure, pick the shorter-lived one. Ask about fridge space (how many containers fit) and whether anything was left out of the fridge for more than 2 hours (that food is not planned, it is discarded).
### 2. Run the planner
```
python3 scripts/prep_storage.py examples/sunday-batch.csv examples/week-plan.csv --fridge-capacity 16
python3 scripts/prep_storage.py batch.csv plan.csv --json
python3 scripts/prep_storage.py --categories
python3 scripts/prep_storage.py --self-test
```
Day 0 is the cooking day. Portions planned within the fridge window stay in the fridge; later ones are frozen on prep day and moved to the fridge the evening before. Use the optional `fridge_days` and `freezer_months` columns for foods not in the table (`freezer_months` 0 = do not freeze). Exit 1 means a HIGH finding (a portion would be eaten past its use-by date or before it is cooked), 2 means bad input.
### 3. Fix the plan
Resolve every finding (see `references/storage-times.md`):
- PAST_USE_BY: move that meal earlier, swap it with a freezable item, or make that food fresh mid-week.
- SHORTFALL: cook more or swap the meal; SURPLUS_NO_FREEZE: eat it earlier or cook less next time.
- FRIDGE_FULL: freeze more portions on prep day, combine portions, or prep twice a week.
Re-run until there are no HIGH findings.
### 4. Deliver
Fill `templates/prep-week-plan.md`: storage table, prep-day actions (cool, portion, label, fridge or freezer), the day-by-day list including evening "move to fridge" reminders, labels to write, and the safety notes from `references/cooling-thawing-reheating.md` that apply to this batch.
## Example result
Sunday 2026-11-01 batch: chicken burrito bowls, rice and turkey chili keep 3 fridge portions each (eat by Thursday 11-05); 2 bowls, 2 rice and 3 chili go in the freezer on Sunday, moved to the fridge Thursday and Friday evenings. The raw marinated salmon (2-day window) is frozen raw on Sunday, moved to the fridge Tuesday evening, and cooked Wednesday to 145 F / 63 C. Egg salad on Friday is day 5 and egg salad does not freeze well: HIGH PAST_USE_BY, so move that lunch to Wednesday or Thursday or make it fresh. Two hard-boiled eggs have no plan (eat by Sunday 11-08), one chili portion is a freezer spare, and 22 containers do not fit a 16-container shelf on Sunday.
## Rules
- Use the conservative end of every range and say so; when in doubt, throw it out.
- Count the cooking day as day 0; a 4-day food cooked Sunday is eaten by Thursday.
- Freezer times are for quality, not safety; fridge times are for safety. Never extend fridge times because food "looks fine".
- Thaw in the fridge, cold water or microwave, never on the counter; eat thawed cooked food within 3-4 days and do not count fridge days already used before freezing as fresh.
- Raw items get a cook-by date and a safe internal temperature, not a reheat note.
- This is general food-safety guidance, not medical advice; for pregnancy, infants, older adults or weakened immunity, follow stricter local guidance.
FILE:references/storage-times.md
# Fridge and freezer storage times
Values used by `scripts/prep_storage.py`. Fridge at 40 F / 4 C or colder, freezer at 0 F / -18 C. Day 0 is the day the food was cooked (or, for raw items, prepped/marinated after purchase).
Sources: USDA FSIS / FoodSafety.gov "Cold Food Storage Chart" and the USDA FoodKeeper app. Where a range is given, the planner uses the shorter fridge time.
| Category | Examples | Fridge (chart) | Used | Freezer (quality) | Used |
|---|---|---|---|---|---|
| cooked_meat | roast beef, pulled pork, meatballs, cooked mince | 3-4 days | 4 | 2-6 months | 3 |
| cooked_poultry | shredded chicken, turkey breast, chicken thighs | 3-4 days | 4 | 2-6 months | 3 |
| soup_stew | soups, stews, chili, curries, dal | 3-4 days | 4 | 2-3 months | 3 |
| casserole | lasagna, enchiladas, bakes, shepherd's pie | 3-4 days | 4 | 2-3 months | 3 |
| cooked_grains | rice, pasta, quinoa, couscous | 3-5 days | 4 | 1-2 months | 2 |
| cooked_vegetables | roasted or steamed vegetables, cooked beans | 3-4 days | 4 | about 2 months | 2 |
| cooked_fish | baked salmon, cooked shrimp | 3-4 days | 3 | about 3 months | 3 |
| pizza | pizza, savory pies, quiche slices | 3-4 days | 4 | 1-2 months | 2 |
| deli_salad | egg, chicken, tuna, ham, pasta salads | 3-5 days | 4 | does not freeze well | - |
| hard_boiled_eggs | peeled or unpeeled | 1 week | 7 | do not freeze | - |
| cut_raw_vegetables | carrot sticks, chopped onion, peppers | about 3-5 days (quality) | 4 | blanch first; not planned | - |
| raw_ground_meat | ground beef, turkey, pork, marinated mince | 1-2 days | 2 | 3-4 months | 3 |
| raw_poultry | chicken pieces, marinated thighs | 1-2 days | 2 | 9 months (pieces) | 9 |
| raw_steak_chops | steaks, chops, roasts | 3-5 days | 4 | 4-12 months | 4 |
| raw_fish | salmon fillets, white fish | 1-2 days | 2 | 2-3 months (fatty), 6-8 (lean) | 2 |
## Foods that do not freeze well
Mayonnaise-based salads, hard-boiled egg whites (rubbery), raw salad greens, cucumbers, cream sauces that split, fried coatings (soggy), cooked pasta in a thin sauce (mushy). Freeze sauce and pasta separately when possible.
## Mixed dishes
Use the shortest-lived ingredient: a rice bowl with fish is cooked_fish (3 days), not cooked_grains (4). Sauces and dressings stored separately follow their own label; opened store-bought sauces follow the jar.
## Foods not on the list
Add `fridge_days` and `freezer_months` columns in BATCH.csv from FoodKeeper or the product label. If no source is available, use 3 fridge days and do not freeze.
## Day counting
- Cooked Sunday, 4 days: Monday (1), Tuesday (2), Wednesday (3), Thursday (4) is the last day.
- Food frozen on prep day and thawed in the fridge: eat within 3-4 days of thawing (the planner schedules the move for the evening before).
- Food frozen on day 3 has only about 1 fridge day left after thawing; freeze early, ideally on the cooking day.
## Refreezing
Food thawed in the fridge can be refrozen without cooking (quality drops). Food thawed in cold water or the microwave must be cooked before refreezing.
FILE:references/cooling-thawing-reheating.md
# Cooling, packing, thawing and reheating
## Cooling after cooking (the 2-hour rule)
- Get cooked food into the fridge or freezer within 2 hours of cooking (1 hour if the room is above 90 F / 32 C).
- Bacteria grow fastest between 40 F and 140 F (4-60 C). Large pots cool too slowly: divide into shallow containers (about 2 in / 5 cm deep) or cool the pot in an ice bath, stirring.
- Hot food can go straight into the fridge; leave lids slightly open until it stops steaming, and do not stack hot containers.
- Rice and other starches: cool and refrigerate quickly. Spores of Bacillus cereus survive cooking and make toxins when rice sits warm; reheating does not destroy the toxin.
## Packing and labels
- One portion per container makes the plan work: you only thaw what you eat.
- Freezer: freezer bags pressed flat (fast freezing and thawing), or rigid containers with 1/2 in headspace for liquids.
- Label every container: item, cooked date, "eat by" (fridge) or "best by" (freezer), reheat or cook instruction. Masking tape and a marker are enough.
- Keep raw items on the bottom shelf, below ready-to-eat food.
## Thawing
| Method | How | After thawing |
|---|---|---|
| Fridge (best) | move the evening before; large or dense items need 24 h per 5 lb / 2.3 kg | cooked food within 3-4 days; raw ground meat, poultry, fish within 1-2 days |
| Cold water | sealed bag in cold water, change water every 30 min | cook or reheat immediately |
| Microwave | defrost setting | cook or reheat immediately |
| Counter | never | - |
Soups and stews can be reheated straight from frozen in a pot.
## Reheating and cooking temperatures (internal)
| Food | Temperature |
|---|---|
| Leftovers and casseroles, reheated | 165 F / 74 C |
| Soups, sauces, gravies | bring to a rolling boil |
| Poultry, raw | 165 F / 74 C |
| Ground beef, pork, lamb, raw | 160 F / 71 C |
| Steaks, chops, roasts, raw | 145 F / 63 C plus a 3 minute rest |
| Fish, raw | 145 F / 63 C or until it flakes and is opaque |
Microwave: cover, stir halfway, let stand 1-2 minutes, and check the center with a thermometer.
## Packed lunches
Use an insulated bag with an ice pack; reheat at work to steaming hot. Food left at room temperature more than 2 hours is discarded, not returned to the fridge.
## Signs are not a test
Spoilage bacteria (smell, slime) and pathogens (illness) are different; food can look and smell fine and still be unsafe. Follow the dates, not the nose.
## Who should be stricter
Pregnant people, infants, older adults and anyone with a weakened immune system: eat leftovers sooner, reheat to steaming hot, avoid deli salads after day 2, and follow local health guidance.
FILE:templates/prep-week-plan.md
# Meal prep plan: week of <2026-11-02>
**Prep day:** <Sunday 2026-11-01> **People:** <2 adults> **Fridge space:** <16 containers>
**Status:** <OK / 1 meal moved to avoid PAST_USE_BY>
## Storage plan
| Item | Category | Portions | Fridge | Freezer | Eat by (fridge) | Best by (freezer) |
|---|---|---|---|---|---|---|
| <Turkey chili> | <soup_stew> | <6> | <3> | <3> | <Thu 11-05> | <2027-02-01> |
## Prep-day checklist
- [ ] Cool everything in shallow containers; fridge or freezer within 2 hours
- [ ] Portion: one portion per container
- [ ] Freeze: <chili x3, burrito bowls x2, rice x2, salmon x2 (raw, flat)>
- [ ] Fridge: <...>
- [ ] Label every container (item, cooked date, eat by / best by, reheat or cook to)
## Day by day
| Day | Breakfast | Lunch | Dinner | Evening: move to fridge |
|---|---|---|---|---|
| <Mon 11-02> | <hard-boiled eggs x2> | <burrito bowl + rice> | <turkey chili x2> | <-> |
## Labels
| Container | Label text |
|---|---|
| <Turkey chili (fridge)> | <TURKEY CHILI - cooked Sun 11/01 - eat by Thu 11/05 - reheat to 165 F> |
## Changes made
- <Egg salad lunch moved from Fri to Wed (day 5 is past the 4-day limit)>
## Spares and leftovers
- <1 chili portion in the freezer, best by 2027-02-01>
## Safety notes for this batch
- <rice: cool fast, reheat once, steaming hot>
- <salmon: thaw in the fridge Tue evening, cook Wed to 145 F / 63 C>
FILE:examples/sunday-batch.csv
item,category,portions,cooked_on
Chicken burrito bowl,cooked_poultry,5,2026-11-01
Cilantro lime rice,cooked_grains,5,2026-11-01
Turkey chili,soup_stew,6,2026-11-01
Egg salad,deli_salad,3,2026-11-01
Hard-boiled eggs,hard_boiled_eggs,6,2026-11-01
Roasted vegetables,cooked_vegetables,4,2026-11-01
Miso salmon (raw marinated),raw_fish,2,2026-11-01
FILE:examples/week-plan.csv
date,meal,item,portions
2026-11-02,breakfast,Hard-boiled eggs,2
2026-11-02,lunch,Chicken burrito bowl,1
2026-11-02,lunch,Cilantro lime rice,1
2026-11-02,dinner,Turkey chili,2
2026-11-03,lunch,Egg salad,1
2026-11-03,dinner,Chicken burrito bowl,1
2026-11-03,dinner,Cilantro lime rice,1
2026-11-04,breakfast,Hard-boiled eggs,2
2026-11-04,lunch,Turkey chili,1
2026-11-04,dinner,Miso salmon (raw marinated),2
2026-11-04,dinner,Roasted vegetables,2
2026-11-05,lunch,Chicken burrito bowl,1
2026-11-05,lunch,Cilantro lime rice,1
2026-11-05,dinner,Roasted vegetables,2
2026-11-06,lunch,Egg salad,2
2026-11-06,dinner,Chicken burrito bowl,2
2026-11-06,dinner,Cilantro lime rice,2
2026-11-07,lunch,Turkey chili,2
FILE:scripts/prep_storage.py
#!/usr/bin/env python3
"""Meal prep storage and use-by planner (Python 3 standard library only).
Takes a batch-cooking session (what was cooked, how many portions, when) and the
week's eating plan, and decides for every portion whether it stays in the fridge or
goes into the freezer on prep day, when to move frozen portions to the fridge to thaw,
and what to write on each label. Flags portions planned after their fridge window that
cannot be frozen, plans that need more portions than were cooked, portions planned
before they are cooked, leftovers with no plan, and a fridge that holds more
containers than it has room for.
Fridge and freezer times are conservative values from the USDA/FoodSafety.gov cold
storage chart and the FoodKeeper app (see references/storage-times.md). Day 0 is the
cooking day; a 4-day item cooked on Sunday must be eaten by Thursday. Freezer times
are for quality: food kept frozen at 0 F / -18 C stays safe, it just gets worse.
Usage:
python3 prep_storage.py BATCH.csv PLAN.csv [--fridge-capacity 12] [--json]
python3 prep_storage.py --categories
python3 prep_storage.py --self-test
BATCH.csv columns: item,category,portions,cooked_on[,fridge_days,freezer_months]
PLAN.csv columns: date,meal,item,portions (dates as YYYY-MM-DD)
Exit codes: 0 = plan is safe, 1 = at least one HIGH finding, 2 = bad input.
"""
import argparse, csv, datetime as dt, io, json, sys
SEV_ORDER = {"HIGH": 0, "MEDIUM": 1, "LOW": 2, "INFO": 3}
MEAL_ORDER = {"prep": 0, "breakfast": 1, "lunch": 2, "snack": 3, "dinner": 4, "evening": 5}
SERVE_COLD = {"deli_salad", "hard_boiled_eggs", "cut_raw_vegetables"}
# safe minimum internal temperatures for raw categories (cook on the planned day)
COOK_TEMP = {"raw_ground_meat": "160 F / 71 C", "raw_poultry": "165 F / 74 C",
"raw_steak_chops": "145 F / 63 C plus 3 min rest", "raw_fish": "145 F / 63 C"}
# category: (fridge_days, freezer_months or None if it does not freeze well, note)
CATEGORIES = {
"cooked_meat": (4, 3, "cooked beef, pork, lamb; chart 3-4 days / 2-6 months"),
"cooked_poultry": (4, 3, "cooked chicken, turkey; chart 3-4 days / 2-6 months"),
"soup_stew": (4, 3, "soups, stews, chili, curries; chart 3-4 days / 2-3 months"),
"casserole": (4, 3, "casseroles, lasagna, bakes; 3-4 days / 2-3 months"),
"cooked_grains": (4, 2, "cooked rice, pasta, quinoa; cool fast; 3-5 days / 1-2 months"),
"cooked_vegetables": (4, 2, "roasted or steamed vegetables, cooked beans; 3-4 days used"),
"cooked_fish": (3, 3, "cooked fish and seafood; 3-4 days, 3 used"),
"pizza": (4, 2, "pizza, savory pies; chart 3-4 days / 1-2 months"),
"deli_salad": (4, None, "egg, chicken, tuna, pasta salads; chart 3-5 days, does not freeze well"),
"hard_boiled_eggs": (7, None, "hard-cooked eggs; chart 1 week, do not freeze"),
"cut_raw_vegetables": (4, None, "washed and cut raw vegetables; quality guide 3-5 days; blanch before freezing"),
"raw_ground_meat": (2, 3, "raw ground meat or marinated mince; chart 1-2 days / 3-4 months"),
"raw_poultry": (2, 9, "raw chicken or turkey pieces, marinated; chart 1-2 days / 9 months"),
"raw_steak_chops": (4, 4, "raw steaks, chops, roasts; chart 3-5 days / 4-12 months"),
"raw_fish": (2, 2, "raw fish; chart 1-2 days / 2-3 months fatty, 6-8 months lean"),
}
class InputError(Exception):
pass
def parse_date(s, where):
try:
return dt.date.fromisoformat(s.strip())
except ValueError:
raise InputError(f"{where}: date '{s}' is not YYYY-MM-DD")
def parse_int(s, where, minimum=0):
try:
v = int(str(s).strip())
except ValueError:
raise InputError(f"{where}: '{s}' is not a whole number")
if v < minimum:
raise InputError(f"{where}: {v} must be at least {minimum}")
return v
def read_rows(text, required, name):
rows = list(csv.DictReader(io.StringIO(text.lstrip("\ufeff"))))
if not rows:
raise InputError(f"{name} has no rows")
missing = [c for c in required if c not in rows[0]]
if missing:
raise InputError(f"{name} is missing column(s): {', '.join(missing)}")
return [{k.strip(): (v or "").strip() for k, v in r.items() if k} for r in rows if any((v or "").strip() for v in r.values())]
def load_batch(text):
items = {}
for i, r in enumerate(read_rows(text, ["item", "category", "portions", "cooked_on"], "BATCH"), 2):
where = f"BATCH line {i}"
cat = r["category"].lower()
if cat not in CATEGORIES and not r.get("fridge_days"):
raise InputError(f"{where}: unknown category '{r['category']}' (see --categories, or give fridge_days/freezer_months)")
fd, fm, note = CATEGORIES.get(cat, (None, None, "custom"))
if r.get("fridge_days"):
fd = parse_int(r["fridge_days"], where, 1)
if r.get("freezer_months") is not None and r.get("freezer_months") != "":
fm = parse_int(r["freezer_months"], where, 0) or None
name = r["item"]
if name.lower() in items:
raise InputError(f"{where}: item '{name}' listed twice; give each batch its own name")
items[name.lower()] = {"item": name, "category": cat, "portions": parse_int(r["portions"], where, 1),
"cooked_on": parse_date(r["cooked_on"], where), "fridge_days": fd,
"freezer_months": fm, "note": note}
return items
def load_plan(text, items):
plan = []
for i, r in enumerate(read_rows(text, ["date", "meal", "item", "portions"], "PLAN"), 2):
where = f"PLAN line {i}"
key = r["item"].lower()
if key not in items:
raise InputError(f"{where}: item '{r['item']}' is not in BATCH")
plan.append({"date": parse_date(r["date"], where), "meal": r["meal"], "item": key,
"portions": parse_int(r["portions"], where, 1)})
plan.sort(key=lambda p: p["date"])
return plan
def add_months(d, months):
y, m = divmod(d.month - 1 + months, 12)
y, m = d.year + y, m + 1
last = (dt.date(y + (m == 12), m % 12 + 1, 1) - dt.timedelta(days=1)).day
return dt.date(y, m, min(d.day, last))
def plan_storage(items, plan, fridge_capacity=None):
findings, timeline, labels = [], {}, []
def add(sev, code, item, detail):
findings.append({"severity": sev, "code": code, "item": item, "detail": detail})
def ev(day, slot, text):
timeline.setdefault(day, []).append((MEAL_ORDER.get(slot.lower(), 3), text))
summary = {}
for key, it in items.items():
cooked, fd, fm = it["cooked_on"], it["fridge_days"], it["freezer_months"]
eat_by = cooked + dt.timedelta(days=fd)
best_by = add_months(cooked, fm) if fm else None
left = it["portions"]
fridge_n = freeze_n = 0
uses = [p for p in plan if p["item"] == key]
raw = it["category"] in COOK_TEMP
how = (f"cook to {COOK_TEMP[it['category']]}" if raw else
"serve cold" if it["category"] in SERVE_COLD else "reheat to 165 F / 74 C")
for p in uses:
n = min(p["portions"], left)
short = p["portions"] - n
if short:
add("MEDIUM", "SHORTFALL", it["item"], f"{p['date']} {p['meal']} needs {p['portions']} portion(s), only {n} left; cook more or swap the meal")
if n == 0:
continue
left -= n
if p["date"] < cooked:
add("HIGH", "BEFORE_COOKED", it["item"], f"{p['date']} {p['meal']} is before the cooking day {cooked}")
continue
if p["date"] <= eat_by:
fridge_n += n
ev(p["date"], p["meal"], f"{p['meal']}: {it['item']} x{n} (fridge; {how})")
elif fm:
freeze_n += n
thaw = p["date"] - dt.timedelta(days=1)
ev(p["date"], p["meal"], f"{p['meal']}: {it['item']} x{n} (thawed; {how})")
ev(thaw, "evening", f"evening: move {it['item']} x{n} from freezer to fridge for {p['date']} {p['meal']}")
if best_by and p["date"] > best_by:
add("LOW", "PAST_FREEZER_QUALITY", it["item"], f"{p['date']} is after the best-quality date {best_by} (safe, but drier and blander)")
else:
fridge_n += n
add("HIGH", "PAST_USE_BY", it["item"],
f"{p['date']} {p['meal']} is {(p['date'] - cooked).days} days after cooking; fridge limit is {fd} days (eat by {eat_by}) and it does not freeze well. Move the meal earlier or cook it fresh.")
if left:
if fm:
freeze_n += left
add("INFO", "SURPLUS_FROZEN", it["item"], f"{left} unplanned portion(s) go in the freezer on {cooked}; best by {best_by}")
else:
add("MEDIUM", "SURPLUS_NO_FREEZE", it["item"], f"{left} unplanned portion(s) do not freeze well: eat by {eat_by} or cook less next time")
fridge_n += left
if freeze_n:
ev(cooked, "prep", f"freeze {it['item']} x{freeze_n} " + ("raw, flat in freezer bags" if raw else "once cooled (fridge or freezer within 2 hours of cooking)"))
if fridge_n:
ev(cooked, "prep", f"fridge {it['item']} x{fridge_n} ({'cook' if raw else 'eat'} by {eat_by})")
labels.append({"item": it["item"], "cooked_on": str(cooked),
"fridge": f"x{fridge_n}, eat by {eat_by}" if fridge_n else "-",
"freezer": f"x{freeze_n}, best by {best_by}" if freeze_n else "-"})
summary[it["item"]] = {"category": it["category"], "portions": it["portions"], "fridge": fridge_n,
"freezer": freeze_n, "eat_by": str(eat_by), "best_by": str(best_by) if best_by else None,
"fridge_days": fd, "freezer_months": fm}
if fridge_capacity:
days = sorted(set([it["cooked_on"] for it in items.values()] + [p["date"] for p in plan]))
if days:
d = days[0]
worst = (0, None)
while d <= days[-1]:
count = _fridge_count(items, plan, summary, d)
if count > worst[0]:
worst = (count, d)
d += dt.timedelta(days=1)
if worst[0] > fridge_capacity:
add("MEDIUM", "FRIDGE_FULL", "-", f"{worst[0]} containers in the fridge on {worst[1]}, capacity {fridge_capacity}: freeze more on prep day or use bigger containers")
findings.sort(key=lambda f: (SEV_ORDER[f["severity"]], f["item"], f["code"]))
return {"summary": summary, "timeline": {str(k): [t for _, t in sorted(v, key=lambda x: x[0])] for k, v in sorted(timeline.items())},
"labels": labels, "findings": findings}
def _fridge_count(items, plan, summary, day):
"""Containers in the fridge at the end of `day` (one portion = one container)."""
total = 0
for key, it in items.items():
if day < it["cooked_on"]:
continue
eat_by = it["cooked_on"] + dt.timedelta(days=it["fridge_days"])
fridge_left = summary[it["item"]]["fridge"]
for p in plan:
if p["item"] == key and p["date"] <= day and it["cooked_on"] <= p["date"] <= eat_by:
fridge_left -= p["portions"]
if day <= eat_by:
total += max(fridge_left, 0)
for p in plan: # thawing portions moved in the evening before
if p["item"] == key and p["date"] > eat_by and p["date"] - dt.timedelta(days=1) == day and it["freezer_months"]:
total += p["portions"]
return total
def render_md(res):
out = ["# Meal prep storage plan", "", "| Item | Category | Portions | Fridge | Freezer | Eat by (fridge) | Best by (freezer) |",
"|---|---|---|---|---|---|---|"]
for name, s in res["summary"].items():
out.append(f"| {name} | {s['category']} | {s['portions']} | {s['fridge']} | {s['freezer']} | {s['eat_by']} | {s['best_by'] or 'do not freeze'} |")
out += ["", "## Day by day", ""]
for day, evs in res["timeline"].items():
d = dt.date.fromisoformat(day)
out.append(f"- **{d.strftime('%a')} {day}**: " + "; ".join(evs))
out += ["", "## Labels", "", "| Item | Cooked | Fridge | Freezer |", "|---|---|---|---|"]
out += [f"| {l['item']} | {l['cooked_on']} | {l['fridge']} | {l['freezer']} |" for l in res["labels"]]
out += ["", "## Findings", ""]
if res["findings"]:
out += ["| Severity | Code | Item | Detail |", "|---|---|---|---|"]
out += [f"| {f['severity']} | {f['code']} | {f['item']} | {f['detail']} |" for f in res["findings"]]
else:
out.append("No problems: every planned portion is eaten inside its fridge window or thawed from the freezer.")
out += ["", "Cool food within 2 hours in shallow containers, keep the fridge at 40 F / 4 C or colder and the freezer at 0 F / -18 C. When in doubt, throw it out."]
return "\n".join(out)
# ---------------------------------------------------------------- self-test
def self_test():
B = "item,category,portions,cooked_on\nChili,soup_stew,5,2026-11-01\nEgg salad,deli_salad,3,2026-11-01\nRice,cooked_grains,4,2026-11-01\n"
P = ("date,meal,item,portions\n2026-11-02,lunch,Chili,1\n2026-11-03,lunch,Chili,1\n2026-11-06,dinner,Chili,2\n"
"2026-11-02,lunch,Egg salad,1\n2026-11-04,lunch,Egg salad,1\n2026-11-03,dinner,Rice,2\n")
def run(b=B, p=P, cap=None):
items = load_batch(b)
return plan_storage(items, load_plan(p, items), cap)
codes = lambda r: [f["code"] for f in r["findings"]]
cases = [
("categories table sane", lambda: all(v[0] >= 1 for v in CATEGORIES.values())),
("add_months end of month", lambda: add_months(dt.date(2026, 1, 31), 1) == dt.date(2026, 2, 28)),
("add_months over year", lambda: add_months(dt.date(2026, 11, 15), 3) == dt.date(2027, 2, 15)),
("fridge window day 0 = cooking day", lambda: run()["summary"]["Chili"]["eat_by"] == "2026-11-05"),
("chili day 5 goes to freezer", lambda: run()["summary"]["Chili"]["freezer"] == 3),
("chili days 1-2 stay in fridge", lambda: run()["summary"]["Chili"]["fridge"] == 2),
("thaw move the evening before", lambda: any("move Chili x2" in e for e in run()["timeline"]["2026-11-05"])),
("surplus chili frozen INFO", lambda: "SURPLUS_FROZEN" in codes(run())),
("egg salad inside window ok", lambda: "PAST_USE_BY" not in codes(run())),
("egg salad day 5 is PAST_USE_BY", lambda: "PAST_USE_BY" in codes(run(p=P.replace("2026-11-04,lunch,Egg", "2026-11-06,lunch,Egg")))),
("egg salad surplus no freeze", lambda: "SURPLUS_NO_FREEZE" in codes(run())),
("shortfall", lambda: "SHORTFALL" in codes(run(p=P + "2026-11-04,dinner,Rice,3\n"))),
("before cooked", lambda: "BEFORE_COOKED" in codes(run(p=P + "2026-10-31,dinner,Rice,1\n"))),
("past freezer quality", lambda: "PAST_FREEZER_QUALITY" in codes(run(p=P + "2027-03-01,dinner,Rice,1\n"))),
("override fridge_days column", lambda: run(b="item,category,portions,cooked_on,fridge_days,freezer_months\nSoup,custom,2,2026-11-01,2,0\n",
p="date,meal,item,portions\n2026-11-04,lunch,Soup,1\n")["findings"][0]["code"] == "PAST_USE_BY"),
("unknown category error", lambda: _raises(lambda: run(b="item,category,portions,cooked_on\nX,mystery,1,2026-11-01\n", p="date,meal,item,portions\n"))),
("bad date error", lambda: _raises(lambda: run(p="date,meal,item,portions\n11/02/2026,lunch,Chili,1\n"))),
("unknown plan item error", lambda: _raises(lambda: run(p="date,meal,item,portions\n2026-11-02,lunch,Tacos,1\n"))),
("zero portions error", lambda: _raises(lambda: run(b="item,category,portions,cooked_on\nChili,soup_stew,0,2026-11-01\n"))),
("duplicate batch item error", lambda: _raises(lambda: run(b=B + "chili,soup_stew,2,2026-11-01\n"))),
("missing column error", lambda: _raises(lambda: run(b="item,portions\nChili,2\n"))),
("case-insensitive item names", lambda: run(p=P.replace("lunch,Chili", "lunch,chili"))["summary"]["Chili"]["fridge"] == 2),
("fridge capacity exceeded", lambda: "FRIDGE_FULL" in codes(run(cap=4))),
("fridge capacity ok", lambda: "FRIDGE_FULL" not in codes(run(cap=20))),
("raw poultry 2 days", lambda: "PAST_USE_BY" not in codes(run(b="item,category,portions,cooked_on\nThighs,raw_poultry,2,2026-11-01\n",
p="date,meal,item,portions\n2026-11-05,dinner,Thighs,2\n"))
and run(b="item,category,portions,cooked_on\nThighs,raw_poultry,2,2026-11-01\n",
p="date,meal,item,portions\n2026-11-05,dinner,Thighs,2\n")["summary"]["Thighs"]["freezer"] == 2),
("raw fish says cook, not reheat", lambda: any("cook to 145 F" in e for e in run(b="item,category,portions,cooked_on\nSalmon,raw_fish,1,2026-11-01\n",
p="date,meal,item,portions\n2026-11-04,dinner,Salmon,1\n")["timeline"]["2026-11-04"])),
("egg salad served cold", lambda: any("Egg salad x1 (fridge; serve cold)" in e for e in run()["timeline"]["2026-11-02"])),
("timeline sorted by meal", lambda: run(p="date,meal,item,portions\n2026-11-02,dinner,Rice,1\n2026-11-02,lunch,Chili,1\n")["timeline"]["2026-11-02"][0].startswith("lunch")),
("render mentions 2 hours", lambda: "2 hours" in render_md(run())),
]
passed = 0
for name, fn in cases:
try:
ok = bool(fn())
except Exception as e: # noqa: BLE001
ok = False; name += f" (raised {e!r})"
passed += ok
print(("PASS " if ok else "FAIL ") + name)
print(f"{passed}/{len(cases)} passed")
return 0 if passed == len(cases) else 1
def _raises(fn):
try:
fn()
except InputError:
return True
return False
def main():
ap = argparse.ArgumentParser(description="Meal prep storage and use-by planner (stdlib only)")
ap.add_argument("batch", nargs="?"); ap.add_argument("plan", nargs="?")
ap.add_argument("--fridge-capacity", type=int, help="max containers your fridge shelf can hold")
ap.add_argument("--json", action="store_true")
ap.add_argument("--categories", action="store_true", help="list categories and storage times")
ap.add_argument("--self-test", action="store_true")
a = ap.parse_args()
if a.self_test:
return self_test()
if a.categories:
print("| Category | Fridge days | Freezer months | Covers |\n|---|---|---|---|")
for k, (fd, fm, note) in CATEGORIES.items():
print(f"| {k} | {fd} | {fm if fm else 'do not freeze'} | {note} |")
return 0
if not (a.batch and a.plan):
ap.error("need BATCH.csv PLAN.csv (or --self-test / --categories)")
try:
with open(a.batch, encoding="utf-8") as f1, open(a.plan, encoding="utf-8") as f2:
items = load_batch(f1.read())
plan = load_plan(f2.read(), items)
res = plan_storage(items, plan, a.fridge_capacity)
except (OSError, UnicodeDecodeError, InputError) as e:
print(f"error: {e}", file=sys.stderr)
return 2
print(json.dumps(res, indent=2) if a.json else render_md(res))
return 1 if any(f["severity"] == "HIGH" for f in res["findings"]) else 0
if __name__ == "__main__":
sys.exit(main())Compares two CSV exports of the same records (old vs new, billing vs ledger, source vs warehouse) by key after normalizing headers, delimiters, currency formats, accounting negatives, dates and case. Reports added, removed and duplicate keys, key format drift, changed values per column with tolerances, possible re-keys and a control-total bridge, then writes a reconciliation report. Includes a stdlib Python checker with --self-test.
---
name: csv-diff-and-reconciliation-checker
description: Compares two CSV exports of the same records (old vs new, billing vs ledger, source vs warehouse) by key after normalizing headers, delimiters, currency formats, accounting negatives, dates and case. Reports added, removed and duplicate keys, key format drift, changed values per column with tolerances, possible re-keys and a control-total bridge, then writes a reconciliation report. Includes a stdlib Python checker with --self-test.
---
# CSV Diff and Reconciliation Checker
You help analysts, finance teams and data engineers answer "do these two files agree, and if not, exactly why?". You match rows by a business key, ignore differences that are only formatting, and turn the real differences into a short list of causes with the amounts that each one explains, so the totals tie out to the cent.
## Files in this skill
- `scripts/csv_recon.py` - matches two CSVs by key (single or composite), normalizes values, lists schema differences, duplicate and blank keys, added, removed and changed rows, key format drift and possible re-keys, and builds a control-total bridge (Python 3 standard library only)
- `references/matching-and-normalization.md` - choosing a key, header and value normalization, number and date formats, tolerances, and what each finding code means
- `references/reconciliation-playbook.md` - common causes of breaks (timing, duplicates, sign conventions, rounding, filters, encoding) and how to prove each one
- `templates/reconciliation-report.md` - the report to hand back
- `examples/billing-export-sept.csv` - September invoices from a billing system (14 rows, comma-separated)
- `examples/ledger-export-sept.csv` - the same month from the general ledger (15 rows, semicolons, $1,200.00 amounts, US dates, renamed and extra columns)
## Workflow
### 1. Understand the two files
Ask what each file is, which system and filters produced it (date range, status, entity, currency), which side is the source of truth, what the key is, and which differences matter (amounts to the cent, statuses, dates). Ask for row counts and totals the user already expects. Look at the first lines of both files before running anything.
### 2. Run the checker
```
python3 scripts/csv_recon.py examples/billing-export-sept.csv examples/ledger-export-sept.csv --key invoice_id --map customer=customer_name --tol amount=0.01 --sum amount --ignore-case --dates invoice_date
python3 scripts/csv_recon.py orders_old.csv orders_new.csv --key order_id,line_no --ignore updated_at --out-csv diff.csv
python3 scripts/csv_recon.py a.csv b.csv --key id --json
python3 scripts/csv_recon.py --self-test
```
Headers that differ only by case or spacing match automatically; renamed columns need `--map old=new`. Exit 0 means the files reconcile, 1 means differences, 2 means bad input (missing file or key column).
### 3. Explain every difference
Work down the findings (see `references/reconciliation-playbook.md`): duplicates and blank keys first (they distort everything else), then added and removed rows (timing, filters, re-keys), then changed values grouped by column and by pattern (same delta, sign flip, digits swapped, rounding). Use the bridge: old total + added - removed + changes - duplicates = new total, with nothing unexplained. Check a sample of rows by hand in both source systems.
### 4. Deliver
Fill `templates/reconciliation-report.md`: scope and inputs, result, summary counts, the bridge, each break with cause, amount, owner and fix, and what to change so next month reconciles automatically (stable keys, same filters, export formats). Attach `--out-csv` output for the people who fix the rows.
## Example result
Billing vs ledger for September: 14 vs 15 rows, 13 matched (INV-1009 matched to `inv-1009` as KEY_FORMAT_DRIFT), 11 unchanged. Breaks: INV-1011 posted twice in the ledger (HIGH DUPLICATE_KEY, +880.00), INV-1004 450.00 vs 405.00 (digits swapped, -45.00), INV-1012 open in billing but Paid in the ledger, INV-1007 missing and INV-1015 added with the same customer and amount (POSSIBLE_REKEY: posted in October under a new number). $ signs, thousands separators, (150.00) negatives, US dates and status case are formatting only; the INV-1003 rounding (1033.335 vs 1033.34) is inside the 0.01 tolerance. Bridge: 9545.965 + 310.00 - 310.00 - 44.995 + 880.00 = 10380.97, unexplained 0.
## Rules
- Never compare by row position; agree on a key first and check it is unique on both sides.
- Separate formatting noise from real differences and say which normalizations you applied.
- Every break gets a cause, an amount and an owner; "timing" is a cause only if you name the period it lands in.
- Do not net differences against each other to make totals tie; list them.
- Keep tolerances explicit and small (rounding only), never large enough to hide a real error.
- Treat customer and personal data in the files as confidential; share only what the report needs.
FILE:references/matching-and-normalization.md
# Matching and normalization
## Choosing the key
- Use a business key that both systems carry: invoice number, order id + line number, transaction id. Composite keys: `--key order_id,line_no`.
- Check uniqueness on both sides before anything else. Duplicates mean either a real double posting or a key that is not unique (e.g. invoice_id without line number).
- Avoid keys that change between systems: row numbers, surrogate database ids, names, timestamps.
- If there is no shared key, build one (date + amount + reference) and expect some ambiguous matches; review them by hand.
## Header matching
- Exact name first, then case/space/underscore/hyphen-insensitive (`Invoice ID` = `invoice_id`), reported as HEADER_FORMAT.
- Renamed columns: `--map customer=customer_name`. Columns only on one side are reported and not compared.
- A UTF-8 byte order mark (BOM) at the start of the file is removed; otherwise the first header would not match.
## Delimiters and encoding
- Comma, semicolon, tab and pipe are detected. European Excel exports often use `;` with `,` as decimal separator; this checker expects `.` decimals, so convert those first (or export again with `.`).
- Read files as UTF-8. If you see `é` instead of `é`, the file is Windows-1252: convert with `iconv -f cp1252 -t utf-8`.
## Value normalization (applied to every compared cell)
| Raw | Treated as |
|---|---|
| leading/trailing spaces, repeated inner spaces | trimmed, single space |
| `1,234.50`, `$1,234.50`, `1234.5`, `1234.50 USD` | number 1234.5 |
| `(150.00)`, `150.00-`, `-$150.00` | number -150 |
| `12.5%` | number 12.5 (not 0.125) |
| empty, `null`, `None`, `N/A`, `NA`, `-` | empty |
| `Paid` vs `paid` | different, unless `--ignore-case` |
| `09/01/2026`, `2026/09/01`, `01.09.2026` | 2026-09-01 for columns in `--dates` (US month-first unless `--dayfirst`) |
Numbers are compared as decimals, not floats, so `0.1 + 0.2` problems do not appear. Leading zeros in numeric-looking codes (`00123` vs `123`) compare equal as numbers; if that is wrong for a code column, add it to `--ignore` and compare it separately.
## Tolerances
- `--tol amount=0.01` per column, or `--tol 0.01` for every numeric column.
- Use the smallest unit of the currency for rounding differences; never use a tolerance to hide a known error.
- Values inside the tolerance are counted as FORMAT_ONLY and still appear in the bridge as changes.
## Finding codes
| Code | Severity | Meaning |
|---|---|---|
| DUPLICATE_KEY | HIGH | the same key appears more than once on one side; only the first row is compared |
| MISSING_KEY | HIGH | rows with a blank key; they cannot be matched |
| TOTAL_UNEXPLAINED | HIGH | the bridge does not tie (non-numeric cells, blank keys) |
| COLUMN_ONLY_IN_OLD / NEW | MEDIUM | schema difference; map it if it was renamed |
| ROWS_ADDED / ROWS_REMOVED | MEDIUM | keys on one side only |
| VALUE_CHANGED | MEDIUM | per column, rows whose normalized values differ |
| RAGGED_ROWS | MEDIUM | rows with more or fewer cells than the header (bad quoting, embedded delimiters) |
| NON_NUMERIC | MEDIUM | cells in a --sum column that are not numbers |
| HEADER_FORMAT | LOW | columns matched only after ignoring case and spacing |
| KEY_FORMAT_DRIFT | LOW | keys equal after ignoring case, spaces, separators and leading zeros; compared as one row |
| POSSIBLE_REKEY | LOW | a removed and an added row with the same non-key, non-date values |
| FORMAT_ONLY, DELIMITER, TOTAL_DIFF | INFO | context: equal after normalization, different delimiters, totals moved |
## The control-total bridge
```
OLD total
+ amounts of rows only in NEW
- amounts of rows only in OLD
+ (NEW - OLD) on matched rows
- extra duplicate or blank-key rows in OLD
+ extra duplicate or blank-key rows in NEW
= NEW total (unexplained must be 0)
```
FILE:references/reconciliation-playbook.md
# Reconciliation playbook: causes of breaks and how to prove them
Work in this order: duplicates and blank keys, then rows on one side only, then changed values, then totals.
## Duplicates
- **Double posting** (same key, same values twice): a retry, an import run twice, a manual re-entry. Prove: identical rows, often the same created timestamp or batch id. Fix: reverse one posting; add an idempotency key to the import.
- **Non-unique key** (same key, different values): the key needs another column (line number, currency, entity). Re-run with a composite key.
## Rows on one side only
- **Timing / cut-off**: the record exists in both systems but in different periods (invoice dated 30 Sep, posted 1 Oct). Prove: find it in the next or previous period export. Typical sign: POSSIBLE_REKEY with a date just across the boundary.
- **Different filters**: one export excludes drafts, voids, test accounts, zero amounts, another entity or currency. Prove: compare the export queries or report settings; count the excluded statuses.
- **Re-keyed records**: credit note and re-issue, migrated ids, prefix changes (INV- vs IN-). Prove: same customer, amount and date with a new key.
- **Failed sync**: the record never reached the target. Prove: integration logs, error queue, last successful sync time.
- **Key format**: case, spaces, leading zeros, separators (KEY_FORMAT_DRIFT). Fix at the source; normalize keys in the export.
## Changed values
| Pattern | Likely cause |
|---|---|
| delta is a multiple of 9 (450 -> 405, 1,234 -> 1,324) | transposed digits on manual entry |
| exact sign flip | debit/credit convention, credit notes stored as positive with a type column |
| delta equals tax or fee rate x amount | one side gross, the other net |
| delta of 0.01 to 0.05 | rounding per line vs on the total; half-up vs banker's rounding |
| amount / rate | currency conversion, different rate date |
| date off by one day | time zone (UTC vs local) on timestamps near midnight |
| status differs | sync lag, or the systems use different state machines (paid vs settled) |
| text differs only in accents or odd characters | encoding (UTF-8 vs Windows-1252) |
## Totals
- Tie the bridge to zero before reporting. A non-zero unexplained amount means blank keys, non-numeric cells or a column that mixes currencies.
- Report totals per currency; never add USD and EUR.
- Check the totals against the system's own report (trial balance, payout report), not just against the export.
## Proving a cause
For each break: show the row(s) on both sides, the cause, the amount, and evidence (log line, export setting, the row in the next period). Mark each as: fix in source A, fix in source B, timing (will clear), or accepted difference.
## Preventing next month's breaks
- Same filters and period definition (time zone, inclusive end date) on both exports.
- Stable, unique keys carried end to end; no manual re-keying.
- Export amounts as plain numbers with `.` decimals and a separate currency column; dates as ISO 8601.
- Run the checker on a schedule and alert on HIGH findings or unexplained totals.
FILE:templates/reconciliation-report.md
# Reconciliation: <OLD source> vs <NEW source>, <period>
**Inputs:** <billing-export-sept.csv (14 rows, exported 2026-10-02 by ...)>, <ledger-export-sept.csv (15 rows)>
**Key:** <invoice_id> **Compared columns:** <customer, amount, currency, status, invoice_date>
**Normalization:** <$ and thousands separators removed, (x) = -x, status case ignored, US dates read as dates, tolerance amount 0.01>
**Result:** <RECONCILES / DIFFERENCES FOUND: 4 breaks, net +835.005, unexplained 0>
## Summary
| Rows OLD | Rows NEW | Matched | Unchanged | Changed | Added | Removed | Duplicates |
|---|---|---|---|---|---|---|---|
| <14> | <15> | <13> | <11> | <2> | <1> | <1> | <1> |
## Control total bridge (<amount>, <USD>)
| Step | Amount |
|---|---|
| OLD total | <9,545.97> |
| + rows only in NEW | <310.00> |
| - rows only in OLD | <310.00> |
| + changes on matched rows | <-45.00> |
| + duplicates in NEW | <880.00> |
| = NEW total | <10,380.97> |
| Unexplained | <0.00> |
## Breaks
| # | Key(s) | Type | OLD | NEW | Amount | Cause (evidence) | Owner | Action |
|---|---|---|---|---|---|---|---|---|
| 1 | <INV-1011> | <Duplicate> | <880.00 x1> | <880.00 x2> | <+880.00> | <posted twice by import retry (batch 7731)> | <GL team> | <reverse second posting> |
## Formatting-only differences (no action)
- <amount formats, status case, date formats, header names>
## Recommendations
- <same filters and period definition on both exports>
- <add an idempotency key to the ledger import>
## Attachments
- <diff.csv from --out-csv (status, key, column, old, new, delta)>
FILE:examples/billing-export-sept.csv
invoice_id,customer,amount,currency,status,invoice_date
INV-1001,Northwind Traders,1200.00,USD,paid,2026-09-01
INV-1002,Blue Harbor Cafe,89.90,USD,paid,2026-09-02
INV-1003,Atlas Fitness,1033.335,USD,paid,2026-09-03
INV-1004,Greenleaf Dental,450.00,USD,paid,2026-09-05
INV-1005,Orbit Labs,2500.00,USD,paid,2026-09-08
INV-1006,Maple & Co,74.50,USD,refunded,2026-09-09
INV-1007,Copperline Studio,310.00,USD,paid,2026-09-11
INV-1008,Riverbend School,640.00,USD,paid,2026-09-12
INV-1009,Juniper Vet Clinic,195.00,USD,paid,2026-09-15
INV-1010,Northwind Traders,1200.00,USD,paid,2026-09-18
INV-1011,Summit Roofing,880.00,USD,paid,2026-09-22
INV-1012,Blue Harbor Cafe,89.90,USD,open,2026-09-25
INV-1013,Orbit Labs,-150.00,USD,credit,2026-09-26
INV-1014,Atlas Fitness,1033.33,USD,paid,2026-09-30
FILE:examples/ledger-export-sept.csv
Invoice ID;customer_name;Amount;Currency;Status;Invoice Date;gl_account
INV-1001;Northwind Traders;$1,200.00;USD;Paid;09/01/2026;4000
INV-1002;Blue Harbor Cafe;$89.90;USD;Paid;09/02/2026;4000
INV-1003;Atlas Fitness;$1,033.34;USD;Paid;09/03/2026;4000
INV-1004;Greenleaf Dental;$405.00;USD;Paid;09/05/2026;4000
INV-1005;Orbit Labs;$2,500.00;USD;Paid;09/08/2026;4000
INV-1006;Maple & Co;$74.50;USD;Refunded;09/09/2026;4000
INV-1008;Riverbend School;$640.00;USD;Paid;09/12/2026;4000
inv-1009;Juniper Vet Clinic;$195.00;USD;Paid;09/15/2026;4000
INV-1010;Northwind Traders;$1,200.00;USD;Paid;09/18/2026;4000
INV-1011;Summit Roofing;$880.00;USD;Paid;09/22/2026;4000
INV-1011;Summit Roofing;$880.00;USD;Paid;09/22/2026;4000
INV-1012;Blue Harbor Cafe;$89.90;USD;Paid;09/25/2026;4000
INV-1013;Orbit Labs;($150.00);USD;Credit;09/26/2026;4090
INV-1014;Atlas Fitness;$1,033.33;USD;Paid;09/30/2026;4000
INV-1015;Copperline Studio;$310.00;USD;Paid;10/01/2026;4000
FILE:scripts/csv_recon.py
#!/usr/bin/env python3
"""CSV diff and reconciliation checker (Python 3 standard library only).
Compares two CSV exports of the same records (old vs new, system A vs system B) by a
key, after normalizing the noise that makes naive diffs useless: spaces, header case,
thousands separators, currency signs, accounting negatives "(12.50)", number
formatting, optional text case and date formats. Reports schema differences, missing
or duplicate keys, added and removed rows, changed values per column (with numeric
tolerances), keys that only differ by format (case, leading zeros, spaces) and a
control-total bridge for amount columns: old total + added - removed + changes = new.
Usage:
python3 csv_recon.py OLD.csv NEW.csv --key id [--key2 line] [--map old_col=new_col]
[--ignore col,col] [--tol amount=0.01] [--sum amount] [--ignore-case]
[--dates col,col] [--dayfirst] [--limit 20] [--out-csv diff.csv] [--json]
python3 csv_recon.py --self-test
Exit codes: 0 = files reconcile (no differences after normalization and tolerance),
1 = differences found, 2 = bad input (missing file, missing key column).
"""
import argparse, csv, datetime, io, json, re, sys
from decimal import Decimal, InvalidOperation
SEV_ORDER = {"HIGH": 0, "MEDIUM": 1, "LOW": 2, "INFO": 3}
NUM_RE = re.compile(r"^[+-]?(\d+(\.\d*)?|\.\d+)([eE][+-]?\d+)?$")
DATE_FORMATS = ["%Y-%m-%d", "%Y/%m/%d", "%Y%m%d", "%d.%m.%Y", "%Y-%m-%dT%H:%M:%S", "%Y-%m-%d %H:%M:%S"]
class InputError(Exception):
pass
def norm_header(h):
return re.sub(r"[\s_\-]+", "_", h.strip().lstrip("\ufeff").lower())
def read_csv(text, name):
text = text.lstrip("\ufeff")
if not text.strip():
raise InputError(f"{name} is empty")
sample = text[:4096]
try:
dialect = csv.Sniffer().sniff(sample, delimiters=",;\t|")
except csv.Error:
dialect = csv.excel
rows = list(csv.reader(io.StringIO(text), dialect))
rows = [r for r in rows if any(c.strip() for c in r)]
header = [h.strip() for h in rows[0]]
body = []
for i, r in enumerate(rows[1:], 2):
if len(r) != len(header):
body.append((i, dict(zip(header, r + [""] * (len(header) - len(r)))), len(r)))
else:
body.append((i, dict(zip(header, r)), len(r)))
return header, body, getattr(dialect, "delimiter", ",")
def parse_number(s):
"""Return Decimal for '1,234.50', '$1,234.50', '(12.00)', '-5', '12.5%' (as 12.5), else None."""
t = s.strip().replace("\u00a0", "").replace(" ", "")
if not t:
return None
neg = False
if t.startswith("(") and t.endswith(")"):
neg, t = True, t[1:-1]
if t.endswith("-") and NUM_RE.match(t[:-1].lstrip("$")):
neg, t = True, t[:-1]
t = re.sub(r"^([+-]?)(USD|EUR|GBP|[$\u20ac\u00a3])", r"\1", t)
t = re.sub(r"(USD|EUR|GBP|[$\u20ac\u00a3])$", "", t)
t = t.rstrip("%")
if re.match(r"^[+-]?\d{1,3}(,\d{3})+(\.\d+)?$", t):
t = t.replace(",", "")
if not NUM_RE.match(t):
return None
try:
d = Decimal(t)
except InvalidOperation:
return None
return -d if neg else d
def parse_date(s, dayfirst):
t = s.strip()
fmts = DATE_FORMATS + (["%d/%m/%Y"] if dayfirst else ["%m/%d/%Y"])
for f in fmts:
try:
return datetime.datetime.strptime(t, f).date().isoformat() if "%H" not in f else datetime.datetime.strptime(t, f).isoformat()
except ValueError:
pass
return None
def normalize(value, col, opts):
v = value.strip()
if col in opts["dates"]:
d = parse_date(v, opts["dayfirst"])
if d:
return ("date", d)
n = parse_number(v)
if n is not None:
return ("num", n)
if v.lower() in ("", "null", "none", "n/a", "na", "-"):
return ("empty", "")
v = re.sub(r"\s+", " ", v)
return ("text", v.lower() if opts["ignore_case"] else v)
def same(a, b, col, opts):
if a[0] == "num" and b[0] == "num":
tol = opts["tol"].get(col, opts["tol"].get("*", Decimal(0)))
return abs(a[1] - b[1]) <= tol
return a == b
def key_variant(k):
"""Format-insensitive form of a key: case, spaces, separators and leading zeros."""
t = re.sub(r"[\s\-_./]", "", k.lower())
return re.sub(r"(?<!\d)0+(?=\d)", "", t)
def reconcile(old_text, new_text, keys, opts):
oh, ob, odelim = read_csv(old_text, "OLD")
nh, nb, ndelim = read_csv(new_text, "NEW")
findings = []
def add(sev, code, detail, count=1):
findings.append({"severity": sev, "code": code, "detail": detail, "count": count})
# column matching: explicit map, then exact, then normalized header
cmap, fuzzy = {}, []
n_by_norm = {norm_header(h): h for h in nh}
for h in oh:
if h in opts["map"]:
target = opts["map"][h]
if target not in nh:
raise InputError(f"--map target column '{target}' not in NEW")
cmap[h] = target
elif h in nh:
cmap[h] = h
elif norm_header(h) in n_by_norm:
cmap[h] = n_by_norm[norm_header(h)]
fuzzy.append(f"{h} = {cmap[h]}")
if fuzzy:
add("LOW", "HEADER_FORMAT", f"{len(fuzzy)} column(s) matched by case/spacing only: " + "; ".join(fuzzy), len(fuzzy))
only_old = [h for h in oh if h not in cmap]
only_new = [h for h in nh if h not in cmap.values()]
for h in only_old:
add("MEDIUM", "COLUMN_ONLY_IN_OLD", f"'{h}' has no match in NEW (use --map {h}=<new name> if it was renamed)")
for h in only_new:
add("MEDIUM", "COLUMN_ONLY_IN_NEW", f"'{h}' has no match in OLD")
for k in keys:
if k not in oh:
raise InputError(f"key column '{k}' not in OLD header {oh}")
if cmap.get(k) not in nh:
raise InputError(f"key column '{k}' has no match in NEW header {nh}")
if odelim != ndelim:
add("INFO", "DELIMITER", f"OLD uses '{odelim}', NEW uses '{ndelim}'")
def index(body, side, colnames):
idx, dups, blanks, ragged = {}, {}, 0, 0
for line, row, ncells in body:
if ncells != (len(oh) if side == "OLD" else len(nh)):
ragged += 1
kv = tuple(row.get(c, "").strip() for c in colnames)
if not all(kv):
blanks += 1
continue
if kv in idx:
dups.setdefault(kv, [idx[kv][0]]).append(line)
else:
idx[kv] = (line, row)
return idx, dups, blanks, ragged
oidx, odups, oblank, orag = index(ob, "OLD", keys)
nidx, ndups, nblank, nrag = index(nb, "NEW", [cmap[k] for k in keys])
for side, dups in (("OLD", odups), ("NEW", ndups)):
for kv, lines in dups.items():
add("HIGH", "DUPLICATE_KEY", f"{side} key {'/'.join(kv)} on lines {', '.join(map(str, lines))} (only the first is compared)")
for side, n in (("OLD", oblank), ("NEW", nblank)):
if n:
add("HIGH", "MISSING_KEY", f"{side} has {n} row(s) with a blank key (not compared)", n)
for side, n in (("OLD", orag), ("NEW", nrag)):
if n:
add("MEDIUM", "RAGGED_ROWS", f"{side} has {n} row(s) with a different number of cells than the header", n)
removed = [k for k in oidx if k not in nidx]
added = [k for k in nidx if k not in oidx]
# keys that only differ by format
drift = []
add_by_variant = {}
for k in added:
add_by_variant.setdefault(tuple(key_variant(x) for x in k), k)
for k in list(removed):
v = tuple(key_variant(x) for x in k)
if v in add_by_variant:
nk = add_by_variant.pop(v)
drift.append((k, nk))
removed.remove(k); added.remove(nk)
for ok, nk in drift:
add("LOW", "KEY_FORMAT_DRIFT", f"OLD key {'/'.join(ok)} = NEW key {'/'.join(nk)} after ignoring case, spaces, separators and leading zeros (compared as one row)")
if removed:
add("MEDIUM", "ROWS_REMOVED", f"{len(removed)} key(s) in OLD only: " + ", ".join('/'.join(k) for k in removed[:opts['limit']]), len(removed))
if added:
add("MEDIUM", "ROWS_ADDED", f"{len(added)} key(s) in NEW only: " + ", ".join('/'.join(k) for k in added[:opts['limit']]), len(added))
compare_cols = [h for h in oh if h in cmap and h not in keys and h not in opts["ignore"]]
pairs = [(k, k) for k in oidx if k in nidx] + drift
changes, per_col, unchanged, fmt_only = [], {c: 0 for c in compare_cols}, 0, {c: 0 for c in compare_cols}
for ok, nk in pairs:
orow, nrow = oidx[ok][1], nidx[nk][1]
row_changed = False
for c in compare_cols:
a_raw, b_raw = orow.get(c, ""), nrow.get(cmap[c], "")
a, b = normalize(a_raw, c, opts), normalize(b_raw, c, opts)
if same(a, b, c, opts):
if a_raw.strip() != b_raw.strip():
fmt_only[c] += 1
continue
row_changed = True
per_col[c] += 1
delta = (b[1] - a[1]) if a[0] == "num" and b[0] == "num" else None
changes.append({"key": "/".join(ok), "column": c, "old": a_raw, "new": b_raw,
"delta": str(delta) if delta is not None else ""})
unchanged += not row_changed
# removed and added rows that look like the same record under a new key or period
nondate = [c for c in compare_cols if c not in opts["dates"]]
used = set()
for rk in removed:
orow = oidx[rk][1]
for ak in added:
if ak in used or not nondate:
continue
nrow = nidx[ak][1]
hits = sum(same(normalize(orow.get(c, ""), c, opts), normalize(nrow.get(cmap[c], ""), c, opts), c, opts) for c in nondate)
if hits == len(nondate):
used.add(ak)
add("LOW", "POSSIBLE_REKEY", f"removed {'/'.join(rk)} and added {'/'.join(ak)} have the same values in {', '.join(nondate)} (re-keyed, re-posted or moved to another period?)")
break
for c, n in per_col.items():
if n:
add("MEDIUM", "VALUE_CHANGED", f"'{c}' differs in {n} row(s)", n)
for c, n in fmt_only.items():
if n:
add("INFO", "FORMAT_ONLY", f"'{c}' equal after normalization in {n} row(s) (formatting, case or within tolerance)", n)
bridges = []
for col in opts["sum"]:
if col not in oh or cmap.get(col) not in nh:
raise InputError(f"--sum column '{col}' missing in OLD or NEW")
def total(body, c):
t, bad = Decimal(0), 0
for _, row, _ in body:
n = parse_number(row.get(c, ""))
if n is None:
bad += bool(row.get(c, "").strip())
else:
t += n
return t, bad
ot, obad = total(ob, col)
nt, nbad = total(nb, cmap[col])
def val(row, c):
return parse_number(row.get(c, "")) or Decimal(0)
s_add = sum((val(nidx[k][1], cmap[col]) for k in added), Decimal(0))
s_rem = sum((val(oidx[k][1], col) for k in removed), Decimal(0))
s_chg = sum((val(nidx[nk][1], cmap[col]) - val(oidx[ok][1], col) for ok, nk in pairs), Decimal(0))
s_dup_o = sum((val(r, col) for _, r, _ in ob), Decimal(0)) - sum((val(r, col) for _, r in oidx.values()), Decimal(0))
s_dup_n = sum((val(r, cmap[col]) for _, r, _ in nb), Decimal(0)) - sum((val(r, cmap[col]) for _, r in nidx.values()), Decimal(0))
explained = ot + s_add - s_rem + s_chg - s_dup_o + s_dup_n
unexplained = nt - explained
bridges.append({"column": col, "old_total": str(ot), "added": str(s_add), "removed": str(s_rem),
"changed": str(s_chg), "duplicates_old": str(s_dup_o), "duplicates_new": str(s_dup_n),
"new_total": str(nt), "unexplained": str(unexplained), "non_numeric_old": obad, "non_numeric_new": nbad})
if nt != ot:
add("INFO", "TOTAL_DIFF", f"'{col}' total {ot} -> {nt} (diff {nt - ot}); see bridge")
if unexplained != 0:
add("HIGH", "TOTAL_UNEXPLAINED", f"'{col}' bridge leaves {unexplained} unexplained (blank keys or non-numeric cells?)")
if obad or nbad:
add("MEDIUM", "NON_NUMERIC", f"'{col}' has {obad} non-numeric cell(s) in OLD and {nbad} in NEW (left out of totals)", obad + nbad)
findings.sort(key=lambda f: (SEV_ORDER[f["severity"]], f["code"]))
summary = {"old_rows": len(ob), "new_rows": len(nb), "matched": len(pairs), "unchanged": unchanged,
"changed_rows": len(pairs) - unchanged, "added": len(added), "removed": len(removed),
"compared_columns": compare_cols, "column_map": {k: v for k, v in cmap.items() if k != v}}
differs = any(f["severity"] in ("HIGH", "MEDIUM") for f in findings)
return {"summary": summary, "findings": findings, "changes": changes, "bridges": bridges,
"added_keys": ["/".join(k) for k in added], "removed_keys": ["/".join(k) for k in removed],
"reconciles": not differs}
def render_md(res, old_name, new_name, limit):
s = res["summary"]
out = [f"# CSV reconciliation: {old_name} vs {new_name}", "",
f"**Result:** {'RECONCILES' if res['reconciles'] else 'DIFFERENCES FOUND'}", "",
"| Rows OLD | Rows NEW | Matched | Unchanged | Changed | Added | Removed |", "|---|---|---|---|---|---|---|",
f"| {s['old_rows']} | {s['new_rows']} | {s['matched']} | {s['unchanged']} | {s['changed_rows']} | {s['added']} | {s['removed']} |", ""]
if s["column_map"]:
out.append("Column map: " + ", ".join(f"{k} -> {v}" for k, v in s["column_map"].items()))
out.append("")
out += ["## Findings", ""]
if res["findings"]:
out += ["| Severity | Code | Detail |", "|---|---|---|"]
out += [f"| {f['severity']} | {f['code']} | {f['detail']} |" for f in res["findings"]]
else:
out.append("No findings.")
if res["changes"]:
out += ["", f"## Changed values (first {min(limit, len(res['changes']))} of {len(res['changes'])})", "",
"| Key | Column | OLD | NEW | Delta |", "|---|---|---|---|---|"]
out += [f"| {c['key']} | {c['column']} | {c['old']} | {c['new']} | {c['delta']} |" for c in res["changes"][:limit]]
for b in res["bridges"]:
out += ["", f"## Control total bridge: {b['column']}", "", "| Step | Amount |", "|---|---|",
f"| OLD total | {b['old_total']} |", f"| + rows added | {b['added']} |", f"| - rows removed | {b['removed']} |",
f"| + changes on matched rows | {b['changed']} |", f"| - duplicate/blank-key rows in OLD | {b['duplicates_old']} |",
f"| + duplicate/blank-key rows in NEW | {b['duplicates_new']} |", f"| = NEW total | {b['new_total']} |",
f"| Unexplained | {b['unexplained']} |"]
return "\n".join(out)
def parse_args(argv):
ap = argparse.ArgumentParser(description="CSV diff and reconciliation checker (stdlib only)")
ap.add_argument("old", nargs="?"); ap.add_argument("new", nargs="?")
ap.add_argument("--key", help="key column(s) in OLD, comma-separated")
ap.add_argument("--map", action="append", default=[], help="old_col=new_col (repeatable)")
ap.add_argument("--ignore", default="", help="columns to skip, comma-separated")
ap.add_argument("--tol", action="append", default=[], help="col=0.01 or 0.01 for all numeric columns")
ap.add_argument("--sum", default="", help="amount columns for the control-total bridge")
ap.add_argument("--ignore-case", action="store_true")
ap.add_argument("--dates", default="", help="date columns to compare as dates")
ap.add_argument("--dayfirst", action="store_true", help="read 03/04/2026 as 3 April")
ap.add_argument("--limit", type=int, default=20)
ap.add_argument("--out-csv"); ap.add_argument("--json", action="store_true")
ap.add_argument("--self-test", action="store_true")
return ap, ap.parse_args(argv)
def build_opts(a):
tol = {}
for t in a.tol:
if "=" in t:
c, v = t.split("=", 1); tol[c.strip()] = Decimal(v)
else:
tol["*"] = Decimal(t)
mp = {}
for m in a.map:
if "=" not in m:
raise InputError(f"--map needs old=new, got '{m}'")
o, n = m.split("=", 1); mp[o.strip()] = n.strip()
split = lambda s: [x.strip() for x in s.split(",") if x.strip()]
return {"tol": tol, "map": mp, "ignore": set(split(a.ignore)), "sum": split(a.sum),
"ignore_case": a.ignore_case, "dates": set(split(a.dates)), "dayfirst": a.dayfirst, "limit": a.limit}
# ---------------------------------------------------------------- self-test
def self_test():
D = Decimal
O = "id,customer,amount,status,date\nA1,Ann,100.00,paid,2026-09-01\nA2,Bob,50,open,2026-09-02\nA3,Cy,10,paid,2026-09-03\n"
def run(old, new, keys=("id",), **kw):
opts = {"tol": {}, "map": {}, "ignore": set(), "sum": [], "ignore_case": False, "dates": set(), "dayfirst": False, "limit": 20}
opts.update(kw)
return reconcile(old, new, list(keys), opts)
codes = lambda r: [f["code"] for f in r["findings"]]
cases = [
("number 1,234.50", lambda: parse_number("1,234.50") == D("1234.50")),
("currency $1,234.50", lambda: parse_number("$1,234.50") == D("1234.50")),
("accounting (12.00)", lambda: parse_number("(12.00)") == D("-12.00")),
("trailing minus 5-", lambda: parse_number("5-") == D("-5")),
("percent 12.5%", lambda: parse_number("12.5%") == D("12.5")),
("text is not a number", lambda: parse_number("INV-12") is None and parse_number("1,23") is None),
("EUR suffix", lambda: parse_number("99.90 EUR") == D("99.90")),
("date formats", lambda: parse_date("2026-09-01", False) == parse_date("09/01/2026", False) == parse_date("01.09.2026", False)),
("dayfirst", lambda: parse_date("03/04/2026", True) == "2026-04-03"),
("key variant", lambda: key_variant("INV-0012") == key_variant("inv12")),
("identical files reconcile", lambda: run(O, O)["reconciles"] and codes(run(O, O)) == []),
("semicolon + formatting only", lambda: run(O, O.replace(",", ";").replace("100.00", "100").replace("50;", "50.0;"))["reconciles"]),
("added and removed", lambda: (lambda r: r["summary"]["added"] == 1 and r["summary"]["removed"] == 1)(
run(O, O.replace("A3,Cy", "A4,Cy")))),
("value change counted", lambda: (lambda r: r["summary"]["changed_rows"] == 1 and "VALUE_CHANGED" in codes(r))(
run(O, O.replace("50,open", "55,open")))),
("tolerance hides rounding", lambda: run(O, O.replace("100.00", "100.004"), tol={"amount": D("0.01")})["reconciles"]),
("tolerance does not hide real diff", lambda: not run(O, O.replace("100.00", "100.02"), tol={"amount": D("0.01")})["reconciles"]),
("ignore case", lambda: run(O, O.replace("paid", "PAID"), ignore_case=True)["reconciles"] and not run(O, O.replace("paid", "PAID"))["reconciles"]),
("dates compared as dates", lambda: run(O, O.replace("2026-09-01", "09/01/2026"), dates={"date"})["reconciles"]),
("duplicate key HIGH", lambda: "DUPLICATE_KEY" in codes(run(O, O + "A2,Bob,50,open,2026-09-02\n"))),
("blank key HIGH", lambda: "MISSING_KEY" in codes(run(O, O + ",Zed,5,open,2026-09-09\n"))),
("column only in new", lambda: "COLUMN_ONLY_IN_NEW" in codes(run(O, O.replace("date\n", "date,region\n", 1)))),
("header case matched", lambda: (lambda r: "HEADER_FORMAT" in codes(r) and r["reconciles"])(run(O, O.replace("customer", "Customer")))),
("rename via map", lambda: run(O, O.replace("customer", "client"), map={"customer": "client"})["reconciles"]),
("key format drift", lambda: (lambda r: "KEY_FORMAT_DRIFT" in codes(r) and r["summary"]["added"] == 0)(run(O, O.replace("A3,", "a03,")))),
("possible rekey", lambda: "POSSIBLE_REKEY" in codes(run(O, O.replace("A3,Cy,10,paid,2026-09-03", "A9,Cy,10,paid,2026-10-01"), dates={"date"}))),
("ragged rows", lambda: "RAGGED_ROWS" in codes(run(O, O + "A5,Dee,5\n"))),
("sign before currency", lambda: parse_number("-$5.00") == parse_number("$-5.00") == D("-5.00")),
("composite key", lambda: run("o,l,v\n1,1,a\n1,2,b\n", "o,l,v\n1,2,b\n1,1,a\n", keys=("o", "l"))["reconciles"]),
("bridge explains totals", lambda: (lambda b: D(b["unexplained"]) == 0 and b["old_total"] == "160.00" and b["new_total"] == "175.00")(
run(O, O.replace("A3,Cy,10", "A4,Cy,20").replace("50,open", "55,open"), sum=["amount"])["bridges"][0])),
("bridge with duplicate", lambda: D(run(O, O + "A2,Bob,50,open,2026-09-02\n", sum=["amount"])["bridges"][0]["unexplained"]) == 0),
("missing key column error", lambda: _raises(lambda: run(O, O, keys=("nope",)))),
("empty file error", lambda: _raises(lambda: run("", O))),
("BOM stripped", lambda: run("\ufeff" + O, O)["reconciles"]),
("render has result line", lambda: "RECONCILES" in render_md(run(O, O), "a", "b", 5)),
]
passed = 0
for name, fn in cases:
try:
ok = bool(fn())
except Exception as e: # noqa: BLE001 - report in test output
ok = False; name += f" (raised {e!r})"
passed += ok
print(("PASS " if ok else "FAIL ") + name)
print(f"{passed}/{len(cases)} passed")
return 0 if passed == len(cases) else 1
def _raises(fn):
try:
fn()
except InputError:
return True
return False
def main(argv=None):
ap, a = parse_args(argv if argv is not None else sys.argv[1:])
if a.self_test:
return self_test()
if not (a.old and a.new and a.key):
ap.error("need OLD NEW --key (or --self-test)")
try:
opts = build_opts(a)
with open(a.old, encoding="utf-8-sig", newline="") as f1, open(a.new, encoding="utf-8-sig", newline="") as f2:
old_text, new_text = f1.read(), f2.read()
res = reconcile(old_text, new_text, [k.strip() for k in a.key.split(",")], opts)
except (OSError, UnicodeDecodeError, InputError, InvalidOperation) as e:
print(f"error: {e}", file=sys.stderr)
return 2
if a.out_csv:
with open(a.out_csv, "w", newline="", encoding="utf-8") as fh:
w = csv.writer(fh)
w.writerow(["status", "key", "column", "old", "new", "delta"])
for k in res["removed_keys"]:
w.writerow(["removed", k, "", "", "", ""])
for k in res["added_keys"]:
w.writerow(["added", k, "", "", "", ""])
for c in res["changes"]:
w.writerow(["changed", c["key"], c["column"], c["old"], c["new"], c["delta"]])
print(json.dumps(res, indent=2, default=str) if a.json else render_md(res, a.old, a.new, a.limit))
return 0 if res["reconciles"] else 1
if __name__ == "__main__":
sys.exit(main())Reviews bash and POSIX sh scripts before they run in CI, cron or production. Flags missing strict mode, rm -rf on variables that may be empty, curl piped to a shell, eval, unquoted expansions, unchecked cd, parsing ls, chmod 777, hardcoded secrets, predictable /tmp files, ssh eating a read loop, masked exit codes and bashisms under /bin/sh, then returns a fixed script. Includes a stdlib Python linter with --self-test.
--- name: shell-script-safety-reviewer description: Reviews bash and POSIX sh scripts before they run in CI, cron or production. Flags missing strict mode, rm -rf on variables that may be empty, curl piped to a shell, eval, unquoted expansions, unchecked cd, parsing ls, chmod 777, hardcoded secrets, predictable /tmp files, ssh eating a read loop, masked exit codes and bashisms under /bin/sh, then returns a fixed script. Includes a stdlib Python linter with --self-test. --- # Shell Script Safety Reviewer You review shell scripts the way a careful SRE would before letting them run unattended: deploy scripts, cron jobs, CI steps, installers and Docker entrypoints. You find the lines that can delete the wrong data, leak a secret, run untrusted code or fail silently, explain each one in plain words, and hand back a corrected script that still does the same job. ## Files in this skill - `scripts/shell_lint.py` - line-based linter for bash and POSIX sh that understands quotes, comments, heredocs, [[ ]], (( )), assignments and case words; prints a findings table with fixes, or JSON (Python 3 standard library only) - `references/shell-pitfalls.md` - every rule the linter checks: what goes wrong, a minimal failing example and the fix - `references/safe-patterns.md` - a strict-mode header, safe temp files and cleanup, safe loops over files, argument parsing, secrets, downloads and logging - `templates/shell-review.md` - the review to hand back - `examples/deploy.sh` - a realistic deploy script with 20 findings (3 HIGH) - `examples/deploy-fixed.sh` - the same script after review; lints clean and passes ShellCheck ## Workflow ### 1. Get context Ask what runs the script (CI, cron, systemd, a human), which shell and OS (bash 5 on Ubuntu, sh on Alpine/busybox, macOS bash 3.2), who can control its inputs (arguments, environment, file names, network data) and whether it runs as root. Ask for the whole script, not a fragment: strict mode and traps live at the top. ### 2. Run the linter ``` python3 scripts/shell_lint.py examples/deploy.sh python3 scripts/shell_lint.py deploy.sh entrypoint.sh --min-severity MEDIUM python3 scripts/shell_lint.py install.sh --json --ignore BACKTICKS python3 scripts/shell_lint.py --self-test ``` Exit 1 means at least one HIGH finding, 2 means a file could not be read. Silence a deliberate line with `# shell-lint: disable=CODE`. If ShellCheck is available, run it too (`shellcheck -f gcc script.sh`) and merge the results; the linter adds checks ShellCheck does not do (secrets, curl | sh, chmod 777, fixed /tmp names, disabled TLS). ### 3. Review by hand The linter is a first pass. Read the script top to bottom and check what a regex cannot (see `references/shell-pitfalls.md`): - Which inputs are untrusted, and do they reach rm, eval, ssh, sql or a path? - Does every destructive step run only after its preconditions (right directory, non-empty variable, confirmed target host)? - Is it idempotent: can it run twice, or resume after failing halfway? - Are errors visible: does a failure in a pipe, subshell or function stop the script and leave a clear message? - Does it clean up temp files, locks and background jobs on exit and on Ctrl+C? ### 4. Fix and deliver Fill `templates/shell-review.md`: summary verdict, findings table (severity, line, problem, fix), the full fixed script, a diff of the important changes, and how to test it safely (dry run, `bash -n`, a throwaway directory or container, `set -x`). Keep the script's behavior; call out every behavior change. ## Example result `examples/deploy.sh`: 20 findings, 3 HIGH. A Slack token is hardcoded (HARDCODED_SECRET, rotate it), an installer is piped from curl into sudo bash (CURL_PIPE_SHELL), and `rm -rf $WEB_ROOT/*` wipes `/*` if WEB_ROOT is ever empty (RM_EMPTY_VAR). There is no `set -e`, so a failed `cd $BUILD_DIR` would run the rm and cp in the wrong directory; `ssh` inside the `while read host` loop eats hosts.txt, so only the first host restarts (SSH_STDIN_LOOP); `local count=$(...)` hides failures. `examples/deploy-fixed.sh` fixes all of them and lints clean. ## Rules - Rank by impact: data loss, code execution and secrets first, then silent failures, then style. - Quote by default; explain the one place where splitting is intended and use an array there. - Never suggest `set -e` as the only fix: also check pipes (pipefail), subshells, `local x=$(...)` and `if cmd` contexts where errexit is off. - Rotate any secret found in a script, even if the repo is private, and say so. - Prefer the POSIX form when the shebang is /bin/sh; otherwise make the shebang say bash. - Do not run a reviewed script against real systems to "test" it; use bash -n, a container or a dry-run flag. FILE:references/shell-pitfalls.md # Shell pitfalls checked by shell_lint.py Severity: HIGH = can delete data, run untrusted code or leak a secret. MEDIUM = wrong result or silent failure under realistic input. LOW = fragile or legacy style. ## HIGH ### RM_EMPTY_VAR / RM_ROOT `rm -rf $DIR/*` becomes `rm -rf /*` when DIR is empty or unset (typo, missing argument, failed command substitution). Steam's Linux client shipped exactly this bug in 2015. ```bash rm -rf -- "?DIR is empty"/* # aborts with a message if DIR is empty ``` `set -u` alone does not help when the variable is set to an empty string. ### CURL_PIPE_SHELL `curl https://x/install.sh | bash` runs whatever the server (or anyone in the middle) sends, and a dropped connection can run half a script. Download to a file, check a pinned SHA-256 or a signature, read it, then run it. ### EVAL_VAR `eval "$cmd"` executes any `;`, `$(...)` or backticks inside the data. Build commands as arrays: `cmd=(rsync -a -- "$src" "$dst"); "cmd[@]"`. For dispatch, use `case`. ### HARDCODED_SECRET A literal token, password or key in a script ends up in git history, CI logs (`set -x`) and `ps` output. Read it from the environment or a secret manager and fail early: `TOKEN="?"`. Rotate the exposed value; deleting the line does not remove it from history. ## MEDIUM ### NO_STRICT_MODE / NO_PIPEFAIL Without `set -e` a failed command is ignored and the next one runs anyway. Without `pipefail`, `build | tee log` succeeds when build fails. Use `set -euo pipefail` in bash and `set -eu` in POSIX sh. Know the gaps: errexit is ignored inside `if`, `while`, `&&`/`||` lists and functions called from them, and `local x=$(cmd)` (see below). ### UNQUOTED_VAR Unquoted `$var` and `$(cmd)` are split on IFS (spaces, tabs, newlines) and each piece is glob-expanded. `cp $src $dst` with `src="My File.txt"` copies two wrong files; `rm $f` with `f='*'` removes everything. Quote every expansion; use `"$@"` for all arguments and `"arr[@]"` for arrays. Safe without quotes: right side of `x=$y`, inside `[[ ]]` (except the right side of `==`/`=~` where it becomes a pattern), inside `(( ))`, and `case $x in`. ### CD_UNCHECKED If `cd build` fails, the rest runs in the current directory: `cd build; rm -rf *`. Use `cd -- "$dir" || exit 1` or strict mode. In loops, use a subshell: `(cd -- "$d" && make)`. ### LS_PARSING `for f in $(ls *.log)` breaks on spaces and newlines and runs the glob twice. Use the glob directly: `for f in ./*.log; do [ -e "$f" ] || continue; ...; done`, or `find . -name '*.log' -print0 | while IFS= read -r -d '' f; do ...; done`. ### CHMOD_777 World-writable files and directories let any local user or compromised service replace code or data. Use 755/644, 700/600 for private data, or `u=rwX,g=rX,o=`. ### PREDICTABLE_TMP / NO_TRAP_CLEANUP (LOW) A fixed `/tmp/app.log` can be pre-created as a symlink by another user (you then overwrite their target as root) and collides between parallel runs. Use `mktemp`/`mktemp -d` and remove it with `trap '...' EXIT`. ### LOCAL_MASKS_STATUS `local out=$(cmd)` returns the status of `local` (0), so `set -e` and `$?` never see cmd fail. The same goes for `export` and `readonly`. Declare first, assign on the next line. ### SSH_STDIN_LOOP `while read host; do ssh "$host" cmd; done < hosts.txt` stops after the first host because ssh reads the rest of stdin. Use `ssh -n` or `< /dev/null`. The same applies to ffmpeg, docker exec -i and anything else that reads stdin. ### TLS_DISABLED `curl -k`, `wget --no-check-certificate` and `StrictHostKeyChecking=no` turn off the check that you are talking to the right server. Fix the CA bundle, pin the host key in known_hosts, or use `StrictHostKeyChecking=accept-new` only for first contact on trusted networks. ### BASHISM_IN_SH Under `#!/bin/sh` (dash on Debian/Ubuntu, busybox on Alpine) these fail: `[[ ]]`, `==` in `[ ]`, `function f`, arrays, `source`, `<<<`, `&>`, `$'...'`. Switch the shebang to bash or use `[ ]`, `=`, `.`, and positional parameters. ## LOW - NO_SHEBANG: the caller's shell runs it; behavior differs between bash, zsh and sh. - READ_WITHOUT_R: `read` without `-r` eats backslashes; without `IFS=` it trims leading and trailing spaces. - BACKTICKS: hard to nest and quote; use `$(...)`. ## Not covered by the linter (check by hand) - Untrusted input reaching sql, ssh or paths (injection, `../` traversal); a leading `-` in file names (use `--`). - Race conditions on lock files; use `flock`. - Missing `wait` for background jobs, and orphaned jobs on exit. - Locale and tool differences (GNU vs BSD sed -i, date -d, readlink -f on macOS). - Logging secrets with `set -x`; turn xtrace off around secret handling. FILE:references/safe-patterns.md # Safe patterns for bash and sh scripts ## Header ```bash #!/usr/bin/env bash # What it does, how to call it, what it needs (env vars, tools). set -euo pipefail IFS=$'\n\t' # optional: no splitting on spaces readonly SCRIPT_DIR="$(cd -- "$(dirname -- "BASH_SOURCE[0]")" && pwd)" ``` POSIX sh: `set -eu` (pipefail exists in dash 0.5.11+ and busybox, but not everywhere). ## Fail with a message ```bash die() { printf 'error: %s\n' "$*" >&2; exit 1; } command -v jq >/dev/null 2>&1 || die "jq is required" [ -d "$TARGET" ] || die "no such directory: $TARGET" ``` ## Arguments ```bash usage() { echo "usage: $0 [-n] <release>" >&2; exit 2; } dry_run=0 while getopts ":n" opt; do case $opt in n) dry_run=1 ;; *) usage ;; esac done shift $((OPTIND - 1)) release="-"; [ -n "$release" ] || usage ``` ## Temp files and cleanup ```bash tmpdir="$(mktemp -d)" cleanup() { rm -rf -- "$tmpdir"; } trap cleanup EXIT trap 'exit 130' INT TERM # EXIT trap still runs ``` ## Destructive steps ```bash target="?WEB_ROOT is empty" case $target in /|/usr|/etc|"$HOME") die "refusing to clean $target" ;; esac run() { if [ "$dry_run" -eq 1 ]; then echo "+ $*"; else "$@"; fi; } run rm -rf -- "$target"/* ``` ## Loops over files and lines ```bash for f in "$dir"/*.csv; do [ -e "$f" ] || continue; process "$f"; done find "$dir" -type f -name '*.log' -print0 | while IFS= read -r -d '' f; do gzip -- "$f"; done while IFS= read -r line || [ -n "$line" ]; do echo "$line"; done < input.txt # last line without newline ``` ## Commands as data ```bash args=(--archive --delete -- "$src/" "$dst/") [ "$dry_run" -eq 1 ] && args=(--dry-run "args[@]") rsync "args[@]" ``` ## Secrets - Read from the environment or a file with 600 permissions: `token="$(<"$CRED_FILE")"`. - Pass to tools via stdin or env, not command-line arguments (visible in `ps`): `curl -H @- <<<"Authorization: Bearer $token"`. - `set +x` around secret handling. ## Downloads ```bash curl -fsSL --retry 3 -o "$tmpdir/tool.tgz" "$URL" echo "$SHA256 $tmpdir/tool.tgz" | sha256sum -c - ``` `-f` makes HTTP errors fail instead of saving the error page. ## Locking and idempotency ```bash exec 9>/var/lock/deploy.lock flock -n 9 || die "another deploy is running" mkdir -p -- "$dir" # no error if it exists ln -sfn -- "$release_dir" current # atomic switch of a symlink ``` ## Logging ```bash log() { printf '%s %s\n' "$(date -u +%FT%TZ)" "$*" >&2; } ``` Send logs to stderr so stdout stays clean for data. ## Testing a script safely 1. `bash -n script.sh` (syntax only), `shellcheck script.sh`, `python3 scripts/shell_lint.py script.sh`. 2. Run with a dry-run flag, then in a throwaway container: `docker run --rm -v "$PWD":/w -w /w bash:5 bash script.sh`. 3. Trace once with `bash -x script.sh` (secrets off). FILE:templates/shell-review.md # Shell script review: <script name> **Runs from:** <cron on web-01 as root / GitHub Actions ubuntu-24.04 / Docker entrypoint (Alpine, busybox sh)> **Shell:** <bash 5.2 / sh (dash)> **Untrusted inputs:** <$1 release tag, hosts.txt, file names in uploads/> **Verdict:** <BLOCK: 3 HIGH findings / OK with fixes / OK> **Summary:** <one or two sentences: the worst risk and what the fix changes> ## Findings | # | Severity | Code | Line | Problem | Fix | |---|---|---|---|---|---| | 1 | <HIGH> | <RM_EMPTY_VAR> | <15> | <rm -rf $WEB_ROOT/* deletes /* if WEB_ROOT is empty> | <rm -rf -- "?"/*> | ## Manual review notes - <inputs that reach rm/ssh/sql/paths, and how they are validated> - <idempotency: what happens if it runs twice or stops halfway> - <cleanup on exit and on Ctrl+C> ## Fixed script ```bash <full corrected script> ``` ## Key changes ```diff - rm -rf $WEB_ROOT/* + rm -rf -- "?"/* ``` ## Behavior changes - <now exits on the first failed command; previously continued> ## How to test safely 1. `bash -n <script>` and `python3 scripts/shell_lint.py <script>` (expect 0 HIGH) 2. `shellcheck <script>` if available 3. Dry run or a throwaway container: `<command>` 4. <rotate the exposed secret: where and who> FILE:examples/deploy.sh #!/bin/bash # Deploy the web app build to /srv/www and restart the service. # Usage: deploy.sh <release-tag> RELEASE=$1 BUILD_DIR=/srv/builds/$RELEASE WEB_ROOT=/srv/www SLACK_TOKEN="xoxb-0000-EXAMPLE-not-real" curl -fsSL https://get.example-tools.dev/install.sh | sudo bash cd $BUILD_DIR echo "Deploying $RELEASE" > /tmp/deploy.log rm -rf $WEB_ROOT/* cp -r dist/* $WEB_ROOT/ for f in $(ls $BUILD_DIR/migrations); do psql -f $BUILD_DIR/migrations/$f app_db done chmod -R 777 $WEB_ROOT/uploads while read host; do ssh -o StrictHostKeyChecking=no deploy@$host "sudo systemctl restart web" done < hosts.txt cleanup() { local count=$(ls /srv/builds | wc -l) echo "Builds on disk: $count" } cleanup curl -s -X POST -H "Authorization: Bearer $SLACK_TOKEN" \ -d "text=Deployed `date`" https://slack.com/api/chat.postMessage FILE:examples/deploy-fixed.sh #!/usr/bin/env bash # Deploy the web app build to /srv/www and restart the service. # Usage: SLACK_TOKEN=... deploy.sh <release-tag> set -euo pipefail RELEASE="?usage: deploy.sh <release-tag>" BUILD_DIR="/srv/builds/RELEASE" WEB_ROOT="/srv/www" SLACK_TOKEN="?set SLACK_TOKEN in the environment" # Installer is pinned and checked instead of curl | bash. installer="$(mktemp)" log="$(mktemp)" trap 'rm -f -- "$installer" "$log"' EXIT curl -fsSL -o "$installer" https://get.example-tools.dev/install-1.4.2.sh echo "3b1f0c9e2d4a5b6c7d8e9f00112233445566778899aabbccddeeff0011223344 $installer" | sha256sum -c - sudo bash "$installer" cd -- "$BUILD_DIR" echo "Deploying RELEASE" > "$log" rm -rf -- "?"/* cp -r dist/. "$WEB_ROOT/" for f in "$BUILD_DIR"/migrations/*.sql; do [ -e "$f" ] || continue psql -v ON_ERROR_STOP=1 -f "$f" app_db done chmod -R u=rwX,g=rX,o= "$WEB_ROOT/uploads" while IFS= read -r host; do ssh -n -o StrictHostKeyChecking=yes "deploy@host" "sudo systemctl restart web" done < hosts.txt report_builds() { local count count="$(find /srv/builds -mindepth 1 -maxdepth 1 -type d | wc -l)" echo "Builds on disk: count" } report_builds curl -fsS -X POST -H "Authorization: Bearer SLACK_TOKEN" \ --data-urlencode "text=Deployed RELEASE at $(date -u +%FT%TZ)" https://slack.com/api/chat.postMessage FILE:scripts/shell_lint.py #!/usr/bin/env python3 """Shell script safety linter (Python 3 standard library only). Reads a bash or POSIX sh script and flags the mistakes that cause data loss, security holes or silent failures: no strict mode, rm -rf on a variable that may be empty, curl | sh, eval on variables, unquoted expansions (word splitting and globbing), unchecked cd, parsing ls, chmod 777, hardcoded secrets, predictable /tmp files, mktemp without a cleanup trap, read without -r, ssh eating a while-read loop's input, local/export masking a failed command substitution, TLS or host-key checks turned off, backticks and bashisms in /bin/sh. It is a fast first pass, not a full parser: heredoc bodies are skipped, quotes, comments, [[ ]], (( )), assignments and case words are understood, and every finding names the line and a concrete fix. Silence a finding on one line with a trailing comment "# shell-lint: disable=CODE[,CODE]". Usage: python3 shell_lint.py script.sh [more.sh ...] [--json] [--min-severity MEDIUM] [--ignore CODE,CODE] python3 shell_lint.py --self-test Exit codes: 0 = no HIGH findings, 1 = at least one HIGH finding, 2 = unreadable input. """ import argparse, json, re, sys SEV_ORDER = {"HIGH": 0, "MEDIUM": 1, "LOW": 2, "INFO": 3} SECRET_NAME = re.compile(r"[A-Z0-9_]*(PASSWORD|PASSWD|TOKEN|SECRET|API_?KEY|ACCESS_KEY|PRIVATE_KEY)[A-Z0-9_]*") FIXES = { "NO_SHEBANG": "Add '#!/usr/bin/env bash' (or '#!/bin/sh' for POSIX) as line 1 so the right shell runs it.", "NO_STRICT_MODE": "Add 'set -euo pipefail' after the shebang (POSIX sh: 'set -eu') so failures stop the script.", "NO_PIPEFAIL": "Add 'set -o pipefail' so 'cmd | tee log' fails when cmd fails.", "RM_EMPTY_VAR": "Guard the variable: rm -rf -- \"?VAR is empty/\"... and quote it; never build rm paths from unchecked input.", "RM_ROOT": "Never rm the filesystem root or /*; point at a specific directory.", "CURL_PIPE_SHELL": "Download to a file, verify a checksum or signature, read it, then run it.", "EVAL_VAR": "Avoid eval; use arrays for commands (cmd=(ls -l \"$dir\"); \"cmd[@]\") or a case statement.", "UNQUOTED_VAR": "Quote expansions: \"$var\", \"arr[@]\", \"$(cmd)\" to stop word splitting and globbing.", "CD_UNCHECKED": "Use 'cd \"$dir\" || exit 1' (or enable set -e) so later commands do not run in the wrong directory.", "LS_PARSING": "Use a glob (for f in ./*.log) or find -print0 | while IFS= read -r -d '' f instead of parsing ls/find output.", "BACKTICKS": "Use $(...) instead of backticks; it nests and quotes predictably.", "CHMOD_777": "Grant only what is needed (755 for dirs/executables, 644 for files, 600 for secrets).", "HARDCODED_SECRET": "Read the secret from the environment or a secret manager (\"?\"), rotate the exposed value.", "PREDICTABLE_TMP": "Use tmp=$(mktemp) or tmpdir=$(mktemp -d) instead of a fixed /tmp name (symlink and race attacks).", "NO_TRAP_CLEANUP": "Add trap 'rm -rf -- \"$tmpdir\"' EXIT right after mktemp.", "READ_WITHOUT_R": "Use 'read -r' (and IFS= read -r line) so backslashes and leading spaces survive.", "LOCAL_MASKS_STATUS": "Split it: 'local out; out=$(cmd)' so set -e and $? see the command's failure.", "TLS_DISABLED": "Remove -k/--insecure/--no-check-certificate/StrictHostKeyChecking=no; fix the CA bundle or pin the host key.", "SSH_STDIN_LOOP": "Use 'ssh -n' (or ssh ... < /dev/null) inside while-read loops; otherwise ssh eats the rest of the input.", "BASHISM_IN_SH": "Either change the shebang to bash or use the POSIX form ([ ], =, . file, no arrays).", } class Finding: def __init__(self, sev, code, line, snippet, problem): self.sev, self.code, self.line, self.snippet, self.problem = sev, code, line, snippet, problem def as_dict(self): return {"severity": self.sev, "code": self.code, "line": self.line, "snippet": self.snippet, "problem": self.problem, "fix": FIXES.get(self.code, "")} def strip_comment(line): """Remove an unquoted comment. '#' starts a comment only at the start of a word.""" state, i = None, 0 while i < len(line): c = line[i] if state is None: if c == "\\": i += 2; continue if c in "'\"`": state = c elif c == "#" and (i == 0 or line[i - 1] in " \t;|&("): return line[:i].rstrip() elif state == "'": if c == "'": state = None else: if c == "\\": i += 2; continue if c == state: state = None i += 1 return line def logical_lines(text): """Yield (line_no, code_without_comment, raw_line, disabled_codes); heredoc bodies skipped.""" lines = text.split("\n") pending = [] # heredoc terminators waiting, in order out = [] for no, raw in enumerate(lines, 1): if pending: delim, dash = pending[0] body = raw.lstrip("\t") if dash else raw if body == delim: pending.pop(0) continue m = re.search(r"#\s*shell-lint:\s*disable=([A-Z_,\s]+)", raw) disabled = {c.strip() for c in m.group(1).split(",")} if m else set() code = strip_comment(raw) for hm in re.finditer(r"<<(-?)\s*(['\"]?)([A-Za-z_][A-Za-z0-9_]*)\2", code): if code[hm.start():hm.start() + 3] == "<<<": continue pending.append((hm.group(3), hm.group(1) == "-")) out.append((no, code, raw, disabled)) return out def match_paren(s, i): """s[i] == '(' ; return index of the matching ')' respecting quotes, or len(s).""" depth, state, j = 0, None, i while j < len(s): c = s[j] if state is None: if c == "\\": j += 2; continue if c in "'\"": state = c elif c == "(": depth += 1 elif c == ")": depth -= 1 if depth == 0: return j elif c == state: state = None elif c == "\\" and state == '"': j += 1 j += 1 return len(s) def unquoted_expansions(code): """Return names of $var / ... / $(...) / $@ expansions that are not inside quotes and not in a context where splitting cannot happen ([[ ]], (( )), assignments, case word).""" found = [] stripped = code.strip() if re.match(r"case\s", stripped): return found state, i, dbl_brackets, arith = None, 0, 0, 0 n = len(code) def word_start(k): while k > 0 and code[k - 1] not in " \t;|&(": k -= 1 return k while i < n: c = code[i] if state == "'": if c == "'": state = None i += 1; continue if c == "\\": i += 2; continue if state is None and c == "'": state = "'"; i += 1; continue if c == '"': state = None if state == '"' else '"' i += 1; continue if state is None and code.startswith("[[", i) and (i == 0 or code[i - 1] in " \t;&|!("): dbl_brackets += 1; i += 2; continue if state is None and code.startswith("]]", i) and dbl_brackets: dbl_brackets -= 1; i += 2; continue if state is None and code.startswith("((", i) and (i == 0 or code[i - 1] in " \t;&|!"): end = match_paren(code, i) i = end + 1; continue if c == "`": end = code.find("`", i + 1) i = (end if end != -1 else n) + 1; continue if c == "$" and i + 1 < n: nxt = code[i + 1] if code.startswith("$((", i): end = match_paren(code, i + 1) i = end + 1; continue if nxt == "(": end = match_paren(code, i + 1) inner = code[i + 2:end] found.extend(unquoted_expansions(inner)) if state is None and not dbl_brackets and not arith: ws = word_start(i) if not re.match(r"[A-Za-z_][A-Za-z0-9_]*(\[[^\]]*\])?\+?=", code[ws:i + 1]): found.append("$(" + inner.strip()[:20] + ")") i = end + 1; continue m = re.match(r"\$(\{[^}]*\}|[A-Za-z_][A-Za-z0-9_]*|[@*]|[0-9])", code[i:]) if m: tok = m.group(0) if state is None and not dbl_brackets: ws = word_start(i) is_assign = re.match(r"[A-Za-z_][A-Za-z0-9_]*(\[[^\]]*\])?\+?=", code[ws:i + 1]) if not is_assign and not tok.startswith("found.append(tok) i += len(tok); continue i += 1 return found def commands(code): """Split a line into simple commands on ; && || | (outside quotes).""" parts, cur, state, i = [], "", None, 0 while i < len(code): c = code[i] if state is None and c in "'\"": state = c elif state and c == state: state = None if state is None and c in ";|&": parts.append(cur); cur = "" while i + 1 < len(code) and code[i + 1] in ";|&": i += 1 i += 1; continue cur += c; i += 1 parts.append(cur) return [p.strip() for p in parts if p.strip()] def lint(text, name="script"): findings = [] lines = logical_lines(text) first = text.split("\n", 1)[0] shebang = first if first.startswith("#!") else "" is_posix_sh = bool(re.search(r"(^#!\s*/bin/sh\b|/env\s+sh\b|/bin/dash\b)", shebang)) all_code = "\n".join(c for _, c, _, _ in lines) strict_e = bool(re.search(r"(^|\n)\s*set\s+(-[a-zA-Z]*e[a-zA-Z]*\b|-o\s+errexit)", all_code)) or bool(re.search(r"\s-[a-z]*e", shebang)) pipefail = "pipefail" in all_code uses_pipes = "|" in all_code.replace("||", "") uses_mktemp = "mktemp" in all_code has_trap = bool(re.search(r"(^|\n)\s*trap\s", all_code)) def add(sev, code, no, snippet, problem, disabled=frozenset()): if code not in disabled: findings.append(Finding(sev, code, no, snippet.strip()[:80], problem)) if not shebang: add("LOW", "NO_SHEBANG", 1, first, "no shebang line; the caller's shell decides how this runs") if not strict_e: add("MEDIUM", "NO_STRICT_MODE", 1, shebang or first, "no 'set -e' (errexit): a failed command does not stop the script") elif uses_pipes and not pipefail and not is_posix_sh: add("LOW", "NO_PIPEFAIL", 1, shebang, "pipes are used but pipefail is off: only the last command's status counts") if uses_mktemp and not has_trap: no = next((n for n, c, _, _ in lines if "mktemp" in c), 1) add("LOW", "NO_TRAP_CLEANUP", no, next(c for n, c, _, _ in lines if n == no), "mktemp is used but nothing removes the temp file on exit or error") read_loop = 0 for no, code, raw, dis in lines: if not code.strip(): continue if re.search(r"(^|[;&|\s])while\b.*\bread\b", code): read_loop += 1 if read_loop and re.search(r"(^|[;&|\s])ssh\s", code) and not re.search(r"\bssh\s+(-\w+\s+)*-\w*n\b|<\s*/dev/null", code): add("MEDIUM", "SSH_STDIN_LOOP", no, code, "ssh inside a while-read loop reads the loop's stdin, so the loop stops after the first item", dis) if read_loop and re.search(r"(^|[;&|\s])done\b", code): read_loop -= 1 # curl | sh if re.search(r"\b(curl|wget)\b[^|]*\|\s*(sudo\s+(-\S+\s+)*)?(ba|z|da|k)?sh\b", code): add("HIGH", "CURL_PIPE_SHELL", no, code, "remote script piped straight into a shell: no review, no integrity check, partial downloads run", dis) if re.search(r"\b(curl\b[^|;]*\s(-k|--insecure)\b|wget\b[^|;]*--no-check-certificate|StrictHostKeyChecking=no)", code): add("MEDIUM", "TLS_DISABLED", no, code, "certificate or host-key verification is turned off (man-in-the-middle risk)", dis) if re.search(r"(^|[;&|(\s])eval\s+.*\$", code): add("HIGH", "EVAL_VAR", no, code, "eval on a string built from variables runs any shell code an attacker or odd filename puts in it", dis) if re.search(r"\bchmod\s+(-[A-Za-z]+\s+)*0?777\b|\bchmod\s+(-[A-Za-z]+\s+)*[augo]*\+rwx\b", code) and re.search(r"777|a\+rwx|o\+rwx", code): add("MEDIUM", "CHMOD_777", no, code, "world-writable permissions let any local user change the files", dis) if "`" in code: add("LOW", "BACKTICKS", no, code, "legacy backtick command substitution", dis) m = re.match(r"\s*(export\s+|readonly\s+|local\s+|declare\s+(-\w+\s+)*)?(" + SECRET_NAME.pattern + r")=(['\"]?)([^$'\"\s][^'\"\s]{3,)", code) if m: val = m.group(6) masked = val[:2] + "*" * max(3, len(val) - 4) + val[-2:] add("HIGH", "HARDCODED_SECRET", no, code.replace(val, masked), f"{m.group(3)} has a literal value in the script (ends up in git, logs and ps output)", dis) if re.match(r"\s*(local|export|declare|readonly)\s+(-\w+\s+)*[A-Za-z_][A-Za-z0-9_]*=\"?\$\(", code): add("MEDIUM", "LOCAL_MASKS_STATUS", no, code, "the builtin's exit status (0) hides a failure of the command substitution, even under set -e", dis) if re.search(r"(^|[^\w.-])/tmp/[A-Za-z0-9_.${}-]+", code) and "mktemp" not in code: add("MEDIUM", "PREDICTABLE_TMP", no, code, "fixed /tmp path: another user can pre-create or symlink it, and parallel runs collide", dis) if re.search(r"\bfor\s+\w+\s+in\s+(\$\(|`)\s*(ls|find)\b", code) or re.search(r"\bls\b[^|]*\|\s*(while|xargs)\b", code): add("MEDIUM", "LS_PARSING", no, code, "parsing ls/find output breaks on spaces, newlines and glob characters in filenames", dis) for cmd in commands(code): words = cmd.split() if not words: continue w0 = words[0] while w0 in ("sudo", "command", "builtin", "exec", "!", "while", "until", "if", "then", "do", "else", "elif", "{", "(") and len(words) > 1: words = words[1:]; w0 = words[0] if w0 == "rm": args = words[1:] rec = any(re.match(r"-[a-zA-Z]*[rR]", a) or a == "--recursive" for a in args) if any(a.strip("'\"") in ("/", "/*") for a in args): add("HIGH", "RM_ROOT", no, cmd, "rm targets / or /*", dis) for a in args: if a.startswith("-") and a != "--": continue if "$" in a and not re.search(r"\$\{[A-Za-z_][A-Za-z0-9_]*:\?", a): a_clean = a.strip("'\"") if re.match(r"\$\{?[A-Za-z_][A-Za-z0-9_]*\}?(/|\"?$)", a_clean) or rec: add("HIGH" if rec else "MEDIUM", "RM_EMPTY_VAR", no, cmd, f"rm{' -r' if rec else ''} on {a}: if the variable is empty or unset this deletes the wrong path (e.g. '/')", dis) break if w0 == "cd" and not strict_e: if not re.search(re.escape(cmd) + r"\s*(\|\||&&)", code) and not re.search(r"(\|\||&&)\s*" + re.escape(cmd), code): add("MEDIUM", "CD_UNCHECKED", no, cmd, "cd may fail and the following commands then run in the wrong directory", dis) if w0 == "read": opts = [a for a in words[1:] if a.startswith("-")] if not any("r" in o for o in opts): add("LOW", "READ_WITHOUT_R", no, cmd, "read without -r mangles backslashes", dis) exps = unquoted_expansions(code) if exps: uniq = list(dict.fromkeys(exps)) shown = ", ".join(uniq[:4]) + (" ..." if len(uniq) > 4 else "") only_echo = re.match(r"\s*(echo|printf)\s+[^|>]*$", code) and not any(e in ("$@", "$*") for e in uniq) if only_echo: add("LOW", "UNQUOTED_VAR", no, code, "unquoted " + shown + " in echo/printf: spacing collapses and * expands", dis) else: add("MEDIUM", "UNQUOTED_VAR", no, code, "unquoted " + shown + ": word splitting and glob expansion", dis) if is_posix_sh: bash = [] if re.search(r"(^|[\s;&|!])\[\[\s", code): bash.append("[[ ]]") if re.search(r"(^|[\s;&|])\[\s[^]]*\s==\s", code): bash.append("== in [ ]") if re.match(r"\s*function\s+\w+", code): bash.append("function keyword") if re.search(r"\b[A-Za-z_]\w*=\(", code): bash.append("array") if re.match(r"\s*source\s", code): bash.append("source") if "<<<" in code: bash.append("here-string") if re.search(r"&>", code): bash.append("&>") if re.search(r"\$'", code): bash.append("$'...'") if bash: add("MEDIUM", "BASHISM_IN_SH", no, code, "bash-only syntax under #!/bin/sh (" + ", ".join(bash) + "); dash and busybox sh will fail", dis) findings.sort(key=lambda f: (SEV_ORDER[f.sev], f.line, f.code)) return findings def render_md(name, findings): counts = {s: sum(1 for f in findings if f.sev == s) for s in SEV_ORDER} out = [f"# Shell lint: {name}", "", f"Findings: {len(findings)} (HIGH {counts['HIGH']}, MEDIUM {counts['MEDIUM']}, LOW {counts['LOW']})", ""] if not findings: out.append("No findings.") return "\n".join(out) out += ["| Severity | Code | Line | Snippet | Problem |", "|---|---|---|---|---|"] for f in findings: snip = f.snippet.replace("|", "\\|").replace("`", "'") out.append(f"| {f.sev} | {f.code} | {f.line} | `{snip}` | {f.problem.replace('|', '/')} |") out += ["", "## Fixes", ""] for code in dict.fromkeys(f.code for f in findings): out.append(f"- {code}: {FIXES[code]}") return "\n".join(out) # ---------------------------------------------------------------- self-test def _codes(src): return [f.code for f in lint(src)] def self_test(): S = "#!/usr/bin/env bash\nset -euo pipefail\n" cases = [ ("clean script has no findings", S + 'dir="$1"\ncp -- "$dir/a" "$dir/b"\n', lambda c: c == []), ("missing shebang", 'set -eu\necho "hi"\n', lambda c: "NO_SHEBANG" in c), ("missing strict mode", "#!/bin/bash\necho ok\n", lambda c: "NO_STRICT_MODE" in c), ("set -e without pipefail and a pipe", "#!/bin/bash\nset -e\nmake | tee log\n", lambda c: "NO_PIPEFAIL" in c), ("set -o errexit counts as strict", "#!/bin/bash\nset -o errexit\nset -o pipefail\necho ok\n", lambda c: c == []), ("rm -rf $VAR/ is HIGH", S + 'rm -rf "$BUILD_DIR"/\n', lambda c: "RM_EMPTY_VAR" in c), ("rm -rf ? is fine", S + 'rm -rf -- "?/"\n', lambda c: "RM_EMPTY_VAR" not in c), ("rm -rf / is RM_ROOT", S + "rm -rf /\n", lambda c: "RM_ROOT" in c), ("curl | bash", S + "curl -fsSL https://x.example/install.sh | sudo bash\n", lambda c: "CURL_PIPE_SHELL" in c), ("curl to file is fine", S + 'curl -fsSLo install.sh "$URL"\n', lambda c: "CURL_PIPE_SHELL" not in c), ("eval with variable", S + 'eval "$cmd"\n', lambda c: "EVAL_VAR" in c), ("unquoted var in command", S + "cp $src $dst\n", lambda c: "UNQUOTED_VAR" in c), ("quoted var is fine", S + 'cp "$src" "$dst"\n', lambda c: "UNQUOTED_VAR" not in c), ("assignment rhs is fine", S + "x=$y\nn=$(date +%s)\n", lambda c: "UNQUOTED_VAR" not in c), ("[[ ]] is fine", S + "if [[ $a == b ]]; then :; fi\n", lambda c: "UNQUOTED_VAR" not in c), ("arith is fine", S + "n=$(( $a + 1 ))\n(( count > $max ))\n", lambda c: "UNQUOTED_VAR" not in c), ("#arr is fine", S + 'printf "%s\\n" #arr[@]\n', lambda c: "UNQUOTED_VAR" not in c), ("unquoted $@", S + "run $@\n", lambda c: "UNQUOTED_VAR" in c), ("unquoted cmd substitution", S + "rm -- $(cat list)\n", lambda c: "UNQUOTED_VAR" in c), ("var inside quoted $( ) still checked", S + 'x="$(basename $path)"\n', lambda c: "UNQUOTED_VAR" in c), ("single quotes protect", S + "echo '$HOME is literal'\n", lambda c: "UNQUOTED_VAR" not in c), ("comment ignored", S + "# rm -rf $DIR/ and curl x | sh\n", lambda c: c == []), ("heredoc body skipped", S + "cat <<'EOF'\nrm -rf $X/\ncurl a | sh\nEOF\n", lambda c: c == []), ("cd unchecked without set -e", "#!/bin/bash\ncd /srv/app\n", lambda c: "CD_UNCHECKED" in c), ("cd || exit is fine", "#!/bin/bash\ncd /srv/app || exit 1\n", lambda c: "CD_UNCHECKED" not in c), ("for in $(ls)", S + "for f in $(ls *.log); do :; done\n", lambda c: "LS_PARSING" in c), ("backticks", S + "d=`date`\n", lambda c: "BACKTICKS" in c), ("chmod 777", S + "chmod -R 777 /srv/app\n", lambda c: "CHMOD_777" in c), ("hardcoded token, value masked", S + 'export API_TOKEN="sk_live_abcdef123456"\n', lambda c: "HARDCODED_SECRET" in c), ("secret from env is fine", S + 'API_TOKEN="?"\n', lambda c: "HARDCODED_SECRET" not in c), ("predictable tmp", S + 'echo "x" > /tmp/deploy.lock\n', lambda c: "PREDICTABLE_TMP" in c), ("mktemp without trap", S + "tmp=$(mktemp)\n", lambda c: "NO_TRAP_CLEANUP" in c), ("mktemp with trap fine", S + "tmp=$(mktemp)\ntrap 'rm -f -- \"$tmp\"' EXIT\n", lambda c: "NO_TRAP_CLEANUP" not in c), ("read without -r", S + 'while read line; do :; done < "$f"\n', lambda c: "READ_WITHOUT_R" in c), ("local masks status", S + "f() {\n local out=$(git rev-parse HEAD)\n}\n", lambda c: "LOCAL_MASKS_STATUS" in c), ("curl -k", S + 'curl -k "$URL" -o x\n', lambda c: "TLS_DISABLED" in c), ("bashisms under /bin/sh", "#!/bin/sh\nset -eu\nif [[ -n \"$x\" ]]; then source ./env; fi\n", lambda c: "BASHISM_IN_SH" in c), ("bash allows [[ ]]", S + 'if [[ -n "$x" ]]; then source ./env; fi\n', lambda c: "BASHISM_IN_SH" not in c), ("disable comment", S + "cp $a $b # shell-lint: disable=UNQUOTED_VAR\n", lambda c: "UNQUOTED_VAR" not in c), ("ssh in while-read loop", S + 'while IFS= read -r h; do\n ssh "$h" uptime\ndone < hosts\n', lambda c: "SSH_STDIN_LOOP" in c), ("ssh -n in loop is fine", S + 'while IFS= read -r h; do\n ssh -n "$h" uptime\ndone < hosts\n', lambda c: "SSH_STDIN_LOOP" not in c), ("case word fine", S + 'case $1 in\n a) echo "a" ;;\nesac\n', lambda c: "UNQUOTED_VAR" not in c), ] passed = 0 for name, src, check in cases: got = _codes(src) ok = check(got) passed += ok print(("PASS " if ok else "FAIL ") + name + ("" if ok else f" -> {got}")) # masking check and exit code check f = [x for x in lint(S + 'export API_TOKEN="sk_live_abcdef123456"\n') if x.code == "HARDCODED_SECRET"][0] ok = "abcdef123456" not in f.snippet passed += ok; print(("PASS " if ok else "FAIL ") + "secret value masked in output") ok = render_md("t", []).endswith("No findings.") passed += ok; print(("PASS " if ok else "FAIL ") + "empty report") total = len(cases) + 2 print(f"{passed}/{total} passed") return 0 if passed == total else 1 def main(): ap = argparse.ArgumentParser(description="Shell script safety linter (stdlib only)") ap.add_argument("files", nargs="*") ap.add_argument("--json", action="store_true") ap.add_argument("--min-severity", default="LOW", choices=list(SEV_ORDER)) ap.add_argument("--ignore", default="", help="comma-separated codes to skip") ap.add_argument("--self-test", action="store_true") a = ap.parse_args() if a.self_test: return self_test() if not a.files: ap.error("give at least one script or --self-test") ignore = {c.strip() for c in a.ignore.split(",") if c.strip()} worst, reports, js = 0, [], {} for path in a.files: try: with open(path, encoding="utf-8") as fh: text = fh.read() except (OSError, UnicodeDecodeError) as e: print(f"error: cannot read {path}: {e}", file=sys.stderr) return 2 fs = [f for f in lint(text, path) if f.code not in ignore and SEV_ORDER[f.sev] <= SEV_ORDER[a.min_severity]] if any(f.sev == "HIGH" for f in fs): worst = 1 js[path] = [f.as_dict() for f in fs] reports.append(render_md(path, fs)) print(json.dumps(js, indent=2) if a.json else "\n\n".join(reports)) return worst if __name__ == "__main__": sys.exit(main())
Medieval illuminated manuscript page on aged vellum: a large blue, red, and gold initial and blocks of illegible blackletter script on the left, a gold-framed miniature of a hooded beekeeper with a smoking stick tending three straw skeps in a walled garden with an apple tree, roses, lilies, and golden bees, inside a gold vine and acanthus border with small snails.
A medieval illuminated manuscript page on aged, stained vellum showing a hooded beekeeper tending three woven straw bee skeps in a walled garden, in a wide landscape 16:9 page view. On the left, a large decorated initial letter at the top left is painted in lapis blue, vermilion red, and burnished gold with small floral patterns inside, followed by short blocks of dense, illegible blackletter-style script in dark brown ink, with the last two lines in red. On the right, a gold-framed miniature painting shows a beekeeper in a long brown hooded robe, face hidden in the hood, bending forward and holding a thin smoking stick toward three dome-shaped straw skeps with small dark entrances, standing on green grass dotted with tiny yellow flowers. Behind him rises a grey crenellated stone garden wall, an apple tree heavy with red-gold apples, pink and red roses, tall white lilies, and spikes of blue delphinium-like flowers, with small golden bees flying in the air. The whole page is surrounded by an ornate border of curling gold vines with blue and red acanthus leaves, and small painted snails sit in the bottom left corner and on the right edge. Flat gothic painting style, fine ink outlines, rich lapis blue, vermilion, and gold palette, gently worn edges. No legible text, no modern elements.
Describe a faulty product or an undelivered service and get a strict JSON plan: a case summary with the remedy to ask for, a timeline, an evidence checklist, deadlines to confirm officially, an escalation ladder from seller support to card disputes and consumer bodies, polite message drafts for each step, a call script, and a follow-up schedule.
1{2 "role": "Calm, organized consumer advocate who helps people get a refund, replacement, or repair for a faulty product or a service that was not delivered as promised, by building a clear evidence file and a polite, escalating plan, without exaggerating, threatening, or giving legal advice",3 "task": "Turn my purchase details, the problem, and what has happened so far into a single strict JSON plan: a case summary with the remedy I should ask for, a timeline of events, an evidence checklist with what I have and what is missing, the deadlines that may apply and must be confirmed, a step-by-step escalation ladder from the seller's own support to card or payment disputes and official consumer bodies, ready-to-send message drafts for each step, a follow-up schedule, and a short call script. Never invent laws, deadlines, policies, case numbers, or facts that I did not give.",4 "context": {5 "item_or_service": "${item:a cordless vacuum cleaner}",6 "seller": "${seller:a large online electronics store}",7 "purchase_date": "${purchase_date:5 weeks ago}",8 "price_paid": "${price:249}",9 "currency": "${currency:EUR}",10 "payment_method": "${payment:credit card}",...+120 more lines
Act as an expert frontend developer specializing in Three.js and WebGL. I want to build a premium 3D sneaker showcase website. Write a complete, single-file HTML code. Requirements: 1) Load a 3D model named 'shoe.glb' using GLTFLoader. 2) Center the model automatically and add responsive scaling. 3) Implement smooth auto-rotation and mouse-tracking parallax effects. 4) Use a luxury dark studio lighting setup with a soft shadow floor effect. 5) Create a modern, responsive glassmorphism UI layer over the canvas with a bold title 'AERO GLIDE', a sleek description, a custom pricing tag, and a hover-animated 'Pre-Order Now' button. Ensure the code is production-ready, highly optimized, and contains no placeholders."
Act as a Shia Islamic Reels & Motion Expert. Produce 9:16 (1080x1920, 15-60s, 25-30fps, MP4 + AEP/PRPROJ) blueprints blending modern trends with reverent Shia aesthetics. Rules: ZERO baked-in text/watermarks in visuals (no text). Strict aniconism: NO holy figures' faces/forms; use light, architecture & vector metaphors. Serene 2.5D pacing, vocal-free ambient audio & subtle SFX.
System Role & Objective You are an elite Short-Form Video Production & Motion Graphics Expert specializing in contemporary Islamic visual identity, with deep cultural and aesthetic sensitivity to Shia Islamic heritage. Your objective is to conceptualize, plan, and produce professional vertical Reels that blend modern visual trends with reverent, authentic Shia Islamic aesthetics under strict technical and artistic guidelines. If visual assets are not provided by the user, you must generate or source complete visual prompts, asset lists, and production blueprints. CRITICAL RULE: No generated image, background, texture, or video clip may contain any embedded or baked-in text, typography, or calligraphy, ensuring zero visual conflict with post-production text layers. Core Production Guidelines & Specifications 1. Technical Specifications Resolution & Aspect Ratio: 1080x1920 pixels (9:16 vertical format). Duration: 15 to 60 seconds (optimized for viewer retention and pacing). Frame Rate: 25–30 fps. Codec & Export Format: H.264 encoded .MP4 for final delivery, accompanied by a structured, high-quality, non-destructive editable project architecture (.AEP for After Effects or .PRPROJ for Premiere Pro). 2. Visual Identity & Color Palettes Aesthetic Direction: Serene, modern, and trend-aligned, incorporating tactile overlays such as fine paper grain and subtle fabric/linen textures. Color Palette Proposals: Provide 3 distinct color palette options for every project, complete with exact HEX codes, anchored around: Deep Emerald Green Midnight / Royal Navy Blue Warm Antique / Brushed Gold Complementary neutral undertones (Warm Ivory, Soft Sand, or Charcoal). 3. Visual Style, Iconography & Cultural Compliance Symbolic Elements: Utilize metaphorical illustrations, geometric arabesques, Islamic architectural motifs (arches, domes, mashrabiyas, lanterns), and authentic Shia heritage symbols. Strict Aniconism Rule: Never depict faces or physical forms of holy figures, Prophets, or Imams. Use reverent symbolic representations (e.g., light rays, architectural silhouettes, nature motifs, symbolic objects) instead. Art Style: Cohesive hand-drawn or clean flat/textured vector illustrations that serve as expressive visual metaphors for the script's core concepts. 4. Motion Design, Pacing & Transitions Motion Language: Smooth, dignified, and contemplative pacing that evokes tranquility and reverence (Sakina and Waqar). Avoid jarring, rapid, or overly aggressive cuts. Transition Techniques: Employ seamless, varied transitions including soft crossfades, 2.5D parallax camera movements, subtle slow zooms, and elegant mask reveals accented with warm golden light or particle strokes. Rhythmic Sync: Align all visual keyframes and scene transitions precisely with the ambient audio cadence. 5. Audio & Sound Design Background Score: Spiritual, inspiring ambient background tracks or vocal-free acoustic/nasheed instrumentals (strictly no lyrics or distracting vocals). Sound Effects (SFX): Subtle, organic transition sounds (soft whooshes, gentle chimes, atmospheric risers, paper textures) mixed at low decibels to enhance immersion without overpowering the voiceover or mood. Licensing: All recommended audio tracks and SFX must carry clear, royalty-free commercial use licenses. 6. Typography & Text Handling (Zero Baked-In Text Policy) Clean Visuals Only: All visual plates, AI generation prompts, backgrounds, and B-roll footage must be 100% free of embedded text, letters, or watermarks (explicitly enforce negative prompts like --no text, typography, letters, watermark, calligraphy when generating visual prompts). Editable Text Workflow: Deliver all on-screen text as a separate, time-coded .SRT subtitle file alongside live, fully editable text layer specifications (font family, weight, tracking, and safe-zone placement) within the source project file. 7. Asset Sourcing & Licensing Quality Standard: Provide high-resolution stock footage, vector graphics, and textures completely free of watermarks. Documentation: Compile a structured Asset Manifest detailing the source link, asset type, and commercial license verification for every visual and audio element used. 8. Delivery Workflow & Deliverables Timeline: Structured for an initial draft delivery within 5–7 days, inclusive of one comprehensive revision round. Final Package Structure: Every completed blueprint/project must organize and deliver: Final rendered video specifications (.MP4). Source project layer structure (.AEP / .PRPROJ hierarchy). The 3 HEX Color Palette options & Texture Guide. Scene-by-scene Storyboard & clean AI/Stock Visual Prompts (text-free). Separate .SRT caption file & Typography specifications. Commercial Asset & Audio Licensing Manifest. Execution Standard Execute every request with senior-level art direction, ensuring complete technical precision, cultural authenticity, and visual sophistication that faithfully reflects a modern, reverent Shia Islamic identity.
A 30-second, 9:16 photoreal 3D animation of four costumed cats (witch, ghost, skeleton, pumpkin) and an upright Frankenstein tiger dancing in sync in a sunny Halloween village courtyard, scripted in six timed scenes with audio cues and strict character-consistency rules. Trending on X, originally shared by @Just_sharon7: https://x.com/Just_sharon7/status/2113964901446094947
Video Style: Ultra-realistic 3D anthropomorphic animals, adorable Halloween costumes, energetic synchronized dancing, whimsical fantasy atmosphere, vibrant autumn colors, cinematic animation, realistic fur textures, smooth character movements, 9:16 vertical format, 4K quality. SETTING: A beautiful Halloween-themed village courtyard during a bright, sunny autumn afternoon. A magnificent Victorian haunted mansion stands in the background, decorated with glowing jack-o'-lanterns, carved pumpkins, hanging lanterns, cobwebs, spooky ornaments, and Halloween decorations. Tall maple trees with brilliant orange, golden-yellow, and crimson-red leaves surround the courtyard. Fallen autumn leaves cover the cobblestone pathway. A vivid blue sky with fluffy white clouds creates a cheerful Halloween atmosphere. MAIN CHARACTERS: Four adorable anthropomorphic cats and one large anthropomorphic tiger, all standing upright on their hind legs. Witch Cat: A fluffy black-and-white cat wearing a pointed black witch hat, a purple witch dress, and a flowing purple cape. Ghost Cat: An adorable fluffy white cat wearing a flowing white ghost costume with a cute ghost hood and tiny black bow tie. Skeleton Cat: A fluffy black-and-white cat wearing a black skeleton jumpsuit featuring white bones and a small black Halloween hat. Pumpkin Cat: A chubby orange tabby cat wearing a bright orange pumpkin costume with a carved jack-o'-lantern face and a small green pumpkin-stem hat. Frankenstein Tiger: A large, realistic orange-and-black striped tiger standing upright like a human, wearing a green Frankenstein-style monster headpiece, a long dark-green Victorian coat, a purple waistcoat, and dark trousers. All characters have realistic animal faces, expressive eyes, detailed fluffy fur, and adorable personalities. SCENE 1 (0–5 seconds): GROUP DANCE INTRO The camera opens with a wide cinematic shot of the Halloween courtyard. Four adorable costumed cats stand in the foreground while the giant Frankenstein tiger stands behind them. All characters begin dancing together to a playful, upbeat Halloween rhythm. The cats bounce on their hind legs, swing their little paws, wiggle their bodies, and move their heads in perfect synchronization. The Frankenstein tiger performs exaggerated, funny dance moves behind them. The camera slowly pushes forward. SCENE 2 (5–10 seconds): CUTE CAT CLOSE-UPS A quick transition reveals the fluffy white ghost cat and the black skeleton cat dancing side by side. Both cats face the camera, raise their front paws near their faces, and perform matching playful paw gestures. Their costumes bounce naturally as they move. The ghost cat tilts its head adorably while the skeleton cat gives a confident little pose. Smooth camera tracking, realistic fur movement, cheerful expressions. SCENE 3 (10–15 seconds): GROUP DANCE FROM BEHIND The camera moves behind the dancing cats. The witch cat's purple cape sways dramatically, the ghost cat's white costume flutters, and the skeleton cat wiggles its hips. The cats perform synchronized side steps toward the Frankenstein tiger. The tiger dances enthusiastically in front of the haunted mansion. Autumn leaves scatter gently across the cobblestones. SCENE 4 (15–20 seconds): FRANKENSTEIN TIGER SOLO A medium close-up focuses on the giant Frankenstein tiger. The tiger performs a hilarious solo dance, crossing its furry arms over its chest, swinging its shoulders, moving its paws rhythmically, and nodding its head. Its long green coat sways naturally. The tiger maintains a serious, comical facial expression while dancing. The camera gently zooms closer, emphasizing the tiger's expressive face and detailed striped fur. SCENE 5 (20–25 seconds): PUMPKIN CAT TAKES CENTER STAGE The adorable chubby orange pumpkin cat steps into the center of the courtyard. It performs a funny bouncing dance, swinging its tiny paws from side to side. The pumpkin costume jiggles with every movement. The other cats dance around it while the Frankenstein tiger follows the rhythm in the background. A quick, playful camera movement emphasizes the energetic choreography. SCENE 6 (25–30 seconds): FINAL HALLOWEEN DANCE All five characters gather together for a final synchronized Halloween dance. The cats perform matching paw waves, little jumps, shoulder movements, and playful head tilts. The Frankenstein tiger stands behind them, performing exaggerated monster-style dance gestures. The camera alternates between a wide group shot and a close-up of the tiger. The video ends with the Frankenstein tiger crossing its furry arms confidently while the cats strike adorable Halloween poses. CAMERA & VISUAL QUALITY: Photorealistic 3D animal animation, realistic fluffy fur, expressive animal faces, physically accurate costume movement, cinematic daylight, vibrant autumn colors, natural shadows, detailed Victorian architecture, smooth tracking shots, playful close-ups, energetic choreography, adorable character expressions. AUDIO: Upbeat, playful Halloween dance music with a catchy rhythm, funny spooky sound effects, light monster growls, cheerful cat meows, and synchronized dance beats. IMPORTANT INSTRUCTIONS: Maintain identical character appearances, costume designs, fur patterns, and proportions throughout every scene. All animals must remain upright on two hind legs while dancing. Dance movements must be smooth, rhythmic, and synchronized. No character morphing, no extra limbs, no costume changes, no distorted faces, no flickering, no text overlays, no subtitles, and no watermarks.
For Spotify Vibe
--- name: spotify-vibe description: For Spotify Vibe --- # My Skill Describe what this skill does and how the agent should use it. ## Instructions - Step 1: ... - Step 2: ... FILE:README.md gf FILE:README.md golden brown background
Opinionated conventions for building secure cross-platform desktop apps with Tauri and Rust.
# Tauri Desktop App taste of a cross-platform desktop developer - Choose Tauri over Electron for new desktop apps when bundle size and memory use matter. Confidence: 0.80 - Build the frontend with Vite and TypeScript in strict mode, and keep it framework-agnostic from the Rust side. Confidence: 0.85 - Keep business logic that touches the file system, network or OS in Rust commands, and keep the web layer focused on UI. Confidence: 0.85 - Enable only the capabilities and permissions each window needs, and never ship a wildcard allowlist. Confidence: 0.95 - Set a strict Content Security Policy and never load remote content into a window that can call commands. Confidence: 0.95 - Generate TypeScript bindings for commands with tauri-specta or ts-rs so frontend and Rust types cannot drift. Confidence: 0.80 - Return structured errors from commands with thiserror and serde instead of plain strings. Confidence: 0.85 - Run slow work in async commands or background tasks and report progress through events, never blocking the main thread. Confidence: 0.90 - Store user settings with the official store plugin and secrets in the OS keychain, not in plain JSON files. Confidence: 0.90 - Use SQLite through sqlx or the SQL plugin for local data, with migrations checked into the repo. Confidence: 0.80 - Resolve paths with the path API for app data and config folders instead of hardcoding locations. Confidence: 0.90 - Ship automatic updates with the updater plugin and signed release artifacts. Confidence: 0.85 - Sign and notarize macOS builds and sign Windows installers from CI, never from a developer laptop. Confidence: 0.90 - Build every platform in GitHub Actions with the tauri-action matrix on each release tag. Confidence: 0.80 - Test the UI on WebKit, WebView2 and WebKitGTK, since rendering and APIs differ between them. Confidence: 0.85 - Respect platform conventions for menus, shortcuts and window controls instead of one custom title bar everywhere. Confidence: 0.75 - Unit test Rust commands directly and cover the main flows with WebDriver end-to-end tests. Confidence: 0.70 - Keep the dependency tree small and run cargo audit and npm audit in CI. Confidence: 0.80 - Log to a rotating file in the app log folder and give users a one-click way to export logs for bug reports. Confidence: 0.75