A shot-by-shot 15-second, 16:9 hand-drawn anime prompt that animates an 8-frame storyboard: a girl cycles through summer rice fields past a wind chime to a riverbank, sips lemon soda and watches a local train cross a bridge before a rising aerial reveal, with timed shots, countryside-only sound design, energy arc and consistency rules. Trending on X, originally shared by @HeyAbhishek: https://x.com/HeyAbhishek/status/2115429393967550598
TITLE THE SUMMER DETOUR Create a 15-second cinematic nostalgic anime animation in 16:9 landscape format. REFERENCE Use the uploaded storyboard as the main visual reference. Follow its 8 shots in order. Keep the girl, bicycle, clothes, soda bottle, rice fields, river, train, and sunlight consistent throughout. Do not add characters, change locations unexpectedly, or introduce fantasy elements. SUBJECTS Girl: Original 13-year-old Japanese girl with shoulder-length dark brown hair, brown eyes, cream blouse, faded navy skirt, white socks, brown shoes, and canvas shoulder bag. Bicycle: Vintage mint-green city bicycle with a silver handlebar, brown saddle, front wire basket, and black tires. Props: One clear glass lemon soda bottle, initially in the basket and later held by the girl. Train: Small cream-and-orange two-car local passenger train. ENVIRONMENT Peaceful Japanese countryside in late summer. Narrow roads, bright green rice paddies, traditional wooden houses, hanging wind chimes, grassy riverbanks, distant railway bridge, green mountains, and large white summer clouds. Warm late-afternoon sunlight with gentle natural shadows. No sunset or dramatic lighting changes. STYLE Premium cinematic 2D anime with beautiful hand-painted backgrounds, soft cel shading, clean linework, natural colors, gentle environmental animation, and expressive but restrained character acting. Create a calm, nostalgic feeling of an ordinary childhood summer afternoon. Avoid photorealism, 3D CGI, neon lighting, excessive effects, and artificial camera movement. AUDIO No music or dialogue. Natural countryside ambience only: cicadas, birds, soft wind, bicycle wheels, wind chimes, flowing river water, soda bottle sounds, and a distant passing train. Keep the soundscape gentle, atmospheric, and continuous. CAMERA AND FRAMING 16:9 cinematic framing. Smooth tracking shots, intimate close-ups, slow camera movements, and beautiful landscape compositions. Use natural shot transitions and consistent sunlight. SHOT-BY-SHOT TIMELINE SHOT 1 (00:00-00:01.5) - THE SUMMER ROAD Wide cinematic shot of a peaceful countryside road surrounded by lush rice fields. The girl pedals her mint-green bicycle slowly toward the distant mountains. Her hair moves gently in the warm breeze. Camera tracks smoothly behind her. SFX: cicadas, gentle wind, bicycle tires. SHOT 2 (00:01.5-00:03) - LITTLE DETAILS Low close-up beside the bicycle. The wheels turn smoothly as sunlight flickers between the spokes. Tall grass and tiny wildflowers sway along the roadside. Camera follows the moving wheels with shallow depth of field. SFX: rolling tires, grass rustling, summer insects. SHOT 3 (00:03-00:05) - THE OPEN FIELDS Beautiful side-tracking shot of the girl cycling beside endless green rice paddies. Enormous fluffy white clouds drift slowly above the distant mountains. Her skirt and hair move naturally in the breeze. Bright sunlight fills the landscape. SFX: wind through rice fields, distant birds, bicycle chain. SHOT 4 (00:05-00:06.5) - THE WIND CHIME The girl cycles past a small traditional wooden house. Camera briefly follows a glass wind chime hanging beneath the roof as it sways gently. Sunlight reflects through the glass while the girl continues down the road in the background. SFX: delicate wind chime, cicadas, soft bicycle movement. SHOT 5 (00:06.5-00:08.5) - THE RIVERBANK Wide shot of a quiet grassy riverbank. The girl arrives, stops her bicycle beside a shady tree, lowers its kickstand, and sits down on the grass. Sunlight sparkles across the flowing river. Camera slowly moves closer. SFX: bicycle stopping, soft footsteps, flowing water. SHOT 6 (00:08.5-00:10) - A SUMMER SIP Intimate medium close-up. The girl takes the lemon soda bottle from her bicycle basket, opens it, and takes a refreshing sip while sitting in the grass. Tiny bubbles rise through the clear glass. She smiles peacefully. SFX: bottle opening, gentle fizz, summer breeze. SHOT 7 (00:10-00:12) - THE PASSING TRAIN Cinematic medium-wide shot from behind the girl's shoulder. A cream-and-orange local train travels slowly across the distant railway bridge above the river. The girl watches quietly as sunlight glows across the passing carriages. SFX: soft distant train wheels, river water, cicadas. SHOT 8 (00:12-00:15) - THE SUMMER MEMORY (SIGNATURE MOMENT) The camera slowly rises above the grassy riverbank into a magnificent aerial panorama. Reveal the winding blue river, endless green rice paddies, scattered countryside houses, distant mountains, and the small train continuing across the bridge. The girl remains a tiny peaceful figure beside her parked bicycle beneath the tree. The huge blue summer sky and beautiful white clouds dominate the final composition. Camera movement gradually slows and settles into a breathtaking wide shot. Hold until the end. SFX: warm countryside breeze, distant train fading away, gentle cicadas. ENERGY ARC Act 1 (0-5s): Freedom. Slow cycling, warm sunlight, peaceful summer landscapes. Act 2 (5-10s): Stillness. Wind chimes, riverbank, a quiet moment with cold soda. Act 3 (10-15s): Nostalgia. Passing train, expansive countryside reveal, and a peaceful ending. FINAL REQUIREMENTS Maintain consistent character proportions, outfit, bicycle design, train appearance, and geography. Use smooth hand-drawn anime motion, believable cycling physics, gentle wind animation, and natural water movement. No additional people, duplicated objects, character morphing, flickering, subtitles, logos, text overlays, or watermarks. The final video should feel like a beautiful, soothing memory of a summer afternoon.
A single-image anime pre-production board for an original 15-second short: a character design sheet (turnarounds, expressions, poses) of a girl and her mint-green bicycle plus an 8-frame cinematic storyboard of a summer ride through Japanese rice fields to a riverbank and a passing train, ready to feed into a video model. Trending on X, originally shared by @HeyAbhishek: https://x.com/HeyAbhishek/status/2115429393967550598
Create one vertical anime development board for an original 15-second short film titled "The Summer Detour". The output must be one combined image with 2 sections: Character design sheet Cinematic storyboard page STYLE: Beautiful nostalgic 2D anime, hand-painted countryside backgrounds, clean anime linework, soft cel shading, lush green scenery, warm summer afternoon sunlight, gentle shadows, and bright blue skies with fluffy clouds. Professional anime production board with red storyboard borders, blue camera arrows, small handwritten camera notes, and beautifully illustrated keyframes. SECTION A: CHARACTER DESIGN Main girl: An original 13-year-old Japanese girl with shoulder-length dark brown hair, soft brown eyes, and a gentle, cheerful expression. Outfit: cream short-sleeved blouse, faded navy skirt, white socks, brown shoes, and a small canvas shoulder bag. Personality: calm, curious, thoughtful, and quietly happy. Show front, side, three-quarter, and back views, plus smiling, thoughtful, relaxed, and surprised expressions. Include cycling, sitting, and looking toward the horizon poses. BICYCLE: Vintage mint-green city bicycle with a silver handlebar, brown saddle, small front wire basket, and simple black tires. A clear glass bottle of lemon soda sits securely inside the basket. Keep the bicycle's design consistent in every frame. SECTION B: STORYBOARD Create 8 storyboard frames in a clean grid, each using 16:9 cinematic framing. Keep the girl, bicycle, countryside, and lighting consistent. Story beats: Wide opening shot of the girl cycling along a peaceful countryside road surrounded by green rice fields. Beautiful close-up of bicycle wheels turning beside swaying grass, sunlight sparkling through the spokes. Side-tracking shot as she cycles past expansive rice paddies beneath enormous fluffy summer clouds. She passes a small traditional house with hanging wind chimes gently swaying in the breeze. She reaches a quiet grassy riverbank, stops, and parks her bicycle beside a tree. Sitting in the grass, she opens her cold lemon soda and takes a refreshing sip. A small cream-and-orange local train crosses a distant bridge over the river. She watches it with a gentle smile. Spectacular wide ending shot: the camera rises to reveal the winding river, endless green fields, distant mountains, passing train, and girl resting beside her bicycle under a magnificent summer sky. ENVIRONMENT: Peaceful rural Japanese countryside, vibrant rice paddies, narrow asphalt roads, traditional wooden houses, wind chimes, wildflowers, a clear river, an old railway bridge, distant green mountains, and beautiful summer clouds. Bright late-afternoon sunlight throughout. FINAL GOAL: Create a beautiful nostalgic anime film development board with soothing countryside scenery, authentic everyday moments, natural cinematic movement, consistent character design, and a breathtaking peaceful ending.
A five-scene, 16:9 ultra-realistic Korean slice-of-life comedy: a young woman with a black bob and her gray tabby cat in a tiger-striped hat, tied to a yellow balloon, share a playground mishap, a golden-hour courtyard moment and a funny dinner at home, with camera direction, mood and character-consistency rules. Trending on X, originally shared by @AizaAi12: https://x.com/AizaAi12/status/2115273090129400014
Create a cinematic, ultra-realistic Korean-style short video featuring a young East Asian woman with a short black bob haircut and bangs, wearing a dark brown hoodie and casual pants. The video combines adorable animal moments, unexpected comedy, and cozy everyday life, with realistic physics, expressive characters, and professional cinematic camera work. Scene 1 – A Funny Day at the Playground: In a modern residential apartment playground, a young woman stands beside a cute gray tabby cat wearing an adorable tiger-striped animal hat. A large yellow helium balloon floats above the cat, creating a funny and unexpected situation. The woman holds a smartphone near her ear while managing the situation. Capture the cat's curious expressions and the woman's natural reactions with soft daylight, realistic fur details, and a cinematic shallow depth of field. Scene 2 – The Unexpected Accident: The woman walks near the playground while the cat and its leash move around her feet. In a sudden, funny moment, she loses her balance and falls onto the soft sandy ground. The cat remains surprisingly calm and curious, creating a humorous contrast. Use dynamic camera angles, natural body movements, realistic physics, and smooth cinematic transitions. Scene 3 – The Cat's Adventure: Show the adorable tabby cat wearing its miniature tiger hat as it walks around the playground. The yellow balloon and leash create playful visual moments. Capture close-ups of the cat's face, whiskers, paws, and fluffy striped fur. Include wide shots of the playground, apartment buildings, benches, and surrounding greenery. Scene 4 – A Cozy Evening: Transition to a peaceful residential courtyard during the evening golden hour. Warm lights glow through the apartment windows as the camera captures a quiet outdoor moment with a small fluffy dog wearing a blue ribbon. Use soft background bokeh, natural environmental details, and a warm cinematic atmosphere. Scene 5 – A Funny Dinner at Home: Inside a cozy, warmly lit modern kitchen, the same young woman sits at a wooden dining table filled with Korean-style home-cooked dishes, rice bowls, and small plates of side dishes. She eats with chopsticks while the adorable miniature cat, still wearing its tiger-striped hat, sits beside her at the table. The cat makes cute, expressive movements and watches the food with curiosity. The woman suddenly notices something unexpected and turns toward the cat with a surprised, confused expression. End with a charming comedic moment that highlights their playful relationship. Visual Style: Ultra-realistic cinematic photography, Korean slice-of-life aesthetic, 4K resolution, natural skin texture, highly detailed animal fur, expressive facial reactions, realistic lighting, smooth camera movements, shallow depth of field, soft daylight, warm evening tones, and professional film-quality color grading. Camera Direction: A combination of medium shots, wide establishing shots, low-angle animal shots, detailed close-ups, gentle tracking movements, and smooth cinematic transitions. Mood: Cute, funny, heartwarming, slightly chaotic, and wholesome. Important Consistency Rules: Keep the woman's facial appearance, short black bob hairstyle, bangs, and brown outfit consistent throughout the video. Maintain the same gray tabby cat and its tiger-striped hat in every scene where it appears. Ensure realistic animal anatomy, natural movements, consistent character appearances, and believable interactions between the characters. Output: A high-quality cinematic short film in 16:9 aspect ratio, with realistic motion, detailed environments, expressive storytelling, and no artificial-looking animation.
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())Turn a short idea into an image-ready and motion-ready brief for one hero shot of a stepping-stone path through a tall bamboo forest at dusk with fireflies and a small wooden footbridge: path and stream, bamboo walls and canopy, bridge, fireflies, light and mist, fixed details, a motion map, and two prompt seeds: a 16:9 still image and an image-to-video animation.
Act as a cinematic landscape designer who writes image-ready and motion-ready scene briefs. Help me design one hero shot of a quiet footpath through a tall bamboo forest at dusk, lit by fireflies. The shot will first be generated as a still image and then animated into a short video that uses that image as its first frame. Your brief must make the still image rich in elements that can move naturally (fireflies drifting and blinking, bamboo stems and leaves swaying, low mist rolling over the ground, a shallow stream glinting, the last blue light of twilight) while keeping the composition calm, symmetrical, readable, and easy to animate. My starting idea: a stone path between tall bamboo at dusk, with fireflies and a little wooden bridge My starting choices: - Path: an old stepping-stone path running straight away from the camera toward the center of the frame - Bamboo: tall, dense green bamboo stems rising on both sides, their tops closing into a canopy with a gap of sky in the middle - Water: a shallow stream trickling down the middle of the path over stones - Focal point: a small arched wooden footbridge with railings crossing in the middle distance - Fireflies: many small glowing yellow-green fireflies scattered through the air and the undergrowth - Atmosphere: low mist hanging around the bridge and over the path - Time and light: deep blue twilight just after sunset, a few clouds in the sky gap - Visual style: photoreal night landscape photography, natural colors - Mood words: calm, magical, hushed, inviting First ask me, in one short message, for anything missing: the camera height (low near the stones, standing eye level, or slightly raised), how centered and symmetrical I want the path, how bright and how many fireflies, how much mist, whether the bridge should be close or far, and anything to avoid (people, animals, lanterns, buildings, text, logos, or copying a specific real photograph or a named landmark). Then produce the brief with these sections: 1. **Scene Overview**: two or three sentences on the place, the moment, the light, and the feeling. Describe an imagined bamboo forest in an East Asian spirit, not a copy of a specific photograph or a famous real grove. 2. **Path and Stream**: the shape and line of the stepping stones toward the vanishing point, the stone texture and moss, how the shallow stream runs down the path from under the bridge, and where the water catches light. 3. **Bamboo Walls and Canopy**: stem thickness, spacing, and nodes on each side, how they rise and lean inward, the leaf canopy, the gap of sky, and the ferns and grasses at their feet. 4. **The Footbridge**: size in the frame, shape of the arch, the wooden railings, the darker opening under it, and how mist gathers around it. 5. **Fireflies**: density, size, color, how they are spread through the air, the undergrowth, and the depth layers, and how their glow should read against the dark bamboo without looking like stars or bokeh dust. 6. **Light, Sky, and Mist**: the twilight sky gradient in the canopy gap, the cool blue ambient light, the warm-green firefly accents, the layers of mist, and how soft the shadows are. 7. **Fixed Visual Details**: a numbered list of 8 to 10 details that must stay identical in the still image and in the video (path line, stream position, bridge position and shape, bamboo walls on both sides, sky gap shape, mist zones, camera position, framing, overall firefly distribution). 8. **Motion Map**: a table of every element that can move in the video, with columns: element, type of motion, direction, speed (very slow, slow, medium), and how strongly it should move. Include the fireflies (drift and blink), the bamboo stems and leaves, the mist, the stream, the light, and the camera move. Keep total motion subtle and physically plausible, with one breeze direction. 9. **Prompt Seed A - Hero Still Image**: a ready-to-paste image prompt under 170 words for a wide landscape 16:9 frame, with the bamboo path, the fireflies, and the footbridge at dusk in the first sentence, ending with: no people, no text, no logos. 10. **Prompt Seed B - Image-to-Video Animation**: a ready-to-paste video prompt under 120 words that starts from the hero still as the first frame, describes the fireflies drifting and blinking, the bamboo swaying gently, the mist rolling low, the stream glinting, one very slow dolly forward along the path toward the bridge for about 6 seconds, and says to keep the path, bamboo, bridge, sky, and framing exactly as in the image with no new objects. Rules: no people, animals, lanterns, or buildings; no text, signs, or logos; no fast camera moves or cuts; and no motion that breaks physics (bamboo bends from the base and does not stretch, the bridge and stones never move, fireflies drift slowly and do not streak, the light does not jump). Use short paragraphs and lists.
Loose watercolor on textured paper: three covered black gondolas moored beside red-and-white striped mooring poles on the Grand Canal at dawn, a domed baroque basilica silhouette and hazy palazzos with a bell tower across the water, a soft peach and lavender sky, and broken violet and pink reflections in calm water.
A loose, atmospheric watercolor painting of covered gondolas moored on the Grand Canal in Venice at dawn, in a landscape 4:3 frame on textured cold-press watercolor paper. In the lower left, three long black gondolas rest side by side, wrapped in dark blue-grey covers, one with a pale tan tarp, the front gondola's curved prow and metal prow ornament rising at the far left edge. Tall wooden mooring poles with red-and-white spiral stripes and small pointed caps stand in the water across the middle and right of the frame, some linked to the gondolas by sagging ropes and chains. Across the canal, a hazy, softly painted skyline of old palazzos stretches to the right, with a slender bell tower, and on the left a large domed baroque basilica with smaller domes and lanterns, in the spirit of Santa Maria della Salute, silhouetted against the light. The sky glows in soft peach, apricot, and lavender washes with drifting clouds. The calm water reflects the poles, gondolas, and sky in broken vertical strokes of violet, pink, and white. Visible brushstrokes, wet-in-wet blooms, granulating pigments, soft edges, and white paper showing through. Peaceful, romantic early-morning mood. No people, no text, no signature.
Turn your family, campground, dates, and gear into a YAML camping weekend plan: booking and rules to confirm, weather, water, fire, and lost-child safety, a packing list by category with own, borrow, rent, or buy, campsite setup, kid-friendly meals and a cooler plan, a daily schedule around naps with rainy-day backups, sleep tips, family jobs, leave-no-trace pack-up, and a post-trip review.
1role: Friendly, safety-minded family camping planner who helps parents organize a relaxed campground weekend with children, from booking and packing to meals, sleep, activities, and leaving no trace, so the trip is fun for the kids and manageable for the adults2task: >3 Turn my family, destination, dates, and gear into a complete family camping weekend plan as YAML:4 a trip summary with readiness and risks, a booking and permits checklist, a weather and safety plan,5 a shared and per-person packing list by category, a campsite setup plan, a simple meal plan with a6 cooler and food storage strategy, a kid-friendly schedule for each day with quiet times and backup7 rainy-day ideas, sleep and comfort tips for children, chores for every family member, a pack-up and8 leave-no-trace checklist, and a short post-trip review.910inputs:...+113 more lines
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
Build a healthy sourdough starter from flour and water and bake your first loaf: what a starter is, a beginner glossary, a 14-day setup table in grams, a feeding routine that fits your workday, how to read readiness signs and smells, a troubleshooting table, mold and safety rules, a fridge and travel plan, a first-loaf walkthrough, discard ideas, and a 3-week plan.
Act as a Beginner Sourdough Starter Coach. You help complete beginners create a healthy sourdough starter from just flour and water, keep it alive, and bake a first simple loaf, using the kitchen, flour, and schedule they actually have. You think like a patient home baker who teaches small community baking classes: you explain what is happening inside the jar in plain language, you tie every instruction to my room temperature, flour, and daily routine, you give weights in grams and simple ratios, and you are honest that the first one to two weeks are often messy and slow. You never promise a fixed day when the starter will be "ready"; you teach me to read the signs instead. My situation: - Kitchen temperature: around 20 C during the day, cooler at night - Flour I can buy: supermarket all-purpose flour and a small bag of whole wheat flour - Water: tap water, slightly chlorinated - Equipment: a kitchen scale, a large glass jar with a loose lid, a spatula, and a regular home oven - Daily schedule: I work from 9 to 6, I can feed it in the morning and the evening - What I already tried: nothing yet, this is my first starter - What I want to bake first: a simple crusty loaf, later pancakes and pizza with the discard - How often I want to bake: once a week on weekends - Anything I want to avoid: wasting a lot of flour, complicated schedules First, in one short message, ask me for anything important that is missing: whether my kitchen gets much warmer or colder at night, whether I have a warm spot (oven with only the light on, top of the fridge), whether I have a covered pot or baking stone for the oven, whether anyone in the household has a gluten or wheat allergy, and whether I will be away for any days in the next two weeks. Then guide me through these sections: 1. **What a Starter Really Is**: a short, friendly explanation of wild yeast and lactic acid bacteria, why flour and water are enough, why the first days can bubble and then go quiet, and what "active" and "mature" mean. 2. **Starter Words You Will Meet**: a short glossary with term and plain meaning: starter, levain, discard, feeding, ratio (for example 1:1:1), hydration, peak, collapse, hooch, float test, bulk fermentation, proofing, and baker's percentage. 3. **Day-by-Day Setup Plan**: a table for the first 14 days with columns: day, what to do, amounts in grams, what I will probably see, and what is normal versus worth adjusting. Start with whole wheat or a whole wheat and all-purpose mix, then move to my main flour, and keep total flour small so I waste little. 4. **Feeding Routine for My Schedule**: a morning and evening routine that fits my workday, how to discard and feed in under five minutes, which ratio to use at my temperature, and how to shift the ratio if it peaks too early or too late. 5. **Reading the Signs**: how to tell it is ready to bake with: it roughly doubles within a predictable window after feeding, domed top, many bubbles, pleasant tangy smell, and passing (or not needing) the float test. Explain what each smell means: yeasty, yogurt, vinegar, nail polish, and when it simply needs feeding. 6. **Troubleshooting Table**: columns: problem, likely cause, what to do. Cover no bubbles after 3 days, big rise on day 2 and then nothing, liquid on top, sluggish rise, too sour, runny texture, fruit flies, and a starter that smells strongly of acetone. 7. **Mold and Safety**: how to tell harmless hooch or dried crust from mold, why pink, orange, or fuzzy spots mean throwing it out and starting again, how to keep the jar clean, and basic kitchen hygiene. Never suggest scraping off mold and continuing. 8. **Fridge and Break Plan**: how to move to weekly fridge feedings once it is strong, how to wake it up two days before baking day, and how to keep it alive if I travel for one or two weeks (thicker starter, dried flakes as a backup). 9. **First Loaf Walkthrough**: a simple same-day or overnight plan for one loaf with weights for flour, water, starter, and salt, a timeline that fits my weekend, stretch-and-folds instead of kneading, how to judge bulk fermentation by the dough and not only the clock, shaping basics, and baking in my oven. If I have no covered pot, give a steam alternative. 10. **Using the Discard**: three easy recipes or ideas (pancakes, crackers, pizza dough boost) with rough amounts, and how long discard keeps in the fridge. 11. **A 3-Week Learning Plan**: week 1 build the starter, week 2 strengthen it and bake a first test loaf, week 3 bake a second loaf and change one thing on purpose, each with a goal and a short note to write in a baking log. Rules: use grams and simple ratios, with cups only as a rough extra; never present a day or hour as a guarantee, always link timing to my temperature and the signs; do not recommend adding commercial yeast to "fix" a starter unless I ask; never advise eating anything that shows mold; flag that sourdough is not gluten-free and not safe for people with celiac disease or a wheat allergy; use plain language, short paragraphs, and tables where they help. End with exactly what I should do today to start, with amounts.
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
For techs and students writing or reviewing a basic motor circuit, who want it stop-dominant, latched correctly and delivered with a test table.
I want you to act as a PLC programmer writing ladder logic for Platform and version. Write a motor start/stop circuit for Conveyor_M1. Inputs, with what TRUE means: - Start_PB (normally open pushbutton, TRUE = pressed) - Stop_PB (wired normally closed, TRUE = healthy, not pressed) - M1_OL_OK (TRUE = overload healthy) - Downstream_Running, Lube_Pressure_OK (TRUE = OK to run) - Reset_PB (normally open, TRUE = pressed) Outputs: M1_Run, M1_Fault. Requirements: 1. Stop-dominant seal-in: Stop, overload trip or loss of any permissive drops the motor, even with Start held. 2. Overload trip latches M1_Fault. Reset clears it only on a rising edge of Reset and only when the overload is healthy again. 3. Start does nothing while the fault is latched or a permissive is missing. No restart on its own when a permissive returns. 4. Each output is written in exactly one rung. Use only instructions that exist on my platform. If you are unsure one exists, mark it UNSURE. Give: the rungs as text in execution order with a comment per rung, your assumptions, then a test table: ID | Precondition | Action | Expected (tags and values) Include Start and Stop pressed together, Stop while running, overload trip and Reset while still tripped, and a permissive dropping and returning.
For maintenance and controls techs inheriting a machine program, who need an explanation they can check line by line before changing anything.
I want you to act as a senior controls technician reading a PLC program I did not write. Platform: any IEC 61131-3 platform. Language: Ladder. Below is the code as text, followed by the I/O list or tag comments if I have them. Explain it rung by rung (or network by network) in a table: Rung | Condition in everyday words | What it does | Scan-order or latch notes | Assumptions Rules: 1. A normally open or normally closed contact in the program examines a bit. It says nothing about how the field device is wired. Take field wiring only from my I/O list; if it is not there, write UNKNOWN. 2. Rungs execute top to bottom and the last write in the scan wins. List every tag written in more than one place, with the rung numbers. 3. For every latch or set coil, name the unlatch or reset that clears it, or write NONE. 4. For every one-shot or edge contact, name its storage bit and say if that bit is used anywhere else. 5. If something depends on code or settings I did not paste (another routine, the HMI, a comms partner), write QUESTION instead of guessing. Do not suggest changes. This is a read, not a review. === CODE === Paste the rungs as text === I/O LIST / TAG COMMENTS === Paste the I/O list or write NONE
A 30-second, 16:9 photoreal Halloween horror short in five timed beats: on a rain-soaked porch a man notices his jack-o'-lantern breathing, finds human eyes inside it by phone flashlight, watches it turn toward him and sees a tiny handprint appear before a cut to black, with production, sound-design and negative-prompt notes. Trending on X, originally shared by @nawalsehar: https://x.com/nawalsehar/status/2115022630004851185
30 seconds | 16:9 | 4K Ultra HD | 30 FPS | Ultra-photorealistic live-action Halloween horror short. A terrifying Halloween night unfolds on a rain-soaked wooden porch, where a real adult man discovers that his carved jack-o'-lantern is behaving strangely. With realistic human actors, practical effects, cinematic lighting, and authentic horror atmosphere, an ordinary Halloween decoration becomes something truly sinister. 0–5 seconds: A rain-soaked wooden porch glistens under dim nighttime lighting. A flickering jack-o'-lantern sits beside the front door. A man in a dark flannel shirt and jeans enters carrying Halloween decorations. Suddenly, he notices the pumpkin slowly expanding and contracting as if it were breathing. 5–12 seconds: The camera slowly pushes toward the pumpkin. Its jagged carved smile subtly widens before returning to normal. The internal candle flickers in sync with the movement. The man kneels cautiously as a faint, wet breathing sound emerges from inside. 12–21 seconds: The pumpkin's light abruptly goes out. Complete silence. The man switches on his phone flashlight, revealing two realistic human eyes deep inside the dark pumpkin, staring directly at him. Terrified, he recoils and drops his phone onto the wet wooden floor, leaving its flashlight beam tilted toward the pumpkin. 21–27 seconds: The man slowly reaches toward the pumpkin again. Before he can touch it, the jack-o'-lantern rotates several inches toward him on its own. Its carved smile remains perfectly still. Frozen with disbelief, he slowly backs toward the door. 27–30 seconds: Extreme close-up of the pumpkin. Its internal light suddenly switches back on. A tiny, fresh handprint gradually appears in condensation across its outer surface. Before the viewer can understand what has happened, the screen cuts instantly to black. Production: Photorealistic live-action visuals, real human actors, consistent character identity, practical pumpkin movement, realistic condensation, natural rain, believable shadows, subtle handheld cinematography, shallow depth of field, cinematic horror lighting, and restrained color grading. Sound design includes rainfall, distant wind, wooden footsteps, soft breathing, subtle fabric movement, a low suspenseful musical rise, and sudden silence before the final reveal. Negative prompt: No animation, cartoon visuals, CGI-looking characters, artificial environments, changing faces, inconsistent clothing, distorted anatomy, unrealistic pumpkin movement, exaggerated jump scares, gore, dialogue, narration, subtitles, text, logos, watermarks, black bars, or abrupt transitions before the final cut.
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 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.