AINews Portal

📝 Prompt Library

Hand-picked prompts for real work, plus the full community collection — copy, fill the brackets, go.

JSON
Shell Script Safety Reviewer

--- 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:?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="${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="${1:-}"; [ -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:?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 -- "${WEB_ROOT:?}"/*> | ## 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 -- "${WEB_ROOT:?}"/* ``` ## 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="${1:?usage: deploy.sh <release-tag>}" BUILD_DIR="/srv/builds/${RELEASE}" WEB_ROOT="/srv/www" SLACK_TOKEN="${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 -- "${WEB_ROOT:?}"/* 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:?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 (\"${API_TOKEN:?}\"), 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 ${VAR:?} is fine", S + 'rm -rf -- "${BUILD_DIR:?}/"\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="${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())

CSV Diff and Reconciliation Checker

--- 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())

Meal Prep Storage and Use-By Planner

--- 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())

SvelteKit App taste

# 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

Serverless Functions taste

# Serverless Functions taste of a cloud backend engineer - Keep each function small and focused on one trigger, but group closely related HTTP routes in one handler to cut cold starts. Confidence: 0.80 - Initialize SDK clients and database pools outside the handler so warm invocations reuse them. Confidence: 0.95 - Use a connection pooler or an HTTP-based database driver, since thousands of concurrent functions will exhaust direct Postgres connections. Confidence: 0.90 - Set an explicit timeout and memory size per function instead of accepting platform defaults. Confidence: 0.85 - Keep bundles small with tree-shaking and lazy imports, and measure cold start time after every major dependency change. Confidence: 0.85 - Make every event handler idempotent, because queues and event sources deliver at least once. Confidence: 0.95 - Move slow or retryable work to a queue and return quickly from HTTP functions. Confidence: 0.90 - Send failed async events to a dead letter queue with an alert on its depth. Confidence: 0.90 - Define functions, permissions and triggers as code with SST, Serverless Framework, SAM or Terraform, never by clicking in the console. Confidence: 0.90 - Grant each function its own least-privilege role scoped to the exact resources it touches. Confidence: 0.95 - Load secrets from a secrets manager or the platform's encrypted env vars, never from the repo. Confidence: 0.95 - Write structured JSON logs with a request ID and correlation ID on every line. Confidence: 0.90 - Trace requests across functions and queues with OpenTelemetry or the platform's tracing. Confidence: 0.80 - Keep business logic in plain modules and keep the handler a thin adapter, so it can be unit tested without the cloud. Confidence: 0.90 - Run integration tests against a real deployed stage per pull request rather than trusting local emulators. Confidence: 0.80 - Set reserved or maximum concurrency on functions that call fragile downstream services. Confidence: 0.85 - Use provisioned concurrency only for latency-critical paths where cold starts were measured to hurt. Confidence: 0.75 - Prefer the arm64 runtime when dependencies support it for better price per performance. Confidence: 0.70 - Set billing alerts and per-function cost dashboards before launch, not after the first surprise invoice. Confidence: 0.85 - Avoid chaining functions that call each other synchronously; use events or a workflow engine like Step Functions instead. Confidence: 0.85 - Choose containers or long-running services for steady high-throughput workloads where serverless costs more. Confidence: 0.75

Realtime WebSocket taste

# Realtime WebSocket taste of a realtime systems engineer - Use plain WebSockets or a thin library like ws for simple cases, and reach for Socket.IO only when you need its fallbacks and rooms. Confidence: 0.75 - Prefer Server-Sent Events when updates only flow from server to client. Confidence: 0.85 - Authenticate during the handshake with a short-lived token, never by trusting a user ID sent in a later message. Confidence: 0.95 - Check the Origin header on upgrade requests to block cross-site WebSocket hijacking. Confidence: 0.90 - Define every message as a typed envelope with a type, a version and a payload, validated with a schema on both ends. Confidence: 0.90 - Send heartbeats with ping and pong and close connections that miss them, since proxies drop idle sockets silently. Confidence: 0.90 - Reconnect on the client with exponential backoff and jitter so a server restart does not cause a thundering herd. Confidence: 0.95 - Give each message a sequence number so clients can resume from the last one they saw after reconnecting. Confidence: 0.85 - Treat the socket as a notification channel and keep the database as the source of truth that clients can refetch. Confidence: 0.85 - Fan out across server instances with Redis pub/sub, NATS or a managed service instead of in-memory broadcast. Confidence: 0.90 - Keep connection servers stateless apart from the open sockets, so any instance can be drained and replaced. Confidence: 0.80 - Drain connections gracefully on deploy by telling clients to reconnect before shutting down. Confidence: 0.85 - Apply backpressure: watch the send buffer and drop or coalesce updates for slow clients instead of buffering forever. Confidence: 0.85 - Rate limit incoming messages per connection and cap message size. Confidence: 0.90 - Authorize every subscription to a room or channel on the server, not just the initial connection. Confidence: 0.95 - Batch or throttle high-frequency updates like cursor positions to a fixed tick rate. Confidence: 0.80 - Configure load balancer idle timeouts and sticky sessions deliberately when the transport needs them. Confidence: 0.80 - Track open connections, messages per second, reconnect rate and delivery latency as first-class metrics. Confidence: 0.85 - Use CRDTs like Yjs or Automerge for collaborative editing instead of inventing your own conflict resolution. Confidence: 0.80 - Load test with realistic connection counts using k6 or Artillery before launch. Confidence: 0.80

Halloween Horror Short: The Witch at the Bus Stop (Cinematic Video)

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.

The Breathing Jack-o'-Lantern: Halloween Horror Short (16:9 Video)

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.

Magical Halloween Neighborhood Street at Dusk (Cinematic Video)

A cinematic, magical Halloween neighborhood street decorated for a festive autumn celebration. Glowing carved Jack o lanterns line the pathways and steps of cozy suburban houses. Vibrant autumn leaves in shades of orange and gold are gently falling through the air. Twinkling purple and warm string lights hang from trees and porches, creating a magical evening glow. Close up shots transition smoothly to vintage lanterns, colorful bowls of candies, and floating soap bubbles drifting across the porch. Ultra-realistic, dreamy fairy-tale atmosphere, 4k resolution, smooth camera movement, magical lighting.

Mobile App

Study the attached sheet and provide me with a summary of it in one slide and action plan on the another slide, go and study it and compare it to aldrees I want a full presentation that shows SWOT analysis of us, propose a full action plan and compare the list of benefits of it. I need it a full study as

PLC Program Reader

I want you to act as a senior controls technician reading a PLC program I did not write. Platform: ${Platform and version:any IEC 61131-3 platform}. Language: ${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}

PLC Fault Tracer From an Operator Symptom

I want you to act as a controls troubleshooter. An operator reported this symptom: "${Operator symptom:Conveyor 3 starts, then drops out after a few seconds}". Platform: ${Platform and version}. I am pasting the source of the blocks, routines or POUs involved, the calling order from the main task, and the I/O list. Start from the output or fault that misbehaves, ${Tag that misbehaves:the motor run output}, and trace it backward across every block. Give a table: Hop | Block / routine / network / line | Exact condition for this hop | Tag I must watch online to confirm it Then: 1. List every write to each tag in the chain, in every block, including both branches of every IF and writes that happen after a block call. 2. Say which block calls which, and whether any call is conditional. 3. Rank your top 3 hypotheses for the symptom, each with one read-only check that would confirm or rule it out. 4. List what you cannot see from this code: HMI writes, network or comms partners, forces, hardware configuration, blocks I did not paste. Do not propose a fix yet. I will send back live values for your watch list first. === CODE (all blocks involved) === ${Paste block sources} === I/O LIST === ${Paste I/O list}

Ladder Start/Stop With Interlocks

I want you to act as a PLC programmer writing ladder logic for ${Platform and version}. Write a motor start/stop circuit for ${Motor name:Conveyor_M1}. Inputs, with what TRUE means: - ${Start tag:Start_PB} (normally open pushbutton, TRUE = pressed) - ${Stop tag:Stop_PB} (wired normally closed, TRUE = healthy, not pressed) - ${Overload tag:M1_OL_OK} (TRUE = overload healthy) - ${Permissive tags:Downstream_Running, Lube_Pressure_OK} (TRUE = OK to run) - ${Reset tag:Reset_PB} (normally open, TRUE = pressed) Outputs: ${Run output:M1_Run}, ${Fault tag: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.

FBD Analog Scaling With Wire-Break Detection

I want you to act as a controls engineer writing a Function Block Diagram (FBD) block for ${Platform and version}. It scales one analog input and detects bad signals. Signal: ${Instrument:tank level transmitter}, ${Signal type:4-20 mA}, engineering range ${Low range:0} to ${High range:5000} ${Units:mm}. Raw card range: ${Raw range: give the raw counts for 4 and 20 mA from your card manual, or write UNKNOWN}. Requirements: 1. Scale raw to engineering units with linear math or the platform's own scaling instruction. 2. Quality states: Good, Wire break (under about 3.6 mA equivalent), Over-range (over about 21 mA equivalent). Bad must stay TRUE for ${Debounce time:500 ms} before it latches, so one noisy sample does not trip it. 3. When Bad, hold the last good value and set a Value_Hold flag. Do not pass a scaled value from a broken wire. 4. Alarm_Bad latches and clears only on a Reset with the signal Good again. If the raw range is UNKNOWN, do not assume one. Show where it goes and list it as a question. Give: the FBD network by network as text (block, inputs, outputs, connections), the same logic as Structured Text so I can check it, your assumptions, and a fault-injection test table: Raw value written | Duration | Expected engineering value | Quality | Alarm_Bad | Value_Hold

Electrical Drawing to I/O List

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: ${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: ${Tag naming 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}

FAT Test Plan Writer

I want you to act as a commissioning engineer writing a Factory Acceptance Test (FAT) plan. Machine: ${Machine description: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}

AI-Generated PLC Code Reviewer

I want you to act as a strict reviewer of PLC code written by an AI, before it is downloaded to a controller. Platform: ${Platform and version}. Language: ${Language}. Below are my numbered requirements, then the code. Do a review in five passes and report each as a table. Do not rewrite the code. Pass 1, requirements: Requirement | Where it is met (block/line) | Met / Partly / Not met | Why Pass 2, instructions: every instruction or function used | Exists on my platform and version? (Yes / No / UNSURE) | Correct parameters and units? Pass 3, structure: timers or edge detectors called inside IFs or conditional branches, reused instances, outputs written in more than one place, latches with no reset. Pass 4, behaviour: what happens on first scan, power cycle, download, mode change, and when an input is stuck or a sensor fails. Pass 5, gaps: anything the code does that no requirement asked for, and requirements with no code at all. Finish with findings ranked by impact: Finding | Block/line | What goes wrong on the machine | Test that would prove it. === REQUIREMENTS === ${Paste numbered requirements} === CODE === ${Paste code}

Alarm Rationalization Assistant

I want you to act as an alarm management specialist. Machine or process: ${Machine or process}. Below is the current alarm list, plus alarm history if I have it. Rationalize it in the spirit of ISA-18.2 / IEC 62682. For each alarm give: Alarm | Keep as alarm / Make it a status message / Remove | Cause | Consequence if ignored | Operator action | Relative priority | Reset condition | Reason Rules: 1. If there is no operator action, it is not an alarm. 2. Use only equipment and causes in my list. Do not invent alarms. 3. Base priority on consequence and how much time the operator has to respond, and state the basis. Then list: - Duplicates: one root cause raising several alarms, with which one to keep. - Chattering or nuisance alarms from the history, with the fix (deadband, on-delay, latch, suppression by state). - Causes with no alarm at all. - For each alarm kept, a one-line alarm text in the pattern: where, what, consequence, action. === ALARM LIST === ${Paste alarm list} === ALARM HISTORY (optional) === ${Paste history or write NONE}

PLC Comment Translator

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 ${Source language:Italian}. Translate them to ${Target language: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 ${Max length: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}

PID Tuning Coach From a Trend

I want you to act as a process control tuning coach. Loop: ${Loop description:tank level controlled by an inlet valve}. Controller: ${PID instruction and platform}. Current settings: Gain ${Gain}, Integral ${Integral time or rate, with units}, Derivative ${Derivative}, loop update time ${Update time}, action ${Direct or reverse}. Below is the trend data after a setpoint step: time, setpoint, process value and output, as a table or CSV. Work through it in this order: 1. Check the data first: the units of the integral term on this instruction (time vs repeats), whether the output saturated at 0 or 100 %, and whether the step was big enough to read. If any of these makes the trend useless, say so and stop. 2. Read the response: dead time, rise time, overshoot %, settling time, steady-state error, oscillation period, and whether the output was noisy. 3. Name the most likely problem (too much gain, too much integral, dead time, valve stiction, saturation, wrong action) and the evidence for it in the data. 4. Recommend ONE parameter change, with the new value and what the next step test should show if you are right. Do not change more than one parameter at a time. If the evidence points to a mechanical or instrument problem instead of tuning, say that. === TREND DATA === ${Paste time, SP, PV, OUT}

Commissioning Checklist Builder

I want you to act as a commissioning lead. Equipment: ${Equipment description:a packaging line with 4 VFD conveyors, 2 pneumatic diverters and a PLC panel}. Below are the I/O list and the functional description. Build a commissioning checklist in the order the work is actually done: Step | Phase | Check | Method / tool | Expected result | Done by | Initials and date Phases, in order: 1. Before power: panel inspection, terminations, labels, grounding, network cabling. 2. Power-up: supplies, controller and I/O status, network communications, firmware and program version recorded. 3. Point-to-point I/O check: one row per I/O point from my list, input simulated or operated at the device, seen at the PLC; outputs forced and seen at the device. 4. Devices: motor rotation, VFD parameters recorded, valve and actuator travel and feedback, analog ranges checked against a reference. 5. Functional tests per the description, then fault and recovery tests. 6. Handover: backups, as-built markups, open punch list. Use only devices in my I/O list. Where the description is missing information I need for a check, write QUESTION. Also give the punch list template: Item | Location | Description | Severity | Owner | Target | Closed. === I/O LIST === ${Paste I/O list} === FUNCTIONAL DESCRIPTION === ${Paste description}

VFD Fault Code Explainer

I want you to act as an experienced drives technician. A variable frequency drive tripped. Drive: ${Drive make, model and firmware if known}. Fault code and text on the keypad: ${Fault code and text}. Motor: ${Motor nameplate: kW or HP, volts, FLA, rpm}. Load: ${Driven load:conveyor}. When it happens: ${When it trips:on start, at full speed, on stop, random}. 1. Explain what this fault means in everyday words and what the drive measured to trip it. If you do not know this exact code for this model, say so, give the most likely meaning based on the text, and tell me which manual section to confirm it in. Do not invent parameter numbers. 2. List likely causes ranked by how common they are for this load and this timing. 3. Give the checks in order, starting with the ones that need no tools or downtime (fault history, DC bus and output current at trip, recent parameter changes), then meter checks, then checks that need the motor uncoupled. For each: what to check, how, and what reading points to which cause. 4. List the drive parameters worth reading and recording (accel/decel time, current limit, motor data, carrier frequency, braking), described by function, not by a parameter number you are unsure of. 5. Say what NOT to do, for example resetting repeatedly without finding the cause. Give the checks as a table: Step | Check | How | Reading that points to a cause.

Act as a Beginner Sourdough Starter Coach

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: ${temperature:around 20 C during the day, cooler at night} - Flour I can buy: ${flour:supermarket all-purpose flour and a small bag of whole wheat flour} - Water: ${water:tap water, slightly chlorinated} - Equipment: ${equipment:a kitchen scale, a large glass jar with a loose lid, a spatula, and a regular home oven} - Daily schedule: ${schedule:I work from 9 to 6, I can feed it in the morning and the evening} - What I already tried: ${history:nothing yet, this is my first starter} - What I want to bake first: ${goal:a simple crusty loaf, later pancakes and pizza with the discard} - How often I want to bake: ${frequency:once a week on weekends} - Anything I want to avoid: ${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.

Salary Negotiation Prep Sheet Builder (JSON)

{ "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", "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.", "context": { "conversation_type": "${conversation_type:raise review with my current manager}", "current_or_offered_role": "${role:senior data analyst}", "company_type_and_size": "${company:a mid-size software company, about 400 people}", "location_and_work_mode": "${location:Lisbon, hybrid 3 days in the office}", "current_pay": "${current_pay:42000 base, no bonus}", "currency": "${currency:EUR}", "time_in_role": "${tenure:2 years and 4 months}", "achievements": "${achievements:built the churn dashboard used by sales leadership, automated the monthly reporting that took 3 days, trained two new analysts}", "scope_changes": "${scope:now own the pricing analytics area that a departed senior used to cover}", "market_data_i_have": "${market_data:a friend in a similar role earns more, nothing else yet}", "number_i_hope_for": "${hope:about 50000}", "other_options": "${options:no other offer, but recruiters contact me sometimes}", "known_constraints": "${constraints:annual review cycle in March, my manager said budgets are tight this year}", "meeting_date_and_format": "${meeting:in 3 weeks, 30 minutes, video call}", "my_style_and_worries": "${style:I get nervous and tend to accept the first answer}" }, "ask_first": [ "Is this a raise or promotion conversation with your current employer, or the salary step of one specific job offer, and who exactly will be in the conversation", "Your full current package: base pay, bonus, equity, benefits, and any recent increase, with dates", "Two to four concrete results from the last 12 months with numbers if possible (time saved, revenue, costs, quality, people helped)", "Any salary ranges you have seen: job posts with ranges, internal pay bands, salary surveys, or recruiter numbers, with the source and date", "The lowest outcome you would honestly accept, and what you would do if you get less (stay and wait, look elsewhere, accept a non-salary trade)" ], "rules": [ "Use only my inputs for facts. If something is missing, write 'unknown' in assumptions, add a question to open_questions, and never guess pay figures, bands, budgets, or what my manager or recruiter said.", "Do not state market salary ranges as facts. In market_data_plan, list the types of sources to check (job posts with published ranges in my location, reputable salary surveys, professional associations, recruiters, internal bands), what to record from each, and how to compare like for like on level, location, and work mode.", "Set three numbers from my inputs and the market plan: walk_away (the lowest I accept), target (what I realistically aim for), and ask (the first number I say, a reasonable step above target). If the market data is not yet checked, mark the numbers as provisional and say exactly what would change them.", "Build the value case from my real achievements and scope changes, written as short, specific statements with the business result first. Never exaggerate, take credit for others' work, or add achievements I did not mention.", "Scripts must be polite, confident, short, and in plain spoken language. No ultimatums, no fake competing offers, no threats to quit, and no personal hardship as the main argument.", "For every likely pushback (budget is tight, wait for the review cycle, you are already at the top of the band, we cannot go above the offer, what is your expectation), give a calm reply and a concrete next step, such as a timeline, a written commitment, or a non-salary trade.", "List non-salary items only if they are realistic for my situation, and rank them by value to me: for example title, scope, review date, bonus, equity, extra leave, remote days, learning budget, or signing bonus for offers.", "Include a silence and 'no' plan: how to pause, what to say, how to ask for the criteria and a date to revisit, and how to end the meeting on good terms.", "This is preparation and coaching, not legal or tax advice. Mention that pay transparency rules and contract terms vary by country and employer and should be confirmed officially when relevant.", "Output only valid JSON matching output_format. No markdown, no comments, no prose outside the JSON." ], "output_format": { "situation_summary": { "conversation_type": "raise | promotion | new_offer", "role": "string", "current_package": "string", "meeting": "string", "readiness": "ready | needs_data | needs_evidence | not_yet", "readiness_reasons": ["string"], "assumptions": ["string"], "open_questions": ["string"] }, "value_case": [ { "achievement": "string", "business_result": "string", "proof_to_bring": "string", "one_line_version": "string" } ], "market_data_plan": [ { "source_type": "string", "what_to_record": "string", "how_to_compare": "string", "status": "have | to_check" } ], "numbers": { "currency": "string", "walk_away": "number | null", "target": "number | null", "ask": "number | null", "provisional": "boolean", "reasoning": ["string"], "what_would_change_them": ["string"] }, "trade_options": [ { "item": "string", "value_to_me": "high | medium | low", "how_to_ask": "string", "realistic": "boolean" } ], "opening_script": { "first_sentence": "string", "value_points": ["string"], "the_ask": "string", "then": "string" }, "pushback_responses": [ { "they_say": "string", "i_reply": "string", "next_step_to_request": "string" } ], "silence_and_no_plan": { "if_silence": "string", "if_no": "string", "criteria_to_ask_for": ["string"], "revisit_date_request": "string", "closing_line": "string" }, "follow_up_email": { "send_when": "string", "subject": "string", "body": "string" }, "rehearsal_plan": [ { "day": "string", "practice": "string", "minutes": "number" } ], "do_and_dont": { "do": ["string"], "dont": ["string"] }, "disclaimers": ["string"] } }

Family Camping Weekend Plan Builder (YAML)

role: 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 adults task: > Turn my family, destination, dates, and gear into a complete family camping weekend plan as YAML: a trip summary with readiness and risks, a booking and permits checklist, a weather and safety plan, a shared and per-person packing list by category, a campsite setup plan, a simple meal plan with a cooler and food storage strategy, a kid-friendly schedule for each day with quiet times and backup rainy-day ideas, sleep and comfort tips for children, chores for every family member, a pack-up and leave-no-trace checklist, and a short post-trip review. inputs: family: ${family:two adults, kids aged 4 and 9, first time camping as a family} destination_type: ${destination:a staffed campground by a lake, about 2 hours by car, drive-in tent sites} dates_and_length: ${dates:Friday afternoon to Sunday noon in late May} expected_weather: ${weather:days around 20 C, nights around 8 C, chance of showers} site_facilities: ${facilities:toilets and showers nearby, drinking water tap, fire rings allowed if no ban} gear_we_own: ${gear:one 4-person tent, two adult sleeping bags, a small camping stove, a cooler, headlamps} gear_missing: ${missing:kids' sleeping bags and sleeping pads, a tarp, camp chairs} cooking_style: ${cooking:simple meals, some cooking on the stove, one campfire dinner} dietary_needs: ${diet:the 9-year-old is vegetarian, no allergies} activities_wanted: ${activities:short lakeside hike, swimming if warm, nature scavenger hunt, s'mores} special_needs: ${special:the 4-year-old still naps after lunch and wakes up at night sometimes} budget: ${budget:moderate, prefer borrowing or renting gear over buying} car_space: ${car:a mid-size hatchback with a roof box} ask_first: - The campground name or type, whether you have a reservation, and the check-in and check-out times - Each child's age, sleep habits, fears or comfort items, swimming ability, and anything that makes them anxious outdoors - Which gear you own, can borrow, or plan to rent, and how much space you have in the car - Any allergies, medical needs, or medicines anyone in the family needs to bring - Whether fires, alcohol, pets, or generators are allowed at your site, and if there are wildlife rules such as food lockers or bear-safe storage rules: - Never invent campground rules, fire bans, permit requirements, opening dates, or weather. Write them as items to confirm with the campground or the local park authority and flag them with confirm true. - Plan for safety first, then comfort, then fun. Include a check of the official weather forecast and fire danger before leaving, a basic first aid kit, sun protection, water safety rules for the kids, and a simple lost-child plan with a meeting point. - Keep children warm and dry at night. Insulation under the sleeping bag matters as much as the bag; give layers for nights at the expected low temperature and a dry change of clothes for each child. - Meals must be simple, safe, and kid-friendly. Plan the cooler so raw meat stays below ready-to-eat food, list what to cook first, keep perishables cold, and store all food and trash according to the site's wildlife rules. - Build each day around the youngest child's energy and nap times, with short activities, free play, and quiet time, and a rainy-day backup for every outdoor activity. - Give every family member, including the kids, age-appropriate jobs for setup, meals, and pack-up. - Follow leave-no-trace basics: stay on trails, use existing fire rings, burn only allowed wood and never trash, put fires out completely with water until cold, pack out all rubbish, and respect quiet hours and other campers. - Packing lists show who it is for, whether we own it, need to borrow or rent it, or must buy it, and mark essentials. - Output only valid YAML matching output_format. No prose outside the YAML. output_format: summary: trip: string nights: number readiness: ready | almost_ready | needs_work top_risks: - string assumptions: - string open_questions: - string booking_and_permits: - item: string status: done | to_do | to_confirm confirm: boolean notes: string weather_and_safety: forecast_checks: - when: string what_to_check: string first_aid_kit: - string water_safety_rules: - string lost_child_plan: meeting_point: string what_kids_should_do: - string fire_safety: - string wildlife_and_food_storage: - string packing_list: - category: shelter | sleep | kitchen | clothing | kids | hygiene | safety | fun | other items: - item: string for: string source: own | borrow | rent | buy essential: boolean campsite_setup: arrival_steps: - step: string who: string layout_tips: - string meal_plan: - day: string meal: breakfast | lunch | dinner | snack menu: string vegetarian_option: string cook_method: stove | campfire | no_cook prep_at_home: string cooler_plan: packing_order: - string keep_cold_tips: - string eat_first: - string daily_schedule: - day: string blocks: - time: string activity: string rainy_day_backup: string notes: string sleep_and_comfort: - child: string tips: - string family_jobs: - person: string jobs: - string pack_up_and_leave_no_trace: - string post_trip_review: questions: - string gear_to_add_next_time: - string

Photoreal Village Blacksmith Forging a Glowing Blade

A photorealistic photograph of a village blacksmith forging a glowing blade inside a rustic stone smithy, in a wide landscape 16:9 frame. A bearded man in his forties with tousled dark hair, a sooty dark long-sleeved work shirt with the sleeves pushed up, and a worn, scorched brown leather apron stands behind a heavy steel anvil in the center, his head bowed in concentration. With one hand he grips long iron tongs that hold a bright orange-hot blade flat on the anvil face; with the other he raises a short-handled forging hammer just above it, mid-strike. A burst of tiny orange sparks sprays from the blade and arcs out to the right and down over the anvil. On the left, a coal forge glows with yellow-orange flames and red embers under a black metal hood and chimney. Behind him, a rough whitewashed stone wall holds a wooden rack with a row of hanging hammers and long-handled tongs. On the right, a small multi-pane window lets in cool, soft grey-blue daylight that falls on the wall and the windowsill. Warm firelight from the forge and the hot metal lights his face, forearms, and apron, contrasting with the cool window light. Dust and soot in the air, gritty textures of leather, iron, and stone, shallow depth of field, cinematic documentary photography, natural colors. No text, no logos.

Macro Photo of a Moss Terrarium with a Tiny Cabin and Waterfall

A photorealistic macro photograph of a round glass bowl terrarium on a wooden desk, holding a tiny moss landscape with a miniature cabin and a waterfall, in a square 1:1 frame. The clear, open-topped glass bowl fills most of the frame and is covered with small condensation droplets on the inside. On the left, a tiny weathered wooden cabin with a steep shingled roof and a small brick chimney sits on a mossy rise; a single window glows warm amber as if a lamp is lit inside. From its door, a winding path of flat grey stepping stones curves down through the moss toward the front of the bowl. On the right, a thin silvery waterfall pours between jagged grey rocks into a small, still pond near the bottom center. Around everything grow plump cushions of bright green moss, delicate ferns, and small leafy plants, with dark soil and pebbles visible through the curved glass at the base. The bowl stands on a warm brown wooden desk with visible grain; the background is a soft blurred window with green foliage and daylight. Soft natural side light, shallow depth of field, crisp detail on the droplets and moss, cozy and magical miniature-world mood. No people, no text, no logos.

Loose Watercolor of Venetian Gondolas on the Grand Canal at Dawn

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.

Bamboo Forest Firefly Path Scene Concept Brief Builder

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: ${idea:a stone path between tall bamboo at dusk, with fireflies and a little wooden bridge} My starting choices: - Path: ${path:an old stepping-stone path running straight away from the camera toward the center of the frame} - Bamboo: ${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: ${water:a shallow stream trickling down the middle of the path over stones} - Focal point: ${focal:a small arched wooden footbridge with railings crossing in the middle distance} - Fireflies: ${fireflies:many small glowing yellow-green fireflies scattered through the air and the undergrowth} - Atmosphere: ${atmosphere:low mist hanging around the bridge and over the path} - Time and light: ${time:deep blue twilight just after sunset, a few clouds in the sky gap} - Visual style: ${style:photoreal night landscape photography, natural colors} - Mood words: ${mood: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.

Bamboo Forest Footpath with Fireflies and a Wooden Footbridge at Dusk

A quiet stepping-stone footpath runs straight ahead through a tall bamboo forest at dusk, lit by hundreds of glowing fireflies, toward a small arched wooden footbridge in the middle distance, seen from a low, centered viewpoint at the start of the path, in a wide landscape 16:9 frame. Large flat grey stepping stones lead from the bottom center of the frame toward the bridge, with a shallow stream trickling down the middle of the path over small stones, flowing out from under the bridge and catching faint blue light. Dense walls of tall, slender green bamboo with visible nodes rise on both sides and lean slightly inward, their leafy tops framing a narrow gap of deep blue twilight sky with soft clouds at the top center. The small wooden bridge has a gentle arch and simple railings, with a pale opening beneath it. Low white mist hangs around the bridge and drifts over the path and the undergrowth. Many tiny yellow-green fireflies glow scattered through the air, among the bamboo, and over the ferns, grasses, and mossy rocks along both edges of the path. Cool blue twilight ambience with warm green-yellow firefly accents, photoreal night landscape photography, natural colors, calm and magical mood. No people, no text, no logos.

Bamboo Forest Fireflies at Dusk (Image-to-Video Animation)

Start from the step 2 image "Bamboo Forest Footpath with Fireflies and a Wooden Footbridge at Dusk" as the exact first frame, and animate it into a calm ${duration:6}-second cinematic shot. Keep the stepping-stone path, the shallow stream running down its center, the small arched wooden footbridge in the middle distance, the tall bamboo walls on both sides, the gap of deep blue twilight sky, the mist around the bridge, and the composition exactly as in the image. The camera makes a very slow, smooth dolly forward along the path toward the bridge. The yellow-green fireflies drift gently through the air and blink softly on and off at different times. The bamboo stems and leaves sway slightly in a light breeze, all in the same direction. Low mist rolls slowly over the path and around the bridge, and the stream glints and ripples as it flows toward the camera. The cool blue twilight light stays steady. Natural, realistic motion, no cuts, no people, no animals, no new objects, no text. Soft sound of trickling water, rustling bamboo leaves, and evening insects.

Markdown Link Checker

--- 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: `![alt](img.png)`, `<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. ![](images/sync-diagram.png) ## 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)). ![Settings screen with the Team folder field highlighted](https://example.com/tidepool/img/settings.png) ## 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": "![logo](img/logo.png)\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": "![](shot.png)\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())

SQL Schema Design Reviewer

--- 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())

Time Zone Meeting Slot Finder

--- 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())

Authentication taste

# Authentication taste of a security-minded backend engineer - Use a well-maintained auth library or provider instead of hand-rolling login, sessions and token handling. Confidence: 0.95 - Hash passwords with Argon2id, or bcrypt with a cost of at least 12, and never with a plain fast hash like SHA-256. Confidence: 0.95 - Prefer server-side sessions in HttpOnly, Secure, SameSite=Lax cookies for first-party web apps over JWTs in localStorage. Confidence: 0.90 - Use short-lived access tokens with rotating refresh tokens when you truly need stateless tokens for APIs or mobile clients. Confidence: 0.85 - Detect refresh token reuse and revoke the whole token family when it happens. Confidence: 0.85 - Regenerate the session ID on login, logout and privilege changes to prevent session fixation. Confidence: 0.95 - Offer passkeys (WebAuthn) as a first-class sign-in method, not only as a second factor. Confidence: 0.80 - Support TOTP or passkeys for MFA and treat SMS codes as a last-resort fallback. Confidence: 0.85 - Rate limit login, signup and password reset per account and per IP, with exponential backoff instead of hard permanent lockouts. Confidence: 0.90 - Return the same generic message for unknown email and wrong password so accounts cannot be enumerated. Confidence: 0.90 - Make password reset and magic link tokens single-use, random, hashed at rest and expiring within 15 to 60 minutes. Confidence: 0.95 - Check passwords against known breached lists and drop arbitrary composition rules like "one symbol required". Confidence: 0.85 - Use OAuth 2.1 Authorization Code with PKCE for every client, and never the implicit flow. Confidence: 0.95 - Validate the state and nonce parameters on every OAuth and OIDC callback. Confidence: 0.95 - Link social logins to existing accounts only after the provider returns a verified email. Confidence: 0.90 - Keep authentication (who you are) separate from authorization (what you can do) and check permissions on the server for every request. Confidence: 0.95 - Re-ask for credentials or a passkey before sensitive actions like changing email, password or payout details. Confidence: 0.85 - Notify users by email on new device sign-ins, password changes and MFA changes. Confidence: 0.80 - Let users see and revoke their active sessions from account settings. Confidence: 0.80 - Log auth events with user ID, IP and user agent, but never log passwords, tokens or reset links. Confidence: 0.95 - Protect cookie-based state-changing requests with SameSite cookies plus CSRF tokens or Origin header checks. Confidence: 0.90 - Store signing keys in a secrets manager and support key rotation with a kid header from day one. Confidence: 0.85 - Write integration tests for the unhappy paths: expired tokens, revoked sessions, replayed links and wrong tenants. Confidence: 0.85

Rails App taste

# 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

Browser Extension taste

# Browser Extension taste of a cross-browser extension developer - Build on Manifest V3 and plan around service workers that can be stopped at any time. Confidence: 0.95 - Use a framework like WXT or Plasmo with TypeScript and Vite to handle builds, reloads and cross-browser manifests. Confidence: 0.80 - Request the fewest permissions possible, and prefer activeTab and optional_permissions over broad host access. Confidence: 0.95 - Never request the all_urls host permission unless the core feature truly needs every site. Confidence: 0.90 - Keep no important state in service worker globals and persist it with chrome.storage instead. Confidence: 0.95 - Use chrome.storage.session for temporary secrets and chrome.storage.local or sync for user settings, with a typed wrapper and defaults. Confidence: 0.85 - Use chrome.alarms for scheduled work instead of setInterval in the background. Confidence: 0.90 - Register all event listeners at the top level of the service worker so they survive restarts. Confidence: 0.90 - Keep content scripts small and inject them programmatically with chrome.scripting when possible. Confidence: 0.85 - Isolate injected UI in a Shadow DOM so page styles and extension styles cannot collide. Confidence: 0.90 - Treat every message from content scripts and web pages as untrusted and validate it with a schema. Confidence: 0.95 - Define message types in one shared module and use typed request and response helpers for runtime messaging. Confidence: 0.85 - Never inject remote code or use eval, and keep a strict Content Security Policy. Confidence: 0.95 - Use declarativeNetRequest for blocking or modifying requests instead of trying to intercept them in code. Confidence: 0.85 - Use the webextension-polyfill or a framework layer so the same code runs on Chrome, Firefox, Edge and Safari. Confidence: 0.80 - Collect as little user data as possible and explain clearly in the store listing what is stored and why. Confidence: 0.90 - Handle install and update events to migrate stored settings between versions. Confidence: 0.85 - Test popup and options pages as normal web UI, and run end-to-end tests with Playwright loading the unpacked extension. Confidence: 0.80 - Automate builds, version bumps and store uploads in CI, with one zip artifact per browser. Confidence: 0.80 - Keep the popup fast by loading heavy data lazily and showing cached state first. Confidence: 0.80 - Write a short permission justification for each permission before submitting to the store. Confidence: 0.85

Korean Slice-of-Life Comedy: A Woman and Her Tiger-Hat Cat (16:9 Video)

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.

Nostalgic Anime Development Board: Character Sheet + 8-Frame Storyboard (The Summer Detour)

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.

The Summer Detour: 15-Second Nostalgic Anime Countryside Short (16:9 Video)

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.

Claude prompt: 12-second SaaS product film in 16:9

Build a 12-second motion design product film in 16:9 (1920x1080, 30 fps) for my software product ${name}, which ${what_it_does_in_one_sentence}. Build it as one HTML page with a single animation timeline, so I can export it as an MP4. Look: light background, one accent colour ${hex}, one clean sans-serif font, soft shadows, lots of white space. Scene 1 (0 to 2.4 s): the headline "${headline}" builds word by word. Each word rises 18 px with a soft overshoot. Only the key word is in the accent colour. Scene 2 (2.4 to 6 s): a small product card appears. Data rows from different tools slide in one after another. Scene 3 (6 to 9.5 s): the rows merge into one clean table. One number counts up to ${result}. Scene 4 (9.5 to 12 s): end card with logo, name and the line "${call_to_action}". Rules: the camera never stands still and moves on one continuous path. One object carries the story from the first second to the end card. Every movement is eased, nothing moves linearly.

Claude prompt: 10-second app ad for Reels and TikTok (9:16)

Build a 10-second vertical motion design ad (1080x1920, 30 fps) for my app ${app_name}, which ${what_it_does_in_one_sentence}. Build it as one HTML page with a single animation timeline, so I can export it as an MP4. Look: very clean and friendly, bright background, rounded cards, one accent colour ${hex}, large type that is easy to read on a phone. Scene 1 (0 to 2.4 s): the hook "[HOOK, max 6 words]" builds word by word in the upper third. Each word rises with a soft overshoot. Scene 2 (2.4 to 5.5 s): a phone screen slides up. It shows the main step of the app: [MAIN STEP, e.g. a receipt is read and split into names]. Scene 3 (5.5 to 8 s): the result pops in as small cards, one after another: ${result}. Scene 4 (8 to 10 s): app icon, name and "${call_to_action}". Rules: keep all text inside the safe area (no text in the top 250 px or bottom 350 px). The camera keeps moving gently. One object carries the story from start to end.

Claude prompt: 3D film as one endless camera zoom (9:16)

Build a 13-second vertical 3D film (1080x1920, 30 fps) for ${brand}, which ${what_it_does_in_one_sentence}. Build it as one web page with real-time 3D and a single animation timeline, so I can export it as an MP4. Idea: one single camera flight with no cuts. Every world sits inside an object of the world before. World 1 (0 to 4 s): a desk in a warm retro look. The camera moves slowly towards a monitor. World 2 (4 to 8 s): the camera flies through the monitor screen and lands in a small room built from ${product_detail}. World 3 (8 to 11.5 s): the camera dives into one object in that room and finds the result: ${result}. End (11.5 to 13 s): the logo of ${brand} as a 3D object, with the line "${call_to_action}". Rules: the camera never stops and never cuts. Soft depth of field, light film grain. Simple, clear shapes that are easy to recognise.

Claude prompt: 14-second product film for an invoicing app (16:9)

Build a 14-second motion design product film in 16:9 (1920x1080, 30 fps) for my app ${name}, which ${what_it_does}. Build it as one HTML page with one animation timeline, so I can export it as an MP4. Look: light grey-green background with a soft grid, white cards with thin borders, one brand colour ${hex}, one clean sans-serif font. The text builds word by word: "${hook_question}" / "${name} ${step_1}, ${step_2}, and ${step_3} for you." / "${result}." Scene 1: the hook question, big and centred. Scenes 2 to 4: one card shows each step. The card from one step becomes the object of the next step. Scene 5: the result with a small "done" stamp. Scene 6: logo, name and "${tagline}". Rules: the camera never stands still. Every move is eased. Key words are in the brand colour, the rest stays dark.

Claude prompt: 11-second product film in a warm map style (16:9)

Build an 11-second motion design product film in 16:9 (1920x1080, 30 fps) for my product ${name}, which ${what_it_does}. Build it as one HTML page with HTML, CSS and inline SVG only (no photos), on one animation timeline, so I can export it as an MP4. Look: a warm map seen from above. Sand-coloured ground with soft contour lines, dark brown type, and ONE signal colour ${hex} for small things only. Text builds word by word: "${problem_in_one_sentence}" / "${name} ${what_it_does_in_one_sentence}." / "${claim}." Scene 1: low, angled view of the map while the problem appears. Scene 2: the camera rises to a top view in the same place. Areas light up one after another. Scene 3: one camera move to a second place. A dark panel shows the result: ${result}. Scene 4: one area turns into the logo. Name and claim appear. Rules: only one camera travel in the whole film. Words grow up from their baseline. Nothing moves linearly.

Claude prompt: 11-second premium product film for an AI tool (16:9)

Build an 11-second motion design product film in 16:9 (1920x1080, 30 fps) for my AI tool ${name}, which ${what_it_does}. Build it as one HTML page with one animation timeline, so I can export it as an MP4. Look: soft grey-green background, cream paper sheets, a dark green app panel, and gold as the only signal colour, used like a highlighter pen. Text builds word by word: "${problem}" / "${name} [does step 1], [step 2], and [step 3]." / "${claim}." Scene 1: a stack of paper sheets with the problem line on top. Scene 2: one line on a sheet gets marked in gold and lifts off as a strip. Scene 3: one camera move to the app panel. The strip lands there as a card. Scene 4: the card opens and shows the better version: ${result}. Scene 5: a gold marker stroke draws the logo. Name and claim appear. Rules: one object carries the story from start to end. Only one camera travel. Every move is eased.

modern acrylic signage

Act as an interior designer and visualization art director. Beside the door, put the logo in modern acrylic signage (transparent, and it has its own coloring). Do not change the picture; just put the signage in a modern way

Scientific Abstract and Brochure Builder with Reference Verification

Create a program for a performance called"", the purpose of which is to help create and verify scientific abstracts and manuals*input :product type> subject>brochure//number of tests:15-25-33-45-100//enter the design sample file*all M must be adhered to in the attached form colors font type that night //first reference //second reference///the first step of the tool performs a reference scan at the bottom of the page makes sure that the selected topic is part of the ideas of the reference//step the second took the main topic from the first reference and expanded through the second reference *with the attachment of all the attachments and illustrations related to the specific topic//reformulating the topic in a human format, not a tool, then the output in the form of a high-quality DVD and format

1Sentence Deck: pitch deck thesis interview

You are running the 1Sentence Deck method from 1sentencedeck.com. Follow it exactly. Interview me using the method’s own questions, one at a time. Wait for my answer before the next question. Never invent numbers or facts for me. The questions, in order: Why are you doing this? For who, who exactly has this problem and how many exist? Why aren’t existing solutions working? How are you doing it? How does it become a business, how do you reach customers and sell? Does your math work, what are the cost and revenue signals? If it’s working, where’s the evidence? Why you, when there are tons of alternatives and giant companies that could copy this? Can this become a huge outcome? Is this a good time? What do you need to get there? What’s your next milestone? From my answers, draft three deck thesis candidates. Each is one sentence in investor language, not customer language. Not what I sell. Why this becomes a huge company. Each must be repeatable (an investor could say it from memory tomorrow) and doubtable (specific enough to challenge). Tell me the tradeoffs and let me pick or edit one. Build the story. Write my answer to each question as one full sentence. These sentences are the headlines. Keep the question order as the default story order, and adjust only where my situation moves a doubt earlier, for example evidence early because nobody knows me. Then propose where each answer lands: its own slide, a shared slide, or a single line on another slide. You are shaping a story, not filling a template. Every question must be answered somewhere. Be honest about holes. If I gave you no real proof for a question, say so plainly and tell me to either get the proof or shrink the thesis. Do not decorate weak claims. Write my ask as one line: raising X to achieve Y in Z timeframe. Finish with the Repeat Test. Show me the cover first: the chosen thesis, stated as the biggest thing in the deck. Then the ordered headlines with where each lands. Then check: does reading only the headlines re-prove the cover? And what is the one sentence an investor repeats to their partner tomorrow? If it’s not the cover, tell me to rewrite, and say what’s competing with it.

My website

Act as an expert UI/UX designer and professional copywriter. I want to build a high-converting, professional website for my language school named WillSmart, located in Pansan City, Wa State (right in front of the Independence Monument / လွတ်လပ်ရေးကျောက်တိုင်အရှေ့). ​Important Requirement: Please write the website content using three languages side-by-side or section-by-section: Myanmar (မြန်မာ), Chinese (中文), and English. Every main section must include all three languages so that a diverse group of parents and students can easily read it. ​School Details to Include: ​Target Age: Starting from 3 years old and above (၃ နှစ်အရွယ်မှစတင်၍). ​Day Class (9:00 AM – 3:00 PM): ​Tuition only: 600 Yuan / month ​Tuition + Healthy Lunch included: 800 Yuan / month ​Evening Class: ​Tuition fee: 400 Yuan / month ​Please design the website sections (Hero, About Us, Location, Programs & Pricing, Why Choose Us, and Contact) clearly with clean formatting and translations in Myanmar, Chinese, and English."*

Community collection: awesome-chatgpt-prompts (CC0, refreshed hourly from GitHub).