db-defaults-taudelta
Skill0 starsChanged 49 days agoMITView on GitHub
Use when adding local data persistence to a Python project — caching, relational storage (simple, complex/OLTP, or analytical/OLAP), document storage, full-text search, vector/embedding search, background job queues, cron/scheduled tasks, pub/sub/event streaming, graph data, blob storage, or time-series data — and no explicit \"evaluate the best DB for this\" request was made. Gives the default technology per category, chosen for permissive licensing, pytest-local testability with no docker/cloud, and low-boilerplate LLM-agent-friendly APIs, so picks stay consistent across projects instead of drifting session to session.
- Installs packages
In this repository16 other files it gives its agents.
- AGENTS.mdAGENTS.md
- invoke-task-conventions-taudeltaSkill
- mcp-python-conventions-taudeltaSkill
- mcp-server-shippingSkill
- plan-conveyorSkill
- polite-mcp-conventions-taudeltaSkill
- python-conventions-taudeltaSkill
- python-refactor-auditSkill
- python-testing-conventions-taudeltaSkill
- release-heraldSkill
- repo-pitchSkill
- research-librarySkill
- session-bash-auditSkill
- session-harvestSkill
- skill-authoring-taudeltaSkill
- skill-fitnessSkill
The file
Show its rules and text
We split this skill into the rules it gives an agent
13 rules. About 0 are common, 0 are distinctive, and 13 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈0
Distinctive · estimate
This author's own choices. Worth a look.
13
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Very specific: line 26
Security is deliberately not a selection factor: Every default here is chosen for local, personal-scale use — nothing on this page should …
- Very specific: line 33
Default: plain stdlib (threading.Lock, time.monotonic, a dict) — no library
- Very specific: line 42
Default: diskcache (Apache-2.0, pure Python)
- Very specific: line 50
Default: stdlib sqlite3, raw SQL
- Very specific: line 57
Default: sqlalchemy + alembic (both MIT)
- Very specific: line 99
Default: duckdb (MIT — IP held by a nonprofit foundation specifically to keep it MIT "in perpetuity")
- Very specific: line 110
Default: tinydb (MIT, pure Python)
- Very specific: line 121
Default: SQLite FTS5 virtual tables — ships with stdlib sqlite3, zero new dependency, built-in bm25() ranking
- Very specific: line 156
Default: apscheduler 3.x (MIT)
- Very specific: line 172
Default: blinker (MIT) — in-process signal/observer dispatch (signal.connect(receiver), signal.send(sender))
- Very specific: line 185
Default: ladybug (MIT, pip install ladybug) — embedded, Cypher query language, no server
- Very specific: line 200
Default: plain pathlib.Path file writes — no library
- Very specific: line 209
Default: duckdb (MIT) — same tool as the Analytical/OLAP category above
≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
Default storage tech per use case
Personal, Python-first, local-first projects. Applies when starting a storage/caching need without an explicit request to analyze alternatives — pick from this table, don't re-litigate from scratch each session. Deviating is fine when a category genuinely doesn't fit or scale requirements outgrow the default (see each category's "Escalate to" line) — the point is to stop a fresh session/model from silently picking something different for no reason, not to forbid judgment calls.
Selection criteria for every entry below: MIT/Apache-2.0/BSD-style permissive license only;
popular and actively maintained (verified via real GitHub/PyPI activity, not vibes); testable fully
inside a plain pytest run — in-memory or tmp_path-scoped, no Docker/cloud account/CI services;
low-boilerplate API a coding agent can use correctly without much ceremony or indirection. Picks
favor the best-fit tool per concern over minimizing the number of technologies in a project — don't
force a consolidation the categories below don't call for.
Cash in the testability criterion: don't mock these. The whole point of picking a store the
suite can start and stop is that tests run the real one, so a double here throws away what the
choice bought. python-conventions-taudelta, "Don't double anything the suite can run for real",
states the rule and the boundary.
Security is deliberately not a selection factor. Every default here is chosen for local, personal-scale use — nothing on this page should be treated as a production/multi-tenant/internet- facing recommendation. Each category's "Escalate to" line is the pick for that situation instead.
In-process ephemeral state
- Snippet:
references/snippets/in-process-state.py - Default: plain stdlib (
threading.Lock,time.monotonic, a dict) — no library - Why: no persistence needed; a library would be pure overhead for "hold one number, guarded by one lock"
- Escalate to: n/a — once it needs to survive a restart, it's the Cache or Relational category instead
Cache (TTL / eviction)
- Snippet:
references/snippets/cache.py - Default:
diskcache(Apache-2.0, pure Python) - Why: disk-backed, handles TTL/eviction so you don't hand-roll it; trivial pytest
tmp_pathfixture - Escalate to: Redis — once multiple processes/machines need to share one cache
Relational — simple (few tables, KV-shaped)
- Snippet:
references/snippets/relational-simple.py - Default: stdlib
sqlite3, raw SQL - Why: zero dependency, no ORM ceremony for a shape this small;
:memory:ortmp_pathin pytest - Escalate to: Postgres
Relational — complex / OLTP (many tables, real joins, migrations, frequent writes)
- Snippet:
references/snippets/relational-oltp.py - Default:
sqlalchemy+alembic(both MIT) - Why: real Engine/Session/declarative-model ceremony, but it buys migration and relationship safety that nothing lighter offers for a genuinely transactional multi-table shape
- Define the column vocabulary once, via the declarative base's
type_annotation_mapfor plain types andAnnotatedaliases for the ones needing per-column arguments. EveryMapped[Decimal]in every model then resolves the same way, so no column opts out by being written from memory in another file. This is also the honest answer to "why the ORM rather than Core" — Core gives you the same SQL with the vocabulary spelled out at each column instead. - Escalate to: Postgres
- The SQLite tier silently corrupts
Decimaland dropstzinfo, and both are invisible on the Postgres you escalate to — which matters here specifically, because "testable fully inside a plainpytestrun" is what puts SQLite under every project following this entry. Measured against SQLAlchemy 2.0.52:Numericround-trips aDecimalthrough a float, with no warning:1234567890123456789.000000001comes back1234567890123456768.0000000000. The mechanism is in the library —engine/default.pysetssupports_native_decimal = Falseand the SQLite dialect does not override it, sosqltypes.pytakes the branch commented "DBAPI returns floats, convert", quantized to scale 10. ATypeDecoratorstoring the exact string is the fix.- A timezone-aware datetime comes back naive. Not a SQLite limitation:
dialects/sqlite/base.py'sDATETIME.bind_processornever readstzinfoat all. - The trap is how they hide. A first probe of the
Decimalcase passes on a ten-digit value, which survives a float intact — so a project whose fixtures use realistic amounts concludesNumericis fine and ships. Probe with a value wide enough to fail.
- Pass
sqlite_strict=Truein__table_args__so the test dialect stops being laxer than the Postgres it stands in for — the failure mode a test-only dialect invites. It is not a free extra keyword, and the snippet shows the whole shape: STRICT accepts onlyINT/INTEGER/REAL/TEXT/BLOB/ANY, socreate_allfails outright onNUMERIC(38, 10), onDATETIME, and on theVARCHAR(64)that a plainString(64)renders (all three measured on 2.0.52).String(64).with_variant(Text(), "sqlite")keeps the real bound on Postgres, and theDecimal/datetimeTypeDecorators above are what make the other two columns legal. Adopting STRICT and closing the two traps are therefore the same piece of work, which is the argument for doing both at once rather than neither. - Alembic's SQLite batch mode is a data-integrity footgun. On SQLite,
batch_alter_tableimplements a change by recreating the table, which drops that table's triggers — and Alembic has no trigger awareness anywhere (a grep for "trigger" across the package returns one unrelated comment). It also skips aCheckConstraintthat is both reflected and unnamed, perbatch.py's ownTODO. Two mitigations, and either is valid: name every constraint, or decide that the SQLite tier never migrates at all and is always created from current metadata.
Analytical / OLAP (read/aggregate-heavy queries over structured data)
- Snippet:
references/snippets/analytical-olap.py - Default:
duckdb(MIT — IP held by a nonprofit foundation specifically to keep it MIT "in perpetuity") - Why:
duckdb.connect(":memory:")gives native SQL over a plain connection, zero ORM ceremony — much lower-boilerplate than SQLAlchemy for this shape, and genuinely fast at joins/aggregation. Single-writer (fine for personal-scale local use); a multi-writer "Quack" protocol shipped May 2026 but is still new - Escalate to: Postgres+DuckDB extension, or MotherDuck
Document store (schemaless / semi-structured JSON)
- Snippet:
references/snippets/document-store.py - Default:
tinydb(MIT, pure Python) - Why: real dict-like API (
db.insert({...}),db.search(Query().field == x)) — no SQL anywhere;MemoryStorage()ortmp_pathfor pytest. Maintainer calls it "maintenance mode" (feature-complete and stable) — still gets bugfix releases, not abandoned - Alternative:
sqlitedict(Apache-2.0) for pure key→object storage with no field queries needed — flag its multi-year commit gap before reaching for it - Escalate to: MongoDB
Full-text search
- Snippet:
references/snippets/full-text-search.py - Default: SQLite
FTS5virtual tables — ships with stdlibsqlite3, zero new dependency, built-inbm25()ranking - Alternative:
bm25s(MIT) for a standalone RAG-style scorer decoupled from a datastore;tantivy(MIT, Python bindings to the Rusttantivysearch engine —pip install tantivy, nottantivy-py, which is a different, stale package) for heavier Lucene-like search once FTS5's feature set is genuinely too thin - Avoid:
Whoosh— long-unmaintained - Escalate to: Elasticsearch or Meilisearch (both need a running server, so are excluded as local defaults)
Vector / embedding similarity search
- Snippet:
references/snippets/vector-search.py - Default:
qdrant-client(Apache-2.0) - Why:
QdrantClient(":memory:")orQdrantClient(path=tmp_path)needs no server at all, and escalating later is a literal constructor-arg swap (url=.../cloud creds) — same class, same methods, verified against Qdrant's own docs. Handles vectors + metadata + persistence in one API - Alternative:
chromadb(Apache-2.0) — far bigger community/mindshare, arguably an even simpler first-touch API (add()/query()); reasonable pick if onboarding ease matters more than a verified no-rewrite escalation path - Escalate to: Qdrant Cloud (client code unchanged) or Pinecone/Weaviate
Background job / message queue
- Snippet:
references/snippets/job-queue.py - Default:
huey(MIT) - Why:
SqliteHuey(..., immediate=True)runs@huey.task()functions synchronously in-process — no consumer subprocess, no orchestration, a plain pytest test just calls the function directly. Small decorator-based API, 15-year track record, near-zero open-issue backlog - Escalate to: Celery/RQ/Dramatiq — all need a real broker (Redis/RabbitMQ), which is the point once you need multiple worker processes/machines
Cron / scheduled recurring tasks
- Snippet:
references/snippets/cron-scheduler.py - Default:
apscheduler3.x (MIT) - Why:
SQLAlchemyJobStorepointed at asqlite:///URL persists schedules across restarts, no external broker, usable purely as a queue if scheduling isn't even needed. Genuine bus-factor risk to know about, not a reason to avoid it outright: single dominant maintainer (1,134 commits vs. next-highest contributor's 6), ~2-year-old unreviewed PRs, and v4 has been in alpha since 2020 with no stable release — 3.x is still the only practical choice and remains actively patched (releases as recently as Jun 2026) - Alternative:
schedule(MIT) — zero-dependency, trivial in-process API, but no persistence (a restart loses all schedules) and itself hasn't been pushed since May 2024; a lightweight escape hatch for pure in-process cases, not a governance-driven replacement - Escalate to: a managed scheduler (e.g. cloud cron) once recurring jobs need to survive beyond one machine
Pub/sub / event streaming
- Snippet:
references/snippets/pubsub.py - Default:
blinker(MIT) — in-process signal/observer dispatch (signal.connect(receiver),signal.send(sender)) - Why: the same library Flask itself uses for signals; zero ceremony, fans out to multiple subscribers in-process, no server, trivially testable
- Escalate to: NATS (Apache-2.0) — genuinely different tool, not a bigger version of this one: needs
a separately-provisioned
nats-serverprocess, and no official/maintained Python pytest-fixture package exists for local testing (the two community attempts are both abandoned), so it doesn't qualify as a local default here even though it's a strong product once you're actually running a server
Graph data
- Snippet:
references/snippets/graph-data.py - Default:
ladybug(MIT,pip install ladybug) — embedded, Cypher query language, no server - Why: this is the actively-maintained continuation of Kuzu, forked by the community 3 days after Kuzu's Oct 2025 archival (Apple acquisition) — same underlying engine, new stewardship, not a rewrite. Daily commits, MIT license, named active maintainers. Still under a year old as a project name, so treat as promising-but-young rather than as battle-tested as, say, SQLite
- Lightweight alternative: for relationship modeling that doesn't need real graph-traversal
performance or a query language, a plain
edges(src, dst, relation)table in your relational store is still simpler — reach forladybugspecifically when you need actual graph algorithms/ traversal at more than toy scale. Pair either withnetworkx(BSD-3-Clause) for in-memory graph algorithms over already-loaded data - Escalate to: Neo4j Aura, Amazon Neptune
Blob / object storage
- Snippet:
references/snippets/blob-storage.py - Default: plain
pathlib.Pathfile writes — no library - Why: zero dependency, trivial
tmp_pathtesting; adding an abstraction for cloud storage you don't have a concrete plan for yet is exactly the case YAGNI is for - Escalate to:
fsspec(BSD-3-Clause) +s3fs/gcsfsonce cloud storage is an actual plan, not a maybe — samefs.open()/fs.ls()calls, only the protocol string changes
Time-series data
- Snippet:
references/snippets/time-series.py - Default:
duckdb(MIT) — same tool as the Analytical/OLAP category above - Why: DuckDB's window functions,
time_bucket, gap-filling, and ASOF joins are genuinely strong for time-series analysis and meaningfully cut boilerplate versus hand-rolling the same queries in raw SQL — the better tool for this concern specifically, independent of whether a project also needs it for OLAP work - Lightweight alternative: a plain SQLite table with a timestamp-indexed column, if the need is truly just "store timestamped rows and filter by range," with no resampling/windowing
- Escalate to: InfluxDB, TimescaleDB
Editing this skill
This file is copied into ~/.agents/skills/db-defaults-taudelta at install time, never symlinked,
so editing the deployed copy is local drift — the exact thing this skill exists to prevent, and
it reaches no other machine. Edit the source in the repo this was installed from, push, and re-run
skills add <that source> --global --skill db-defaults-taudelta to refresh every project's copy. If
you installed it from someone else's repo rather than your own fork, the source is theirs: open an
issue or a pull request there instead.
Starter snippets
references/snippets/ has one real, ruff-clean, self-contained Python file per category above —
each a verified pip install command in its docstring plus a working test_* function showing the
pytest-local pattern, directly copy-pasteable rather than a tutorial to adapt. Each category's
"Snippet:" line above links straight to its own file, so there's no need to open the others. This is
also where two real naming traps caught while writing them are documented at the point they matter:
full-text-search.py (the tantivy vs tantivy-py PyPI mixup) and cron-scheduler.py (the
nonexistent SQLiteJobStore class name).
Full rationale
See references/rationale.md — the GitHub/PyPI evidence, the options
that were considered and rejected per category, and the reasoning behind every branch/alternative
above.
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

