DEV Community: Dusty Mumphrey

Building a Multi-Generation Pedigree Tree in PostgreSQL

Dusty Mumphrey — Fri, 03 Apr 2026 12:45:00 +0000

You have a record that points to two parent records. Each parent points to two more. You need to walk the tree up to N generations and return the full ancestor graph.

If you have worked with org charts, bill-of-materials structures, file systems, or category trees, you have seen one version of this problem. Pedigrees are the version where both parents matter, the graph doubles in width at every generation, and incorrect data has real consequences for the people who depend on it.

I built ReptiDex, a mobile app that tracks lineage for animal breeders. Parent-offspring linking and multi-generation pedigree trees are in production, tracking real animals for real breeders. 50 paid subscribers and 200 animals tracked within 9 days of launch. This article covers the actual data model and the actual queries. Not a toy example.

Here is what we will cover: the adjacency list model with self-referential foreign keys, recursive CTEs for ancestor and descendant traversal, sibling classification, the denormalization decision for caching pedigree trees, performance indexing, and why the closure table was not the right call for this use case.

This article does not cover coefficient of inbreeding (COI) calculation in depth. But it does explain why traversal depth is the prerequisite for COI, so you understand what this foundation enables.

The Data Model

The schema is an adjacency list applied to a binary parent graph. An animals table with sire_id and dam_id columns that reference the same table's primary key.

CREATE TABLE animals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  species VARCHAR(50) NOT NULL,
  sex VARCHAR(10),
  date_of_birth DATE,
  sire_id UUID REFERENCES animals(id),
  dam_id UUID REFERENCES animals(id),
  created_at TIMESTAMPTZ DEFAULT now()
);

Self-referential foreign keys enforce integrity at the database level. A sire_id that points to a nonexistent animal gets rejected by Postgres before any application code runs. This is not optional for lineage data. If the database allows orphan references, every pedigree you generate and every breeding decision you make from that data inherits the same uncertainty.

Both foreign keys are nullable. Not every animal has known parents. An animal imported from another breeding program might have documented grandparents but an unrecorded dam. The schema has to accommodate incomplete records gracefully. The pedigree tree simply terminates at that node rather than breaking the query.

If you are evaluating alternative models, here is why adjacency list wins for this shape of data. Nested sets do not work because the tree is not a strict hierarchy. An animal can be the sire of offspring across many different dams, producing multiple subtrees that share a root. Materialized paths get unwieldy when the tree doubles in width every generation. A 10-generation pedigree path would be a 1,023-element string. Closure tables are worth a real comparison and I will address them later in this article.

Walking Up the Tree: Ancestor Traversal

The core query. Given one animal, return every ancestor up to N generations.

WITH RECURSIVE pedigree AS (
  -- Base case: the animal we start from
  SELECT id, name, sire_id, dam_id, 0 AS generation
  FROM animals
  WHERE id = :animal_id

  UNION ALL

  -- Recursive case: walk up to both parents
  SELECT a.id, a.name, a.sire_id, a.dam_id, p.generation + 1
  FROM animals a
  JOIN pedigree p ON a.id = p.sire_id OR a.id = p.dam_id
  WHERE p.generation < :max_generations
)
SELECT * FROM pedigree;

The base case selects the starting animal at generation 0. The recursive term joins back on the CTE, matching any animal whose ID appears as a sire_id or dam_id of an already-found record. The generation counter increments on each recursive step. The WHERE clause on generation depth is the recursion guard.

Result shape

For a 4-generation pedigree, the base case returns 1 row (the animal itself). Generation 1 returns up to 2 rows (sire and dam). Generation 2 returns up to 4 (grandparents). Generation 3 returns up to 8 (great-grandparents). Generation 4 returns up to 16 (great-great-grandparents). A complete 4-generation tree is 31 rows maximum. This is a binary tree that doubles at each level: 2^(n+1) - 1 total nodes for n generations.

The OR in the join matters

a.id = p.sire_id OR a.id = p.dam_id means the same ancestor can appear multiple times if it shows up on both the sire side and the dam side of the pedigree. In animal breeding, this is not a bug. It is the definition of inbreeding, and detecting these duplicate appearances is the entire foundation of COI calculation.

Do not DISTINCT them away. The duplicates are the signal. If animal X appears as both the paternal great-grandfather and the maternal great-great-grandfather, that is a meaningful data point. Wright's coefficient of inbreeding depends on counting exactly these shared paths through common ancestors.

Adding context to each ancestor

In practice, you want more than just the ancestor's name. You want to know which side of the pedigree they fall on and their relationship to the starting animal.

WITH RECURSIVE pedigree AS (
  SELECT
    id, name, sire_id, dam_id,
    0 AS generation,
    'self' AS relationship,
    ARRAY[id] AS path
  FROM animals
  WHERE id = :animal_id

  UNION ALL

  SELECT
    a.id, a.name, a.sire_id, a.dam_id,
    p.generation + 1,
    CASE
      WHEN a.id = p.sire_id THEN 'sire'
      WHEN a.id = p.dam_id THEN 'dam'
    END,
    p.path || a.id
  FROM animals a
  JOIN pedigree p ON a.id = p.sire_id OR a.id = p.dam_id
  WHERE p.generation < :max_generations
)
SELECT * FROM pedigree WHERE generation > 0;

The relationship column tells you whether each ancestor was reached through the sire or dam link at each step. The path array records the full traversal path from the starting animal to this ancestor, which becomes useful for COI path coefficient calculation. You can reconstruct exactly how the query walked from the subject animal to any given ancestor, and whether that path went through the sire side or the dam side at each branch.

Walking Down the Tree: Descendant Traversal

The reverse direction. Given one animal, find all recorded offspring, and their offspring, down N generations.

WITH RECURSIVE descendants AS (
  SELECT id, name, sire_id, dam_id, 0 AS generation
  FROM animals
  WHERE id = :animal_id

  UNION ALL

  SELECT a.id, a.name, a.sire_id, a.dam_id, d.generation + 1
  FROM animals a
  JOIN descendants d ON a.sire_id = d.id OR a.dam_id = d.id
  WHERE d.generation < :max_generations
)
SELECT * FROM descendants WHERE generation > 0;

The join direction is flipped from the ancestor query. For ancestors, you match a parent's id against the current row's sire_id/dam_id. For descendants, you match a child's sire_id/dam_id against the current row's id.

This query answers questions that matter in practice. A breeder wants to see every animal produced from a specific founding sire across all pairings. A registry wants to audit how many registered offspring trace back to a particular animal. A health study needs to track outcomes across all descendants of an animal with a known genetic condition. In each case, the starting point is one animal and the question is "what came after it."

Siblings and Half-Siblings

A practical query that is harder than it looks. Full siblings share both sire and dam. Paternal half-siblings share only the sire. Maternal half-siblings share only the dam.

SELECT
  a.id,
  a.name,
  a.date_of_birth,
  CASE
    WHEN a.sire_id = :sire_id AND a.dam_id = :dam_id THEN 'full'
    WHEN a.sire_id = :sire_id THEN 'paternal_half'
    WHEN a.dam_id = :dam_id THEN 'maternal_half'
  END AS sibling_type
FROM animals a
WHERE a.id != :animal_id
  AND (a.sire_id = :sire_id OR a.dam_id = :dam_id)
  AND a.sire_id IS NOT NULL
  AND a.dam_id IS NOT NULL
ORDER BY sibling_type, a.date_of_birth;

The CASE expression classifies all three types in a single pass. The IS NOT NULL guards prevent animals with unknown parents from being falsely classified as siblings through a NULL match.

This query matters for breeding programs because half-sibling data informs pairing decisions. If a prospective dam has 12 paternal half-siblings already in the program, the breeder may want to diversify. If two animals share a sire that is known to carry a recessive health condition, the breeder needs to know before pairing them.

The Denormalization Decision

The recursive CTE runs on every request if you query it live. For a 4-generation tree, that is manageable. The query touches at most 31 rows. For deeper trees, higher traffic, or mobile clients where you want snappy page loads, you hit a decision point.

Option 1: Cache the tree as JSON

When a new animal is created or parents are assigned, traverse the tree once and store the result as a JSON column on the animal record.

async def build_pedigree_tree(animal_id, db, depth=1, max_depth=4):
    if depth > max_depth:
        return None

    animal = await db.get(animal_id)
    if animal is None:
        return None

    return {
        "id": str(animal.id),
        "name": animal.name,
        "sire": await build_pedigree_tree(
            animal.sire_id, db, depth + 1, max_depth
        ),
        "dam": await build_pedigree_tree(
            animal.dam_id, db, depth + 1, max_depth
        ),
    }

The max_depth parameter does double duty as a recursion guard and a circular reference safeguard. The resulting JSON is a nested tree that maps directly to what a pedigree UI component needs to render.

Add a pedigree_cache JSONB column to the animals table. When a hatchling is created, compute the tree and store it. Subsequent reads are a single-row lookup. No recursion at read time.

ALTER TABLE animals ADD COLUMN pedigree_cache JSONB;

The tradeoff is staleness. Cached trees go stale when parent records are updated retroactively. If someone corrects a sire assignment on an animal that has 50 descendants, all 50 cached trees are now wrong. Two approaches to handle this:

Eager invalidation: When a parent assignment changes, mark all descendants' caches as stale. A background job rebuilds them. This is correct but can cascade. Correcting one sire assignment on a prolific animal can trigger hundreds of rebuilds.

-- Flag stale caches for all descendants of the updated animal
WITH RECURSIVE desc AS (
  SELECT id FROM animals WHERE sire_id = :updated_id OR dam_id = :updated_id
  UNION ALL
  SELECT a.id FROM animals a JOIN desc d ON a.sire_id = d.id OR a.dam_id = d.id
)
UPDATE animals SET pedigree_cache = NULL WHERE id IN (SELECT id FROM desc);

Lazy rebuild: Add a pedigree_stale BOOLEAN DEFAULT false flag. Set it on invalidation. Rebuild on next read if stale. This spreads the compute cost across reads rather than doing it all at write time. The first viewer after a correction waits for the rebuild. Everyone after that gets the cached version.

ReptiDex uses the eager approach. In a mobile app serving individual animal profiles, eliminating recursive queries from the hot path is worth the write-time cost. Pedigree corrections are rare. Profile views are constant.

Option 2: Application-level cache with TTL

A Redis or in-memory cache keyed by animal ID. The tree is computed on first request and served from cache until expiration. No schema change needed.

This works well when reads vastly outnumber writes, which is true for pedigrees. Animals are created once. Pedigrees are viewed many times. A 5-minute TTL means the worst case is a 5-minute delay before a correction is visible, which is acceptable for most use cases.

The downside is that you are still running the recursive CTE on every cache miss. If you have a large number of animals with infrequent access, the cache hit rate may be low and you are not saving much. The JSON column approach guarantees one computation per record regardless of access patterns.

Adjacency List vs. Closure Table

If you have researched hierarchical data in Postgres, you have seen closure tables recommended. Here is the honest comparison for the pedigree use case.

What a closure table is

A separate table stores every ancestor-descendant pair with a depth value:

CREATE TABLE pedigree_closure (
  ancestor_id UUID REFERENCES animals(id),
  descendant_id UUID REFERENCES animals(id),
  depth INTEGER NOT NULL,
  PRIMARY KEY (ancestor_id, descendant_id)
);

Querying "all ancestors of X" becomes a flat SELECT * FROM pedigree_closure WHERE descendant_id = :animal_id. No recursion needed at read time. Querying "all descendants of X" is equally flat. Depth filtering is a WHERE clause.

Why it was not the right call here

Write amplification. When a new animal is created with two known parents, the closure table needs an entry for every ancestor of both parents, plus the new animal's direct relationships. For a 10-generation pedigree with a complete tree (1,023 ancestors), creating one new animal inserts over 1,000 rows into the closure table. In a mobile app where breeders log hatchlings in batches of 8 to 12 animals at once, the write cost multiplies fast.

Corrections are expensive. When a breeder corrects a parent assignment, the closure table needs a cascading delete-and-rebuild for every descendant of the affected animal. With the adjacency list, you update one foreign key. The recursive CTE recalculates the tree correctly at read time with no additional maintenance.

-- Adjacency list: correcting a sire is one UPDATE
UPDATE animals SET sire_id = :correct_sire_id WHERE id = :animal_id;

-- Closure table: correcting a sire requires deleting and rebuilding
-- all closure rows for the animal AND every descendant
-- This is a multi-step operation that can affect thousands of rows

Bounded depth. The pedigree is a binary tree with a known maximum useful depth. COI calculations rarely need more than 10 generations. The tree is bounded, the read cost is predictable, and depth limiting in the CTE is trivial. The closure table's read advantage is most compelling for unbounded or very deep hierarchies (corporate org charts with hundreds of levels, deeply nested category systems). For a binary tree with a practical ceiling of 10 levels, the adjacency list with a recursive CTE and proper indexing performs well.

Duplicate ancestor detection. For COI calculation, you need to know when the same ancestor appears on both sides of the pedigree. With a recursive CTE, duplicates appear naturally in the result set. With a closure table, you have flat ancestor-descendant pairs. Reconstructing the path through which each ancestor was reached requires additional columns or a separate path table, adding complexity that the CTE gives you for free.

When to choose a closure table

If your read-to-write ratio is extreme, your tree is deep or unbounded, and corrections are rare, the closure table is a better fit. Read-heavy reference data with infrequent updates is the sweet spot. Pedigrees have frequent writes (new animals, batch hatchling creation), corrections that need to be cheap, and bounded depth. The adjacency list is less operational burden for this shape of problem.

Performance: Indexes and Query Planning

The indexes that matter for pedigree traversal:

CREATE INDEX ix_animals_sire ON animals (sire_id);
CREATE INDEX ix_animals_dam ON animals (dam_id);

Without these, every recursive step does a sequential scan on the animals table. With them, each join in the recursive term uses an index lookup. The difference is negligible at 100 animals and significant at 10,000.

If you are running in a multi-tenant environment (which registries are), composite indexes scoped to the tenant:

CREATE INDEX ix_animals_org_sire ON animals (org_id, sire_id);
CREATE INDEX ix_animals_org_dam ON animals (org_id, dam_id);
CREATE INDEX ix_animals_org_id ON animals (org_id, id);

The leading org_id column means the index is only scanning rows within the tenant's data. Without it, the query walks the entire table's index and filters after the fact.

Reading the query plan

Run EXPLAIN ANALYZE on your recursive CTE and look for these signals:

Healthy: Nested loop joins using index scans on each recursive step. Each step looks up a small number of rows by primary key or foreign key. The total execution time scales linearly with the number of ancestors found.

Unhealthy: Hash joins or sequential scans in the recursive term. This usually means missing indexes on sire_id and dam_id. Add the indexes above and re-run.

Recursion safety

Postgres does not have a built-in recursion limit for CTEs (unlike some databases). The WHERE p.generation < :max_generations clause is your protection. Without it, a circular reference would run until the query is cancelled or memory is exhausted.

Circular references should be impossible if your foreign key constraints are correct. An animal cannot be its own ancestor if you enforce that sire_id != id AND dam_id != id. But data migrations and bulk imports can introduce inconsistencies that the schema does not catch. The depth limit is a safety net.

-- Add a constraint to prevent self-referencing
ALTER TABLE animals ADD CONSTRAINT no_self_parent
  CHECK (sire_id != id AND dam_id != id);

This catches the obvious case. It does not catch indirect cycles (A is sire of B, B is sire of A). For that, the depth limit in the CTE is your defense. In practice, indirect cycles in animal pedigree data are extremely rare because they require physically impossible ancestry. But if you are importing historical data from spreadsheets, trust the depth limit more than you trust the source data.

What This Foundation Enables

Everything above is the prerequisite for the calculation breeders actually care about: the coefficient of inbreeding.

Wright's coefficient of inbreeding requires identifying common ancestors on both the sire and dam sides of a pedigree, then calculating path coefficients through those shared ancestors. The formula walks every path from the sire side to a common ancestor and back down the dam side, summing the contributions.

You cannot do this without a traversable multi-generation tree. The duplicate appearances that show up when the same ancestor appears on both sides of the pedigree are the signal. The traversal depth determines how many generations of common ancestors you can detect. A 3-generation pedigree misses shared great-great-grandparents. A 10-generation pedigree catches relationships that are invisible at shallower depths.

Most platforms that call themselves registries never get to COI because they never build the traversal infrastructure. They store parents. Maybe grandparents. But they do not build a graph you can walk programmatically at arbitrary depth. Everything in this article is building that foundation.

If your platform can run the ancestor query from Section 2 at 10 generations of depth and identify duplicate ancestor appearances across the sire and dam subtrees, you have everything you need to implement Wright's coefficient. That is a separate article. But the foundation is this one.

Putting It Together: A Complete Example

Here is a minimal, runnable example. Create the table, insert a 3-generation pedigree, and run both the ancestor and descendant queries.

-- Schema
CREATE TABLE animals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  species VARCHAR(50) NOT NULL DEFAULT 'gecko',
  sex VARCHAR(10),
  sire_id UUID REFERENCES animals(id),
  dam_id UUID REFERENCES animals(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  CONSTRAINT no_self_parent CHECK (sire_id != id AND dam_id != id)
);

CREATE INDEX ix_animals_sire ON animals (sire_id);
CREATE INDEX ix_animals_dam ON animals (dam_id);

-- Seed data: 3-generation pedigree
-- Great-grandparents (generation 3)
INSERT INTO animals (id, name, sex) VALUES
  ('00000000-0000-0000-0000-000000000001', 'Atlas', 'male'),
  ('00000000-0000-0000-0000-000000000002', 'Ivy', 'female'),
  ('00000000-0000-0000-0000-000000000003', 'Titan', 'male'),
  ('00000000-0000-0000-0000-000000000004', 'Maple', 'female'),
  ('00000000-0000-0000-0000-000000000005', 'Blaze', 'male'),
  ('00000000-0000-0000-0000-000000000006', 'Pearl', 'female'),
  ('00000000-0000-0000-0000-000000000007', 'Onyx', 'male'),
  ('00000000-0000-0000-0000-000000000008', 'Ruby', 'female');

-- Grandparents (generation 2)
INSERT INTO animals (id, name, sex, sire_id, dam_id) VALUES
  ('00000000-0000-0000-0000-000000000009', 'Jasper', 'male',
    '00000000-0000-0000-0000-000000000001',
    '00000000-0000-0000-0000-000000000002'),
  ('00000000-0000-0000-0000-000000000010', 'Luna', 'female',
    '00000000-0000-0000-0000-000000000003',
    '00000000-0000-0000-0000-000000000004'),
  ('00000000-0000-0000-0000-000000000011', 'Rex', 'male',
    '00000000-0000-0000-0000-000000000005',
    '00000000-0000-0000-0000-000000000006'),
  ('00000000-0000-0000-0000-000000000012', 'Hazel', 'female',
    '00000000-0000-0000-0000-000000000007',
    '00000000-0000-0000-0000-000000000008');

-- Parents (generation 1)
INSERT INTO animals (id, name, sex, sire_id, dam_id) VALUES
  ('00000000-0000-0000-0000-000000000013', 'Duke', 'male',
    '00000000-0000-0000-0000-000000000009',
    '00000000-0000-0000-0000-000000000010'),
  ('00000000-0000-0000-0000-000000000014', 'Fern', 'female',
    '00000000-0000-0000-0000-000000000011',
    '00000000-0000-0000-0000-000000000012');

-- Subject animal (generation 0)
INSERT INTO animals (id, name, sex, sire_id, dam_id) VALUES
  ('00000000-0000-0000-0000-000000000015', 'Nova', 'female',
    '00000000-0000-0000-0000-000000000013',
    '00000000-0000-0000-0000-000000000014');

-- Query 1: Ancestor traversal (3 generations from Nova)
WITH RECURSIVE pedigree AS (
  SELECT id, name, sire_id, dam_id, 0 AS generation
  FROM animals WHERE id = '00000000-0000-0000-0000-000000000015'
  UNION ALL
  SELECT a.id, a.name, a.sire_id, a.dam_id, p.generation + 1
  FROM animals a
  JOIN pedigree p ON a.id = p.sire_id OR a.id = p.dam_id
  WHERE p.generation < 3
)
SELECT generation, name FROM pedigree ORDER BY generation, name;

-- Query 2: Descendant traversal (all descendants of Atlas)
WITH RECURSIVE descendants AS (
  SELECT id, name, sire_id, dam_id, 0 AS generation
  FROM animals WHERE id = '00000000-0000-0000-0000-000000000001'
  UNION ALL
  SELECT a.id, a.name, a.sire_id, a.dam_id, d.generation + 1
  FROM animals a
  JOIN descendants d ON a.sire_id = d.id OR a.dam_id = d.id
  WHERE d.generation < 5
)
SELECT generation, name FROM descendants WHERE generation > 0
ORDER BY generation, name;

-- Query 3: Siblings of Nova (all animals sharing at least one parent)
SELECT
  a.name,
  CASE
    WHEN a.sire_id = '00000000-0000-0000-0000-000000000013'
     AND a.dam_id = '00000000-0000-0000-0000-000000000014' THEN 'full'
    WHEN a.sire_id = '00000000-0000-0000-0000-000000000013' THEN 'paternal_half'
    WHEN a.dam_id = '00000000-0000-0000-0000-000000000014' THEN 'maternal_half'
  END AS sibling_type
FROM animals a
WHERE a.id != '00000000-0000-0000-0000-000000000015'
  AND (a.sire_id = '00000000-0000-0000-0000-000000000013'
    OR a.dam_id = '00000000-0000-0000-0000-000000000014')
  AND a.sire_id IS NOT NULL
  AND a.dam_id IS NOT NULL;

Copy this into psql and it runs. The ancestor query returns 15 rows: Nova, her 2 parents, 4 grandparents, and 8 great-grandparents. The descendant query from Atlas returns Jasper, Duke, and Nova. The sibling query returns nothing because Nova is the only offspring of Duke and Fern in this dataset. Add another animal with the same parents and the query picks it up immediately.

Wrapping Up

The pedigree tree is one of those problems that looks simple on the surface. Just store the parents. It reveals its depth when you actually try to query it, validate it, and build on top of it.

The self-referential foreign keys are the integrity layer. They guarantee that every parent reference points to a real animal. The recursive CTE is the traversal engine. It walks the tree to arbitrary depth with a single query. The denormalization decision is the performance lever. Cache the tree when reads dominate writes and you want to keep recursion off the hot path.

All of the queries in this article run in production in ReptiDex, tracking real animals for real breeders. The data model has handled corrections, incomplete records, external breeder references, and batch hatchling creation without breaking.

The complete SQL example above is self-contained and runnable. Copy it, adapt the schema to your domain, and you have a working pedigree system in Postgres.

I run Built By Dusty, a software studio that builds custom applications for breeders and breed organizations. The full React Native + Supabase starter repo is at github.com/Dusttoo/react-native-expo-supabase-starter. If you are building lineage tracking for any domain, I would like to hear what you are working on.

Why I Built a Registry Instead of Just Buying One

Dusty Mumphrey — Tue, 31 Mar 2026 23:00:00 +0000

I went looking for registry software for a gecko breeding community. What I found was a spreadsheet someone shared in a Facebook group, a desktop app last updated during the Obama administration, and a lot of clubs making do with Google Forms.

The tools that exist were built for AKC-style dog kennel clubs decades ago. Nothing was built for the way modern breeders actually work. So I built one.

What a Registry Actually Does (and Why Most People Get It Wrong)

A registry is not a database of animals. It is a chain of verified claims. This animal descends from these parents. This breeder produced it. These show results were judged by these people under these rules. Every link in that chain has to hold up.

Most breed clubs treat registration as a form you fill out. Someone submits a name, a date of birth, maybe a photo. A volunteer reviews it and clicks approve. That works fine until it doesn't. Until someone disputes a pairing. Until a title gets attached to the wrong animal. Until you realize your "registry" is actually a spreadsheet with no referential integrity.

Real registry infrastructure requires the data model to enforce lineage verification at the schema level. Not as a manual review step. Not as a note someone leaves in a cell. At the schema level, where bad data gets rejected before it ever enters the system.

If you want to see what that gap looks like in practice, compare what AKC runs to what a typical exotic animal club runs. AKC has decades of engineering behind verified pedigrees, title tracking, and ownership transfers. Most exotic clubs have a Facebook group and good intentions. That is not a criticism. It is the opportunity.

The Four Problems No One Wants to Talk About

Lineage verification

Parent-offspring linking that can't be faked or hand-waved. The record either traces back through verified parents or it doesn't. There is no "trust me, the sire was my holdback from 2019." The system confirms it or rejects it.

This is the foundation everything else depends on. If your lineage data is unreliable, every pedigree you generate and every breeding decision you make from that data inherits the same uncertainty.

In ReptiDex, lineage is enforced at the database level using self-referential foreign keys. A sire_id that points to a non-existent animal gets rejected by Postgres before any application code runs. The reverse traversal works too. Given any parent, you can enumerate every recorded offspring directly.

# app/models/gecko.py — self-referential lineage on the Gecko model

sire_id: Mapped[Optional[str]] = mapped_column(
    String, ForeignKey("geckos.gecko_id"), nullable=True
)
dam_id: Mapped[Optional[str]] = mapped_column(
    String, ForeignKey("geckos.gecko_id"), nullable=True
)
sire = relationship(
    "Gecko",
    remote_side=[gecko_id],
    backref="offspring_from_sire",
    foreign_keys=[sire_id],
    lazy="selectin",
)
dam = relationship(
    "Gecko",
    remote_side=[gecko_id],
    backref="offspring_from_dam",
    foreign_keys=[dam_id],
    lazy="selectin",
)

This is the canonical SQL pattern for directed parent-child graphs within a single table. The remote_side argument tells the ORM that the relationship points back to the same table, but treats the other end as the parent. It is simple, composable, and it means lineage integrity is a database constraint. Not a policy. Not a hope.

Show result integrity

If a registry tracks titles and championships, those results need to be tied to verified animals. Not display names someone typed into a form. Not a screenshot posted in a group chat. A show result should reference a registered animal by its unique identifier, recorded by a verified judge, at a verified event.

Without this, titles are just text. Any club that wants long-term credibility needs show results to be auditable, not anecdotal.

Pedigree depth and inbreeding risk

This is where most "registry" tools fall apart entirely. Calculating a coefficient of inbreeding (COI) requires multi-generation pedigree depth. You need to walk the tree, find common ancestors, and run Wright's path coefficient method against the results.

Most platforms never get there because they never build the foundation. They store parents. Maybe grandparents. But they do not build a traversable pedigree graph that can scale to the depth COI calculation demands.

ReptiDex builds that foundation. When a hatchling is created, the system recursively traverses the lineage graph and caches a multi-generation pedigree tree directly on the record.

# app/services/breeding_service.py — recursive pedigree builder, depth-limited

async def build_pedigree_tree(
    gecko: Optional[Gecko],
    db: AsyncSession,
    depth: int = 1,
    max_depth: int = 3,
) -> Optional[Dict[str, Any]]:
    if gecko is None or depth > max_depth:
        return None

    sire = None
    if gecko.sire_id:
        result = await db.execute(
            select(Gecko)
            .filter(Gecko.gecko_id == gecko.sire_id)
            .options(selectinload(Gecko.sire), selectinload(Gecko.dam))
        )
        sire = result.scalars().first()

    dam = None
    if gecko.dam_id:
        result = await db.execute(
            select(Gecko)
            .filter(Gecko.gecko_id == gecko.dam_id)
            .options(selectinload(Gecko.sire), selectinload(Gecko.dam))
        )
        dam = result.scalars().first()

    return {
        "id": gecko.gecko_id,
        "name": gecko.name,
        "morph": gecko.morph,
        "image": gecko.profile_image,
        "sire": await build_pedigree_tree(sire, db, depth + 1, max_depth),
        "dam": await build_pedigree_tree(dam, db, depth + 1, max_depth),
    }

The max_depth parameter doubles as a recursion guard and a circular reference safeguard. The cached tree is a deliberate denormalization. Instead of traversing the graph live on every request, the pedigree is computed once at creation and stored as JSON on the record. That trade-off holds up well until parent records are updated retroactively, which is a solvable edge case.

COI calculation is the next layer on top of this. The traversal infrastructure is already in production. The math is on the roadmap. But the point stands: if your platform cannot even walk the tree, it will never answer the question breeders actually care about. "Is this pairing safe?"

Breeder identity and ownership transfer

An animal changes hands. The registry needs to know who produced it, who owns it now, and how that transfer happened. This sounds simple until you realize most clubs track ownership with screenshots of PayPal receipts or a DM that says "sold."

A real ownership transfer is a first-class event in the system. It has a timestamp, a sender, a receiver, and it updates the canonical record. The animal's history stays intact. The breeder's production record stays intact. Nothing gets lost in a group chat.

Why Off-the-Shelf Doesn't Work

Existing tools fall into two buckets. Generic database platforms like Airtable and Notion have no concept of lineage. You can build a table of animals, but you cannot express "this animal's sire is that animal, and here are four generations of ancestors, and here is the inbreeding coefficient for a proposed pairing." That is not a feature gap. It is a category gap.

The other bucket is legacy kennel software. Applications built for dog clubs in the early 2000s that assume a single-breed, single-club model with a desktop-only workflow. They work for the community they were built for. They do not translate to the exotic animal world, where breeders work across species, collections change hands frequently, and nobody is running a Windows desktop app in 2026.

The real problem is not features. It is domain modeling. A registry is a directed graph of relationships with trust constraints at every edge. An animal node connects to parent nodes, breeder nodes, owner nodes, and event nodes. Each connection has rules about what makes it valid. You cannot bolt that onto a spreadsheet and call it infrastructure.

I know this because I built it. ReptiDex launched on the App Store in early 2025 and hit 50 paid subscribers within 9 days. It already has parent-offspring linking, a multi-generation pedigree tree, and QR-code-to-live-record functionality. It is not a concept. It is in production, tracking real animals for real breeders, right now.

What I'd Tell a Club That's Thinking About This

Start with your data model, not your feature list. If your schema can't answer "show me every descendant of this animal and their health outcomes," you don't have a registry. You have a contact list with extra steps.

Ownership transfer is the hard part. Not technically. Politically. Every club has to decide what "verified" means for their community. Does a transfer require both parties to confirm? Is there a fee? Can it be reversed? The software should enforce whatever the club decides. It should not make the decision for them.

Think in generations. A registry that only tracks one generation deep is a baby book. The real value compounds over time as pedigree depth grows. Five generations of clean data lets you make breeding decisions that one generation never could. Build for the breeders who will use this platform in ten years, not just the ones signing up today.

You do not need to build this from scratch. But you do need someone who understands the domain. A generic dev shop will build you a CRUD app and call it a registry. It will have forms and tables and a login screen. It will not have lineage verification, pedigree traversal, or the domain logic that separates a real registry from a database with a nice UI.

I am a breeder who also happens to be a senior software engineer. I have been in the animal world since I was five years old. I showed American Bullies under ABKC and UKC. I breed crested geckos. I built ReptiDex because I needed it to exist, and it did not. That is a different starting point than "we found this niche on a market research call."

If you run a breed club or registry organization and any of this sounded familiar, I would like to hear what you are dealing with. The infrastructure gap is real, and it is solvable.

Get in touch →

What Breeders Actually Need From Software (And What Developers Assume They Need)

Dusty Mumphrey — Fri, 27 Mar 2026 19:45:00 +0000

It's 11pm. A breeder has three browser tabs open, a Google Sheet with color-coded columns, and a notebook sitting next to the keyboard with handwritten pairing notes from two seasons ago. They're trying to figure out whether a planned breeding will push the coefficient of inbreeding above a threshold they've set for their program. Nothing on their screen talks to anything else. They've been doing this for years.

Not because they haven't looked for better options. They have. They've tried CRMs, inventory apps, animal management platforms, and general-purpose databases. Every tool either bends the problem into something it wasn't, or buries the actual work under features built for a different kind of business entirely.

This is the starting point for almost every serious breeder I've talked to. And it's the starting point for understanding why software built for breeders keeps failing them.

What Developers Assume Breeders Need

The reasonable guesses aren't wrong. They're just incomplete.

A developer approaching the breeder space for the first time would probably reach for a familiar toolkit. A CRM to track customers and waitlists. A database or spreadsheet for animal records. A calendar for scheduling. A storefront for selling. Maybe a notes field or two.

These tools exist. Some of them are excellent at what they do. Breeders have tried them. The failure point is always the same: these tools treat breeding like inventory management. Animals become product records. Bloodlines become tags. The actual craft, the decision-making, the verification, the longitudinal tracking that spans years and generations, falls completely through the cracks.

The gap isn't a missing feature. It's a missing frame of reference.

What Breeders Actually Need

1. Lineage integrity, not just lineage storage

A field in a database that says "Sire: Rocky" is not lineage. It's a note.

Real lineage is verified, multi-generational, and tamper-resistant. It is the foundation of the animal's value. When a buyer pays a premium for a dog, a gecko, or any production animal, they are buying the bloodline as much as the animal itself. An unverifiable or inconsistently documented pedigree doesn't just weaken a sale. It erodes trust in the entire breeding program.

This is why registries exist. The AKC has been solving this problem for 140 years. ABKC built it from scratch for a new breed. The model works: verified registration, traceable multi-generation pedigrees, records that transfer with the animal. What breeders need from software is a version of that infrastructure that works at the program level, not just at the registry level.

Storing a sire and dam field is not lineage. Lineage is structural. This is the core problem that ReptiDex solves with multi-generation pedigree trees and cross-collection lineage linking, and it's the foundation of the registry and pedigree platforms I build for breed clubs.

2. Pairing logic, not just pairing records

Breeders don't just need to log who was bred to whom. They need to understand what a pairing means before it happens.

Coefficient of inbreeding (COI) calculations. Trait probability distributions. Genetic incompatibility flags. Restrictions on first-generation linebreeding, which organizations like the Gold Standard Gecko Club have built into their competition rules precisely because it matters. A form with a sire field and a dam field captures none of this.

The pairing decision is where the breeding program lives or dies. The software either helps you make it well or it doesn't help you at all. Most software doesn't help at all. The breeding records and genetics apps I build for breeders start here, with trait probability, COI awareness, and genetic compatibility checks built into the pairing workflow.

3. Clutch and litter tracking built for biology, not inventory

A litter is not a batch. A clutch is not a shipment.

Offspring need to be tracked individually, from first record to final placement, with their own records linking directly back to parents. Weights at hatch or birth. Dates. Morphs or phenotypes. Retained animals. Placed animals. Animals lost. Each one is a data point in the lineage tree. Each one has downstream consequences for the program.

Generic inventory tools treat offspring as a transaction. You record the sale, close the ticket, move on. But a breeder may be tracking animals they placed five years ago because the buyer wants to breed back into the line, or because they're building a multi-generation study, or because that animal's offspring just showed up at a show and they want the record.

The biology doesn't end at the sale. The software has to reflect that.

4. Show and competition history that travels with the animal

A dog's show record is part of its value. So is a gecko's placement history. Judges, events, placements, titles, pointed wins, majors. That history needs to live on the animal's profile, not in a separate spreadsheet, not in someone's email archive.

When a buyer is evaluating a stud or a broodmother, they want to see the full picture. When a registry is verifying eligibility for competition, they need documented proof. When a breeder is making pairing decisions, the show record of both animals is relevant information.

No CRM is designed to think about an individual animal as a long-term record of achievement. That's not a CRM problem. CRMs are built for people, not animals. The category mismatch is total.

5. Waitlist management that respects how breeders actually sell

Breeder waitlists are not e-commerce carts. Shopify cannot model them. A checkout flow cannot capture them.

A waitlist for a specific pairing might start before the animals are even bred. There are deposits, which may or may not be refundable depending on outcome. There are first-right-of-refusal arrangements. There are cohort assignments, where buyers pick pick order rather than picking specific animals. There are communication threads tied to a specific pairing that need to stay connected to the records, not float off into a general inbox.

And when the clutch or litter doesn't go as planned, because sometimes it doesn't, all of that needs to be renegotiated, documented, and communicated. A shopping cart abandonment workflow is not going to help you here. This is exactly why I built Geckistry as a custom sales platform from scratch rather than forcing my gecko business onto Shopify.

Why the Gap Exists

This is not a problem of bad developers. It is a structural problem.

You cannot spec out what you have never lived. A developer who has never sat with a stud book, never calculated a COI, never navigated a waitlist dispute when a litter came out smaller than projected, does not know what questions to ask. They build what makes sense from the outside. It looks reasonable. It fails in practice, quietly, in ways that don't always surface in a demo or a trial period.

The domain knowledge is not a bonus. It is the product.

This pattern shows up in every industry where the work is specialized. Healthcare software built without clinical input produces tools that technically meet requirements and fail at the bedside. Legal technology built without practicing attorneys builds workflows that don't map to how cases actually move. The breeder space is no different. The gap between what software promises and what breeders actually need is almost always a gap in domain understanding, not engineering capability. That gap is the entire reason Built By Dusty exists.

What Staying on Spreadsheets Actually Costs

The spreadsheet and notebook system works until it doesn't. Most breeders don't feel the cost of it until something breaks.

Hours per week on manual data entry that should be automatic. Lineage errors that surface years later and are nearly impossible to trace. Pairing decisions made without full genetic context because the information exists but isn't connected. Waitlist disputes because the terms were never formally documented. Records that live in one person's head and disappear when a hard drive fails or a phone breaks.

The competition for better breeder software is not other software. It is the status quo. And the status quo has real costs that most breeders have simply learned to absorb.

The Standard Already Exists

The AKC figured this out in 1884. The ABKC built it for a breed that didn't exist yet and got it right. The pattern is consistent across every established species club that takes lineage seriously: verified registration, multi-generation pedigree, show records, transferable titles, and a system of trust that makes the record worth something.

What breeders need from modern software is that same infrastructure, updated, accessible at the program level, and integrated into the daily work of running a breeding operation. Not a replacement for registries. An extension of what registries do, built for the breeder who is living the work.

The standard is not new. The software that meets it is.

Built From the Inside

BBD exists because I am a breeder first. I've been in dogs since I was five years old. My family bred and showed ADBA American Pit Bull Terriers, moved into American Bullies around 2005 when the breed was still being formed, and I was part of the group that established ABKC during those early years. I've made group placements in UKC. I breed crested geckos. I built ReptiDex because I needed it and nothing adequate existed.

ReptiDex launched with 50 paid subscribers and 200 animals tracked in its first nine days. Not because of marketing. Because breeders recognized something built by someone who understood the problem. You can read the full build stories for ReptiDex, Geckistry, and Texas Top Notch Frenchies in the case studies.

The best software for breeders will never come from someone who Googled the problem. It will come from someone who lived it.

If you're a developer working in this space, this is the bar. If you're a breeder evaluating your options, this is what to ask for.

I build breeder websites, records and genetics apps, sales platforms, and registry systems through my studio, Built By Dusty. Every one of them is built on software I use in my own breeding program. If you're a breeder who is ready to replace the spreadsheets, or a breed club that needs real infrastructure, I'd like to hear from you.

I cut Claude API costs by 90% with prompt caching. Here's what I learned before I had to shut it down.

Dusty Mumphrey — Wed, 25 Mar 2026 16:30:00 +0000

867 Discord servers. 1,000+ active users. $10–11 every time someone played a one-hour D&D session.

I was the only engineer. There was no revenue. And that number wasn't going down on its own.

I want to be upfront before we go any further: Scrollbook is no longer running.

I built it because I was always the Dungeon Master. My wife, my son, and I had a standing D&D night, and I wanted to actually play for once instead of running the whole session. So I built an AI dungeon master to take my seat. It worked well enough that I shared it. I did not expect anyone else to care.

They did. 867 servers and 1,000+ users later, I was looking at $10-11 every time someone played a one-hour session with no revenue, no paywall, and no plan for either. (Scrollbook is one of three production projects I break down in my case studies. The other two are live and generating revenue. The contrast is instructive.) I shut it down because the cost of operating it solo, without a monetization model that kept pace with usage, made it unsustainable. By the time I pulled the plug, prompt caching had dropped that same session to $0.50-1.50. The technical solution worked. The business math didn't.

Both of those things are worth talking about.

This post covers the technical side in detail: what the problem was, what I changed, and the actual production code behind it. The business lesson is at the end. I'd argue it's the more important one.

The Cost Problem

Every message to Claude sent the entire conversation context from scratch. In a D&D session, that context grows with every exchange between the player and the AI.

Before caching, each API call looked like this:

[system prompt: ~1,800 lines of D&D rules + Cipher's personality]
[campaign context: setting, NPCs, quests, locations, active encounter]
[character context: stats, equipment, spells, conditions, companions]
[party context: all active players and their characters]
[message history: every exchange in the session so far]
[current question: "can I grapple the goblin?"]

The system prompt and campaign context alone sat at 4,000–5,000 tokens, reprocessed at full price on every single message.

A one-hour D&D session averages 15–25 back-and-forth exchanges. Context grows on each call. At Sonnet pricing ($3.00/M input, $15.00/M output): $10–11 per session. Multiply that across hundreds of active servers running concurrent sessions and it stops being a line item. It becomes a ceiling. Every new user makes the situation structurally worse.

The Architecture

Scrollbook runs on six services:

Service	Role
`bot/`	Discord bot — receives player commands
`api/`	REST API for the companion web app
`shared/services/cipher_service.py`	Owns all Anthropic API calls
`shared/services/ai_usage_tracker.py`	Token counting and budget enforcement
`shared/services/ai_extraction_service.py`	PDF/content extraction via Bedrock
`infrastructure/`	AWS CDK — ECS Fargate, RDS, ALB

cipher_service.py is the single point of contact with the Anthropic API. Context is assembled per-request by ContextManager.build_context(), pulling campaign data, character stats, active party, quests, encounters, and NPCs from Postgres — all scoped to the Discord guild ID.

Here is the insight that unlocked the fix: the system prompt and campaign context were structurally identical on every request for a given server. The D&D rules, Cipher's personality, the campaign world — none of it changes message-to-message. It was being sent and fully reprocessed every single time, on every message, for every server.

What Prompt Caching Actually Is

Anthropic caches the prefix of your prompt on their infrastructure for a TTL window. Subsequent requests that match that prefix byte-for-byte skip the reprocessing cost. Instead of paying full input token price, you pay roughly 10% of that on a cache hit.

A few things that matter:

Prefix, not arbitrary sections. The cache applies to the beginning of your prompt. Everything you want cached must come before everything that changes. This means prompt order is the entire game.

Cache hits vs. misses. A hit means the prefix was already in cache; you pay about 10% of the normal input token price. A miss means the prefix gets written to cache at roughly 1.25x the normal input token price — slightly more expensive than a regular call, but a one-time cost within each TTL window. After the first message in a session, you want hits almost exclusively.

The TTL is 5 minutes for the ephemeral cache type on Anthropic's infrastructure. For active D&D sessions this is fine — messages come fast. For a server that runs one session a week, you pay write costs every time with zero read benefit. The math only works at session density.

This is a first-class API feature, not a workaround. You opt in by passing structured content blocks with a cache_control field instead of a plain string. Two lines of code. Anthropic's infrastructure handles everything else.

One more thing worth saying clearly: this is not client-side caching. You are not storing API responses locally. You are telling Anthropic's infrastructure which portion of your prompt is stable so it does not need to recompute it.

The Implementation

Centralizing Prompt Assembly

With six services in play, the first structural requirement was centralizing all prompt assembly into one place. The cacheable prefix must be byte-for-byte identical across every request. That cannot happen if prompts are assembled in multiple code paths and concatenated at call time. A trailing space, a newline difference, a Unicode normalization inconsistency — any of it produces a full cache miss.

All prompt assembly in Scrollbook runs through one function: cipher_service.py:_build_conversational_prompt().

Prompt Order

The ordering decision is the whole thing:

1. System prompt (D&D rules + Cipher personality)        CACHED
2. Campaign and character context (per-guild, stable)    included in cache
3. Conversation history [0 ... N-3]                      CACHED at breakpoint
4. Conversation history [N-2, N-1]                       NOT cached
5. Current question                                      NOT cached

Static content at the top. Dynamic content at the bottom. The most expensive tokens, cached. The tokens that change on every message, not cached.

The Code

Before caching, the system prompt was passed as a plain string:

# Every call: full system text + context, reprocessed at full price every time
response = self.anthropic_client.messages.create(
    model=self.model_id,
    system=full_system_text,  # plain string, no caching
    messages=messages,
)

After caching, it becomes a structured content block:

# cipher_service.py:2070-2079
if self.enable_caching:
    system_blocks = [
        {
            "type": "text",
            "text": full_system_text,
            "cache_control": {"type": "ephemeral"}  # two lines
        }
    ]
else:
    system_blocks = [{"type": "text", "text": full_system_text}]

The conversation history gets a second cache breakpoint at the third-to-last message, capturing the entire prior session:

# cipher_service.py:2084-2098
for i, msg in enumerate(conversation_history):
    content_blocks = [{"type": "text", "text": msg["content"]}]

    if self.enable_caching:
        is_last_two = i >= len(conversation_history) - 2
        # Cache breakpoint at third-to-last message
        if not is_last_two and i == len(conversation_history) - 3:
            content_blocks[0]["cache_control"] = {"type": "ephemeral"}

    messages.append({"role": msg["role"], "content": content_blocks})

# Current question is never cached
messages.append({"role": "user", "content": [{"type": "text", "text": question}]})

Two cache breakpoints: one on the system prompt, one on the conversation history. The Anthropic API limits the number of cache control markers per request, so placement matters. You want those markers positioned to maximize the ratio of cached-to-uncached tokens on every call — that ratio is what drives your actual savings.

The API call itself barely changes. The system parameter is now a content block array instead of a string:

# cipher_service.py:2221-2228
response = self.anthropic_client.messages.create(
    model=self.model_id,
    max_tokens=self.max_tokens,
    temperature=self.temperature,
    system=system_blocks,  # content block array instead of plain string
    messages=msgs,
    tools=tools_to_use,
)

The Multi-Tenant Problem

867 servers means 867 sets of campaign state — different characters, different HP totals, different active encounters, different party compositions. Keeping per-guild context out of a polluted shared prefix requires a specific architectural decision.

In Scrollbook, guild-specific data lives inside the cached block:

# cipher_service.py:2066-2068
context_section = context.to_prompt_section()
full_system_text = f"{system_prompt_text}\n\n{context_section}"
# This full_system_text then receives the cache_control block

This works because campaign context is stable within a session. Cipher updates game state via tool calls when something changes — it does not receive externally updated context as new input mid-session. For the duration of an active session, the system prompt plus campaign context is genuinely identical across every message for that guild. Each guild gets its own cached prefix. No cross-contamination.

If your situation is different — if state changes externally between messages — that dynamic content needs to live below the cache breakpoint, not inside it.

The Results

A one-hour session that cost $10–11 dropped to $0.50–1.50.

To verify you are actually hitting the cache, read the usage object on the response. Do not assume. Log it explicitly:

# cipher_service.py:2268-2288
if self.enable_caching and hasattr(response, "usage"):
    usage = response.usage
    input_tokens = getattr(usage, "input_tokens", 0)
    cache_read_tokens = getattr(usage, "cache_read_input_tokens", 0)
    cache_creation = getattr(usage, "cache_creation_input_tokens", 0)

    if cache_read_tokens > 0:
        savings_pct = (
            cache_read_tokens / (input_tokens + cache_read_tokens)
        ) * 100
        logger.info(
            f"Cache HIT: {cache_read_tokens} tokens read from cache "
            f"({savings_pct:.1f}% savings), {input_tokens} new tokens"
        )
    elif cache_creation > 0:
        logger.info(f"Cache MISS: {cache_creation} tokens written to cache")

Three fields to understand:

input_tokens — tokens billed at full price this call
cache_creation_input_tokens — tokens written to cache, billed at approximately 1.25x the base input token price (one-time cost per TTL window)
cache_read_input_tokens — tokens read from cache, billed at approximately 10% of normal (this is where the 90% savings comes from)

The feature flag that controlled it all:

# shared/config/settings.py:86-98
anthropic_enable_prompt_caching: bool = Field(
    default=True, description="Enable Anthropic prompt caching (90% cost savings)"
)

# Bedrock fallback has no equivalent — hardcoded off
bedrock_enable_prompt_caching: bool = Field(
    default=False, description="Enable prompt caching (not supported on AWS Bedrock)"
)

A note on Bedrock: At the time Scrollbook was built, Bedrock did not support prompt caching. That gap made it a non-starter as the primary provider and locked the architecture to the direct Anthropic API. Bedrock has since caught up — prompt caching went GA in April 2025, with 1-hour TTL support added in January 2026. If you are on Bedrock today, the same technique applies.

When optimization becomes load-bearing infrastructure, provider lock-in follows. That was true when I built this. It is less true now.

Gotchas That Will Kill Your Cache Hit Rate

Prompt order is everything. If you accidentally flip the ordering — campaign context before system prompt, for example — every call is a full miss. The cache matches from the beginning of the prompt in sequence. There is no partial matching.

Dynamic content in the cached prefix. This is the hardest mistake to catch. Timestamps, counters, random values, user-specific data — anything that changes per-message, if it bleeds into the section you are trying to cache, every call is a miss. In Scrollbook, character HP and active conditions are inside the cached block intentionally, because Cipher controls those updates via tool calls. If your state changes externally, that content belongs below the breakpoint.

The 5-minute TTL cliff. Servers with long gaps between messages cold-start on every session. Write costs get paid repeatedly with zero read benefit. The math works at session density. For sparse traffic, run the calculation before assuming caching helps.

Whitespace and encoding. The prefix match is byte-level. A trailing space, a newline inconsistency, a Unicode normalization difference — any of it is a miss. Prompt assembly must run through a single code path. If you are concatenating in multiple places, you will have inconsistency you cannot see.

Don't assume, verify. The logging block above takes ten minutes to add. Add it. The usage object will tell you immediately whether your cache hit rate matches your expectations. Ship it before you ship the feature.

Why I Still Had to Shut It Down

The honest math: 90% off still leaves 10% of a cost that grows with usage.

At $0.50–1.50 per session across 867 servers with no subscription revenue, the situation improved dramatically and remained unsustainable. I had bought runway. I had not fixed the underlying problem.

There was no paywall. No subscription tier. No mechanism for Scrollbook to generate revenue as usage scaled. Every new server was a new cost center with nothing offsetting it. Prompt caching made the slope of that curve shallower. It did not change the direction.

Beyond the API costs: solo maintenance at that user count meant incident response, server reliability, and the full weight of being the only person accountable to 867 active communities. That is not something you can optimize your way out of.

What I would do differently: charge earlier. I know that is a strange thing to say about something I built so my family could play D&D together. But the moment it left that context and became someone else's tool, it became a product. I just did not treat it like one. Even a small subscription changes the entire math and the entire psychology of the product.

I built the technical foundation first, optimized costs second, and never got to monetization. The right order is the reverse: figure out how this sustains itself, then build, then optimize. I applied that lesson to the next two products I shipped. ReptiDex launched with a three-tier subscription model on day one and hit 50 paid subscribers in 9 days. Geckistry collects payment at checkout. Both are still running.

What to Take From This

Prompt caching is a real, production-grade optimization. The cache_control field is two lines of code. A 90% reduction in inference cost is achievable if your prompt has a large, stable prefix and your traffic density is high enough for cache reads to consistently outpace cache writes.

If you are building on Claude at any meaningful scale, look at your prompt structure. If you are sending the same system prompt on every request and that prompt is long, you are paying for reprocessing you do not need.

But the bigger lesson is not technical. If you are building an AI product solo, get to monetization before you get to optimization. The optimization I built here was real and it worked. The product did not survive anyway — not because the code was wrong, but because I treated cost reduction as a substitute for a business model.

It is not.

I run Built By Dusty, a software studio that builds custom apps and sales platforms for animal breeders and small businesses. The AI cost optimization techniques from Scrollbook now power features in the breeding software I deliver to clients. If you're building on Claude at scale, or you're a founder with a product that has real infrastructure costs to manage, I'd like to hear from you.

All code references in this article are from the actual Scrollbook production codebase. The codebase is private, but every snippet shown here ran in production.

How Claude Helps Me as a Neurodivergent Freelancer

Dusty Mumphrey — Sat, 21 Mar 2026 18:19:51 +0000

I started a new project last month. A cool new app. Revolutionary, obviously. The kind of idea that hits you at 11 PM and has you standing at your desk by midnight with a fresh repo and a head full of architecture decisions.

I went hard. Hours. Days. Skipping meals, forgetting to reply to messages, operating in that beautiful tunnel where the code just flows and every problem has a solution you can almost see before you finish typing the question. I built the core. I solved the hard parts. I got it to the point where the remaining work was clear, scoped, and totally achievable.

And then I stopped caring about it.

Not consciously. I didn't decide to abandon it. I just woke up one morning and the pull was gone. The dopamine faucet turned off. What was left was finish work. Polish. Edge cases. The kind of steady, unglamorous effort that turns a prototype into a product. And my brain had already moved on to the next interesting problem.

If you're neurodivergent and you've built anything, you know this exact feeling. The graveyard of 80%-done projects. The guilt. The frustration of knowing you're capable of extraordinary output but somehow can't make yourself do the last 20%.

This is not a discipline problem

I run a solo software studio called Built By Dusty. I build tools for animal breeders. I'm also a breeder myself. Crested geckos, and before that, American Bullies and APBTs going back to when I was five years old. I have 9+ years of professional engineering experience across fintech, healthcare, and government. I've shipped production software for CBP, ICE, and FEMA. I'm not lacking in ability or work ethic.

What I am is wired differently.

My neurodivergency looks like completing work exceptionally fast. Thriving in the go-go-go. Actually enjoying context switching, which I know sounds like heresy to the deep-work crowd. I can juggle three codebases, a client call, and a breeding record update in the same afternoon and feel energized by it.

The cost is that project timelines get muddled. Tasks asked of me in the throes of a hyperfixation vanish from my memory like they never existed. Someone sends me an important email while I'm three hours deep in a database migration, and that email might as well have been addressed to someone else. It's gone. Not because I don't care. Because my brain physically was not available to receive it.

The tool graveyard

I tried everything. I really did.

AI calendars that promised to organize my day. They'd generate a beautiful schedule by 8 AM. By 8:45 I'd already blown past it because I got pulled into something more interesting, and the rest of the day's plan became fiction.

Pomodoro timers. Twenty-five minutes on, five minutes off. The problem is that when I'm locked in, a timer going off doesn't make me take a break. It makes me angry. And when I'm not locked in, no timer is going to manufacture focus.

Written planners. I own a genuinely embarrassing number of notebooks with exactly twelve pages of neat, hopeful planning followed by nothing.

Task management apps. I've signed up for most of them. The ones that require manual input fall apart because updating the system is exactly the kind of low-stimulation maintenance task my brain deprioritizes. The ones that try to automate everything make assumptions about how I work that don't match reality.

The common thread: none of these tools could keep up with the speed of my brain. They all assumed a pace and a pattern that isn't mine. They wanted me to slow down, break things into small pieces, follow a linear path. That's not how I operate. I operate in bursts. I need a system that can match that.

What changed

I started using Claude as a rubber duck.

Not for code generation. For thinking. I'd be deep in a FastAPI project and I'd ask, "What are the security drawbacks of JWTs versus session tokens for this use case?" Or I'd be structuring a new service and ask, "What's the best way to organize routes in this API if I know I'm going to add multi-tenancy later?" Technical questions where I didn't need someone to write the code. I needed someone to think through the problem with me.

That was the first thing that felt different. Every other tool I'd tried wanted to manage me. Claude just showed up and matched whatever speed I was moving at. I could fire off three questions in two minutes, get useful answers, and keep building. Or I could slow down and have a long back-and-forth about architecture tradeoffs. It met me where I was instead of asking me to be somewhere else.

Claude Code came much later. The connectors to Jira and Confluence came later still. But the foundation was that first experience of having a thinking partner that didn't try to change how I work. It just worked the way I already do.

That distinction matters more than any feature list. Every productivity tool I'd tried before was built on the assumption that I needed to be reformed. Slower. More methodical. More consistent. Claude doesn't assume any of that. It just holds the thread.

The morning context reload

Here's what a typical morning looks like now. I sit down. I open Claude. And I say something like: "I'm picking back up today. What was I working on Thursday?"

That's it. No digging through browser tabs. No scrolling through Jira trying to reconstruct what I was doing three days ago. No reading old commit messages hoping they jog my memory.

Claude has my Jira board connected. It has my Confluence docs. It can see my project tickets, my outreach pipeline, my content calendar. So when I ask what I was doing Thursday, it doesn't guess. It checks. It tells me I was halfway through writing RLS policies for tenant isolation on Breed Ledger, that I had a follow-up email to send to a breed club, and that I'd left a note about a failing CI pipeline on a ReptiDex ticket.

For a neurotypical person, this might sound like a convenience. For me, it's the difference between a productive morning and an hour of floundering while I try to remember where I left off. That context reload used to cost me real time every single day. Sometimes it cost me the whole day, because by the time I'd reconstructed the state, the motivation window had closed.

Thinking out loud without losing the thought

As I started trusting Claude with technical questions, I started trusting it with harder ones. Business decisions. Strategy. The kind of thing a solo founder normally processes alone in the shower and then forgets half of by the time they sit back down.

Here's a recent example. I have two products. ReptiDex, a breeding records app that's live, stable, and has paying subscribers. And Breed Ledger, a platform for breed clubs and registries that's earlier stage. I'd just lost my first pitch for Breed Ledger. The breed club chose a different tool for their immediate need. My second prospect was promising but months away from a decision.

My brain wanted to go back to ReptiDex. I had a list of features that were genuinely exciting to build. Fun problems. The kind of work that would let me hyperfixate for a week and feel productive the whole time.

So I talked it through with Claude. Not a polished strategy prompt. Just: "I'm torn between pushing ReptiDex features or continuing to build out Breed Ledger even though the first deal didn't land. What do you think?"

Claude told me Breed Ledger was the smarter play. ReptiDex is in a good, stable place. Users are on it. I can watch how they interact with what's already there and let that data inform the next set of features in a few months. Meanwhile, Breed Ledger needs a working demo. When the next breed club conversation happens, showing a real, functional platform is worth more than any pitch deck. Building the demo now means I'm ready when the opportunity shows up instead of scrambling to build it after.

That answer saved me from a classic neurodivergent trap: chasing the dopamine of fun work instead of doing the strategic work that actually moves the business forward. I knew that. Somewhere in the back of my mind, I knew it. But knowing something and having someone lay it out clearly, with the context of your actual business situation, are two different things.

This is the rubber duck that talks back. And for someone whose brain generates ideas at a pace that outstrips their ability to evaluate them, having a patient, context-aware sounding board is transformative. I can externalize my thinking without worrying that the thought will disappear before I finish processing it. It's captured. It's in the conversation. I can come back to it.

Then came Claude Code

Claude Chat was the thinking partner. Claude Code, when it arrived, became the momentum keeper.

I want to be honest about this part because it's the part that matters most.

There are days where executive function is just low. Where starting feels impossible. Not because the work is hard. Because the activation energy required to go from "sitting in my chair" to "writing the first line of code" might as well be infinite.

On those days, Claude Code is the difference between shipping and stalling. I don't have to create from zero. I can describe what I need, and Claude produces a first draft I can react to. A set of database migration files. A test suite skeleton. An API endpoint with the boilerplate already handled.

Reacting is easier than creating when your brain is fighting you. Editing a draft takes less activation energy than staring at a blank file. This is not laziness. This is a real, practical accommodation for how my brain works on its worst days. And it means those days still produce output instead of guilt.

And on the good days? When hyperfocus is locked in and the code is flowing? Claude Code keeps me there. I can stay in the zone and let it handle the boilerplate so my attention stays on the hard parts. I run multiple Claude Code instances reviewing each other's work while I focus on architecture and direction. The momentum doesn't break.

The connectors tied it all together

First it was the chat. Then Claude Code. Then the connectors came online, and that's when the whole system clicked.

The MCP integrations let Claude reach into Jira, Confluence, and Google Drive without me having to copy-paste context into a chat window.

I run my entire business through Jira. Every outreach conversation. Every content piece. Every product feature. Every bug. When I say "what's the status on the GSGC deal?" I don't have to navigate to the board. Claude checks the ticket and tells me.

When I'm mid-hyperfixation on a code problem and someone asks me about a content deadline, I can say "when is that breeder website article due?" without leaving my editor. The answer comes back with the actual status from the actual ticket.

This matters because the organizational overhead of running a solo business is the silent killer for neurodivergent founders. It's not the core work that breaks you. It's the meta-work. The tracking. The updating. The remembering. Every minute you spend maintaining your project management system is a minute you're not building. And for people like me, that maintenance is exactly the kind of low-stimulation task that gets deprioritized until something falls through the cracks.

The connectors mean less maintenance. Less context switching to update a system. Less "I'll do it later" that turns into "I forgot it existed."

What actually changed

I want to be specific because vague claims don't help anyone.

Follow-ups that used to slip now have a system catching them. I can ask Claude every morning if anything in my outreach pipeline has gone quiet, and it checks the actual tickets instead of relying on my memory.

Feature scope conversations happen in writing. When I talk through a product decision with Claude, that conversation exists. I can reference it. It doesn't mutate in my memory the way verbal decisions do.

Bad days don't mean lost days. Even when executive function is low, I can still move tickets forward because the barrier to entry is lower. Describe what you need. React to what comes back. Ship.

I spend less energy on "what should I be doing right now" and more energy on the work itself. That question used to eat an hour some mornings. Now it takes thirty seconds.

This is not a Claude ad

I want to be clear about what I'm saying and what I'm not.

I'm not saying Claude fixed my brain. My brain works the way it works. I'm not saying Claude is the only tool that could do this. I'm saying it's the first tool that matched the way I actually operate instead of asking me to operate differently.

The speed. The context awareness. The ability to pick up where I left off without me having to reconstruct the state. The patience to answer the same question I asked three days ago without making me feel bad about it. The connectors that reduce the organizational tax of running a business.

For me, as a neurodivergent solo founder, that combination turned out to be the thing that finally worked.

If this sounds like you

If you're neurodivergent and freelancing or running a solo business, the best tool isn't the one that makes you more disciplined. It's the one that makes discipline less necessary.

Stop trying to fix the way your brain works. Find tools that work with it.

That's what I did. And for the first time, the projects are getting finished.

How I solved Supabase's chainable query builder problem in React Native tests

Dusty Mumphrey — Thu, 19 Mar 2026 00:30:00 +0000

Every React Native + Supabase tutorial ends the same way. The app works in the simulator. Tests are left as an exercise for the reader.

This one is different.

I recently published react-native-expo-supabase-starter, a production-ready starter extracted from ReptiDex, a mobile app I built and launched solo. 50 paid subscribers and 200 animals tracked within 9 days of launch. The starter includes the full auth flow, a RevenueCat subscription system, TanStack Query, and Zustand. But the part most people ask about is the testing setup. I wrote a detailed breakdown of the full build if you want the complete story.

Specifically: how do you actually test Supabase queries in Jest without a running database or a fake HTTP server?

Here's the problem and how I solved it.

The Problem

Supabase queries are fluent chains:

const { data, error } = await supabase
  .from('profiles')
  .select('*')
  .eq('id', userId)
  .single()

A naive jest.mock('@supabase/supabase-js') breaks because each method in the chain returns a new object. The mock returns undefined partway down the chain and you get cannot read properties of undefined errors that look nothing like what actually went wrong.

The internet's solution is usually one of three things:

Option 1: Hit a real test database. This works but it's slow, requires a running Supabase instance, and makes tests order-dependent. Not unit tests.

Option 2: MSW to intercept at the HTTP layer. This works better, but it's heavy setup for testing individual service methods. You're essentially running a fake HTTP server to test a function that calls one table.

Option 3: A flat jest.mock that returns the same object from every method. This is what most Stack Overflow answers suggest. It looks like this:

jest.mock('@supabase/supabase-js', () => ({
  createClient: () => ({
    from: jest.fn(() => ({
      select: jest.fn().mockReturnThis(),
      eq: jest.fn().mockReturnThis(),
      single: jest.fn().mockResolvedValue({ data: mockData, error: null }),
    })),
  }),
}))

The problem with this approach is that it's module-level. You can't change the resolved value per test without resetting the entire mock. And it uses mockReturnThis(), which returns the mock function itself rather than the chain object, so verify() assertions on specific methods become unreliable.

None of these options are what you actually want for unit testing service methods. What you want is to inject a mock client and control exactly what each query resolves to, per test.

The Fix: Dependency Injection and a Chainable Mock Helper

Two things work together here.

First: inject the Supabase client into your services rather than importing it directly. This makes the client swappable in tests without module-level mocking.

// src/services/profile-service.ts
import { SupabaseClient } from '@supabase/supabase-js'
import { Database } from '../types/database'
import { Profile, ProfileUpdate } from '../types'

export class ProfileService {
  constructor(private client: SupabaseClient<Database>) {}

  async getProfile(userId: string): Promise<Profile> {
    const { data, error } = await this.client
      .from('profiles')
      .select('*')
      .eq('id', userId)
      .single()

    if (error) throw error
    return data
  }

  async updateProfile(userId: string, updates: ProfileUpdate): Promise<Profile> {
    const { data, error } = await this.client
      .from('profiles')
      .update({ ...updates, updated_at: new Date().toISOString() })
      .eq('id', userId)
      .select()
      .single()

    if (error) throw error
    return data
  }
}

In production code you instantiate it with the real client:

const profileService = new ProfileService(supabase)

In tests you pass in a mock. No module patching needed.

Second: build a chainable mock helper that correctly returns the same chain object from every method, and resolves at the terminal call.

// tests/helpers/supabase-chain-mock.ts

export function createSupabaseChainMock(resolvedValue: unknown, error?: unknown) {
  const chain: Record<string, jest.Mock> = {}

  const terminal = error
    ? jest.fn().mockRejectedValue(error)
    : jest.fn().mockResolvedValue({ data: resolvedValue, error: null })

  const returnChain = jest.fn().mockReturnValue(chain)

  // Filter/builder methods: all return the chain for continued chaining
  chain.select = returnChain
  chain.insert = returnChain
  chain.update = returnChain
  chain.delete = returnChain
  chain.upsert = returnChain
  chain.eq = returnChain
  chain.neq = returnChain
  chain.gt = returnChain
  chain.gte = returnChain
  chain.lt = returnChain
  chain.lte = returnChain
  chain.like = returnChain
  chain.ilike = returnChain
  chain.in = returnChain
  chain.is = returnChain
  chain.order = returnChain
  chain.limit = returnChain
  chain.range = returnChain
  chain.filter = returnChain
  chain.match = returnChain
  chain.not = returnChain
  chain.or = returnChain
  chain.contains = returnChain
  chain.containedBy = returnChain
  chain.overlaps = returnChain

  // Terminal calls: resolve the chain
  chain.single = terminal
  chain.maybeSingle = terminal
  chain.then = jest.fn((resolve: (val: unknown) => void) =>
    Promise.resolve({ data: resolvedValue, error: null }).then(resolve)
  )

  return {
    from: jest.fn().mockReturnValue(chain),
    chain,
  }
}

The key insight is that returnChain always returns the same chain object. No matter how long the query chain is, every method in it is a mock you can make assertions against. The terminal calls (.single(), .maybeSingle(), and .then() for bare awaits) are where you inject the resolved value or error.

Data Factories Keep Tests Readable

The other pattern that makes a real difference is factory functions for test data. Instead of building raw objects inline, you write a function with typed defaults and override only what the test cares about.

// tests/helpers/factories/profile.factory.ts
import { Profile } from '../../../src/types'

export function createMockProfile(overrides: Partial<Profile> = {}): Profile {
  return {
    id: 'user-uuid-1',
    updated_at: '2024-01-01T00:00:00Z',
    username: 'testuser',
    full_name: 'Test User',
    avatar_url: null,
    subscription_tier: 'free',
    ...overrides,
  }
}

Every field has a sensible default. The test only specifies the fields that are relevant to what it's testing.

What Tests Actually Look Like

Put it together and your tests become precise and readable:

// tests/services/profile-service.test.ts
import { ProfileService } from '../../src/services/profile-service'
import { createSupabaseChainMock } from '../helpers/supabase-chain-mock'
import { createMockProfile } from '../helpers/factories/profile.factory'

describe('ProfileService', () => {
  describe('getProfile', () => {
    it('returns a profile for a given user', async () => {
      const mockProfile = createMockProfile({ username: 'dusty' })
      const { from, chain } = createSupabaseChainMock(mockProfile)
      const mockClient = { from } as any

      const service = new ProfileService(mockClient)
      const result = await service.getProfile('user-uuid-1')

      expect(result).toEqual(mockProfile)
      expect(from).toHaveBeenCalledWith('profiles')
      expect(chain.eq).toHaveBeenCalledWith('id', 'user-uuid-1')
      expect(chain.single).toHaveBeenCalled()
    })

    it('throws when the query fails', async () => {
      const dbError = { message: 'Permission denied', code: '42501' }
      const { from } = createSupabaseChainMock(null, dbError)
      const mockClient = { from } as any

      const service = new ProfileService(mockClient)

      await expect(service.getProfile('user-uuid-1')).rejects.toEqual(dbError)
    })
  })
})

You get exact assertions on which table was queried, which filters were applied, and which terminal call was used. The error case requires no additional setup beyond passing an error to createSupabaseChainMock. Tests run in milliseconds with no network calls.

Auth is Different

The Supabase auth API doesn't use the fluent query builder, so it needs a different approach. Auth methods return promises directly, which means you can mock them with straightforward jest functions rather than chainable mocks.

function createMockAuthClient(overrides: Record<string, jest.Mock> = {}) {
  return {
    auth: {
      signInWithPassword: jest.fn(),
      signUp: jest.fn(),
      signOut: jest.fn(),
      getSession: jest.fn(),
      ...overrides,
    },
  } as any
}

it('returns session data on success', async () => {
  const mockSession = { user: { id: 'user-uuid-1' }, access_token: 'token' }
  const mockClient = createMockAuthClient({
    signInWithPassword: jest.fn().mockResolvedValue({
      data: { session: mockSession },
      error: null,
    }),
  })

  const service = new AuthService(mockClient)
  const result = await service.signIn('test@example.com', 'password123')

  expect(result.session).toEqual(mockSession)
  expect(mockClient.auth.signInWithPassword).toHaveBeenCalledWith({
    email: 'test@example.com',
    password: 'password123',
  })
})

Two different mock patterns for two different parts of the Supabase client. The chainable mock for database queries, plain jest mocks for auth. Both use dependency injection, so neither requires touching module-level mocks.

RevenueCat Testing

The starter also includes a full RevenueCat subscription system (Free, Pro, Premium) and test coverage for it. RevenueCat's SDK isn't chainable so the approach is module-level mocking, but scoped and typed correctly:

jest.mock('react-native-purchases', () => ({
  __esModule: true,
  default: {
    setLogLevel: jest.fn(),
    configure: jest.fn(),
    logIn: jest.fn(),
    logOut: jest.fn(),
    getOfferings: jest.fn(),
    purchasePackage: jest.fn(),
    restorePurchases: jest.fn(),
    getCustomerInfo: jest.fn(),
  },
  LOG_LEVEL: { ERROR: 'ERROR' },
}))

import Purchases from 'react-native-purchases'
const mockPurchases = Purchases as jest.Mocked<typeof Purchases>

With helper functions to build realistic CustomerInfo and Offering shapes, the tests cover all three tier states (free, pro, premium), the package-not-found error case, and restore flows.

When to Use Each Approach

Chainable mock helper: unit testing individual service methods. Fast, precise, no external dependencies.

Auth mock pattern: testing auth flows at the service layer where promise-based methods are the surface area.

MSW or a real Supabase test instance: integration tests that need to exercise multiple layers together, or tests that verify RLS policies are behaving correctly. These are slower but test things the unit approach can't.

The three approaches are complementary. The starter uses the first two. The third is documented in docs/supabase-setup.md for when you need it.

The Repo

The full starter is at github.com/Dusttoo/react-native-expo-supabase-starter.

It includes everything covered here plus the full app: Expo Router with file-based navigation, Supabase auth with session persistence via AsyncStorage, a paywall screen with package selection and restore purchases, a profile screen with tier-aware UI, and setup docs for both Supabase and RevenueCat.

The chainable mock helper, auth mock pattern, and factory functions are all in tests/helpers/ and are designed to be copied directly into your own project. The same testing patterns power the breeding records and genetics apps I build for clients. If the approach works for a multi-tenant app tracking animal pedigrees across collections, it'll work for yours.

I run Built By Dusty, a software studio that builds mobile apps, custom storefronts, and breeder websites for animal breeders and small businesses. If you're building a mobile app and want a senior engineer who has shipped one solo to the App Store, I'd like to hear from you.

I built a phenotype generator for crested gecko genetics. Here's how I modeled a hobby that can't agree on its own rules.

Dusty Mumphrey — Mon, 16 Mar 2026 11:00:00 +0000

Crested gecko morphs are one of the most commercially significant trait systems in the reptile hobby. Serious breeders run pairings worth thousands of dollars based on genetic predictions. And the community still actively debates how many of those traits actually work.

Breeders are manually constructing phenotype strings, getting them wrong, listing animals inaccurately, and making pairing decisions on bad information. Not because they're careless. Because the species is young, the documentation is inconsistent, and for some traits, scientific consensus simply doesn't exist yet.

I'm an active crested gecko breeder. I also built Geckistry, a full breeding management and e-commerce platform that runs my own operation. When I got to the genetics features, I had to solve a problem most developers never encounter: how do you build a rule engine when the domain experts disagree on the rules?

Here's what I built and how it works.

Code reference for this article: github.com/Dusttoo/reptile-genetics-engine

Why This Is Harder Than It Looks

Most people who've taken a biology class know the basics of Mendelian genetics. Dominant traits show in one copy. Recessive traits need two. Plug in the parents, predict the offspring. Done.

Crested geckos are not pea plants.

The system I built has to handle six distinct dominance patterns simultaneously:

export type DominancePattern =
  | 'DOMINANT'
  | 'RECESSIVE'
  | 'INCOMPLETE_DOMINANT'
  | 'CO_DOMINANT'
  | 'POLYGENIC'
  | 'FIXED'
  | 'UNKNOWN'

That last one is worth stopping on. UNKNOWN is not a missing field or an error state. It's a first-class value in the enum. The system explicitly says: we don't know how this trait inherits, so we'll treat it conservatively like recessive until evidence says otherwise.

That design decision came directly from the hobby. There are traits breeders have been working with for years where the inheritance mechanism is still genuinely unclear. Pretending the system knows something it doesn't would produce confident wrong answers. UNKNOWN produces honest uncertain ones.

Polygenic traits go further. For those, Mendelian math gets thrown out entirely and replaced with probability estimates grounded in observation:

// Both parents express the trait
return { hom: 0.30, het: 0.50, absent: 0.20 }

// One parent expresses the trait
return { hom: 0.10, het: 0.40, absent: 0.50 }

// Neither parent expresses the trait — still possible
return { hom: 0.02, het: 0.08, absent: 0.90 }

These numbers are not derived from theory. They're priors built from observation, designed to be corrected as real breeding data accumulates. More on that in a moment.

The Data Model

The genetic rule system lives in a set of database tables, not in application code. This was a deliberate architectural choice. The hobby's understanding of crested gecko genetics is still evolving. Hardcoding rules means touching code every time consensus shifts. Keeping rules in the database means a schema migration and a data update.

The core tables:

CREATE TABLE alleles (
  gene_locus_code   TEXT NOT NULL UNIQUE,  -- e.g., "LW", "PH", "y"
  common_name       TEXT NOT NULL,          -- e.g., "Lilly White", "Phantom", "Yellow"
  trait_category    allele_trait_category[] NOT NULL,
  dominance_pattern dominance_pattern NOT NULL,
  allele_notations  TEXT[],                -- e.g., ["LW+", "lw"]
  notes_evidence    TEXT,
  identification_tips TEXT,
  historical_notes  TEXT
);

CREATE TABLE allele_relationships (
  source_allele_id  UUID REFERENCES alleles(id),
  target_allele_id  UUID REFERENCES alleles(id),
  relationship_type allele_relationship_type NOT NULL,
  notes             TEXT,
  UNIQUE(source_allele_id, target_allele_id, relationship_type)
);

CREATE TABLE gecko_alleles (
  gecko_id      UUID REFERENCES geckos(id),
  allele_id     UUID REFERENCES alleles(id),
  is_homozygous BOOLEAN DEFAULT false,
  visibility    TEXT DEFAULT 'visual',   -- breeder override
  UNIQUE(gecko_id, allele_id)
);

The relationship_type enum is where the interaction rules live. It currently supports SUPPRESSES, REQUIRES, ENHANCES, LETHAL_HOMOZYGOUS, and INTERACTS_WITH. Adding a new relationship type as community consensus forms is a single ALTER TYPE migration, not a code change.

The visibility field on gecko_alleles is the pragmatic escape hatch. When a gecko's visual expression doesn't match what the genetics predict, a breeder can set it to possible_het, 100%_het, not_visual, or unknown. This bypasses the genetic classification logic entirely for that gecko-allele pair:

if (visibility === 'not_visual') {
  suppressedTraits.push({ name, suppressedBy: 'manual override', ... })
  continue  // Skip genetics logic entirely
}

The system knows when to get out of the way.

The Learning Layer

The static rule tables handle what we know. A separate set of tables handles what we're learning.

Every time a breeder records what an offspring actually turned out to be, a PostgreSQL trigger fires and updates allele_cross_statistics. The confidence score on any given prediction is calculated as:

-- Confidence: 1 - 1/(1 + n/10)
-- Reaches ~0.5 at 10 samples, ~0.75 at 30 samples
confidence_score = 1.0 - (1.0 / (1.0 + total_offspring::DECIMAL / 10.0))

At zero samples, the system uses pure Mendelian math. As breeders record real offspring, observed ratios gradually displace theoretical ones. The confidence score reflects how much the prediction is grounded in actual data versus theory. A sigmoid that reaches roughly 0.5 at 10 samples and 0.75 at 30 is a reasonable curve for a species where most breeders are working with small sample sizes.

Three Cases That Show the Depth

Fatal Combinations: Lilly White

Lilly White is one of the most popular crested gecko morphs. It's also one of the most important to handle correctly: breeding two Lilly Whites together produces offspring where the homozygous form is lethal. Double Lilly White animals do not survive.

The rule is encoded as a LETHAL_HOMOZYGOUS relationship in allele_relationships. When the prediction engine encounters a pairing where both parents carry Lilly White, it checks for this relationship and surfaces a warning:

if (blendedRates.hom > 0) {
  const lethalRels = relationships.filter(
    r => r.relationship_type === 'LETHAL_HOMOZYGOUS' &&
         (r.source_allele_id === alleleId || r.target_allele_id === alleleId)
  )
  for (const rel of lethalRels) {
    warnings.push(
      `Homozygous ${alleleName} is lethal.${rel.notes ? ` ${rel.notes}` : ''} ` +
      `Offspring showing HOM for this allele will not survive.`
    )
  }
}

The warning is non-blocking by design. It surfaces into a warnings: string[] in the result rather than throwing an error. The system does not prevent a breeder from entering a biologically impossible combination. It surfaces the consequence clearly and lets them decide. That's the right call for a tool used by people who know the domain.

True Allelic Traits: Sable and Cappuccino

Sable and Cappuccino are two distinct crested gecko morphs that occupy the same gene locus. An animal can carry one or the other, but not both. In classical genetics, these are called allelic variants of the same locus.

The current data model doesn't enforce a hard shared-locus constraint. Each allele has its own row. The mechanism for "can't have both" is a SUPPRESSES relationship in allele_relationships. The name engine checks for this relationship when both alleles are present on the same gecko:

const presentAlleleIds = new Set(safeAlleles.map(a => a.allele_id))

for (const rel of relationships) {
  if (!presentAlleleIds.has(rel.source_allele_id)) continue
  if (!presentAlleleIds.has(rel.target_allele_id)) continue
  if (rel.relationship_type === 'SUPPRESSES') {
    suppressionMap.set(rel.target_allele_id, sourceName)
  }
}

The suppressed allele moves to suppressedTraits rather than appearing in the phenotype name. This is a reasonable approximation for "one masks the other" and it correctly handles the practical outcome for breeders, even without a hard locus constraint at the data layer. A future schema iteration could enforce this more strictly. The current model gets the phenotype output right, which is what matters for now.

Polygenic Traits: Where the Hobby Runs Out of Answers

Polygenic traits are the genuinely messy case, and the system handles them by not pretending otherwise.

When dominancePattern === 'POLYGENIC', the inheritance rate calculator switches to a completely different function. And in the phenotype name, polygenic traits get soft language that reflects the actual state of knowledge:

case 'POLYGENIC':
  return {
    type: 'visible',
    trait: {
      inheritanceLabel: isHom ? 'Strong' : 'Present',
      genotypeNotation: null,  // No HOM/HET notation for polygenic traits
    }
  }

There's no PH/PH style genotype notation for polygenic traits. The meaningful output is how strongly the trait expresses, not how many copies the animal carries. That distinction isn't meaningful for polygenic inheritance given where the hobby's understanding currently sits. The system outputs "Strong" or "Present" rather than inventing precision it doesn't have.

Input and Output

Here's a concrete example of what the engine produces. The input is a set of gecko_alleles records:

Allele	Code	Pattern	Homozygous
Yellow	`y`	DOMINANT	false
Harlequin	`HR`	DOMINANT	true
Empty Back	`EB`	RECESSIVE	false
Phantom	`PH`	RECESSIVE	false

What each allele resolves to:

y/+ is dominant and visible. It becomes "Yellow Based" in the phenotype name.
HR/HR is dominant and homozygous. It becomes "Harlequin." Dominant traits don't use HOM/HET labels.
EB/+ is recessive and heterozygous. It's not visible but gets carried as "Het Empty Back."
PH/+ is recessive and heterozygous. Same treatment: "Het Phantom."

Output:

phenotypeName:    "Yellow Based Harlequin"
genotypeNotation: "EB/+ PH/+"
carriedTraits:    ["Het Empty Back (EB/+)", "Het Phantom (PH/+)"]
visibleTraits:    ["Yellow (BASE_COLOR)", "Harlequin (PATTERN_MODIFIER, Homozygous)"]
warnings:         []

Now add Lilly White as a heterozygous (LW/+, INCOMPLETE_DOMINANT):

phenotypeName:    "Yellow Based Lilly White Harlequin"
genotypeNotation: "LW/+ EB/+ PH/+"
visibleTraits:    [..., "Lilly White (SPECIAL_TRAIT, Het)"]

Change Lilly White to homozygous (LW/LW):

phenotypeName:    "Yellow Based Super Lilly White Harlequin"
inheritanceLabel: "Super"
warnings:         ["⚠ Lilly White is lethal when homozygous (Super form)"]

The System Also Runs in Reverse

The phenotype name engine goes from genotype to phenotype string. There's a companion system that goes the other direction.

phenotype-inference.ts takes what a breeder observes and works backward to infer genotype. It generates questions structured by dominance pattern: if a recessive trait is visible, the animal must be homozygous and the system says so with high confidence. If a dominant trait is absent, it's absent with certainty. If a polygenic trait is visible, zygosity is genuinely hard to determine and the system says that too.

It also maintains a list of het markers: visual characteristics that indicate carrier status even when the underlying trait isn't expressed. In crested geckos, only one has been proven:

const KNOWN_HET_MARKERS = [
  {
    characteristicName: 'Blush',
    description: 'Red/rosey colored cheeks — proven indicator of het red base (r/+). ' +
                 'One of the few reliable het markers in the species.',
    associatedAlleleCodes: ['r'],
    isProvenMarker: true,
  }
]

The list being intentionally small, and the isProvenMarker boolean distinguishing proven from suspected, is a design decision worth naming. The system is honest about what the hobby doesn't know yet.

What It Unlocked

Before this system, constructing an accurate phenotype string meant holding the full rule set in your head. You'd have to remember the notation conventions, know which traits are dominant versus recessive, correctly apply every interaction, and not make a mistake across 50 animals with a dozen trait combinations each. I built this for my own collection first, and it immediately changed how I manage and list animals in Geckistry.

Right now the generator is live in Geckistry for my personal use. I'm in the process of bringing the same logic into ReptiDex, where it will be available to the broader keeper community. The same genetics architecture also powers the breeding records and genetics apps I build for other breeders. I'm also expanding the covered species with help from experts in other communities. Crested geckos have an unusually complex and well-documented morph library, which made them the right place to build and prove the system. But the architecture was designed from the start to support any species where the genetics are well enough understood to encode.

The deeper payoff as it scales is the learning layer. Every clutch outcome a breeder records makes the system's predictions more accurate. Over time it becomes a live dataset of observed genetics across species, correcting its own priors as the hobby's understanding grows. For breed clubs and registries, this kind of verified genetic data is foundational. It's a core piece of the registry and pedigree platforms I build for organizations.

The full code reference for this system, including the schema, resolution logic, and confidence formula, is available on GitHub at reptile-genetics-engine.

I run Built By Dusty, a software studio that builds breeding records apps, breeder websites, and registry platforms for animal breeders. The genetics engine above is open source, and the production version of it is what powers every breeding platform I deliver to clients. You can read the full case studies to see what that looks like in practice. If you're working on a domain with complex, contested rule systems, or you're a breeder who wants tooling like this for your own operation, I'd like to hear from you.

I built and launched a mobile app in 3 months as a solo engineer. Here's exactly what happened.

Dusty Mumphrey — Fri, 13 Mar 2026 01:46:33 +0000

You breed reptiles. At any given time you're tracking weights, feeding schedules, clutch dates, pairing history, and morph genetics across dozens of animals. Every tool that exists was built for something adjacent but not quite right. Spreadsheets, generic pet apps, pen and paper. So you build the thing that doesn't exist yet.

That's ReptiDex. Here's what three months of building it actually looked like.

The Problem

Reptile breeding is a data problem masquerading as a hobby.

Take a single clutch of ball pythons. You need to record two parents, each with their own morph genetics, lineage, and acquisition history. The clutch has a lay date, an expected hatch window, and an incubation temperature log. Each egg hatches separately. Each hatchling gets its own weight series. You're weighing at 30, 60, and 90 days to assess growth. Each animal that sells needs a buyer record and a price. The ones you keep get folded back into future pairings.

That's one clutch. A serious breeder might run 20 to 40 clutches in a single season across multiple species: ball pythons, leopard geckos, crested geckos, monitors. Each species has different care requirements and genetic notation systems.

Spreadsheets have the right instinct but the wrong tool. You can model anything in Excel if you're willing to engineer it yourself, but you'll re-engineer it every year as your operation grows. The existing mobile apps treat reptiles like fish. Something you keep, maybe photograph, and occasionally feed. None of them model breeding pairs, lineage graphs, or clutch-level weight tracking. None of them handle multi-user collections where a partner also needs access.

ReptiDex was built to solve these specific, concrete data problems. Every feature traces back to something that was genuinely annoying to do in a spreadsheet. It's also the foundation behind the breeding records and genetics software I build for other breeders through my studio.

The Stack

React Native with Expo. I needed iOS and Android from day one. The breeder market splits fairly evenly across platforms and launching single-platform would have halved my addressable market. Expo's managed workflow meant I could focus on product rather than Xcode configuration and native module wrangling. The tradeoff is occasional framework constraints, but for a solo launch the velocity win is decisive.

Supabase. PostgreSQL with built-in auth, storage, and row-level security. The appeal wasn't just a managed Postgres instance. It was RLS, which solved multi-tenancy at the database layer rather than the application layer. Every query is automatically scoped to the authenticated tenant. It's not magic, but it removes an entire class of authorization mistakes from the codebase.

TanStack Query and Zustand. Server state and client state are different problems that benefit from different tools. TanStack Query handles async fetching, caching, and background refresh. Zustand manages UI state that doesn't need to live on the server. Keeping these concerns separated made the codebase easier to reason about as features grew.

RevenueCat. Subscription management is not something you want to build yourself. Receipt validation across iOS and Android is a rabbit hole. RevenueCat abstracts both payment flows into a single API and gives you a real-time subscription dashboard. The three-tier model (Free, Pro at $4.99/month, Premium at $9.99/month) was straightforward to implement once it was wired in.

The Build

I started with the mobile app and worked from the inside out. Authentication first, then the dashboard. I spent more time on that dashboard than anything else in the early build, because it's where users live. Reptile keepers are using this app in their reptile rooms with dirty hands and an animal that needs attention. The dashboard needed quick actions. Logging a feeding, recording a shedding, marking a hatching. These had to be two taps, not five. Friction at the feature level means the app stops getting used.

Once I was happy with the core husbandry layer, I moved into the breeding side. This was the part I personally needed most as an active crested gecko breeder, and I built it the way I build my own workflows: iteratively, inside my own operation. If a feature had too much friction, I felt it before any beta tester did. That's how the breeding pairs, clutch records, and lineage system got to where they are. Not from a spec. From actual use.

After the mobile app was solid, I replicated the full experience as a web version. The core logic lived in a shared service layer from the beginning because I knew cross-platform was the goal. The UI needed platform-specific adjustments, but the business logic underneath was built once.

The final push before App Store submission was security hardening, extensive usability testing, and polish. Multi-tenant architecture has real attack surface if you're not deliberate about it, and I'd built with security in mind throughout. But I wasn't going to ask people to pay for something I hadn't pressure-tested. I brought in beta testers to find the friction I'd gone blind to after three months of building.

The hardest stretch was the end. Every time I thought it was ready, another critical bug surfaced that had been sitting quietly in the codebase waiting for exactly the wrong moment. Some of that was real. Some of it was my own perfectionism making it hard to call something done when I knew people would be paying for it. I was building on evenings and weekends around my day job the entire time. When I wasn't working, I was working on ReptiDex.

One Decision That Paid Off

The pedigree resolution system is the most technically interesting problem ReptiDex solved, and getting it right early paid off throughout the rest of the build. It's also the core of the registry and pedigree platform architecture I offer to breed clubs.

Here's the domain problem: a reptile's pedigree includes parents, grandparents, and great-grandparents. In a single-breeder app this is simple. Every animal in the tree lives in the same database tenant. But ReptiDex supports cross-tenant lineage linking. If you bred your female with another breeder's male, that sire lives in their collection, not yours. The two tenants are separate. The sire's owner controls its visibility.

This means "who is this animal's sire" is not a simple foreign key lookup. It's a resolution problem with four possible outcomes for any given parent slot:

1. A lineage_links row exists + linked animal is accessible  → linked-live
2. A lineage_links row exists, but the source record is gone → linked-archived (snapshot)
3. No lineage_links row, but sire_id/dam_id is set           → local
4. None of the above                                         → unknown

Here's the actual resolution function:

async function resolveParent(
  currentAnimal: AnimalWithPedigree,
  role: 'sire' | 'dam'
): Promise<ResolvedParent> {
  // 1. Check lineage_links for a cross-tenant link
  const link = await LineageLinksService.getLinkForRole(currentAnimal.id, role);

  if (link) {
    if (link.status === 'pending') {
      return { source: 'linked-pending', snapshot: link.snapshot, linkId: link.id };
    }

    if (link.status === 'approved') {
      if (!link.linked_animal_id) {
        return { source: 'linked-archived', snapshot: link.snapshot, linkId: link.id };
      }

      const { data: linkedAnimal, error } = await supabase
        .from('animals')
        .select(ANIMAL_SELECT)
        .eq('id', link.linked_animal_id)
        .single();

      if (!error && linkedAnimal) {
        return {
          source: 'linked-live',
          animal: linkedAnimal,
          linkId: link.id,
          isSameTenant: linkedAnimal.tenant_id === subjectTenantId,
          snapshot: link.snapshot,
        };
      }

      return { source: 'linked-archived', snapshot: link.snapshot, linkId: link.id };
    }
  }

  // 2. Fall back to within-tenant sire_id/dam_id
  const parentId = role === 'sire' ? currentAnimal.sire_id : currentAnimal.dam_id;
  if (!parentId) return { source: 'unknown' };

  const { data: localAnimal } = await supabase
    .from('animals')
    .select(ANIMAL_SELECT)
    .eq('id', parentId)
    .single();

  return localAnimal ? { source: 'local', animal: localAnimal } : { source: 'unknown' };
}

The key design decision is that lineage_links takes strict priority over sire_id/dam_id. If a cross-tenant link exists, we always use it, even if a local FK is also set. This keeps the source of truth unambiguous and prevents a pedigree from silently splitting across two resolution paths.

This function also handles a subtle real-world case: what happens when another breeder deletes an animal you've linked to? Instead of a broken reference, linked-archived kicks in and renders from the immutable snapshot stored at link-creation time. The pedigree stays complete even if the external record disappears.

One Decision That Bit Me

Offline mode was the right product decision and the wrong scope decision for a three-month launch.

Breeders work in enclosure rooms, outbuildings, and basements with poor signal. An app that requires connectivity to log a feeding or record a weight fails at exactly the moment it's needed. So I built an offline-first architecture with a sync queue. Mutations go into a local queue, apply optimistically, and sync when connectivity returns.

The implementation works. But it added meaningful complexity to every feature. Every mutation had to be written twice. Once for the optimistic local update, and once for the server sync. Edge cases multiplied: what happens when the same record is edited offline on two devices? What happens when a sync fails midway? Testing sync behavior in Jest required simulating network conditions, which is unpleasant.

The smarter move would have been to launch without it, ship to a beta group, and measure how often the connectivity issue actually came up. It might have been less common than assumed. Offline support could have been a v1.1 feature built from real user feedback rather than projected pain. The technical work wasn't wasted, but it extended the build more than it needed to for day one.

The Launch

App Store submission was relatively smooth, with two exceptions. It got rejected twice. Once for missing terms of use in the description, and once for a small metadata issue. Neither was a technical problem. Both were fixable within a day. The review process taught me that the App Store will find the things you didn't think to check. Submit earlier than you think you need to.

The moment it went live, I started sharing in the reptile groups I was already part of. These weren't cold audiences. I was a member of these communities, not a marketer dropping a link. Shortly after launch I partnered with a reptile enclosure company for a giveaway, which gave the early momentum a real push.

In the days after launch I was watching Sentry, PostHog, and my own custom analytics dashboard more than I'd like to admit. I wrote a full case study breaking down all three of my production projects if you want the complete picture.

When that first paid subscriber came in, I was over the moon. It instantly made every late night worth it. I had shipped products before, for fintech companies and healthcare platforms, as a cog in someone else's pipeline. This was different. The planning, the business model, the branding, the code, the users. All of it was mine. That subscriber wasn't a metric in someone else's dashboard. It was proof that something I built from scratch, alone, solved a real problem well enough that a stranger handed over their credit card for it.

Within the first nine days: 50 paid subscribers and 200 animals tracked.

Those numbers matter because they weren't free users. These were paying customers who converted fast. That's confirmation that the problem was real and the execution was good enough to earn trust on day one.

What I'd Do Differently

Narrower first scope. The feature set that shipped was comprehensive: weight tracking, breeding pairs, clutches, lineage graphs, care guides, import/export, multi-user collections, and notifications. It all works. But a leaner v1 built around weight tracking and basic animal records would have shipped faster and generated earlier signal about what the market actually paid for.

Offline mode as v1.1. Covered above. Ship when you have evidence of the problem, not anticipation of it.

Earlier App Store submission. The review process surfaces things you didn't think to check: edge cases in permission request flows, metadata requirements, and small policy details that can kick off another review cycle. Getting into the queue earlier reduces the blast radius of late discoveries.

What's Next

ReptiDex is a live product and I'm still building it. The backlog is now shaped by what users are actually doing inside the app, which turns out to be a better requirements document than anything written before launch.

I also run Built By Dusty, a software studio that builds custom breeding software, breeder websites, and sales platforms for animal breeders. The same architecture behind ReptiDex is what gets adapted for other species and other operations. If you're a breeder who wants to try the app, or a founder sitting on a domain with messy, specific data requirements that off-the-shelf tools can't handle, I'd like to hear from you.