Database Architecture for BrickTracker 1.4

Frederik Baerentsen included in Development

2026-01-22 7957 words 38 minutes

Contents

BrickTracker 1.4 represents a significant evolution in how we track LEGO collections. While previous versions focused exclusively on tracking complete sets, version 1.4 introduces support for individual minifigures and individual parts—loose pieces that exist outside of any specific set. I’ll try and provides a technical deep-dive into the database architecture changes that made this possible, walking through seven designed migrations and the real-world problems they solve.

Why This Feature Matters: The Community Request

This wasn’t just a nice-to-have feature. It was one of the most requested capabilities from the BrickTracker community. Two GitHub issues (#68 and #69) captured the frustration collectors were experiencing.

Issue #68: “Ability to add parts” was opened in March 2025 by a user who noted: “It seems like parts can only be added via sets. An independent part management would be nice. For example, adding parts to different part collections.”

The issue gained traction. One user commented: “bulk parts inventory, IMO is just as important as set inventory and is just as much part of any Lego collection as sets are.” Another user who discovered BrickTracker said: “I am moving away from Rebrickable as this is much speedier while browsing. But I am missing parts…”

Issue #69: “Ability to add minifigures” highlighted a specific pain point: collectible minifigure series.

One frustrated user tried everything to add their loose R2-D2 and C-3PO minifigures from Rebrickable’s database (fig-010443 and fig-002514), but couldn’t figure it out. My response was disappointing for them: “Those individual figures can’t be added right now. They will have to be added as part of a set.” Their reply summed it up: “Okay… thanks for working on it! I will just wait on those and seems messy to add a kit I do not have.”

The Project Scope Challenge

BrickTracker’s project scope documentation explicitly stated what the application was “Not Ideal For,” including:

Custom build creators (MOCs) - Doesn’t track My Own Creations or custom builds

The limitations page was even more specific:

Building & Customization:

No custom build tracking

No virtual building or 3D modeling

Parts can only be added within existing sets

This created a paradox: collectors wanted to track bulk parts for MOC planning (“Do I have enough 2x4 red bricks for this custom build?”), but the scope explicitly excluded this use case.

After multiple users requested these features, I wanted to reconsider the project scope.

The breakthrough realization: By implementing individual parts with customizable lots, we could actually support basic MOC inventory tracking without building a full MOC management system. A user could create a lot named “Death Star MOC - In Progress” and track all the parts they’ve gathered for that custom build. This wasn’t the original goal, but it became a happy side effect of the architectural decisions I made.

When version 1.4 is released, the scope documentation will need updating. MOC inventory is now technically possible through the part lots system, even if we’re not building a full MOC design suite. Rebrickable’s API specifically does not support MOC inventory download.

The Challenge

Before 1.4, BrickTracker’s data model was simple but inflexible:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


┌─────────────────────┐
│  bricktracker_sets  │
│  (user instances)   │
├─────────────────────┤
│ id (UUID)           │──┐
│ set (foreign key)   │  │    Links to Rebrickable reference data
│ description         │  │    for set information (name, year, theme)
│ storage             │  │
│ purchase_date       │  │   ┌────────────────────┐
│ purchase_location   │  │   │  bricktracker_     │
│ purchase_price      │  └──▸│  set_owners        │
└─────────────────────┘      │  (many-to-many)    │
         │                   ├────────────────────┤
         │                   │ id (UUID)          │
         │                   │ owner_<uuid> BOOL  │ Dynamic columns
         │                   │ owner_<uuid> BOOL  │ added at runtime
         │                   └────────────────────┘
         │
         │ Foreign key constraint
         ▼
┌─────────────────────┐
│ rebrickable_sets    │
│ (reference data)    │
├─────────────────────┤
│ set (primary key)   │ Example: "75192-1" (Millennium Falcon)
│ name                │
│ year                │
│ theme_id            │
│ num_parts           │
└─────────────────────┘

This architecture worked great for complete sets. Each set instance (bricktracker_sets) was tied to official LEGO set data from Rebrickable (rebrickable_sets). The metadata system allowed multiple people to co-own sets, tag them, and assign statuses. This was perfect for families and shared collections.

But real-world LEGO collecting doesn’t fit into neat set-based boxes:

Collectible minifigures: A loose Darth Vader minifigure from a yard sale (no set number)
Bulk part purchases: A bag of assorted 2x4 bricks from Bricklink in various colors
Part-out remains: You built one set from a box of three, and the extra parts are sitting in a bin
MOC planning: Parts you’re accumulating for a custom creation
Spares and duplicates: All those extra pieces from set builds over the years

These items don’t belong to any specific set, yet collectors still want to track them: storage locations, purchase information, ownership (in shared collections), tags for organization, and problem tracking for missing/damaged pieces.

The question became: How do I extend this elegant set-based architecture to support unaffiliated items without creating a completely separate system?

Design Goals

The database architecture for version 1.4 needed to satisfy several competing requirements:

1. Reuse Existing Infrastructure

The metadata tables (bricktracker_set_owners, bricktracker_set_tags, bricktracker_set_statuses) and reference tables (bricktracker_metadata_storages, bricktracker_metadata_purchase_locations) already exists. Rather than duplicating this infrastructure, I wanted to extend it to serve all entity types.

Why this matters: Maintenance complexity. If we had separate individual_part_owners, individual_part_tags, etc., any change to the metadata system would need to be replicated across multiple tables. By consolidating, we maintain a single source of truth.

2. Maintain Referential Integrity

Individual items still need to reference Rebrickable’s data. A loose 2x4 brick is still part number “3001” in a specific color, and that part has properties (name, category, material) stored in rebrickable_parts. We couldn’t lose this connection.

3. Support Complex Queries

Users need to ask questions like:

“Show me all red parts across my entire collection (sets AND individual parts)”
“What’s stored in bin A-3?”
“Which items did I buy from seller X?”
“What parts am I missing across everything I own?”

These queries need to be fast, even with thousands of parts.

4. Allow Flexible Organization

Some parts come individually. Others come in bulk lots. The schema needed to support both standalone parts and grouped collections without forcing one model or the other.

5. Track Completeness

For individual minifigures, collectors care about completeness. If you buy a used minifigure that’s missing its lightsaber, you want to track that. The part-level tracking from sets needed to extend to individual minifigures.

6. Preserve Backward Compatibility

Existing set tracking functionality couldn’t regress. The migration path from 1.3 to 1.4 had to be seamless, with no data loss and no manual intervention required.

The Solution: Seven Migrations

I didn’t start out with seven migrations in mind, but during developement and testing seven migrations emerged. Rather than attempting a single massive schema change, I went with the seven sequenced migrations (0021-0027). Each migration had a focused purpose and could be tested independently. Let’s walk through each one in detail.

Migration 0021: Foundation Tables

Purpose: Establish the core tables for individual minifigures and individual parts.

This migration created four new tables that mirror the existing set-based architecture:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54


                     ┌──────────────────────────────┐
                     │  rebrickable_minifigures     │
                     │  (reference data)            │
                     ├──────────────────────────────┤
                     │ figure (primary key)         │  Example: "fig-012345"
                     │ name                         │  Example: "Darth Vader"
                     │ num_parts                    │  How many pieces make up this fig
                     └──────────────────────────────┘
                                   ▲
                                   │
                                   │ Foreign key: links to reference data
                                   │
┌──────────────────────────────────┴─────────────────────────────┐
│  bricktracker_individual_minifigures                           │
│  (user instances of loose minifigures)                         │
├────────────────────────────────────────────────────────────────┤
│ id (UUID, primary key)                                         │  Unique instance ID
│ figure (foreign key) ───────────────────────────────┐          │  Which minifigure type
│ quantity                                            │          │  How many of this fig
│ description                                         │          │  User notes
│ storage (foreign key) ────────────┐                 │          │  Where it's stored
│ purchase_date                     │                 │          │  When acquired (Unix timestamp)
│ purchase_location (foreign key) ──┼─────────┐       │          │  Where purchased
│ purchase_price                    │         │       │          │  How much paid
└───────────────────────────────────┼─────────┼───────┼──────────┘
                                    │         │       │
                                    ▼         ▼       │
              ┌──────────────────────┐  ┌──────────────────────┐
              │  bricktracker_       │  │  bricktracker_       │
              │  metadata_storages   │  │  metadata_purchase_  │
              │                      │  │  locations           │
              │  (Reused from sets!) │  │  (Reused from sets!) │
              └──────────────────────┘  └──────────────────────┘
                                                       │
         ┌─────────────────────────────────────────────┘
         │
         │ Foreign key: minifigure instance
         ▼
┌────────────────────────────────────────────────────────────────┐
│  bricktracker_individual_minifigure_parts                      │
│  (constituent parts of each minifigure)                        │
├────────────────────────────────────────────────────────────────┤
│ id (foreign key to minifigure)                                 │  Which instance
│ part (foreign key)                                             │  Part number
│ color (foreign key)                                            │  Color ID
│ spare                                                          │  Is this a spare part?
│ quantity                                                       │  How many expected
│ element                                                        │  LEGO element ID (for ordering)
│ rebrickable_inventory                                          │  Rebrickable inventory ID
│ missing (count)                                                │  How many are missing
│ damaged (count)                                                │  How many are damaged
│ checked (boolean)                                              │  Has this been verified?
│ PRIMARY KEY (id, part, color, spare)                           │  Composite key
└────────────────────────────────────────────────────────────────┘

Design Decision: Composite Primary Key

The parts table uses (id, part, color, spare) as a composite primary key. This might seem overly complex, but it solves a real problem: spare parts.

Many LEGO sets include spare tiny parts (1x1 plates, clips, etc.). Rebrickable’s inventory data marks these with spare=1. A minifigure might have:

1× part 64567 in Light Bluish Gray (regular)
2× part 64567 in Light Bluish Gray (spare)

Without the spare column in the primary key, we’d have to store these as a single row with confusing semantics. With it, they’re naturally separate rows.

Design Decision: Problem Tracking Fields

The missing, damaged, and checked fields extend the set-based problem tracking to individual minifigures. This enables workflows like:

Buy a used minifigure lot on Bricklink
Add them to BrickTracker
Check each one piece-by-piece, marking missing parts
The “Problems” view now shows what you need to order

Individual minifigure parts tracking interface

The Individual Parts Table

Individual parts are simpler than minifigures as they don’t have constituent pieces to track:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


┌────────────────────────────────────────────────────────────────┐
│  bricktracker_individual_parts                                 │
│  (loose parts not belonging to any set)                        │
├────────────────────────────────────────────────────────────────┤
│ id (UUID, primary key)                                         │  Unique instance ID
│ part (foreign key to rebrickable_parts)                        │  Part number (e.g., "3001")
│ color (foreign key)                                            │  Color ID (e.g., 5 = red)
│ quantity                                                       │  How many of this part/color
│ description                                                    │  User notes
│ storage (foreign key)                                          │  Where stored
│ purchase_date                                                  │  When acquired
│ purchase_location (foreign key)                                │  Where purchased
│ purchase_price                                                 │  Cost
└────────────────────────────────────────────────────────────────┘

At this point, individual parts existed but had no problem tracking fields. That comes later in migration 0022.

Indexes: Planning for Performance

Migration 0021 also created eight indexes. Indexes aren’t glamorous, but they’re critical for performance:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


-- Lookup all instances of a specific minifigure type
CREATE INDEX idx_bricktracker_individual_minifigures_figure
ON bricktracker_individual_minifigures(figure);

-- "What's in storage bin A-3?"
CREATE INDEX idx_bricktracker_individual_minifigures_storage
ON bricktracker_individual_minifigures(storage);

-- "What did I buy from Bricklink last month?"
CREATE INDEX idx_bricktracker_individual_minifigures_purchase_location
ON bricktracker_individual_minifigures(purchase_location);

CREATE INDEX idx_bricktracker_individual_minifigures_purchase_date
ON bricktracker_individual_minifigures(purchase_date);

-- "Show me all minifigures with missing/damaged parts"
CREATE INDEX idx_bricktracker_individual_minifigure_parts_id_missing_damaged
ON bricktracker_individual_minifigure_parts(id, missing, damaged);

-- "Find all instances of part 3001 in red"
CREATE INDEX idx_bricktracker_individual_minifigure_parts_part_color
ON bricktracker_individual_minifigure_parts(part, color);

-- Similar indexes for individual_parts table...

Each index answers a specific user query quickly. Without these, even moderate collections (10,000+ parts) would become sluggish.

Migration 0022: Part Lots System

Purpose: Add “lots” as a way to group related individual parts, and extend problem tracking to individual parts.

The issue was that collectors often buy parts in batches:

A Bricklink order of 50 different parts for a MOC
A pick-a-brick cup from a LEGO store
A bag of parts from a yard sale
The leftovers after parting out a set

These aren’t random parts: they’re related. You bought them together, they might be stored together, they cost a specific total amount. Tracking them individually was possible, but cumbersome. Users wanted a way to say: “These 50 parts came from the same Bricklink order for $35.49 on 2024-12-15.”

Enter part lots: a logical container for grouping related parts.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35


┌────────────────────────────────────────┐
│  bricktracker_individual_part_lots     │
│  (grouping container for parts)        │
├────────────────────────────────────────┤
│ id (UUID, primary key)                 │
│ name                                   │  Custom title (e.g., "Bricklink Order #12345")
│ description                            │  User notes
│ created_date                           │  When the lot was created
│ storage (foreign key)                  │  Default storage for parts in this lot
│ purchase_location (foreign key)        │  Where acquired
│ purchase_date                          │  When acquired
│ purchase_price                         │  Total cost for the lot
└────────────────────────────────────────┘
                ▲
                │
                │ lot_id (nullable foreign key)
                │
┌───────────────┴────────────────────────┐
│  bricktracker_individual_parts         │
│  (MODIFIED via table recreation)       │
├────────────────────────────────────────┤
│ id (UUID, primary key)                 │
│ part (foreign key)                     │
│ color (foreign key)                    │
│ quantity                               │
│ description                            │
│ storage (foreign key)                  │
│ purchase_date                          │
│ purchase_location (foreign key)        │
│ purchase_price                         │
│ missing (NEW)                          │  Problem tracking
│ damaged (NEW)                          │  Problem tracking
│ checked (NEW)                          │  Verification status
│ lot_id (NEW, nullable foreign key)     │  Optional: part of a lot?
└────────────────────────────────────────┘

The SQLite Limitation: Table Recreation

SQLite doesn’t support ALTER TABLE ADD CONSTRAINT for foreign keys. If you need to add a column with a foreign key constraint to an existing table, you must:

Create a new table with the desired schema
Copy all data from the old table
Drop the old table
Rename the new table

Migration 0022 does exactly this, wrapped in a transaction for safety:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50


BEGIN TRANSACTION;

-- Create new table with lot_id column
CREATE TABLE "bricktracker_individual_parts_new" (
    "id" TEXT NOT NULL,
    "part" TEXT NOT NULL,
    "color" INTEGER NOT NULL,
    "quantity" INTEGER NOT NULL DEFAULT 1,
    "description" TEXT,
    "storage" TEXT,
    "purchase_date" REAL,
    "purchase_location" TEXT,
    "purchase_price" REAL,
    "missing" INTEGER NOT NULL DEFAULT 0,
    "damaged" INTEGER NOT NULL DEFAULT 0,
    "checked" BOOLEAN NOT NULL DEFAULT 0,
    "lot_id" TEXT,  -- NEW COLUMN
    PRIMARY KEY("id"),
    FOREIGN KEY("part", "color") REFERENCES "rebrickable_parts"("part", "color_id"),
    FOREIGN KEY("storage") REFERENCES "bricktracker_metadata_storages"("id"),
    FOREIGN KEY("purchase_location") REFERENCES "bricktracker_metadata_purchase_locations"("id"),
    FOREIGN KEY("lot_id") REFERENCES "bricktracker_individual_part_lots"("id") ON DELETE SET NULL
);

-- Copy existing data (explicit columns, not SELECT *)
INSERT INTO "bricktracker_individual_parts_new"
    (id, part, color, quantity, description, storage,
     purchase_date, purchase_location, purchase_price,
     missing, damaged, checked, lot_id)
SELECT
    id, part, color, quantity, description, storage,
    purchase_date, purchase_location, purchase_price,
    missing, damaged, checked, NULL  -- All existing parts have no lot
FROM "bricktracker_individual_parts";

-- Drop old table
DROP TABLE "bricktracker_individual_parts";

-- Rename new table
ALTER TABLE "bricktracker_individual_parts_new" RENAME TO "bricktracker_individual_parts";

-- Recreate indexes (they were dropped with the old table)
CREATE INDEX idx_bricktracker_individual_parts_part_color
ON bricktracker_individual_parts(part, color);
-- ... (additional indexes)

CREATE INDEX idx_individual_parts_lot_id
ON bricktracker_individual_parts(lot_id);

COMMIT;

Critical Bug Fix

The original version of this migration had a subtle but breaking bug:

1
2
3


-- BEFORE (causes "13 columns but 12 values were supplied" error)
INSERT INTO "bricktracker_individual_parts_new"
SELECT * FROM "bricktracker_individual_parts";

The problem: SELECT * returns 12 columns from the old table, but the new table has 13 columns (it added lot_id). SQLite requires an exact column match.

The fix: explicitly list columns and map them:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- AFTER (explicit column mapping)
INSERT INTO "bricktracker_individual_parts_new"
    (id, part, color, quantity, description, storage,
     purchase_date, purchase_location, purchase_price,
     missing, damaged, checked, lot_id)
SELECT
    id, part, color, quantity, description, storage,
    purchase_date, purchase_location, purchase_price,
    missing, damaged, checked, NULL
FROM "bricktracker_individual_parts";

This ensures that existing parts get NULL for lot_id (they’re not part of any lot), and the column count matches perfectly.

Lesson: Never use SELECT * in migrations. Always be explicit about column mapping, even if it’s verbose.

Migration 0023: Lot Indexes

Purpose: Add indexes to support fast filtering of lots by storage and purchase location.

This was a small but important follow-up to migration 0022. With lots created, users would inevitably ask:

“Show me all lots stored in the basement”
“What did I buy from seller X?”

Without indexes, these queries require full table scans:

1
2
3
4
5


-- Without index: reads EVERY row in bricktracker_individual_part_lots
SELECT * FROM bricktracker_individual_part_lots
WHERE storage = 'basement-bin-A1';

-- With index: uses idx_individual_part_lots_storage to jump directly to matching rows

The migration added two simple but critical indexes:

1
2
3
4
5
6
7


-- Fast filtering by storage location
CREATE INDEX idx_individual_part_lots_storage
ON bricktracker_individual_part_lots(storage);

-- Fast filtering by purchase location
CREATE INDEX idx_individual_part_lots_purchase_location
ON bricktracker_individual_part_lots(purchase_location);

For a collection with 100 lots, the performance difference might be negligible. But for power users with 1,000+ lots, these indexes are the difference between instant results and multi-second waits.

I’ve learned that it is better to index early; It’s much harder to retrofit indexes into a production system with 50,000 rows than to include them from the start.

Migration 0024: Color Translation Table

Purpose: Support cross-referencing between Rebrickable and Bricklink color systems.

This migration addressed a subtle but annoying problem: color ID mismatches between LEGO data sources.

BrickTracker uses Rebrickable as its primary API for LEGO data (sets, parts, minifigures, colors). Rebrickable has comprehensive data and a generous API. However, many collectors also use Bricklink for buying and selling parts.

The problem: Rebrickable and Bricklink use different color ID systems.

For example, “Trans-Light Blue” is:

Rebrickable: color ID 41
Bricklink: color ID 15

LEGO and other data providers even use other IDs:

LEGO: color ID 42
LDraw: color ID 43
BrickOwl: color ID 101

If a user exports their BrickTracker inventory to import into Bricklink (or vice versa), the color IDs don’t match. Parts show up as the wrong color, or imports fail entirely.

Migration 0024 added a translation table:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


┌──────────────────────────────────────────┐
│  rebrickable_colors                      │
│  (color reference with Bricklink map)    │
├──────────────────────────────────────────┤
│ color_id (INT, primary key)              │  Rebrickable color ID (e.g., 293)
│ name (TEXT)                              │  Color name (e.g., "Trans-Light Blue")
│ rgb (TEXT)                               │  Hex color code (e.g., "68BCC5")
│ is_trans (BOOLEAN)                       │  Is this a transparent color?
│ bricklink_color_id (INT, nullable)       │  Bricklink equivalent (e.g., 15)
│ bricklink_color_name (TEXT, nullable)    │  Bricklink name (may differ)
└──────────────────────────────────────────┘

With this table, BrickTracker can now:

Export to Bricklink: Convert Rebrickable color IDs to Bricklink color IDs in exports
Import from Bricklink: Map incoming Bricklink color IDs to Rebrickable IDs
Display both: Show users both color systems in the UI for clarity

The data is populated via Rebrickable’s /api/v3/lego/colors/ endpoint, which includes Bricklink mappings in the response:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60


{
  "id": 41,
  "name": "Trans-Light Blue",
  "rgb": "AEEFEC",
  "is_trans": true,
  "external_ids": {
    "BrickLink": {
      "ext_ids": [
        15
      ],
      "ext_descrs": [
        [
          "Trans-Light Blue"
        ]
      ]
    },
    "BrickOwl": {
      "ext_ids": [
        101
      ],
      "ext_descrs": [
        [
          "Transparent Light Blue"
        ]
      ]
    },
    "Peeron": {
      "ext_ids": [
        null
      ],
      "ext_descrs": [
        [
          "trltblue"
        ]
      ]
    },
    "LDraw": {
      "ext_ids": [
        43
      ],
      "ext_descrs": [
        [
          "Trans_Medium_Blue"
        ]
      ]
    },
    "LEGO": {
      "ext_ids": [
        42
      ],
      "ext_descrs": [
        [
          "Tr. Lg blue",
          "TR.L.BLUE",
          "TR. LIGHT BLUE"
        ]
      ]
    }
  }
}

The index on bricklink_color_id ensures fast lookups when importing Bricklink data:

1
2
3
4
5
6
7


CREATE INDEX idx_rebrickable_colors_bricklink
ON rebrickable_colors(bricklink_color_id);

-- Fast query: "What's Rebrickable's equivalent of Bricklink color 15?"
SELECT color_id, name FROM rebrickable_colors
WHERE bricklink_color_id = 15;
-- Returns: 41, "Trans-Light Blue"

Why this matters: Interoperability. BrickTracker isn’t an island. Collectors use multiple tools, and smooth data exchange makes the ecosystem healthier. By supporting both color systems, we reduce friction and make BrickTracker a better “data hub” for LEGO collections.

Migration 0025: Performance Optimization Attempt (A Cautionary Tale)

Purpose: Add composite indexes to speed up common query patterns—but this is where theory met reality.

By early January 2026, BrickTracker 1.4 was ready for pre-release testing. I migrating my collection (100,000+ parts) to version 1.4 but I started seeing sluggish performance on specific views:

The lot detail page was slow to load when lots had 500+ parts
The “Problems” view took several seconds with many missing/damaged parts
Filtering parts by lot was noticeably slower than filtering sets by storage

The obvious solution: add composite indexes. Migration 0025 added three indexes:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


BEGIN TRANSACTION;

-- Composite index for lot part listing
CREATE INDEX idx_individual_parts_lot_id_part_color
ON bricktracker_individual_parts(lot_id, part, color);

-- Problem tracking index for individual parts
CREATE INDEX idx_individual_parts_missing_damaged
ON bricktracker_individual_parts(missing, damaged);

-- Checked state index for individual minifigure parts
CREATE INDEX idx_individual_minifigure_parts_checked
ON bricktracker_individual_minifigure_parts(id, checked);

COMMIT;

The Theory

Composite indexes should help queries that filter on one column and sort by others. For example:

1
2
3
4
5


-- Hypothetical query we thought we were optimizing
SELECT part, color, quantity, missing, damaged
FROM bricktracker_individual_parts
WHERE lot_id = 'abc123'
ORDER BY part, color;

With (lot_id, part, color) indexed together, SQLite could theoretically:

Use the index to find rows where lot_id = 'abc123'
Those rows would already be sorted by (part, color) in the index
No separate sort step needed—instant results!

The Reality Check

Here’s what actually happens in the lot parts query (individual_part_lot/list/parts.sql):

1
2
3
4
5
6
7
8
9


SELECT /* ... columns ... */
FROM bricktracker_individual_parts
INNER JOIN rebrickable_parts
    ON individual_parts.part = rebrickable_parts.part
    AND individual_parts.color = rebrickable_parts.color_id
WHERE individual_parts.lot_id = :lot_id
ORDER BY rebrickable_parts.name ASC, individual_parts.color ASC
    -- ^^^^^^^^^^^^^^^^^^^^^^^^
    -- Sorting by the JOINED table's name column!

The problem: The query sorts by rebrickable_parts.name (the human-readable part name from the joined table), not by bricktracker_individual_parts.part (the part number). The composite index on (lot_id, part, color) can’t help because it doesn’t include the joined table’s sort column.

SQLite’s query plan:

Use idx_individual_parts_lot_id (from migration 0022) to filter by lot
Join to rebrickable_parts to get part names
Sort in memory by rebrickable_parts.name

The fancy composite index? Unused.

Similarly, the “problems” query uses:

1
2


WHERE missing > 0 OR damaged > 0
ORDER BY part, color

The OR clause prevents efficient index usage. SQLite can’t easily use an index on (missing, damaged) when the query is “missing OR damaged”, it would need to scan both columns separately and merge results. A table scan is often faster.

The checked index? No queries in the codebase actually filter by checked status for individual minifigure parts. This index was implemented during a late night session and my thoughts process had been everywhere we reference missing or damaged, we also reference checked. But as a user I never need to filter for checked parts, as it’s a semi temporary method to see how far a sorting session or audit has come

The checked index? No queries in the codebase actually filter by checked status for individual minifigure parts. This index was implemented during a late-night session, and my thought process was basically: everywhere we reference missing or damaged, we should also reference checked.

In practice, though, as a user I never need to filter for checked parts, since it’s only a semi-temporary way to track how far a sorting session or audit has progressed. The index also applies to minifigures rather than parts, as the other two indexes do.

Writing this post made me realize the extent of my brain fog, and I’ll most likely go back and fix this before version 1.4 is released.

What We Should Have Done

Profile first, optimize second: Run EXPLAIN QUERY PLAN on actual queries before creating indexes
Match real queries, not hypothetical ones: The lot listing sorts by part name, not part number
Test with production data: The performance issues were real, but these indexes didn’t solve them

To actually optimize the lot listing, we’d need:

An index on rebrickable_parts(name) (reference data table)
Or restructure the query to avoid sorting by joined columns

To optimize the problems query, we’d need:

Rewrite with UNION instead of OR: WHERE missing > 0 UNION WHERE damaged > 0
Or add a computed column: has_problems AS (missing > 0 OR damaged > 0) and index that

After using version 1.4 for a few weeks, I don’t have the performance issues I first saw. I will not be adding new indexes for now.

The Honest Assessment

Migration 0025 exists, and it doesn’t hurt (indexes are cheap on reads), but it doesn’t provide the dramatic speedups we expected.

Real-world impact: Minimal. The existing single-column indexes from migrations 0021-0022 handle the queries adequately.

Lesson learned: This is a valuable reminder that database optimization requires profiling actual queries, not guessing.

Migration 0026: Referential Integrity Consistency

Purpose: Standardize foreign key ON DELETE behavior across all tables.

This migration was less about performance and more about database hygiene.

When migration 0022 initially created the part lots table, it used ON DELETE SET NULL for foreign keys:

1
2
3


FOREIGN KEY("storage")
    REFERENCES "bricktracker_metadata_storages"("id")
    ON DELETE SET NULL

The intention was user-friendly: if you delete a storage location, any lots referencing it would have their storage column set to NULL (meaning “no storage assigned”) rather than causing a foreign key constraint error.

However, this was inconsistent with the rest of BrickTracker. Every other table (sets, individual minifigures, individual parts) used ON DELETE RESTRICT (the default):

1
2
3


FOREIGN KEY("storage")
    REFERENCES "bricktracker_metadata_storages"("id")
-- Default: ON DELETE RESTRICT

With RESTRICT, attempting to delete a storage location that’s still referenced by any entity fails with an error: “FOREIGN KEY constraint failed”. This forces the user to either:

Reassign all items to a different storage location first
Explicitly delete those items

It’s slightly less convenient, but it prevents accidental data loss. If you delete “Basement Bin A-3” and it silently orphans 50 lots by setting their storage to NULL, you might not notice until later when you’re trying to find those parts.

Migration 0026 standardized part lots to use RESTRICT:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34


BEGIN TRANSACTION;

-- Recreate bricktracker_individual_part_lots with ON DELETE RESTRICT (default)
CREATE TABLE "bricktracker_individual_part_lots_new" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "name" TEXT,
    "description" TEXT,
    "created_date" REAL NOT NULL,
    "storage" TEXT,
    "purchase_location" TEXT,
    "purchase_date" REAL,
    "purchase_price" REAL,
    FOREIGN KEY("storage") REFERENCES "bricktracker_metadata_storages"("id"),
    FOREIGN KEY("purchase_location") REFERENCES "bricktracker_metadata_purchase_locations"("id")
);

-- Copy existing data (no changes to data, just schema)
INSERT INTO "bricktracker_individual_part_lots_new"
SELECT * FROM "bricktracker_individual_part_lots";

DROP TABLE "bricktracker_individual_part_lots";
ALTER TABLE "bricktracker_individual_part_lots_new" RENAME TO "bricktracker_individual_part_lots";

-- Recreate indexes
CREATE INDEX idx_individual_part_lots_created_date
ON bricktracker_individual_part_lots(created_date);

CREATE INDEX idx_individual_part_lots_storage
ON bricktracker_individual_part_lots(storage);

CREATE INDEX idx_individual_part_lots_purchase_location
ON bricktracker_individual_part_lots(purchase_location);

COMMIT;

Why this matters: Consistency reduces cognitive load. If different tables behave differently when you delete metadata, you have to remember which is which. Standardizing on RESTRICT makes the system predictable: “If I try to delete something that’s in use, I’ll get an error telling me what’s using it.”

The UI can then provide a better experience: “This storage location is used by 5 sets, 3 minifigures, and 2 part lots. Reassign them first, or delete them to proceed.”

Migration 0027: Consolidated Metadata

Purpose: Remove foreign key constraints from metadata tables so they can serve all entity types.

This is the most architecturally significant migration, and it required a different approach: pure Python logic instead of SQL.

The Problem: Entity Type Explosion

In BrickTracker 1.3, metadata tables had foreign keys back to sets:

1
2
3
4
5
6


CREATE TABLE "bricktracker_set_owners" (
    "id" TEXT PRIMARY KEY,
    "owner_<uuid>" BOOLEAN NOT NULL DEFAULT 0,
    "owner_<uuid>" BOOLEAN NOT NULL DEFAULT 0,
    FOREIGN KEY("id") REFERENCES "bricktracker_sets"("id") ON DELETE CASCADE
);

This enforced referential integrity: you couldn’t assign an owner to an entity that didn’t exist. If you deleted a set, its owner row was automatically deleted (ON DELETE CASCADE).

But in 1.4, we have four entity types: sets, individual minifigures, individual parts, and part lots. Each needs owners, tags, and statuses.

Option A: Separate metadata tables for each entity type

1
2
3
4
5
6
7


bricktracker_set_owners
bricktracker_individual_minifigure_owners
bricktracker_individual_part_owners
bricktracker_individual_part_lot_owners
bricktracker_set_tags
bricktracker_individual_minifigure_tags
...

This is a maintenance nightmare. Every change to the metadata system (adding a new owner, renaming a tag) would need to be replicated across 12 tables.

Option B (chosen): Remove foreign key constraints, consolidate metadata

1
2
3
4
5
6
7
8


┌────────────────────────┐
│ bricktracker_set_owners│  Name unchanged for backward compatibility
├────────────────────────┤
│ id (UUID, NO FK)       │───▶  Matches ANY entity UUID
│ owner_<uuid> BOOL      │      ├─ Sets
│ owner_<uuid> BOOL      │      ├─ Individual minifigures
└────────────────────────┘      ├─ Individual parts
                                └─ Part lots

By removing the foreign key constraint, we gain flexibility: the id column can reference any entity type. The table name stays bricktracker_set_owners for backward compatibility (changing it would break 1.3 -> 1.4 upgrades), but it logically becomes a “universal owners table.”

Application-Level Integrity

Removing the foreign key means we lose database-level enforcement. The database won’t stop you from inserting an owner row for a non-existent entity. This is a trade-off:

Lost: Database-level referential integrity
Gained: Flexibility to serve multiple entity types with one table

To compensate, the application layer enforces integrity:

UUID uniqueness: All entities (sets, minifigures, parts, lots) use UUIDs as primary keys, generated via Python’s uuid.uuid4(). The UUID space is large enough (2^122 possible values) that collisions are astronomically unlikely.
Cascade deletions: When an entity is deleted, the application explicitly deletes its metadata:

1
2
3
4
5
6
7
8


def delete_individual_part(part_id: str):
    # Delete the part
    db.execute("DELETE FROM bricktracker_individual_parts WHERE id = ?", (part_id,))

    # Delete its metadata
    db.execute("DELETE FROM bricktracker_set_owners WHERE id = ?", (part_id,))
    db.execute("DELETE FROM bricktracker_set_tags WHERE id = ?", (part_id,))
    db.execute("DELETE FROM bricktracker_set_statuses WHERE id = ?", (part_id,))

No orphan metadata: The UI doesn’t allow creating metadata for entities that don’t exist. You can’t assign an owner to a UUID unless that UUID corresponds to a valid entity.

This pattern is similar to polymorphic associations in object–relational mapping. The difference is we’re implementing it manually at the schema level.

Why Python, Not SQL?

Migration 0027 is unique: the SQL file contains only a comment:

1
2
3


-- description: Remove foreign key constraints from consolidated metadata tables

-- This migration is implemented entirely in Python (see migrations/0027.py)

The actual logic is in bricktracker/migrations/0027.py:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


def upgrade(db):
    """Remove FK constraints from metadata tables."""

    for table_name in ['bricktracker_set_owners', 'bricktracker_set_tags', 'bricktracker_set_statuses']:
        # Get current schema
        schema = get_table_schema(db, table_name)

        # Extract column definitions (dynamically, because owner/tag/status columns are added at runtime)
        columns = extract_columns(schema)

        # Create new table without FK constraint
        create_sql = f"CREATE TABLE {table_name}_new ({', '.join(columns)})"
        db.execute(create_sql)

        # Copy data
        db.execute(f"INSERT INTO {table_name}_new SELECT * FROM {table_name}")

        # Drop old, rename new
        db.execute(f"DROP TABLE {table_name}")
        db.execute(f"ALTER TABLE {table_name}_new RENAME TO {table_name}")

Why Python instead of SQL?

Because the metadata tables have dynamic columns. When a user creates a new owner named “Alice,” the application adds a column owner_<alice_uuid> at runtime:

1

ALTER TABLE bricktracker_set_owners ADD COLUMN "owner_abc123" BOOLEAN DEFAULT 0;

This means the schema isn’t known at migration-writing time. We can’t hard-code the columns in SQL. The Python code introspects the current schema, extracts whatever columns exist, and recreates the table without foreign keys.

Lesson: Schema migrations don’t have to be pure SQL. When you have dynamic schema or complex logic, Python (or your language of choice) provides more flexibility.

The Complete 1.4 Schema

After all seven migrations, here’s the full architecture:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101


                  REFERENCE DATA (from Rebrickable API)
                  ┌─────────────────────────────────┐
                  │  rebrickable_sets               │  Official LEGO sets
                  │  rebrickable_minifigures        │  Minifigure definitions
                  │  rebrickable_parts              │  Part catalog
                  │  rebrickable_colors             │  Color mappings (Rebrickable ↔ Bricklink)
                  └─────────────────────────────────┘
                              ▲ ▲ ▲
                              │ │ │
                              │ │ └─────────────────┐
              ┌───────────────┘ │                   │
              │                 │                   │
              │                 │                   │
┌─────────────┴──────┐  ┌───────┴────────┐  ┌───────┴──────────┐
│ bricktracker_sets  │  │ bricktracker_  │  │ bricktracker_    │
│                    │  │ individual_    │  │ individual_parts │
│ (Set instances)    │  │ minifigures    │  │                  │
├────────────────────┤  │                │  ├──────────────────┤
│ id (UUID, PK)      │  │ (Loose figs)   │  │ id (UUID, PK)    │
│ set (FK)           │  ├────────────────┤  │ part (FK)        │
│ description        │  │ id (UUID, PK)  │  │ color (FK)       │
│ storage (FK)       │  │ figure (FK)    │  │ quantity         │
│ purchase_date      │  │ quantity       │  │ description      │
│ purchase_location  │  │ description    │  │ storage (FK)     │
│ purchase_price     │  │ storage (FK)   │  │ purchase_date    │
└────────┬───────────┘  │ purchase_date  │  │ purchase_location│
         │              │ purchase_loc.  │  │ purchase_price   │
         │              │ purchase_price │  │ missing          │
         │              └───────┬────────┘  │ damaged          │
         │                      │           │ checked          │
         │                      │           │ lot_id (FK) ─────┼──┐
         │                      ▼           └──────────────────┘  │
         │        ┌────────────────────┐                          │
         │        │ bricktracker_      │                          │
         │        │ individual_        │                          │
         │        │ minifigure_parts   │                          │
         │        │                    │                          │
         │        │ (Parts of figs)    │                          │
         │        ├────────────────────┤                          │
         │        │ id (FK to minifig) │                          │
         │        │ part (FK)          │                          │
         │        │ color (FK)         │                          │
         │        │ spare              │                          │
         │        │ quantity           │                          │
         │        │ missing            │                          │
         │        │ damaged            │                          │
         │        │ checked            │                          │
         │        │ PK(id,part,color,  │                          │
         │        │    spare)          │                          │
         │        └────────────────────┘                          │
         │                                                        │
         │              ┌─────────────────────────────────────────┘
         │              │
         │              ▼
         │   ┌────────────────────────────┐
         │   │ bricktracker_individual_   │
         │   │ part_lots                  │
         │   │                            │
         │   │ (Grouped parts)            │
         │   ├────────────────────────────┤
         │   │ id (UUID, PK)              │
         │   │ name                       │  Custom title for lot
         │   │ description                │
         │   │ created_date               │
         │   │ storage (FK)               │
         │   │ purchase_location (FK)     │
         │   │ purchase_date              │
         │   │ purchase_price             │
         │   └────────────────────────────┘
         │
         │
         │        CONSOLIDATED METADATA (shared across ALL entities)
         │        ┌───────────────────────────────────────────────┐
         ├───────▸│ bricktracker_set_owners                       │
         ├───────▸│ bricktracker_set_tags                         │
         ├───────▸│ bricktracker_set_statuses                     │
         │        │                                               │
         │        │ Schema:                                       │
         │        │ - id (UUID, matches entity UUID)              │
         │        │ - owner_<uuid> BOOLEAN (dynamic columns)      │
         │        │ - tag_<uuid> BOOLEAN (dynamic columns)        │
         │        │ - status_<uuid> BOOLEAN (dynamic columns)     │
         │        │                                               │
         │        │ No FK constraint! Works with any entity type. │
         │        └───────────────────────────────────────────────┘
         │                         ▲ ▲ ▲ ▲
         └─────────────────────────┘ │ │ │
                   ┌─────────────────┘ │ │
                   │   ┌───────────────┘ │
                   │   │   ┌─────────────┘
                   │   │   │
                   │   │   │ All entities (sets, minifigs, parts, lots)
                   │   │   │ can have owners, tags, and statuses
                   │   │   │

                  SHARED REFERENCE METADATA
                  ┌─────────────────────────────────┐
                  │ bricktracker_metadata_storages  │  User-defined storage bins
                  │ bricktracker_metadata_purchase_ │  User-defined purchase sources
                  │   locations                     │  (Bricklink sellers, stores, etc.)
                  └─────────────────────────────────┘

This architecture achieves all six design goals:

Reuse existing infrastructure: Metadata tables serve all entity types
Maintain referential integrity: Foreign keys to Rebrickable reference data
Support complex queries: Indexes enable fast filtering and joining
Allow flexible organization: Both standalone parts and grouped lots
Track completeness: Problem tracking for individual minifigures
Preserve backward compatibility: Sets table unchanged, migrations additive

Query Patterns: Putting It All Together

The unified schema enables powerful cross-entity queries. Here are real-world examples.

1. Find All Missing Parts Across Your Entire Collection

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57


-- Missing parts from sets
SELECT
    'Set' as entity_type,
    s.id,
    s."set" as set_number,
    rs.name as set_name,
    p.part,
    rp.name as part_name,
    p.color,
    rc.name as color_name,
    p.missing as qty_missing
FROM bricktracker_sets s
JOIN bricktracker_parts p ON s.id = p.id
JOIN rebrickable_sets rs ON s."set" = rs."set"
JOIN rebrickable_parts rp ON p.part = rp.part AND p.color = rp.color_id
JOIN rebrickable_colors rc ON p.color = rc.color_id
WHERE p.missing > 0

UNION ALL

-- Missing parts from individual minifigures
SELECT
    'Individual Minifigure' as entity_type,
    m.id,
    m.figure as set_number,
    rm.name as set_name,
    p.part,
    rp.name as part_name,
    p.color,
    rc.name as color_name,
    p.missing as qty_missing
FROM bricktracker_individual_minifigures m
JOIN bricktracker_individual_minifigure_parts p ON m.id = p.id
JOIN rebrickable_minifigures rm ON m.figure = rm.figure
JOIN rebrickable_parts rp ON p.part = rp.part AND p.color = rp.color_id
JOIN rebrickable_colors rc ON p.color = rc.color_id
WHERE p.missing > 0

UNION ALL

-- Damaged individual parts (also counts as "missing" since you need replacements)
SELECT
    'Individual Part' as entity_type,
    p.id,
    p.part as set_number,
    rp.name as set_name,
    p.part,
    rp.name as part_name,
    p.color,
    rc.name as color_name,
    p.damaged as qty_missing
FROM bricktracker_individual_parts p
JOIN rebrickable_parts rp ON p.part = rp.part AND p.color = rp.color_id
JOIN rebrickable_colors rc ON p.color = rc.color_id
WHERE p.damaged > 0 OR p.missing > 0

ORDER BY set_name, part_name;

This query combines results from three different entity types into a single unified “what do I need to order?” list. The entity_type column lets you see where each part comes from.

Problems view showing missing parts from sets, minifigures, and individual parts in one unified list

2. Find Everything Stored in a Specific Location

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


-- "I'm reorganizing my basement. What's in bin A-3?"

SELECT 'Set' as type, "set" as item_id, rs.name
FROM bricktracker_sets s
JOIN rebrickable_sets rs ON s."set" = rs."set"
WHERE s.storage = 'basement-bin-A3'

UNION ALL

SELECT 'Minifigure' as type, figure as item_id, rm.name
FROM bricktracker_individual_minifigures m
JOIN rebrickable_minifigures rm ON m.figure = rm.figure
WHERE m.storage = 'basement-bin-A3'

UNION ALL

SELECT 'Part' as type, part || ' (' || rc.name || ')' as item_id, rp.name
FROM bricktracker_individual_parts p
JOIN rebrickable_parts rp ON p.part = rp.part AND p.color = rp.color_id
JOIN rebrickable_colors rc ON p.color = rc.color_id
WHERE p.storage = 'basement-bin-A3'

UNION ALL

SELECT 'Part Lot' as type, id as item_id, COALESCE(name, 'Unnamed Lot')
FROM bricktracker_individual_part_lots
WHERE storage = 'basement-bin-A3';

With indexes on the storage column in each table (idx_bricktracker_sets_set_storage, idx_individual_minifigures_storage, etc.), this query returns instantly even with thousands of items.

3. Find All Items You Own (Shared Collections)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


-- Multi-person household, each person has their own LEGO collection
-- "Show me everything Alice owns"

-- Assuming Alice's owner UUID is 'abc-123-def'

SELECT 'Set' as type, s."set" as item, rs.name
FROM bricktracker_sets s
JOIN bricktracker_set_owners o ON s.id = o.id
JOIN rebrickable_sets rs ON s."set" = rs."set"
WHERE o."owner_abc-123-def" = 1

UNION ALL

SELECT 'Minifigure' as type, m.figure as item, rm.name
FROM bricktracker_individual_minifigures m
JOIN bricktracker_set_owners o ON m.id = o.id
JOIN rebrickable_minifigures rm ON m.figure = rm.figure
WHERE o."owner_abc-123-def" = 1

UNION ALL

SELECT 'Part Lot' as type, id as item, COALESCE(name, 'Unnamed Lot')
FROM bricktracker_individual_part_lots l
JOIN bricktracker_set_owners o ON l.id = o.id
WHERE o."owner_abc-123-def" = 1;

Notice how the bricktracker_set_owners table serves all entity types. The join ON s.id = o.id works identically for sets, minifigures, and part lots because they all use UUIDs as primary keys.

4. Calculate Total Investment by Purchase Location

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32


-- "How much have I spent on Bricklink vs LEGO stores?"

SELECT
    mpl.name as purchase_location,
    COUNT(*) as num_items,
    SUM(total_spent) as total_spent
FROM (
    SELECT purchase_location, purchase_price as total_spent
    FROM bricktracker_sets
    WHERE purchase_price IS NOT NULL

    UNION ALL

    SELECT purchase_location, purchase_price as total_spent
    FROM bricktracker_individual_minifigures
    WHERE purchase_price IS NOT NULL

    UNION ALL

    SELECT purchase_location, purchase_price as total_spent
    FROM bricktracker_individual_parts
    WHERE purchase_price IS NOT NULL

    UNION ALL

    SELECT purchase_location, purchase_price as total_spent
    FROM bricktracker_individual_part_lots
    WHERE purchase_price IS NOT NULL
) combined
JOIN bricktracker_metadata_purchase_locations mpl ON combined.purchase_location = mpl.id
GROUP BY mpl.name
ORDER BY total_spent DESC;

This query aggregates purchase data across all entity types, giving you a complete financial picture of your collection. This is useful for the statistics page.

5. Find Duplicate Parts (Do I Already Own This?)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35


-- Before buying a part, check if you already have it

-- Search for: Part 3001 (2x4 brick) in red (color 5)

SELECT
    'Set' as source,
    s."set" || ': ' || rs.name as location,
    SUM(p.quantity) as qty_owned,
    GROUP_CONCAT(DISTINCT s.storage) as stored_in
FROM bricktracker_sets s
JOIN rebrickable_sets rs ON s."set" = rs."set"
JOIN bricktracker_parts p ON s.id = p.id
WHERE p.part = '3001' AND p.color = 4
GROUP BY s."set", rs.name

UNION ALL

SELECT
    'Individual Part' as source,
    COALESCE(p.description, 'Individual part') as location,
    p.quantity as qty_owned,
    p.storage as stored_in
FROM bricktracker_individual_parts p
WHERE p.part = '3001' AND p.color = 4

UNION ALL

SELECT
    'Part Lot' as source,
    COALESCE(l.name, 'Part lot ' || l.id) as location,
    p.quantity as qty_owned,
    l.storage as stored_in
FROM bricktracker_individual_parts p
JOIN bricktracker_individual_part_lots l ON p.lot_id = l.id
WHERE p.part = '3001' AND p.color = 4;

The result: “You already own 47 of these: 12 in Set 75192-1 (Millennium Falcon), 10 in Part Lot ‘MOC - Modular Bakery’, and 25 as individual parts.”

1
2
3
4
5
6


source           location                                       qty_owned  stored_in
---------------  ---------------------------------------------  ---------  ------------------------------------
Set              10792-1: Drill Spinner Vehicle                 2          b64c572b_e2c4_4124_9641_272d59dec75f
Individual Part  Individual part                                4          119fa30c_13cf_4243_94ac_9444b9b91c4c
Individual Part  Individual part                                6
Part Lot         Part lot 5f440c7e-99ad-46ca-8a58-743a2df0a244  4          a047e6e8_4be6_4d1e_b62f_b58b77eaf2a2

Part details page showing all sources where this part appears

Lessons Learned

Building these migrations taught several valuable lessons that apply beyond BrickTracker.

1. Consolidated Metadata is Powerful

Removing foreign keys from metadata tables seemed risky at first. “What if someone inserts an owner row for a non-existent entity?” But the flexibility gained was worth it:

One bricktracker_set_owners table serves four entity types
No code duplication for metadata operations
Adding a fifth entity type requires no metadata schema changes

2. SQLite Limitations Require Workarounds

SQLite is fantastic for embedded databases, but its limitations require workarounds:

Can’t add foreign keys via ALTER TABLE: Table recreation required
Can’t modify columns: Table recreation required
No DROP COLUMN: Table recreation required

The pattern: Create new table, copy data, drop old, rename new. It’s verbose but reliable.

3. Migration Sequencing Matters

We could have crammed everything into one migration, but breaking it into seven had benefits:

Easier debugging: If migration 0024 fails, you know exactly what went wrong (color table creation), not “something in that 500-line migration”
Incremental deployment: Early testers can possible upgrade to 0021-0023 to test core functionality before 0024-0027 is ready
Clear history: Each migration has a focused purpose documented in its -- description: comment

4. Documentation is Essential

Each migration starts with:

1

-- description: [What this migration does in one sentence]

This seems trivial, but six months later when I’m investigating a bug, I’ll be grateful. The description answers: “Why does this table have this structure?” without needing to read 100 lines of SQL.

What’s Next? Future Possibilities

The 1.4 schema isn’t just about supporting what I built: it’s a foundation for future features. Here are ideas, that might (or might not) be implemented in a future version:

1. MOC Planning and Bill of Materials

The feature: Import a MOC’s parts list (from Rebrickable, Bricklink, or a manual CSV), compare it to your inventory, and generate a “shopping list” of what you still need to buy.

The lots system already provides the infrastructure: create a lot named “MOC: Modular Bakery Building,” upload the parts list, mark parts you’ve already gathered. The remaining parts become your shopping list.

Why it matters: This is the feature that changes BrickTracker’s scope from “set tracking” to “collection management.” As mentioned earlier, the project scope will need updating to reflect this new capability.

2. Part Location Search

The feature: “Where did I put all my blue Technic pins?” A reverse lookup: given a part/color, show everywhere it appears in your collection, including which storage bins.

The result: “You have 37 blue Technic pins: 12 in Set 42083-1 (Bugatti Chiron, stored in bin B-5), 18 in Part Lot ‘Technic Spares’ (bin C-2), and 7 loose (bin A-1).”

3. Bulk Operations

The feature: “Move all parts from lot A to lot B” or “Merge three part lots into one.”

Currently, lots are immutable after creation. But the schema supports editing:

The UI could make this a drag-and-drop operation: select parts, drag to different lot.

Implications for Project Scope

Recall that BrickTracker’s project scope explicitly stated:

Not Ideal For:

Custom build creators (MOCs) - Doesn’t track My Own Creations or custom builds

And the limitations page:

Building & Customization:

No custom build tracking

Parts can only be added within existing sets

After 1.4, these statements are no longer accurate. While BrickTracker doesn’t provide MOC design tools (3D modeling, instruction generation), it will provide MOC inventory management:

Create a part lot with a custom name: “MOC: Modular Police Station”
Add parts to the lot (via search, CSV upload, or quick-add from sets)
Track which parts you’ve gathered and which you still need
Assign storage locations, purchase info, and tags to the lot
Share the lot with co-builders using the owners system

This addresses the use case from issue #68:

“bulk parts inventory, IMO is just as important as set inventory and is just as much part of any Lego collection as sets are.”

The scope documentation will be updated to reflect this.

Conclusion

BrickTracker 1.4’s database evolution: from a set-only system to a multi-entity collection manager, demonstrates that thoughtful schema design can accommodate significant feature expansion while maintaining performance, data integrity, and backward compatibility.

The architectural decisions; consolidated metadata, composite indexes, application-level integrity, part lots, weren’t just technical solutions. They were informed by real user needs expressed in Gitea issues.

Where to Get BrickTracker 1.4

BrickTracker 1.4 is now available for testing with the Docker tag pre-1.4. The full release includes:

Individual minifigures tracking with part-level completeness
Individual parts tracking (standalone and in lots)
Part lots for grouping bulk purchases and MOC planning
Quick-add from set parts tables
CSV import for bulk part addition
Cross-entity search and filtering
Unified problems view
Full metadata support (owners, tags, statuses, storage, purchase info) for all entities

Backup your database before upgrading! Migrations 0021-0027 have no automatic rollback.

The complete test guide is available on Gitea.

BrickTracker is an open-source, self-hosted LEGO collection management system built with Python, Flask, and SQLite. It’s designed for enthusiasts who want full control over their data without relying on cloud services.