fix(sidecar): make additional-images flag live-toggleable from admin

Reviewed-on: #161

.dockerignore

@@ -8,11 +8,17 @@ static/sets
 Dockerfile
 compose.yaml
 
+# Local data directories
+local/
+offline/
+data/
+
 # Documentation
 docs/
 LICENSE
 *.md
 *.sample
+.code-workspace
 
 # Temporary
 *.csv
@@ -26,11 +32,8 @@ LICENSE
 **/__pycache__
 *.pyc
 
-# Git
-.git
-
-# IDE
-.vscode
+# Hidden directories
+.?*
 
 # Dev
 test-server.sh

.env.sample

@@ -179,6 +179,19 @@
 # Default: false
 # BK_HIDE_WISHES=true
 
+# Optional: Hide the 'Individual Minifigures' entry from the menu. Does not disable the route.
+# Default: false
+# BK_HIDE_INDIVIDUAL_MINIFIGURES=true
+
+# Optional: Hide the 'Individual Parts' entry from the menu. Does not disable the route.
+# Default: false
+# BK_HIDE_INDIVIDUAL_PARTS=true
+
+# Optional: Hide the 'Add to individual parts' quick-add buttons in parts tables.
+# The column header with menu options (mark all missing, check all, etc.) remains visible.
+# Default: false
+# BK_HIDE_QUICK_ADD_INDIVIDUAL_PARTS=true
+
 # Optional: Change the default order of minifigures. By default ordered by insertion order.
 # Useful column names for this option are:
 # - "rebrickable_minifigures"."figure": minifigure ID (e.g., "fig-001234")
@@ -207,11 +220,11 @@
 
 # Optional: Change the default order of parts. By default ordered by insertion order.
 # Useful column names for this option are:
-# - "bricktracker_parts"."part": part number (e.g., "3001")
-# - "bricktracker_parts"."spare": part is a spare part (0 or 1)
-# - "bricktracker_parts"."quantity": quantity of this part
-# - "bricktracker_parts"."missing": number of missing parts
-# - "bricktracker_parts"."damaged": number of damaged parts
+# - "combined"."part": part number (e.g., "3001")
+# - "combined"."spare": part is a spare part (0 or 1)
+# - "combined"."quantity": quantity of this part
+# - "combined"."missing": number of missing parts
+# - "combined"."damaged": number of damaged parts
 # - "rebrickable_parts"."name": part name
 # - "rebrickable_parts"."color_name": part color name
 # - "total_missing": total missing across all sets (composite field)
@@ -219,7 +232,7 @@
 # - "total_quantity": total quantity across all sets (composite field)
 # - "total_sets": number of sets containing this part (composite field)
 # - "total_minifigures": number of minifigures with this part (composite field)
-# Default: "rebrickable_parts"."name" ASC, "rebrickable_parts"."color_name" ASC, "bricktracker_parts"."spare" ASC
+# Default: "rebrickable_parts"."name" ASC, "rebrickable_parts"."color_name" ASC, "combined"."spare" ASC
 # Examples:
 # BK_PARTS_DEFAULT_ORDER="total_missing" DESC, "rebrickable_parts"."name" ASC
 # BK_PARTS_DEFAULT_ORDER="rebrickable_parts"."color_name" ASC, "rebrickable_parts"."name" ASC
@@ -481,6 +494,43 @@
 # BK_STATISTICS_DEFAULT_EXPANDED=false
 
 # Optional: Enable dark mode by default
-# When true, the application starts in dark mode. 
+# When true, the application starts in dark mode.
 # Default: false
 # BK_DARK_MODE=true
+
+# Optional: Customize badge order for Grid view (set cards on /sets/)
+# Comma-separated list of badge keys in the order they should appear
+# Available badges: theme, tag, year, parts, instance_count, total_minifigures,
+#   total_missing, total_damaged, owner, storage, purchase_date, purchase_location,
+#   purchase_price, instructions, rebrickable, bricklink
+# Default: theme,year,parts,total_minifigures,owner
+# BK_BADGE_ORDER_GRID=theme,year,parts,total_minifigures,owner,storage
+
+# Optional: Customize badge order for Detail view (individual set details page)
+# Comma-separated list of badge keys in the order they should appear
+# Use the same badge keys as BK_BADGE_ORDER_GRID
+# Default: theme,tag,year,parts,instance_count,total_minifigures,total_missing,total_damaged,owner,storage,purchase_date,purchase_location,purchase_price,instructions,rebrickable,bricklink
+# BK_BADGE_ORDER_DETAIL=theme,tag,year,parts,owner,storage,purchase_date,rebrickable,bricklink
+
+# Optional: Show/hide individual BrickData (sidecar) elements on the set detail page
+# All default to true (shown). Badges are controlled separately via BK_BADGE_ORDER_DETAIL.
+# These take effect live (no restart). Set to false to hide.
+# BK_SIDECAR_SHOW_DESIGNER: the set designer line
+# BK_SIDECAR_SHOW_DESCRIPTION: the "About this set" description
+# BK_SIDECAR_SHOW_NOTES: the Brickset notes
+# BK_SIDECAR_SHOW_PRICE_PAID: price comparison "Paid" row (and "Change vs paid")
+# BK_SIDECAR_SHOW_PRICE_MSRP: price comparison retail/MSRP rows (and "Saved vs retail")
+# BK_SIDECAR_SHOW_PRICE_MARKET: price comparison "Worth now" market value rows
+# Default: true
+# BK_SIDECAR_SHOW_DESIGNER=false
+# BK_SIDECAR_SHOW_DESCRIPTION=false
+# BK_SIDECAR_SHOW_NOTES=false
+# BK_SIDECAR_SHOW_PRICE_PAID=false
+# BK_SIDECAR_SHOW_PRICE_MSRP=false
+# BK_SIDECAR_SHOW_PRICE_MARKET=false
+
+# Optional: which BrickLink market value the set-detail "Change vs paid" compares
+# against: used or new (falls back to the other when one is missing). The statistics
+# page shows both regardless of this setting. Live-changeable.
+# Default: used
+# BK_SIDECAR_PRICE_BASIS=new

.gitignore

@@ -36,3 +36,6 @@ vitepress/
 # Local data
 offline/
 data/
+
+# Hidden folders
+.?*

CHANGELOG.md

@@ -1,16 +1,281 @@
 # Changelog
 
-## Upcoming - 1.3.2
+## 1.5
+
+### Enhancements
+
+- **BrickData integration**: New BrickData companion service that enriches set detail pages live with data from Brickset and BrickLink (description, extra metadata fields, pricing and badges). Data is pulled on demand rather than cached locally, so it always reflects the current source
+  - Added BrickData settings with an admin UI and a live/restart split so most options apply without a container restart
+- **Box art cover override**: Browse a set's additional images from BrickData and pick any of them (including official box art) as the cover image
+- **Admin-defined custom fields per set** (#146): Admins can define typed custom fields that then appear on each set, with a mass-edit screen to apply metadata across multiple selected sets at once
+- **Retail (MSRP) price and savings** (#144): Set detail shows the lego.com retail price for a chosen region (US/UK/CA/DE) as a badge and in the price comparison, plus a "saved vs retail" figure (MSRP minus what you paid) per set and summed on the Statistics page
+- **BrickLink market value** (#160): Set detail shows the current BrickLink market value (new/used average, min/max on hover) pulled on demand from BrickData, with a collection-value estimate on the Statistics page
+- **Instructions badge and stats** (#154): Sets with downloaded instruction files now show an "instructions" badge (respects `BK_HIDE_SET_INSTRUCTIONS` and the badge order), and the Statistics page counts how many sets have instructions and the total number of instruction files
+- **Statistics: price and instructions stats**: Added a paid / retail / market price comparison and instruction-related statistics
+- **Retirement dates for already-retired sets** (#37): The wishlist now falls back to BrickData for sets that the upcoming-retirements CSV does not cover, so long-retired sets show a retirement date instead of "Not found". Dates are colour-coded (yellow for upcoming, red for already retired), and the set detail "Retired" badge shows the full exit date when available
+- **New grid filters** (#141): Added parts-range, year-range and "None" filters to the set grid
+- **Audit mode for parts tables**: A new "Start audit" entry in the parts table header menu opens a focused, keyboard-driven popup that walks the visible parts one at a time (respecting the active filter and sort), showing a large image, the part name, color and needed quantity. Arrow keys move between parts, Enter/Space save and advance, and a Missing/Found toggle records the missing count (Found mode works out the missing count from what you found). Optimized for phones with a numeric keypad and the image kept in view
+
+### Bug Fixes
+
+- **Fixed instructions never linking to sets with alphanumeric ids** (#166): Instructions for "Pick a Brick" / promotional builds whose set id begins with letters (e.g. `EG00029-1`) were orphaned and never associated with the set; the matching now handles alphanumeric set ids
+- **Fixed font size on the landing page** (#167): Item titles in the "Latest added parts" section on the homepage now match the smaller size used by the sets and minifigures sections
+- **Fixed sets with unmappable BrickLink parts never clearing the "need refresh" list** (#163): Sets containing parts that Rebrickable has no BrickLink mapping for (e.g. length-specific pneumatic hoses) kept reappearing on the Refresh admin page right after being refreshed; these unmappable parts no longer flag a set as needing a refresh
+
+- **Fixed `BK_HIDE_SPARE_PARTS` being ignored on set detail pages**: Spare parts were still shown in the parts inventory on a set's detail page (and in per-minifigure part tables) even with the setting enabled. The set-specific and minifigure part queries now honor the flag like the other part lists do
+- **Fixed price coverage exceeding sensible bounds** (#156): Price coverage is now divided by all priced item types and clamped to 100%
+- **Fixed per-color counts on parts sub-cards** (#159): Set and minifigure counts are now computed per color
+- **Fixed missing purchase date picker on the bulk add page** (#162)
+
+## 1.4.1
+
+### Enhancements
+
+- **Added per-column header filters to the Parts table on the set details page**: The main Parts table now has an always-visible filter row under the column headers
+  - Name: case-insensitive substring search
+  - Color: dropdown populated from the colors present in the current results
+  - Missing / Damaged: All / With / Without dropdowns
+  - Checked: All / Checked / Unchecked
+  - Filters combine (AND) and re-evaluate live as missing/damaged values or checkboxes change. Bulk actions (mark all missing, check all, etc.) only affect the rows visible under the active filter. Scoped to the main Parts table only; per-minifigure tables stay sort-only
+- **"Reset to Defaults" confirmation now uses a Bootstrap modal instead of a browser dialog**: Replaced the native browser `confirm()` popup with a consistent Bootstrap modal matching the style of BrickTracker
+
+### Bug Fixes
+
+- **Fixed prices on the Statistics page being rounded to whole numbers** (Issue #146): All price values now display with two decimal places (`%.2f`) instead of being rounded to whole numbers (`%.0f`)
+
+- **Fixed "Reset to Defaults" blanking all settings instead of restoring them** (Issue #149a, branch `bugfix/issue-149a`): "Reset to Defaults" was clearing all fields to empty/false instead of populating them with their actual default values
+  - `resetToDefaults()` now reads from `window.DEFAULT_CONFIG` and restores each field to its proper default, matching the same logic used on initial page load
+- **Fixed `BK_INSTRUCTIONS_ALLOWED_EXTENSIONS` being treated as a string instead of a list** (Issue #149b, branch `bugfix/issue-149b`): When this setting was saved via the admin panel, it was stored and cast as a plain string rather than a list, causing it to be iterated character by character (e.g. `['.', 'p', 'd', 'f']` instead of `['.pdf']`)
+  - Added `allowed_extensions` to the list-type keyword detection in `_cast_value()`, matching the existing pattern used for `badge_order` settings
+- **Fixed crash when importing sets containing minifigures or parts with no image on Rebrickable** (Issue #149c, branch `bugfix/issue-149c`): Adding or refreshing a set would fail entirely if any minifigure or part had no image URL, with error `Invalid URL '': No scheme supplied`
+  - Rebrickable returns an empty string (not `None`) for missing images; normalize empty strings to `None` at the point of ingestion in `rebrickable_minifigure.py` and `individual_minifigure.py`, matching the existing pattern in `rebrickable_set.py`
+  - Updated `rebrickable_image.py` to treat empty strings the same as `None` throughout, falling back to the configured nil placeholder image
+  - Note: the originally reported sets could no longer reproduce the crash (images may have since been added on Rebrickable), so this fix is based on assumptions only
+- **Fixed previously added set being re-added when adding an individual minifigure** (Issue #150, branch `bugfix/issue-150`): After adding a set, entering a `fig-` ID and confirming would add the previous set again instead of the minifigure, if user did not reload inbetween. 
+  - `add.js` was creating a second `BrickMinifigureSocket` with its own listeners on the same button and input as `BrickSetSocket`, causing duplicate socket events and cross-socket state confusion
+- **Fixed purchase date, price, and notes not being saved when adding an individual minifigure** (Issue #151, branch `bugfix/issue-151`): Filling in purchase date, price, or notes before clicking Add had no effect, only purchase location was saved
+  - `BrickMinifigureSocket` was missing references to `#add-purchase-date`, `#add-purchase-price`, and `#add-description`, so those fields were never read or included in the socket emit
+  - The backend already supported all three fields. This was just a frontend error
+- **Fixed purchase date and price not being converted when adding an individual minifigure** (Issue #151 follow-up): Purchase date was stored as a raw `YYYY/MM/DD` string and price as a raw string instead of a Unix epoch float and float respectively, causing them to be silently dropped from statistics aggregations
+  - `IndividualMinifigure.download()` now mirrors the conversion logic already present in `BrickSet.download()`: date parsed via `datetime.strptime` to timestamp, price cast to `float`
+- **Fixed a price of 0 being treated as no price** (Issue #153): Setting a purchase price of `0` on sets, individual minifigures, or parts was indistinguishable from having no price set at all
+  - Replaced truthiness checks (`if price`, `price or ''`) with explicit `None` checks throughout badge display, management input fields, and inline price update endpoints
+- **Fixed deleting a wish with an owner assigned** (Issue #152, branch `bugfix/issue-152`): Resolved foreign key constraint error when removing a set from the wishlist that had an owner assigned
+  - Wish owners are now deleted before the wish itself, respecting the FK constraint
+
+## 1.4
+
+### Bug Fixes
+
+- **Fixed client-side table sorting corruption** (Issue #136): Resolved data corruption when using sort buttons with DataTables header sorting in client-side pagination mode
+  - Sort buttons now trigger actual table header clicks instead of using separate `columns.sort()`
+  - Header clicks sync button states to match current sort
+  - Prevents misaligned images, colors, and links when mixing sorting methods
+- **Fixed storage deletion error handling**: Added proper validation and user-friendly error messages when attempting to delete storage locations that are still in use
+  - Shows detailed count of items using the storage (sets, individual minifigures, individual parts, part lots)
+  - Provides clickable link to storage details page for easy navigation
+  - Prevents accidental deletion of storage locations with referenced items
+- **Fixed bulk parts redirect**: Corrected endpoint reference from `individual_part.list_all` to `individual_part.list` after route function rename
+- **Fixed purchase location templates**: Created missing template files for purchase location pages
+- **Fixed set refresh functionality**: Resolved issues with refreshing sets from Rebrickable
+  - Fixed foreign key constraint errors during refresh by reusing existing set IDs instead of generating new UUIDs
+  - Implemented UPDATE-then-INSERT pattern to properly update existing parts while preserving user tracking data
+  - Part quantities now correctly sync with Rebrickable during refresh
+  - User tracking data (`checked`, `missing`, `damaged`) is preserved across refreshes
+  - New parts from Rebrickable are added to local inventory during refresh
+  - Orphaned parts (parts no longer in Rebrickable's inventory) are now properly removed during refresh
+  - Refresh now works correctly for both set parts and minifigure parts
+  - Uses temporary tracking table to identify which parts are still valid before cleanup
+- **Fixed Socket.IO connections behind reverse proxies**: Resolved WebSocket disconnection issues when using Traefik, Nginx, or other reverse proxies
+  - Root cause: Setting `BK_DOMAIN_NAME` enables strict CORS checking that fails with reverse proxies
+  - Solution: Leave `BK_DOMAIN_NAME` empty for reverse proxy deployments (allows all origins by default)
+  - Added debug logging for Socket.IO connections to help troubleshoot proxy issues
+- **Fixed bulk import hanging on empty set numbers**: Resolved issue where trailing commas in bulk import input would cause infinite loops
+  - Empty strings from trailing commas (e.g., `"10312, 21348, "`) are now filtered out before processing
+  - Prevents "Set number cannot be empty" errors from blocking the bulk import queue
+- **Added notes display toggles**: Added configuration options to show/hide notes on grid and detail views
+  - New `BK_SHOW_NOTES_GRID` setting (default: `false`) - controls whether notes appear on grid view cards
+  - New `BK_SHOW_NOTES_DETAIL` setting (default: `true`) - controls whether notes appear on set detail pages
+  - Notes display as an info alert box below badges when enabled
+  - Both settings can be toggled in Admin -> Live Settings panel without container restart
+  - Fixed consolidated SQL query to include description field for proper notes display in server-side pagination
+- **Fixed permission denied when running as non-root user** (Issue #138): Resolved container startup failure when using `user:` directive in docker-compose
+  - Added `chmod -R a+rX /app` to Dockerfile to ensure all files are readable regardless of build environment
+  - Added commented `user:` example in `compose.yaml` to document non-root support
+
+### Breaking Changes
+
+- **Parts default order column names changed**: The `BK_PARTS_DEFAULT_ORDER` environment variable now uses `"combined"` instead of `"bricktracker_parts"` for column references
+  - If you have a custom `BK_PARTS_DEFAULT_ORDER` setting, update column references:
+    - `"bricktracker_parts"."spare"` → `"combined"."spare"`
+    - `"bricktracker_parts"."part"` → `"combined"."part"`
+    - `"bricktracker_parts"."quantity"` → `"combined"."quantity"`
+  - Or remove the custom setting to use the new defaults
+  - See `.env.sample` for the full list of available column names
 
 ### New Features
 
+- **Sortable Checked column** (Issue #137): The "Checked" column in set inventory tables can now be sorted
+  - Click the "Checked" header to sort by checked/unchecked status
+  - Works in both parts table and part lots table
+
+- **Quick-add individual parts toggle**: New `BK_HIDE_QUICK_ADD_INDIVIDUAL_PARTS` setting to hide the quick-add menu in set parts tables
+  - Hides the "Add to individual parts" option in the row menu dropdown
+  - Useful when you want individual parts tracking enabled but don't need quick-add from set inventory
+
+- **Individual Minifigures Tracking**
+  - Track loose/individual minifigures outside of sets
+  - Part-level tracking for individual minifigures with problem states (missing/damaged/checked)
+  - Complete metadata support (owners, tags, statuses, storage, purchase info)
+  - Purchase tracking with date, location, and price
+  - Quick navigation from set minifigures to individual instances
+  - Filter and search capabilities
+  - Feature flags:
+    - `BK_HIDE_INDIVIDUAL_MINIFIGURES`: Hides individual minifigures UI elements (navbar menu item, links from minifigure detail pages)
+    - `BK_DISABLE_INDIVIDUAL_MINIFIGURES`: Enables read-only mode - all individual minifigure pages remain accessible but with all editing fields disabled (quantity, parts table, metadata inputs), delete buttons hidden, and write operations blocked.
+
+- **Individual Parts Tracking**
+  - Track loose parts outside of sets and minifigures
+  - Quick-add functionality from set parts tables
+  - Complete metadata support (owners, tags, storage, purchase info)
+  - Problem tracking (missing/damaged/checked states)
+  - Purchase tracking with date, location, and price
+  - Bulk part addition interface
+  - Feature flags:
+    - `BK_HIDE_INDIVIDUAL_PARTS`: Hides individual parts UI elements (navbar menu item, "Add Parts" button, links from part detail pages)
+    - `BK_DISABLE_INDIVIDUAL_PARTS`: Enables read-only mode - all individual parts and lot pages remain accessible but with all editing fields disabled (quantity, missing/damaged, parts table, metadata inputs), delete buttons hidden, "Add Parts" menu item removed, and write operations blocked. The /add/ page also hides the "Adding individual parts?" section.
+
+- **Part Lots System**
+  - Organize individual parts into logical lots/collections
+  - Lot-level metadata (name, description, created date)
+  - Shared metadata across lot (storage, purchase info)
+  - View all parts in a lot with filtering
+
+- **Purchase Location Management**
+  - Centralized purchase location tracking for sets, individual minifigures, parts, and lots
+  - New purchase location management page (`/purchase-locations/`)
+  - Track which items were purchased from each location
+  - Integrated with existing storage and owner metadata systems
+
+- **Rebrickable Color Database**
+  - Caches color information from Rebrickable API
+  - Provides BrickLink color ID mapping
+  - Reduces repeated API calls for color data
+  - Supports export functionality with correct color IDs
+
+- **Export Functionality**
+  - Added export system in admin panel for sets, parts, and problem parts
+  - Export accordion in `/admin/` with three main categories:
+    - **Export Sets**: Rebrickable CSV format for collection tracking
+    - **Export All Parts**: Three formats available:
+      - Rebrickable CSV (Part, Color, Quantity)
+      - LEGO Pick-a-Brick CSV (elementId, quantity)
+      - BrickLink XML (wanted list format)
+    - **Export Missing/Damaged Parts**: Same three formats as parts exports
+  - All exports aggregate quantities automatically (parts by part+color, LEGO by element ID)
+  - BrickLink exports use proper BrickLink part numbers and color IDs when available
+  - Format information displayed in UI for user guidance
+- **Badge Order Customization**
+  - Added customizable badge ordering for set cards and detail pages
+  - Separate configurations for Grid view (`/sets/` cards) and Detail view (individual set pages)
+  - Configure via environment variables in `.env` file:
+    - `BK_BADGE_ORDER_GRID`: Comma-separated badge keys for grid view (default: theme,year,parts,total_minifigures,owner)
+    - `BK_BADGE_ORDER_DETAIL`: Comma-separated badge keys for detail view (default: all 16 badges)
+  - Can also be configured via Live Settings page in admin panel under "Default Ordering & Formatting"
+  - Changes apply immediately without restart when edited via admin panel
+  - 16 available badge types: theme, tag, year, parts, instance_count, total_minifigures, total_missing, total_damaged, owner, storage, purchase_date, purchase_location, purchase_price, instructions, rebrickable, bricklink
+- **Front Page Parts Display**
+  - Added latest/random parts section to the front page alongside sets and minifigures
+  - Shows 6 parts with quantity badges and other relevant information
+  - Respects `BK_RANDOM` configuration (random selection when enabled, latest when disabled)
+  - Respects `BK_HIDE_SPARE_PARTS` configuration
+  - Respects `BK_HIDE_ALL_PARTS` configuration for "All parts" button visibility
+- **NOT Filter Toggle Buttons**
+  - Added toggle buttons next to all filter dropdowns to switch between "equals" and "not equals" modes
+  - Visual feedback: Button displays red with "not equals" icon (≠) when in NOT mode
+  - Works with all filter types: Status, Theme, Owner, Storage, Purchase Location, Tag, and Year
+  - Supports both client-side and server-side pagination modes
+  - Filter chains persist NOT states across page reloads via URL parameters (e.g., `?theme=-frozen&status=-has-missing`)
+  - Clear filters button resets all toggle states to equals mode
+  - Enables complex filter combinations like "Show me 2025 sets that are NOT Frozen theme AND have missing pieces"
+- **Notes/Comments Field**
+  - Added general notes field to set details for storing custom notes and comments
+  - Accessible via Management -> Notes accordion section on set detail pages
+  - Auto-save functionality with visual feedback (save icon updates on change)
+  - Notes display prominently below badges on set cards when populated
+  - Supports multi-line text input with configurable row height
+  - Clear button to quickly remove notes
+- **Bulk Set Refresh**
+  - Added batch refresh functionality for updating multiple sets at once
+  - New "Bulk Refresh" button appears on Admin -> Sets needing refresh page
+  - Pre-populates text-area with comma-separated list of all sets needing refresh
+  - Follows same pattern as bulk add with progress tracking and set card preview
+  - Shows real-time progress with current set being processed
+  - Failed sets remain in input field for easy retry
+
+### Database Improvements
+
+- **Standardized ON DELETE Behavior**: Unified foreign key deletion handling across all metadata tables
+  - All metadata foreign keys now use RESTRICT (prevent deletion if referenced)
+  - Prevents accidental deletion of storage locations or purchase locations that are in use
+- **Performance Indexes Added**: New composite indexes for common query patterns
+  - `idx_individual_parts_lot_id_part_color` - Optimizes listing parts within a lot
+  - `idx_individual_parts_missing_damaged` - Optimizes finding parts with problems
+  - `idx_individual_minifigure_parts_checked` - Optimizes finding unchecked parts in minifigures
+- **Consolidated Metadata Tables**: Migration 0027 removes foreign key constraints from metadata junction tables
+  - `bricktracker_set_owners`, `bricktracker_set_tags`, `bricktracker_set_statuses` now accept any entity type
+  - Enables reusing metadata tables for sets, individual minifigures, individual parts, and lots
+- **Fixed Schema Drop Script**: Resolved foreign key constraint errors during database reset
+  - Added proper table drop ordering (children before parents)
+  - Implemented `PRAGMA foreign_keys OFF/ON` wrapping
+  - Includes all new tables from migrations 0021-0027
+
+
+### Configuration & Environment Variables
+
+- **New Configuration Options**:
+  - `BK_HIDE_INDIVIDUAL_MINIFIGURES` - Hide individual minifigures UI elements in navigation
+  - `BK_DISABLE_INDIVIDUAL_MINIFIGURES` - Block write operations for individual minifigures (view-only mode)
+  - `BK_HIDE_INDIVIDUAL_PARTS` - Hide individual parts UI elements in navigation
+  - `BK_DISABLE_INDIVIDUAL_PARTS` - Block write operations for individual parts (view-only mode)
+  - `BK_BADGE_ORDER_GRID` - Customize badge order on set cards in grid view (comma-separated list)
+  - `BK_BADGE_ORDER_DETAIL` - Customize badge order on set detail pages (comma-separated list)
+  - `BK_SHOW_NOTES_GRID` - Show notes on set cards in grid view (default: false)
+  - `BK_SHOW_NOTES_DETAIL` - Show notes on set detail pages (default: true)
+  - All new settings support live configuration updates via Admin panel
+
+### Technical Improvements
+
+- **Route Protection Decorators**: New decorator pattern for feature flag enforcement
+  - `@require_individual_minifigures_write` - Blocks writes when feature is disabled
+  - `@require_individual_parts_write` - Blocks writes when feature is disabled
+  - Allows viewing existing data while preventing new additions
+- **SQL Query Organization**: New query directory structure for individual features
+  - `bricktracker/sql/individual_minifigure/` - All individual minifigure queries
+  - `bricktracker/sql/individual_part/` - All individual part queries
+  - `bricktracker/sql/individual_part_lot/` - All part lot queries
+  - `bricktracker/sql/rebrickable_colors/` - Color reference queries
+  - `bricktracker/sql/rebrickable_parts/` - Part reference queries
+- **Database Migrations**: 7 new migrations (0021-0027)
+  - 0021: Individual minifigures and parts tables
+  - 0022: Individual part lots system with proper foreign keys
+  - 0023: Performance indexes for individual features
+  - 0024: Rebrickable colors cache table
+  - 0025: Additional composite indexes for query optimization
+  - 0026: Standardized ON DELETE behavior across metadata tables
+  - 0027: Consolidated metadata tables (remove FK constraints)
+
+
+## 1.3.1
+
+### New Functionality
+
 - **Database Integrity Check and Cleanup**
   - Added database integrity scanner to detect orphaned records and foreign key violations
   - New "Check Database Integrity" button in admin panel scans for issues
   - Detects orphaned sets, parts, and parts with missing set references
-  - Two-step cleanup process with Bootstrap modal confirmation
   - Warning prompts users to backup database before cleanup
-  - Automatic cleanup removes all orphaned records in one operation
+  - Cleanup removes all orphaned records in one operation
   - Detailed scan results show affected records with counts and descriptions
 - **Database Optimization**
   - Added "Optimize Database" button to re-create performance indexes
@@ -20,14 +285,13 @@
   - Runs `PRAGMA optimize` for additional query plan optimization
   - Helpful after importing backup databases that may lack performance optimizations
 
-## 1.3.1
-
 ### Bug Fixes
 
 - **Fixed foreign key constraint errors during set imports**: Resolved `FOREIGN KEY constraint failed` errors when importing sets with parts and minifigures
   - Fixed insertion order in `bricktracker/part.py`: Parent records (`rebrickable_parts`) now inserted before child records (`bricktracker_parts`)
   - Fixed insertion order in `bricktracker/minifigure.py`: Parent records (`rebrickable_minifigures`) now inserted before child records (`bricktracker_minifigures`)
   - Ensures foreign key references are valid when SQLite checks constraints
+  
 - **Fixed set metadata updates**: Owner, status, and tag checkboxes now properly persist changes on set details page
   - Fixed `update_set_state()` method to commit database transactions (was using deferred execution without commit)
   - All metadata updates (owner, status, tags, storage, purchase info) now work consistently

Dockerfile

@@ -11,6 +11,9 @@ RUN pip install --no-cache-dir -r requirements.txt
 # Bricktracker
 COPY . .
 
+# Ensure all files are readable by non-root users (supports user: directive in compose)
+RUN chmod -R a+rX /app
+
 # Set executable permissions for entrypoint script
 RUN chmod +x entrypoint.sh
 

README.md

@@ -6,6 +6,8 @@ A web application for organizing and tracking LEGO sets, parts, and minifigures.
 
 <a href="https://www.buymeacoffee.com/frederikb" target="_blank"><img src="https://cdn.buymeacoffee.com/buttons/v2/default-yellow.png" alt="Buy Me A Coffee" height="41" width="174"></a>
 
+<a href="https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_button_id=48JEEKLCGB8DJ"><img src="./docs/images/blue.svg" height="40"></a>  
+
 ## Features
 
 - Track multiple LEGO sets with their parts and minifigures

bricktracker/app.py

@@ -11,12 +11,15 @@ from werkzeug.middleware.proxy_fix import ProxyFix
 from bricktracker.configuration_list import BrickConfigurationList
 from bricktracker.login import LoginManager
 from bricktracker.navbar import Navbar
+from bricktracker.sidecar import BrickSidecar
 from bricktracker.sql import close
 from bricktracker.template_filters import replace_query_filter
 from bricktracker.version import __version__
 from bricktracker.views.add import add_page
 from bricktracker.views.admin.admin import admin_page
+from bricktracker.views.admin.custom_field import admin_custom_field_page
 from bricktracker.views.admin.database import admin_database_page
+from bricktracker.views.admin.export import admin_export_page
 from bricktracker.views.admin.image import admin_image_page
 from bricktracker.views.admin.instructions import admin_instructions_page
 from bricktracker.views.admin.owner import admin_owner_page
@@ -30,11 +33,15 @@ from bricktracker.views.admin.theme import admin_theme_page
 from bricktracker.views.data import data_page
 from bricktracker.views.error import error_404
 from bricktracker.views.index import index_page
+from bricktracker.views.individual_minifigure import individual_minifigure_page
+from bricktracker.views.individual_part import individual_part_page
 from bricktracker.views.instructions import instructions_page
 from bricktracker.views.login import login_page
 from bricktracker.views.minifigure import minifigure_page
 from bricktracker.views.part import part_page
+from bricktracker.views.purchase_location import purchase_location_page
 from bricktracker.views.set import set_page
+from bricktracker.views.sidecar import sidecar_page
 from bricktracker.views.statistics import statistics_page
 from bricktracker.views.storage import storage_page
 from bricktracker.views.wish import wish_page
@@ -137,18 +144,24 @@ def setup_app(app: Flask) -> None:
     app.register_blueprint(add_page)
     app.register_blueprint(data_page)
     app.register_blueprint(index_page)
+    app.register_blueprint(individual_minifigure_page)
+    app.register_blueprint(individual_part_page)
     app.register_blueprint(instructions_page)
     app.register_blueprint(login_page)
     app.register_blueprint(minifigure_page)
     app.register_blueprint(part_page)
+    app.register_blueprint(purchase_location_page)
     app.register_blueprint(set_page)
+    app.register_blueprint(sidecar_page)
     app.register_blueprint(statistics_page)
     app.register_blueprint(storage_page)
     app.register_blueprint(wish_page)
 
     # Register admin routes
     app.register_blueprint(admin_page)
+    app.register_blueprint(admin_custom_field_page)
     app.register_blueprint(admin_database_page)
+    app.register_blueprint(admin_export_page)
     app.register_blueprint(admin_image_page)
     app.register_blueprint(admin_instructions_page)
     app.register_blueprint(admin_retired_page)
@@ -187,6 +200,16 @@ def setup_app(app: Flask) -> None:
     # Register custom Jinja2 filters
     app.jinja_env.filters['replace_query'] = replace_query_filter
 
+    # Expose the sidecar feature flag and helper to every template so they can
+    # do `{% if sidecar_enabled %}`. Health is best-effort and cached, so this
+    # is cheap to evaluate on render and never blocks on a dead sidecar.
+    @app.context_processor
+    def inject_sidecar() -> dict[str, object]:
+        return {
+            'sidecar_enabled': BrickSidecar.enabled(),
+            'sidecar': BrickSidecar,
+        }
+
     # Make sure all connections are closed at the end
     @app.teardown_request
     def teardown_request(_: BaseException | None) -> None:

bricktracker/config.py

@@ -10,6 +10,8 @@ from typing import Any, Final
 CONFIG: Final[list[dict[str, Any]]] = [
     {'n': 'AUTHENTICATION_PASSWORD', 'd': ''},
     {'n': 'AUTHENTICATION_KEY', 'd': ''},
+    # BrickLink minifigure links disabled - Rebrickable doesn't provide BrickLink minifigure IDs
+    # {'n': 'BRICKLINK_LINK_MINIFIGURE_PATTERN', 'd': 'https://www.bricklink.com/v2/catalog/catalogitem.page?M={figure}'},  # noqa: E501
     {'n': 'BRICKLINK_LINK_PART_PATTERN', 'd': 'https://www.bricklink.com/v2/catalog/catalogitem.page?P={part}&C={color}'},  # noqa: E501
     {'n': 'BRICKLINK_LINK_SET_PATTERN', 'd': 'https://www.bricklink.com/v2/catalog/catalogitem.page?S={set_num}'},  # noqa: E501
     {'n': 'BRICKLINK_LINKS', 'c': bool},
@@ -17,6 +19,10 @@ CONFIG: Final[list[dict[str, Any]]] = [
     {'n': 'DATABASE_TIMESTAMP_FORMAT', 'd': '%Y-%m-%d-%H-%M-%S'},
     {'n': 'DEBUG', 'c': bool},
     {'n': 'DEFAULT_TABLE_PER_PAGE', 'd': 25, 'c': int},
+    {'n': 'DISABLE_INDIVIDUAL_MINIFIGURES', 'c': bool},
+    {'n': 'DISABLE_INDIVIDUAL_PARTS', 'c': bool},
+    {'n': 'DISABLE_QUICK_ADD_INDIVIDUAL_PARTS', 'c': bool},
+    {'n': 'HIDE_QUICK_ADD_INDIVIDUAL_PARTS', 'c': bool},
     {'n': 'DOMAIN_NAME', 'e': 'DOMAIN_NAME', 'd': ''},
     {'n': 'FILE_DATETIME_FORMAT', 'd': '%d/%m/%Y, %H:%M:%S'},
     {'n': 'HOST', 'd': '0.0.0.0'},
@@ -29,7 +35,9 @@ CONFIG: Final[list[dict[str, Any]]] = [
     {'n': 'ADMIN_DEFAULT_EXPANDED_SECTIONS', 'd': ['database'], 'c': list},
     {'n': 'HIDE_ALL_INSTRUCTIONS', 'c': bool},
     {'n': 'HIDE_ALL_MINIFIGURES', 'c': bool},
+    {'n': 'HIDE_INDIVIDUAL_MINIFIGURES', 'c': bool},
     {'n': 'HIDE_ALL_PARTS', 'c': bool},
+    {'n': 'HIDE_INDIVIDUAL_PARTS', 'c': bool},
     {'n': 'HIDE_ALL_PROBLEMS_PARTS', 'e': 'BK_HIDE_MISSING_PARTS', 'c': bool},
     {'n': 'HIDE_ALL_SETS', 'c': bool},
     {'n': 'HIDE_ALL_STORAGES', 'c': bool},
@@ -47,7 +55,7 @@ CONFIG: Final[list[dict[str, Any]]] = [
     {'n': 'NO_THREADED_SOCKET', 'c': bool},
     {'n': 'PARTS_SERVER_SIDE_PAGINATION', 'c': bool},
     {'n': 'SETS_SERVER_SIDE_PAGINATION', 'c': bool},
-    {'n': 'PARTS_DEFAULT_ORDER', 'd': '"rebrickable_parts"."name" ASC, "rebrickable_parts"."color_name" ASC, "bricktracker_parts"."spare" ASC'},  # noqa: E501
+    {'n': 'PARTS_DEFAULT_ORDER', 'd': '"rebrickable_parts"."name" ASC, "rebrickable_parts"."color_name" ASC, "combined"."spare" ASC'},  # noqa: E501
     {'n': 'PARTS_FOLDER', 'd': 'data/parts'},
     {'n': 'PARTS_PAGINATION_SIZE_DESKTOP', 'd': 10, 'c': int},
     {'n': 'PARTS_PAGINATION_SIZE_MOBILE', 'd': 5, 'c': int},
@@ -97,4 +105,35 @@ CONFIG: Final[list[dict[str, Any]]] = [
     {'n': 'STATISTICS_SHOW_CHARTS', 'd': True, 'c': bool},
     {'n': 'STATISTICS_DEFAULT_EXPANDED', 'd': True, 'c': bool},
     {'n': 'DARK_MODE', 'c': bool},
+    {'n': 'BADGE_ORDER_GRID', 'd': ['theme', 'year', 'parts', 'total_minifigures', 'instructions', 'owner'], 'c': list},
+    {'n': 'BADGE_ORDER_DETAIL', 'd': ['theme', 'tag', 'year', 'retired', 'parts', 'instance_count', 'total_minifigures', 'total_missing', 'total_damaged', 'owner', 'storage', 'purchase_date', 'purchase_location', 'purchase_price', 'msrp', 'value', 'dimensions', 'weight', 'instructions', 'rebrickable', 'bricklink'], 'c': list},
+    {'n': 'SHOW_NOTES_GRID', 'd': False, 'c': bool},
+    {'n': 'SHOW_NOTES_DETAIL', 'd': True, 'c': bool},
+    # Sidecar (brickset-sidecar / BrickData) integration. Opt-in: an empty
+    # SIDECAR_URL means the whole feature is off and everything degrades.
+    {'n': 'SIDECAR_URL', 'd': ''},
+    {'n': 'SIDECAR_TIMEOUT', 'd': 5, 'c': int},
+    {'n': 'SIDECAR_DEFAULT_COVER', 'd': 'rebrickable'},
+    # Opt-in browsing of every BrickLink image the sidecar exposes for a set
+    # (a carousel on the detail page + extra cover sources). Off keeps the
+    # plain single cover behaviour.
+    {'n': 'SIDECAR_ADDITIONAL_IMAGES', 'd': False, 'c': bool},
+    {'n': 'SIDECAR_RETAIL_REGION', 'd': 'US'},
+    {'n': 'SIDECAR_AUTO_FETCH_PRICE', 'c': bool},
+    # Currency to request BrickLink market values in (e.g. EUR). Empty lets the
+    # sidecar use its default. Sent as a query parameter only when set; sidecars
+    # that do not support it simply ignore it.
+    {'n': 'SIDECAR_CURRENCY', 'd': ''},
+    # Per-element show/hide toggles for the BrickData enrichment on the set
+    # detail page. All default on so behaviour is unchanged; badges are handled
+    # separately via BADGE_ORDER_DETAIL.
+    {'n': 'SIDECAR_SHOW_DESIGNER', 'd': True, 'c': bool},
+    {'n': 'SIDECAR_SHOW_DESCRIPTION', 'd': True, 'c': bool},
+    {'n': 'SIDECAR_SHOW_NOTES', 'd': True, 'c': bool},
+    {'n': 'SIDECAR_SHOW_PRICE_PAID', 'd': True, 'c': bool},
+    {'n': 'SIDECAR_SHOW_PRICE_MSRP', 'd': True, 'c': bool},
+    {'n': 'SIDECAR_SHOW_PRICE_MARKET', 'd': True, 'c': bool},
+    # Which BrickLink market value the set-detail "Change vs paid" compares
+    # against: 'used' or 'new'. Statistics shows both regardless.
+    {'n': 'SIDECAR_PRICE_BASIS', 'd': 'used'},
 ]

bricktracker/config_manager.py

@@ -17,7 +17,9 @@ LIVE_CHANGEABLE_VARS: Final[List[str]] = [
     'BK_ADMIN_DEFAULT_EXPANDED_SECTIONS',
     'BK_HIDE_ALL_INSTRUCTIONS',
     'BK_HIDE_ALL_MINIFIGURES',
+    'BK_HIDE_INDIVIDUAL_MINIFIGURES',
     'BK_HIDE_ALL_PARTS',
+    'BK_HIDE_INDIVIDUAL_PARTS',
     'BK_HIDE_ALL_PROBLEMS_PARTS',
     'BK_HIDE_ALL_SETS',
     'BK_HIDE_ALL_STORAGES',
@@ -26,6 +28,7 @@ LIVE_CHANGEABLE_VARS: Final[List[str]] = [
     'BK_HIDE_TABLE_DAMAGED_PARTS',
     'BK_HIDE_TABLE_MISSING_PARTS',
     'BK_HIDE_TABLE_CHECKED_PARTS',
+    'BK_DISABLE_QUICK_ADD_INDIVIDUAL_PARTS',
     'BK_HIDE_WISHES',
     'BK_MINIFIGURES_PAGINATION_SIZE_DESKTOP',
     'BK_MINIFIGURES_PAGINATION_SIZE_MOBILE',
@@ -54,6 +57,11 @@ LIVE_CHANGEABLE_VARS: Final[List[str]] = [
     'BK_STATISTICS_SHOW_CHARTS',
     'BK_STATISTICS_DEFAULT_EXPANDED',
     'BK_DARK_MODE',
+    # Badge order preferences
+    'BK_BADGE_ORDER_GRID',
+    'BK_BADGE_ORDER_DETAIL',
+    'BK_SHOW_NOTES_GRID',
+    'BK_SHOW_NOTES_DETAIL',
     # Default ordering and formatting
     'BK_INSTRUCTIONS_ALLOWED_EXTENSIONS',
     'BK_MINIFIGURES_DEFAULT_ORDER',
@@ -63,6 +71,8 @@ LIVE_CHANGEABLE_VARS: Final[List[str]] = [
     'BK_STORAGE_DEFAULT_ORDER',
     'BK_WISHES_DEFAULT_ORDER',
     # URL and Pattern Variables
+    # BrickLink minifigure links disabled - no ID mapping available
+    # 'BK_BRICKLINK_LINK_MINIFIGURE_PATTERN',
     'BK_BRICKLINK_LINK_PART_PATTERN',
     'BK_BRICKLINK_LINK_SET_PATTERN',
     'BK_REBRICKABLE_IMAGE_NIL',
@@ -76,7 +86,23 @@ LIVE_CHANGEABLE_VARS: Final[List[str]] = [
     'BK_RETIRED_SETS_FILE_URL',
     'BK_RETIRED_SETS_PATH',
     'BK_THEMES_FILE_URL',
-    'BK_THEMES_PATH'
+    'BK_THEMES_PATH',
+    # Sidecar integration (everything but the URL is live-changeable; the URL
+    # itself requires a restart, see RESTART_REQUIRED_VARS)
+    'BK_SIDECAR_TIMEOUT',
+    'BK_SIDECAR_DEFAULT_COVER',
+    'BK_SIDECAR_RETAIL_REGION',
+    'BK_SIDECAR_AUTO_FETCH_PRICE',
+    'BK_SIDECAR_ADDITIONAL_IMAGES',
+    'BK_SIDECAR_CURRENCY',
+    # Per-element show/hide toggles for the BrickData set-detail enrichment
+    'BK_SIDECAR_SHOW_DESIGNER',
+    'BK_SIDECAR_SHOW_DESCRIPTION',
+    'BK_SIDECAR_SHOW_NOTES',
+    'BK_SIDECAR_SHOW_PRICE_PAID',
+    'BK_SIDECAR_SHOW_PRICE_MSRP',
+    'BK_SIDECAR_SHOW_PRICE_MARKET',
+    'BK_SIDECAR_PRICE_BASIS'
 ]
 
 # Environment variables that require restart
@@ -85,6 +111,8 @@ RESTART_REQUIRED_VARS: Final[List[str]] = [
     'BK_AUTHENTICATION_KEY',
     'BK_DATABASE_PATH',
     'BK_DEBUG',
+    'BK_DISABLE_INDIVIDUAL_PARTS',
+    'BK_DISABLE_INDIVIDUAL_MINIFIGURES',
     'BK_DOMAIN_NAME',
     'BK_HOST',
     'BK_PORT',
@@ -102,7 +130,10 @@ RESTART_REQUIRED_VARS: Final[List[str]] = [
     'BK_PURCHASE_DATE_FORMAT',
     'BK_PURCHASE_CURRENCY',
     'BK_REBRICKABLE_USER_AGENT',
-    'BK_USER_AGENT'
+    'BK_USER_AGENT',
+    # Sidecar base URL only: changing it rebinds the whole feature, so it needs
+    # a restart. The other sidecar settings are live-changeable.
+    'BK_SIDECAR_URL'
 ]
 
 class ConfigManager:
@@ -179,8 +210,8 @@ class ConfigManager:
 
     def _cast_value(self, var_name: str, value: Any) -> Any:
         """Cast value to appropriate type based on variable name"""
-        # List variables (admin sections) - Check this FIRST before boolean check
-        if 'sections' in var_name.lower():
+        # List variables (admin sections, badge order) - Check this FIRST before boolean check
+        if any(keyword in var_name.lower() for keyword in ['sections', 'badge_order', 'allowed_extensions']):
             if isinstance(value, str):
                 return [section.strip() for section in value.split(',') if section.strip()]
             elif isinstance(value, list):
@@ -188,13 +219,13 @@ class ConfigManager:
             else:
                 return []
         # Integer variables (pagination sizes, delays, etc.) - Check BEFORE boolean check
-        if any(keyword in var_name.lower() for keyword in ['_size', '_page', 'delay', 'min_', 'per_page', 'page_size']):
+        if any(keyword in var_name.lower() for keyword in ['_size', '_page', 'delay', 'min_', 'per_page', 'page_size', '_timeout', '_hours']):
             try:
                 return int(value)
             except (ValueError, TypeError):
                 return 0
         # Boolean variables - More specific patterns to avoid conflicts
-        if any(keyword in var_name.lower() for keyword in ['hide_', 'server_side_pagination', '_links', 'random', 'skip_', 'show_', 'use_', '_consolidation', '_charts', '_expanded']):
+        if any(keyword in var_name.lower() for keyword in ['hide_', 'disable_', 'server_side_pagination', '_links', 'random', 'skip_', 'show_', 'use_', '_consolidation', '_charts', '_expanded', 'auto_fetch']):
             if isinstance(value, str):
                 return value.lower() in ('true', '1', 'yes', 'on')
             return bool(value)
@@ -326,6 +357,20 @@ class ConfigManager:
             'BK_USE_REMOTE_IMAGES': 'Use remote images from Rebrickable CDN instead of local',
             'BK_STATISTICS_SHOW_CHARTS': 'Show collection growth charts on statistics page',
             'BK_STATISTICS_DEFAULT_EXPANDED': 'Expand all statistics sections by default',
-            'BK_DARK_MODE': 'Enable dark mode theme'
+            'BK_DARK_MODE': 'Enable dark mode theme',
+            'BK_SIDECAR_URL': 'Base URL of the brickset-sidecar container (e.g. http://localhost:3335). Leave empty to disable all sidecar features.',
+            'BK_SIDECAR_TIMEOUT': 'Request timeout in seconds for sidecar calls',
+            'BK_SIDECAR_DEFAULT_COVER': 'Default cover image source for bulk add: rebrickable, box, or set',
+            'BK_SIDECAR_RETAIL_REGION': 'LEGO.com retail price region for MSRP: US, UK, CA, or DE',
+            'BK_SIDECAR_AUTO_FETCH_PRICE': 'Automatically fetch BrickLink market value when viewing a set (respects the cache TTL), so you do not have to press "Fetch value". Off by default as it is the slower live path.',
+            'BK_SIDECAR_ADDITIONAL_IMAGES': 'Browse every BrickLink image the sidecar exposes for a set: adds a carousel on the set detail page and extra cover sources. Off keeps the plain single cover.',
+            'BK_SIDECAR_CURRENCY': 'Currency to request BrickLink market values in, e.g. EUR. Empty uses the sidecar default. (The sidecar must support the currency query parameter.)',
+            'BK_SIDECAR_SHOW_DESIGNER': 'Show the BrickData set designer on the set detail page',
+            'BK_SIDECAR_SHOW_DESCRIPTION': 'Show the BrickData "About this set" description on the set detail page',
+            'BK_SIDECAR_SHOW_NOTES': 'Show the BrickData Brickset notes on the set detail page',
+            'BK_SIDECAR_SHOW_PRICE_PAID': 'Show the "Paid" row (and "Change vs paid") in the BrickData price comparison',
+            'BK_SIDECAR_SHOW_PRICE_MSRP': 'Show the retail/MSRP rows (and "Saved vs retail") in the BrickData price comparison',
+            'BK_SIDECAR_SHOW_PRICE_MARKET': 'Show the "Worth now" market value rows in the BrickData price comparison',
+            'BK_SIDECAR_PRICE_BASIS': 'Which BrickLink market value the set-detail "Change vs paid" compares against: used or new (falls back to the other when one is missing). Statistics shows both regardless.'
         }
         return help_text.get(var_name, 'No help available for this variable')

bricktracker/individual_minifigure.py

@@ -0,0 +1,566 @@
+import logging
+import traceback
+from datetime import datetime
+from typing import Any, Self, TYPE_CHECKING
+from uuid import uuid4
+
+from flask import current_app, url_for
+
+from .exceptions import NotFoundException, DatabaseException, ErrorException
+from .parser import parse_minifig
+from .rebrickable import Rebrickable
+from .rebrickable_minifigure import RebrickableMinifigure
+from .set_owner_list import BrickSetOwnerList
+from .set_purchase_location_list import BrickSetPurchaseLocationList
+from .set_storage_list import BrickSetStorageList
+from .set_tag_list import BrickSetTagList
+from .sql import BrickSQL
+
+if TYPE_CHECKING:
+    from .socket import BrickSocket
+
+logger = logging.getLogger(__name__)
+
+
+# Individual minifigure (not associated with a set)
+class IndividualMinifigure(RebrickableMinifigure):
+    # Queries
+    select_query: str = 'individual_minifigure/select/by_id'
+    insert_query: str = 'individual_minifigure/insert'
+
+    # Delete an individual minifigure
+    def delete(self, /) -> None:
+        BrickSQL().executescript(
+            'individual_minifigure/delete',
+            id=self.fields.id
+        )
+
+    # Import an individual minifigure into the database
+    def download(self, socket: 'BrickSocket', data: dict[str, Any], /) -> bool:
+        # Load the minifigure
+        if not self.load(socket, data, from_download=True):
+            return False
+
+        try:
+            # Insert into the database
+            socket.auto_progress(
+                message='Minifigure {figure}: inserting into database'.format(
+                    figure=self.fields.figure
+                ),
+                increment_total=True,
+            )
+
+            # Generate an UUID for self
+            self.fields.id = str(uuid4())
+
+            # Save the storage
+            storage = BrickSetStorageList.get(
+                data.get('storage', ''),
+                allow_none=True
+            )
+            self.fields.storage = storage.fields.id if storage else None
+
+            # Save the purchase location
+            purchase_location = BrickSetPurchaseLocationList.get(
+                data.get('purchase_location', ''),
+                allow_none=True
+            )
+            self.fields.purchase_location = purchase_location.fields.id if purchase_location else None
+
+            # Save purchase date and price
+            purchase_date = data.get('purchase_date', None)
+            if purchase_date == '':
+                purchase_date = None
+            if purchase_date is not None:
+                try:
+                    purchase_date = datetime.strptime(purchase_date, '%Y/%m/%d').timestamp()
+                except Exception:
+                    purchase_date = None
+            self.fields.purchase_date = purchase_date
+
+            purchase_price = data.get('purchase_price', None)
+            if purchase_price == '':
+                purchase_price = None
+            if purchase_price is not None:
+                try:
+                    purchase_price = float(purchase_price)
+                except Exception:
+                    purchase_price = None
+            self.fields.purchase_price = purchase_price
+
+            # Save quantity and description
+            self.fields.quantity = int(data.get('quantity', 1))
+            self.fields.description = data.get('description', '')
+
+            # IMPORTANT: Insert rebrickable minifigure FIRST
+            # bricktracker_individual_minifigures has FK to rebrickable_minifigures
+            self.insert_rebrickable_loose()
+
+            # Now insert into bricktracker_individual_minifigures
+            # Use no_defer=True to ensure the insert happens before we insert parts
+            # (parts have a foreign key constraint on this id)
+            self.insert(commit=False, no_defer=True)
+
+            # Save the owners
+            owners: list[str] = list(data.get('owners', []))
+            for id in owners:
+                owner = BrickSetOwnerList.get(id)
+                owner.update_individual_minifigure_state(self, state=True)
+
+            # Save the tags
+            tags: list[str] = list(data.get('tags', []))
+            for id in tags:
+                tag = BrickSetTagList.get(id)
+                tag.update_individual_minifigure_state(self, state=True)
+
+            # Load the parts (elements) for this minifigure
+            if not self.download_parts(socket):
+                return False
+
+            # Commit the transaction to the database
+            socket.auto_progress(
+                message='Minifigure {figure}: writing to the database'.format(
+                    figure=self.fields.figure
+                ),
+                increment_total=True,
+            )
+
+            BrickSQL().commit()
+
+            # Info
+            logger.info('Minifigure {figure}: imported (id: {id})'.format(
+                figure=self.fields.figure,
+                id=self.fields.id,
+            ))
+
+            # Complete
+            socket.complete(
+                message='Minifigure {figure}: imported (<a href="{url}">Go to the minifigure</a>)'.format(
+                    figure=self.fields.figure,
+                    url=self.url()
+                ),
+                download=True
+            )
+
+        except Exception as e:
+            socket.fail(
+                message='Error while importing minifigure {figure}: {error}'.format(
+                    figure=self.fields.figure,
+                    error=e,
+                )
+            )
+
+            logger.debug(traceback.format_exc())
+
+            return False
+
+        return True
+
+    # Download parts (elements) for this individual minifigure
+    def download_parts(self, socket: 'BrickSocket', /) -> bool:
+        try:
+            # Check if we have cached parts data from load()
+            if hasattr(self, '_cached_parts_response'):
+                response = self._cached_parts_response
+                logger.debug('Using cached parts data from load()')
+            else:
+                # Need to fetch parts data
+                socket.auto_progress(
+                    message='Minifigure {figure}: loading parts from Rebrickable'.format(
+                        figure=self.fields.figure
+                    ),
+                    increment_total=True,
+                )
+
+                logger.debug('rebrick.lego.get_minifig_elements("{figure}")'.format(
+                    figure=self.fields.figure,
+                ))
+
+                # Load parts data from Rebrickable API
+                import json
+                from rebrick import lego
+
+                parameters = {
+                    'api_key': current_app.config['REBRICKABLE_API_KEY'],
+                    'page_size': current_app.config['REBRICKABLE_PAGE_SIZE'],
+                }
+
+                response = json.loads(lego.get_minifig_elements(
+                    self.fields.figure,
+                    **parameters
+                ).read())
+
+            socket.auto_progress(
+                message='Minifigure {figure}: saving parts to database'.format(
+                    figure=self.fields.figure
+                ),
+            )
+
+            # Insert each part into individual_minifigure_parts table
+            from .rebrickable_part import RebrickablePart
+
+            if 'results' in response:
+                logger.debug('Processing {count} parts for minifigure {figure}'.format(
+                    count=len(response["results"]),
+                    figure=self.fields.figure
+                ))
+
+                for idx, result in enumerate(response['results']):
+                    part_num = result['part']['part_num']
+                    color_id = result['color']['id']
+
+                    logger.debug(
+                        'Part {current}/{total}: {part_num} (color: {color_id}, quantity: {quantity})'.format(
+                            current=idx+1,
+                            total=len(response["results"]),
+                            part_num=part_num,
+                            color_id=color_id,
+                            quantity=result["quantity"]
+                        )
+                    )
+
+                    # Insert rebrickable part data first
+                    part_data = RebrickablePart.from_rebrickable(result)
+                    logger.debug('Rebrickable part data keys: {keys}'.format(
+                        keys=list(part_data.keys())
+                    ))
+
+                    # Insert into rebrickable_parts if not exists
+                    BrickSQL().execute(
+                        'rebrickable/part/insert',
+                        parameters=part_data,
+                        commit=False,
+                    )
+
+                    # Download part image if not using remote images
+                    if not current_app.config['USE_REMOTE_IMAGES']:
+                        # Create a RebrickablePart instance for image download
+                        from .set import BrickSet
+                        try:
+                            part_instance = RebrickablePart(record=part_data)
+                            from .rebrickable_image import RebrickableImage
+                            RebrickableImage(
+                                BrickSet(),  # Dummy set
+                                minifigure=self,
+                                part=part_instance,
+                            ).download()
+                        except Exception as e:
+                            logger.warning(
+                                'Could not download image for part {part_num}: {error}'.format(
+                                    part_num=part_num,
+                                    error=e
+                                )
+                            )
+
+                    # Insert into bricktracker_individual_minifigure_parts
+                    individual_part_params = {
+                        'id': self.fields.id,
+                        'part': part_num,
+                        'color': color_id,
+                        'spare': result.get('is_spare', False),
+                        'quantity': result['quantity'],
+                        'element': result.get('element_id'),
+                        'rebrickable_inventory': result['id'],
+                    }
+                    logger.debug('Individual part params: {params}'.format(
+                        params=individual_part_params
+                    ))
+
+                    BrickSQL().execute(
+                        'individual_minifigure/part/insert',
+                        parameters=individual_part_params,
+                        commit=False,
+                    )
+
+                logger.debug('Successfully inserted all {count} parts'.format(
+                    count=len(response["results"])
+                ))
+            else:
+                logger.warning('No results in parts response for minifigure {figure}'.format(
+                    figure=self.fields.figure
+                ))
+
+            # Clean up cached data
+            if hasattr(self, '_cached_parts_response'):
+                delattr(self, '_cached_parts_response')
+
+            return True
+
+        except Exception as e:
+            socket.fail(
+                message='Error loading parts for minifigure {figure}: {error}'.format(
+                    figure=self.fields.figure,
+                    error=e,
+                )
+            )
+            logger.debug(traceback.format_exc())
+            return False
+
+    # Insert the individual minifigure from Rebrickable
+    def insert_rebrickable_loose(self, /) -> None:
+        # Insert the Rebrickable minifigure to the database
+        # Note: We override the parent's insert_rebrickable since we don't have a brickset
+        from .rebrickable_image import RebrickableImage
+
+        # Explicitly build parameters for rebrickable_minifigures insert
+        params = {
+            'figure': self.fields.figure,
+            'number': self.fields.number,
+            'name': self.fields.name,
+            'image': self.fields.image,
+            'number_of_parts': self.fields.number_of_parts,
+        }
+
+        BrickSQL().execute(
+            RebrickableMinifigure.insert_query,
+            parameters=params,
+            commit=False,
+        )
+
+        # Download image locally if not using remote images
+        if not current_app.config['USE_REMOTE_IMAGES']:
+            # Create a dummy BrickSet for RebrickableImage
+            # RebrickableImage checks minifigure first before set, so this works
+            from .set import BrickSet
+            try:
+                RebrickableImage(
+                    BrickSet(),  # Dummy set - not used since minifigure takes priority
+                    minifigure=self,
+                ).download()
+                logger.debug('Downloaded image for individual minifigure {figure}'.format(
+                    figure=self.fields.figure
+                ))
+            except Exception as e:
+                logger.warning(
+                    'Could not download image for individual minifigure {figure}: {error}'.format(
+                        figure=self.fields.figure,
+                        error=e
+                    )
+                )
+
+    # Load the minifigure from Rebrickable
+    def load(
+        self,
+        socket: 'BrickSocket',
+        data: dict[str, Any],
+        /,
+        *,
+        from_download=False,
+    ) -> bool:
+        # Reset the progress
+        socket.progress_count = 0
+        socket.progress_total = 2
+
+        try:
+            # Check if individual minifigures are disabled
+            from flask import current_app
+            if current_app.config.get('DISABLE_INDIVIDUAL_MINIFIGURES', False):
+                raise ErrorException(
+                    'Individual minifigures system is disabled. '
+                    'Only set-based minifigures can be added.'
+                )
+
+            socket.auto_progress(message='Parsing minifigure number')
+            figure = parse_minifig(str(data['figure']))
+
+            socket.auto_progress(
+                message='Minifigure {figure}: loading from Rebrickable'.format(
+                    figure=figure,
+                ),
+            )
+
+            logger.debug('rebrick.lego.get_minifig_elements("{figure}")'.format(
+                figure=figure,
+            ))
+
+            # Load from Rebrickable using get_minifig_elements
+            # This gives us both minifigure info and parts in one call
+            import json
+            from rebrick import lego
+
+            parameters = {
+                'api_key': current_app.config['REBRICKABLE_API_KEY'],
+                'page_size': current_app.config['REBRICKABLE_PAGE_SIZE'],
+            }
+
+            response = json.loads(lego.get_minifig_elements(
+                figure,
+                **parameters
+            ).read())
+
+            # Extract minifigure info from the first part's metadata
+            if 'results' in response and len(response['results']) > 0:
+                first_part = response['results'][0]
+
+                # Build minifigure data from the response
+                self.fields.figure = first_part['set_num']
+                self.fields.number_of_parts = response['count']
+
+                # We need to fetch the proper name and image from get_minifig()
+                # This is a small additional call but gives us the proper minifigure data
+                try:
+                    # get_minifig() only needs api_key, not page_size
+                    minifig_params = {
+                        'api_key': current_app.config['REBRICKABLE_API_KEY']
+                    }
+                    minifig_response = json.loads(lego.get_minifig(
+                        figure,
+                        **minifig_params
+                    ).read())
+                    self.fields.name = minifig_response.get('name', "Minifigure {figure}".format(figure=figure))
+
+                    # Use the minifig image from get_minifig() - this is the assembled minifig
+                    self.fields.image = minifig_response.get('set_img_url')
+
+                    # Extract number from figure (e.g., fig-005997 -> 5997)
+                    try:
+                        self.fields.number = int(figure.split('-')[1])
+                    except:
+                        self.fields.number = 0
+
+                except Exception as e:
+                    logger.warning('Could not fetch minifigure name: {error}'.format(
+                        error=e
+                    ))
+                    self.fields.name = "Minifigure {figure}".format(figure=figure)
+                    # Try to extract number anyway
+                    try:
+                        self.fields.number = int(figure.split('-')[1])
+                    except:
+                        self.fields.number = 0
+
+                    # Fallback: try to extract image from first part with element_id
+                    self.fields.image = None
+                    for result in response['results']:
+                        if result.get('element_id') and result['part'].get('part_img_url'):
+                            self.fields.image = result['part']['part_img_url']
+                            break
+
+                # Store the parts data for later use in download
+                self._cached_parts_response = response
+            else:
+                raise NotFoundException('Minifigure {figure} has no parts in Rebrickable'.format(
+                    figure=figure
+                ))
+
+            # Download minifigure image during preview if not using remote images
+            if not from_download and not current_app.config['USE_REMOTE_IMAGES'] and self.fields.image:
+                from .rebrickable_image import RebrickableImage
+                from .set import BrickSet
+                try:
+                    RebrickableImage(
+                        BrickSet(),
+                        minifigure=self,
+                    ).download()
+                    logger.debug('Downloaded preview image for minifigure {figure}'.format(
+                        figure=self.fields.figure
+                    ))
+                except Exception as e:
+                    logger.warning(
+                        'Could not download preview image for minifigure {figure}: {error}'.format(
+                            figure=self.fields.figure,
+                            error=e
+                        )
+                    )
+
+            socket.emit('MINIFIGURE_LOADED', self.short(
+                from_download=from_download
+            ))
+
+            if not from_download:
+                socket.complete(
+                    message='Minifigure {figure}: loaded from Rebrickable'.format(
+                        figure=self.fields.figure
+                    )
+                )
+
+            return True
+
+        except Exception as e:
+            # Check if this is the "disabled" error - if so, show cleaner message
+            error_msg = str(e)
+            if 'Individual minifigures system is disabled' in error_msg:
+                socket.fail(message=error_msg)
+            else:
+                socket.fail(
+                    message='Could not load the minifigure from Rebrickable: {error}. Data: {data}'.format(
+                        error=error_msg,
+                        data=data,
+                    )
+                )
+
+            if not isinstance(e, (NotFoundException, ErrorException)):
+                logger.debug(traceback.format_exc())
+
+        return False
+
+    # Return a short form of the minifigure
+    def short(self, /, *, from_download: bool = False) -> dict[str, Any]:
+        return {
+            'download': from_download,
+            'image': self.url_for_image(),
+            'name': self.fields.name,
+            'figure': self.fields.figure,
+        }
+
+    # Select an individual minifigure by ID
+    def select_by_id(self, id: str, /) -> Self:
+        # Save the ID parameter
+        self.fields.id = id
+
+        # Import status list here to get metadata columns
+        from .set_status_list import BrickSetStatusList
+
+        # Pass metadata columns to the query (using set tables which now handle all entities)
+        context = {
+            'owners': BrickSetOwnerList.as_columns() if BrickSetOwnerList.list() else '',
+            'statuses': BrickSetStatusList.as_columns(all=True) if BrickSetStatusList.list(all=True) else '',
+            'tags': BrickSetTagList.as_columns() if BrickSetTagList.list() else '',
+        }
+
+        if not self.select(**context):
+            raise NotFoundException(
+                'Individual minifigure with ID {id} was not found in the database'.format(
+                    id=id,
+                ),
+            )
+
+        return self
+
+    # URL to this individual minifigure instance
+    def url(self, /) -> str:
+        return url_for('individual_minifigure.details', id=self.fields.id)
+
+    # String representation for debugging
+    def __repr__(self, /) -> str:
+        figure = getattr(self.fields, 'figure', 'unknown')
+        name = getattr(self.fields, 'name', 'Unknown')
+        qty = getattr(self.fields, 'quantity', 0)
+        return f'<IndividualMinifigure {figure} "{name}" qty:{qty}>'
+
+    # URL for updating quantity
+    def url_for_quantity(self, /) -> str:
+        return url_for('individual_minifigure.update_quantity', id=self.fields.id)
+
+    # URL for updating description
+    def url_for_description(self, /) -> str:
+        return url_for('individual_minifigure.update_description', id=self.fields.id)
+
+    # Parts
+    def generic_parts(self, /):
+        from .part_list import BrickPartList
+        return BrickPartList().from_individual_minifigure(self)
+
+    # Override from_rebrickable to handle minifigure data
+    @staticmethod
+    def from_rebrickable(data: dict[str, Any], /, **_) -> dict[str, Any]:
+        # Extracting number
+        number = int(str(data['set_num'])[5:])
+
+        return {
+            'figure': str(data['set_num']),
+            'number': int(number),
+            'name': str(data['set_name']),
+            'image': str(data['set_img_url']) if data.get('set_img_url') else None,
+            'number_of_parts': int(data.get('num_parts', 0)),
+        }

bricktracker/individual_minifigure_list.py

@@ -0,0 +1,98 @@
+import logging
+from typing import Self
+
+from .individual_minifigure import IndividualMinifigure
+from .record_list import BrickRecordList
+from .set_owner_list import BrickSetOwnerList
+from .set_status_list import BrickSetStatusList
+from .set_tag_list import BrickSetTagList
+
+logger = logging.getLogger(__name__)
+
+
+# Individual minifigures list
+class IndividualMinifigureList(BrickRecordList[IndividualMinifigure]):
+    # Queries
+    all_query: str = 'individual_minifigure/list/all'
+    instances_by_figure_query: str = 'individual_minifigure/select/instances_by_figure'
+    using_storage_query: str = 'individual_minifigure/list/using_storage'
+    using_purchase_location_query: str = 'individual_minifigure/list/using_purchase_location'
+    without_storage_query: str = 'individual_minifigure/list/without_storage'
+
+    def __init__(self, /):
+        super().__init__()
+
+    # Load all individual minifigures
+    def all(self, /) -> Self:
+        # Prepare context with metadata columns
+        context = {
+            'owners': BrickSetOwnerList.as_columns() if BrickSetOwnerList.list() else 'NULL AS "no_owners"',
+            'statuses': BrickSetStatusList.as_columns(all=True) if BrickSetStatusList.list(all=True) else 'NULL AS "no_statuses"',
+            'tags': BrickSetTagList.as_columns() if BrickSetTagList.list() else 'NULL AS "no_tags"',
+        }
+
+        self.list(override_query=self.all_query, **context)
+        return self
+
+    # Load all individual instances of a specific minifigure figure
+    def instances_by_figure(self, figure: str, /) -> Self:
+        self.fields.figure = figure
+
+        # Prepare context with metadata columns (using consolidated metadata tables)
+        context = {
+            'owners': BrickSetOwnerList.as_columns() if BrickSetOwnerList.list() else 'NULL AS "no_owners"',
+            'statuses': BrickSetStatusList.as_columns(all=True) if BrickSetStatusList.list(all=True) else 'NULL AS "no_statuses"',
+            'tags': BrickSetTagList.as_columns() if BrickSetTagList.list() else 'NULL AS "no_tags"',
+        }
+
+        # Load the instances from the database
+        self.list(override_query=self.instances_by_figure_query, **context)
+
+        return self
+
+    # Load all individual minifigures using a specific storage
+    def using_storage(self, storage: 'BrickSetStorage', /) -> Self:
+        # Save the storage parameter
+        self.fields.storage = storage.fields.id
+
+        # Load the minifigures from the database
+        self.list(override_query=self.using_storage_query)
+
+        return self
+
+    # Load all individual minifigures using a specific purchase location
+    def using_purchase_location(self, purchase_location: 'BrickSetPurchaseLocation', /) -> Self:
+        # Save the purchase location parameter
+        self.fields.purchase_location = purchase_location.fields.id
+
+        # Load the minifigures from the database
+        self.list(override_query=self.using_purchase_location_query)
+
+        return self
+
+    # Load all individual minifigures without storage
+    def without_storage(self, /) -> Self:
+        # Load minifigures with no storage
+        self.list(override_query=self.without_storage_query)
+
+        return self
+
+    # Base individual minifigure list
+    def list(
+        self,
+        /,
+        *,
+        override_query: str | None = None,
+        order: str | None = None,
+        limit: int | None = None,
+        **context,
+    ) -> None:
+        # Load the individual minifigures from the database
+        for record in super().select(
+            override_query=override_query,
+            order=order,
+            limit=limit,
+            **context
+        ):
+            individual_minifigure = IndividualMinifigure(record=record)
+            self.records.append(individual_minifigure)

bricktracker/individual_part.py

@@ -0,0 +1,917 @@
+import logging
+import os
+import traceback
+from typing import Any, Self, TYPE_CHECKING
+from urllib.parse import urlparse
+from uuid import uuid4
+
+from flask import current_app, url_for
+import requests
+from shutil import copyfileobj
+
+from .exceptions import NotFoundException, DatabaseException, ErrorException
+from .record import BrickRecord
+from .set_owner_list import BrickSetOwnerList
+from .set_purchase_location_list import BrickSetPurchaseLocationList
+from .set_storage_list import BrickSetStorageList
+from .set_tag_list import BrickSetTagList
+from .sql import BrickSQL
+
+if TYPE_CHECKING:
+    from .socket import BrickSocket
+
+logger = logging.getLogger(__name__)
+
+
+# Individual part (standalone, not associated with a set or minifigure)
+class IndividualPart(BrickRecord):
+    # Queries
+    select_query: str = 'individual_part/select/by_id'
+    insert_query: str = 'individual_part/insert'
+    update_query: str = 'individual_part/update'
+
+    def __init__(
+        self,
+        /,
+        *,
+        record: Any | None = None
+    ):
+        super().__init__()
+
+        # Ingest the record if it has one
+        if record is not None:
+            self.ingest(record)
+
+    # Select a specific individual part by UUID
+    def select_by_id(self, id: str, /) -> Self:
+        from .set_owner_list import BrickSetOwnerList
+        from .set_status_list import BrickSetStatusList
+        from .set_tag_list import BrickSetTagList
+
+        self.fields.id = id
+        if not self.select(
+            override_query=self.select_query,
+            owners=BrickSetOwnerList.as_columns(),
+            statuses=BrickSetStatusList.as_columns(all=True),
+            tags=BrickSetTagList.as_columns(),
+        ):
+            raise NotFoundException(
+                'Individual part with id "{id}" not found'.format(id=id)
+            )
+        return self
+
+    # Delete an individual part
+    def delete(self, /) -> None:
+        sql = BrickSQL()
+        sql.executescript(
+            'individual_part/delete',
+            id=self.fields.id
+        )
+        sql.commit()
+
+    # Generate HTML ID for form elements
+    def html_id(self, prefix: str | None = None, /) -> str:
+        components: list[str] = ['individual-part']
+
+        if prefix is not None:
+            components.append(prefix)
+
+        components.append(self.fields.part)
+        components.append(str(self.fields.color))
+        components.append(self.fields.id)
+
+        return '-'.join(components)
+
+    # URL for quantity update
+    def url_for_quantity(self, /) -> str:
+        return url_for('individual_part.update_quantity', id=self.fields.id)
+
+    # URL for description update
+    def url_for_description(self, /) -> str:
+        return url_for('individual_part.update_description', id=self.fields.id)
+
+    # URL for problem (missing/damaged) update
+    def url_for_problem(self, problem_type: str, /) -> str:
+        if problem_type == 'missing':
+            return url_for('individual_part.update_missing', id=self.fields.id)
+        elif problem_type == 'damaged':
+            return url_for('individual_part.update_damaged', id=self.fields.id)
+        else:
+            raise ValueError(f'Invalid problem type: {problem_type}')
+
+    # URL for checked status update
+    def url_for_checked(self, /) -> str:
+        return url_for('individual_part.update_checked', id=self.fields.id)
+
+    # URL for purchase date update
+    def url_for_purchase_date(self, /) -> str:
+        return url_for('individual_part.update_purchase_date', id=self.fields.id)
+
+    # URL for purchase price update
+    def url_for_purchase_price(self, /) -> str:
+        return url_for('individual_part.update_purchase_price', id=self.fields.id)
+
+    # URL for this part's detail page
+    def url(self, /) -> str:
+        return url_for('individual_part.details', id=self.fields.id)
+
+    def url_for_delete(self, /) -> str:
+        return url_for('individual_part.delete_part', id=self.fields.id)
+
+    def url_for_image(self, /) -> str:
+        if current_app.config.get('USE_REMOTE_IMAGES', False):
+            if hasattr(self.fields, 'image') and self.fields.image:
+                return self.fields.image
+            else:
+                return current_app.config.get('REBRICKABLE_IMAGE_NIL', '')
+        else:
+            from .rebrickable_image import RebrickableImage
+
+            if hasattr(self.fields, 'image') and self.fields.image:
+                image_id, _ = os.path.splitext(os.path.basename(urlparse(self.fields.image).path))
+
+                if image_id:
+                    return RebrickableImage.static_url(image_id, 'PARTS_FOLDER')
+
+            return RebrickableImage.static_url(RebrickableImage.nil_name(), 'PARTS_FOLDER')
+
+    # String representation for debugging
+    def __repr__(self, /) -> str:
+        """String representation for debugging"""
+        part_id = getattr(self.fields, 'part', 'unknown')
+        color_id = getattr(self.fields, 'color', 'unknown')
+        qty = getattr(self.fields, 'quantity', 0)
+        return f'<IndividualPart {part_id} color:{color_id} qty:{qty}>'
+
+    # Get or fetch color information from rebrickable_colors table
+    @staticmethod
+    def get_or_fetch_color(color_id: int, /) -> dict[str, Any] | None:
+        sql = BrickSQL()
+
+        # Check if color exists in cache
+        result = sql.fetchone('rebrickable_colors/select/by_color_id', parameters={'color_id': color_id})
+
+        if result:
+            # Color found in cache
+            return {
+                'color_id': result[0],
+                'name': result[1],
+                'rgb': result[2],
+                'is_trans': result[3],
+                'bricklink_color_id': result[4],
+                'bricklink_color_name': result[5]
+            }
+
+        # Color not in cache, fetch from API
+        try:
+            import rebrick
+            import json
+
+            rebrick.init(current_app.config['REBRICKABLE_API_KEY'])
+            color_response = rebrick.lego.get_color(color_id)
+            color_data = json.loads(color_response.read())
+
+            # Extract BrickLink color info
+            bricklink_color_id = None
+            bricklink_color_name = None
+
+            if 'external_ids' in color_data and 'BrickLink' in color_data['external_ids']:
+                bricklink_info = color_data['external_ids']['BrickLink']
+                if 'ext_ids' in bricklink_info and bricklink_info['ext_ids']:
+                    bricklink_color_id = bricklink_info['ext_ids'][0]
+                if 'ext_descrs' in bricklink_info and bricklink_info['ext_descrs']:
+                    bricklink_color_name = bricklink_info['ext_descrs'][0][0] if bricklink_info['ext_descrs'][0] else None
+
+            # Store in cache
+            sql.execute('rebrickable_colors/insert', parameters={
+                'color_id': color_data['id'],
+                'name': color_data['name'],
+                'rgb': color_data.get('rgb'),
+                'is_trans': color_data.get('is_trans', False),
+                'bricklink_color_id': bricklink_color_id,
+                'bricklink_color_name': bricklink_color_name
+            })
+            sql.connection.commit()
+
+            logger.info('Cached color {color_id} ({color_name}) with BrickLink ID {bricklink_id}'.format(
+                color_id=color_id,
+                color_name=color_data["name"],
+                bricklink_id=bricklink_color_id
+            ))
+
+            return {
+                'color_id': color_data['id'],
+                'name': color_data['name'],
+                'rgb': color_data.get('rgb'),
+                'is_trans': color_data.get('is_trans', False),
+                'bricklink_color_id': bricklink_color_id,
+                'bricklink_color_name': bricklink_color_name
+            }
+
+        except Exception as e:
+            logger.warning('Could not fetch color {color_id} from API: {error}'.format(
+                color_id=color_id,
+                error=e
+            ))
+            return None
+
+    # Download image for this part
+    def download_image(self, image_url: str, /, *, image_filename: str | None = None) -> None:
+        if not image_url:
+            return
+
+        # Use provided filename or extract from URL
+        if image_filename:
+            image_id = image_filename
+        else:
+            image_id, _ = os.path.splitext(os.path.basename(urlparse(image_url).path))
+
+        if not image_id:
+            return
+
+        # Build path (same pattern as RebrickableImage)
+        parts_folder = current_app.config['PARTS_FOLDER']
+        extension = 'jpg'  # Everything is saved as jpg
+
+        # If folder is an absolute path (starts with /), use it directly
+        # Otherwise, make it relative to app root (current_app.root_path)
+        if parts_folder.startswith('/'):
+            base_path = parts_folder
+        else:
+            base_path = os.path.join(current_app.root_path, parts_folder)
+
+        path = os.path.join(base_path, f'{image_id}.{extension}')
+
+        # Avoid downloading if file exists
+        if os.path.exists(path):
+            return
+
+        # Create directory if it doesn't exist
+        os.makedirs(os.path.dirname(path), exist_ok=True)
+
+        # Download the image
+        try:
+            response = requests.get(image_url, stream=True)
+            if response.ok:
+                with open(path, 'wb') as f:
+                    copyfileobj(response.raw, f)
+                logger.info('Downloaded image to {path}'.format(path=path))
+        except Exception as e:
+            logger.warning('Could not download image from {url}: {error}'.format(
+                url=image_url,
+                error=e
+            ))
+
+    # Load available colors for a part
+    def load_colors(self, socket: 'BrickSocket', data: dict[str, Any], /) -> bool:
+        # Check if individual parts are disabled
+        if current_app.config.get('DISABLE_INDIVIDUAL_PARTS', False):
+            socket.fail(message='Individual parts system is disabled.')
+            return False
+
+        try:
+            # Extract part number
+            part_num = str(data.get('part', '')).strip()
+
+            if not part_num:
+                raise ErrorException('Part number is required')
+
+            # Fetch available colors from Rebrickable
+            import rebrick
+            import json
+
+            rebrick.init(current_app.config['REBRICKABLE_API_KEY'])
+
+            # Setup progress tracking
+            socket.progress_count = 0
+            socket.progress_total = 2  # Fetch part info + fetch colors
+
+            try:
+                # Get part information for the name
+                socket.auto_progress(message='Fetching part information')
+                part_response = rebrick.lego.get_part(part_num)
+                part_data = json.loads(part_response.read())
+                part_name = part_data.get('name', part_num)
+
+                # Get all available colors for this part
+                socket.auto_progress(message='Fetching available colors')
+                colors_response = rebrick.lego.get_part_colors(part_num)
+                colors_data = json.loads(colors_response.read())
+
+                # Extract the results
+                colors = colors_data.get('results', [])
+
+                if not colors:
+                    raise ErrorException(f'No colors found for part {part_num}')
+
+                # Download images locally if USE_REMOTE_IMAGES is False
+                if not current_app.config.get('USE_REMOTE_IMAGES', False):
+                    # Add image downloads to progress
+                    socket.progress_total += len(colors)
+
+                    for color in colors:
+                        image_url = color.get('part_img_url', '')
+                        element_id = color.get('elements', [])
+                        # Use first element_id if available, otherwise extract from URL
+                        if element_id and len(element_id) > 0:
+                            image_filename = str(element_id[0])
+                        else:
+                            # Fallback: extract from URL
+                            image_filename = None
+                            if image_url:
+                                image_filename, _ = os.path.splitext(os.path.basename(urlparse(image_url).path))
+
+                        if image_url and image_filename:
+                            socket.auto_progress(message='Downloading image for {color}'.format(
+                                color=color.get("color_name", "color")
+                            ))
+                            try:
+                                self.download_image(image_url, image_filename=image_filename)
+                            except Exception as e:
+                                logger.warning('Could not download image for part {part_num} color {color_id}: {error}'.format(
+                                    part_num=part_num,
+                                    color_id=color.get("color_id"),
+                                    error=e
+                                ))
+
+                # Emit the part colors loaded event
+                logger.info('Emitting {count} colors for part {part_num} ({part_name})'.format(
+                    count=len(colors),
+                    part_num=part_num,
+                    part_name=part_name
+                ))
+
+                socket.emit(
+                    'PART_COLORS_LOADED',
+                    {
+                        'part': part_num,
+                        'part_name': part_name,
+                        'colors': colors,
+                        'count': len(colors)
+                    }
+                )
+
+                logger.info('Successfully loaded {count} colors for part {part_num}'.format(
+                    count=len(colors),
+                    part_num=part_num
+                ))
+                return True
+
+            except Exception as e:
+                error_msg = str(e)
+
+                # Provide helpful error message for printed/decorated parts
+                if '404' in error_msg or 'Not Found' in error_msg:
+                    # Check if this might be a printed part (has letters/pattern code)
+                    base_part = ''.join(c for c in part_num if c.isdigit())
+
+                    if base_part and base_part != part_num:
+                        raise ErrorException(
+                            'Part {part_num} not found in Rebrickable. This appears to be a printed/decorated part. '
+                            'Try searching for the base part number: {base_part}'.format(
+                                part_num=part_num,
+                                base_part=base_part
+                            )
+                        )
+                    else:
+                        raise ErrorException(
+                            'Part {part_num} not found in Rebrickable. '
+                            'Please verify the part number is correct.'.format(
+                                part_num=part_num
+                            )
+                        )
+                else:
+                    raise ErrorException(
+                        'Could not fetch colors for part {part_num}: {error}'.format(
+                            part_num=part_num,
+                            error=error_msg
+                        )
+                    )
+
+        except Exception as e:
+            error_msg = str(e)
+            socket.fail(message=f'Could not load part colors: {error_msg}')
+
+            if not isinstance(e, (NotFoundException, ErrorException)):
+                logger.debug(traceback.format_exc())
+
+            return False
+
+    # Add a new individual part
+    def add(self, socket: 'BrickSocket', data: dict[str, Any], /) -> bool:
+        # Check if individual parts are disabled
+        if current_app.config.get('DISABLE_INDIVIDUAL_PARTS', False):
+            socket.fail(message='Individual parts system is disabled.')
+            return False
+
+        try:
+            # Reset progress
+            socket.progress_count = 0
+            socket.progress_total = 3
+
+            socket.auto_progress(message='Validating part and color')
+
+            # Extract data
+            part_num = str(data.get('part', '')).strip()
+            color_id = int(data.get('color', -1))
+            quantity = int(data.get('quantity', 1))
+
+            if not part_num:
+                raise ErrorException('Part number is required')
+            if color_id < 0:
+                raise ErrorException('Valid color ID is required')
+            if quantity <= 0:
+                raise ErrorException('Quantity must be greater than 0')
+
+            # Check if color info was pre-loaded (from load_colors)
+            color_data = data.get('color_info', None)
+            part_name = data.get('part_name', None)
+
+            # Validate part+color exists in rebrickable_parts
+            # If not, fetch from Rebrickable or use pre-loaded data and insert
+            sql = BrickSQL()
+            result = sql.fetchone('rebrickable_parts/check_exists', parameters={'part': part_num, 'color_id': color_id})
+            exists = result[0] > 0
+
+            # Store image URL for downloading later
+            image_url = None
+
+            if not exists:
+                # Fetch full color information (with BrickLink mapping)
+                socket.auto_progress(message='Fetching color information')
+                full_color_info = IndividualPart.get_or_fetch_color(color_id)
+
+                # If we have pre-loaded color data, use it; otherwise fetch from Rebrickable
+                if color_data and part_name:
+                    # Use pre-loaded data from get_part_colors() response
+                    socket.auto_progress(message='Using cached part info')
+
+                    image_url = color_data.get('part_img_url', '')
+
+                    # Extract image_id from element_id or URL
+                    element_ids = color_data.get('elements', [])
+                    if element_ids and len(element_ids) > 0:
+                        image_id = str(element_ids[0])
+                    elif image_url:
+                        image_id, _ = os.path.splitext(os.path.basename(urlparse(image_url).path))
+                    else:
+                        image_id = None
+
+                    # Insert into rebrickable_parts using the pre-loaded data
+                    sql.execute('rebrickable_parts/insert_with_preloaded_data', parameters={
+                        'part': part_num,
+                        'color_id': color_id,
+                        'color_name': color_data.get('color_name', ''),
+                        'color_rgb': full_color_info.get('rgb') if full_color_info else None,
+                        'color_transparent': full_color_info.get('is_trans') if full_color_info else None,
+                        'bricklink_color_id': full_color_info.get('bricklink_color_id') if full_color_info else None,
+                        'bricklink_color_name': full_color_info.get('bricklink_color_name') if full_color_info else None,
+                        'name': part_name,
+                        'image': image_url,
+                        'image_id': image_id,
+                        'url': current_app.config['REBRICKABLE_LINK_PART_PATTERN'].format(part=part_num, color=color_id)
+                    })
+                else:
+                    # Fetch from Rebrickable (fallback for old workflow)
+                    socket.auto_progress(message='Fetching part info from Rebrickable')
+                    import rebrick
+                    import json
+
+                    # Initialize rebrick with API key
+                    rebrick.init(current_app.config['REBRICKABLE_API_KEY'])
+
+                    try:
+                        # Get part information
+                        part_info = json.loads(rebrick.lego.get_part(part_num).read())
+
+                        # Get color information (this also caches it in rebrickable_colors)
+                        # full_color_info already fetched above, but get again to be sure
+                        if not full_color_info:
+                            full_color_info = IndividualPart.get_or_fetch_color(color_id)
+
+                        # Get part+color specific info (for the image and element_id)
+                        part_color_info = json.loads(rebrick.lego.get_part_color(part_num, color_id).read())
+
+                        # Get image URL
+                        image_url = part_color_info.get('part_img_url', part_info.get('part_img_url', ''))
+
+                        # Extract image_id from element_ids or URL
+                        element_ids = part_color_info.get('elements', [])
+                        if element_ids and len(element_ids) > 0:
+                            image_id = str(element_ids[0])
+                        elif image_url:
+                            image_id, _ = os.path.splitext(os.path.basename(urlparse(image_url).path))
+                        else:
+                            image_id = None
+
+                        # Insert into rebrickable_parts with BrickLink color info
+                        sql.execute('rebrickable_parts/insert_with_preloaded_data', parameters={
+                            'part': part_info['part_num'],
+                            'color_id': full_color_info['color_id'] if full_color_info else color_id,
+                            'color_name': full_color_info['name'] if full_color_info else '',
+                            'color_rgb': full_color_info['rgb'] if full_color_info else None,
+                            'color_transparent': full_color_info['is_trans'] if full_color_info else None,
+                            'bricklink_color_id': full_color_info.get('bricklink_color_id') if full_color_info else None,
+                            'bricklink_color_name': full_color_info.get('bricklink_color_name') if full_color_info else None,
+                            'name': part_info['name'],
+                            'image': image_url,
+                            'image_id': image_id,
+                            'url': part_info['part_url']
+                        })
+
+                    except Exception as e:
+                        error_msg = str(e)
+
+                        # Provide helpful error message for printed/decorated parts
+                        if '404' in error_msg or 'Not Found' in error_msg:
+                            base_part = ''.join(c for c in part_num if c.isdigit())
+
+                            if base_part and base_part != part_num:
+                                raise ErrorException(
+                                    f'Part {part_num} with color {color_id} not found in Rebrickable. '
+                                    f'This appears to be a printed/decorated part. '
+                                    f'Try using the base part number: {base_part}'
+                                )
+                            else:
+                                raise ErrorException(
+                                    f'Part {part_num} with color {color_id} not found in Rebrickable. '
+                                    f'Please verify the part number is correct.'
+                                )
+                        else:
+                            raise ErrorException(
+                                f'Part {part_num} with color {color_id} not found in Rebrickable: {error_msg}'
+                            )
+            else:
+                # Part already exists in rebrickable_parts, get the image URL
+                result = sql.fetchone('rebrickable_parts/select/image_by_part_color', parameters={'part': part_num, 'color_id': color_id})
+                if result and result[0]:
+                    image_url = result[0]
+
+            # Generate UUID and insert individual part
+            socket.auto_progress(message='Adding part to collection')
+            part_id = str(uuid4())
+
+            # Get storage and purchase location
+            storage = BrickSetStorageList.get(
+                data.get('storage', ''),
+                allow_none=True
+            )
+            purchase_location = BrickSetPurchaseLocationList.get(
+                data.get('purchase_location', ''),
+                allow_none=True
+            )
+
+            # Set fields
+            self.fields.id = part_id
+            self.fields.part = part_num
+            self.fields.color = color_id
+            self.fields.quantity = quantity
+            self.fields.missing = 0
+            self.fields.damaged = 0
+            self.fields.checked = 0
+            self.fields.description = data.get('description', '')
+            self.fields.lot_id = None  # Single parts are not in a lot
+            self.fields.storage = storage.fields.id if storage else None
+            self.fields.purchase_location = purchase_location.fields.id if purchase_location else None
+            self.fields.purchase_date = data.get('purchase_date', None)
+            self.fields.purchase_price = data.get('purchase_price', None)
+
+            # Insert into database
+            self.insert(commit=False, no_defer=True)
+
+            # Save owners
+            owners: list[str] = list(data.get('owners', []))
+            for owner_id in owners:
+                owner = BrickSetOwnerList.get(owner_id)
+                owner.update_individual_part_state(self, state=True)
+
+            # Save tags
+            tags: list[str] = list(data.get('tags', []))
+            for tag_id in tags:
+                tag = BrickSetTagList.get(tag_id)
+                tag.update_individual_part_state(self, state=True)
+
+            # Commit
+            sql.connection.commit()
+
+            # Download image if we have a URL
+            if image_url:
+                try:
+                    self.download_image(image_url)
+                except Exception as e:
+                    # Don't fail the whole operation if image download fails
+                    logger.warning('Could not download image for part {part_num} color {color_id}: {error}'.format(
+                        part_num=part_num,
+                        color_id=color_id,
+                        error=e
+                    ))
+
+            # Get color name for success message
+            color_name = 'Unknown'
+            if color_data and color_data.get('color_name'):
+                color_name = color_data.get('color_name')
+            elif full_color_info and full_color_info.get('name'):
+                color_name = full_color_info.get('name')
+
+            # Generate link to part details page
+            part_url = url_for('part.details', part=part_num, color=color_id)
+
+            socket.complete(
+                message=f'Successfully added part {part_num} in {color_name} (<a href="{part_url}">View details</a>)'
+            )
+            return True
+
+        except Exception as e:
+            error_msg = str(e)
+            if 'Individual parts system is disabled' in error_msg:
+                socket.fail(message=error_msg)
+            else:
+                socket.fail(
+                    message=f'Could not add individual part: {error_msg}'
+                )
+
+            if not isinstance(e, (NotFoundException, ErrorException)):
+                logger.debug(traceback.format_exc())
+
+            return False
+
+    # Create multiple individual parts (bulk mode - no lot)
+    def create_bulk(self, socket: 'BrickSocket', data: dict[str, Any], /) -> bool:
+        """
+        Create multiple individual parts without creating a lot.
+
+        Expected data format:
+        {
+            'cart': [
+                {
+                    'part': '3001',
+                    'part_name': 'Brick 2 x 4',
+                    'color_id': 1,
+                    'color_name': 'White',
+                    'quantity': 10,
+                    'color_info': {...}
+                },
+                ...
+            ],
+            'storage': 'storage_id',
+            'purchase_location': 'purchase_location_id',
+            'purchase_date': timestamp,
+            'purchase_price': 0.0,
+            'owners': ['owner_id1', ...],
+            'tags': ['tag_id1', ...]
+        }
+        """
+        try:
+            # Validate cart data
+            cart = data.get('cart', [])
+            if not cart or not isinstance(cart, list):
+                raise ErrorException('Cart is empty or invalid')
+
+            socket.auto_progress(
+                message=f'Adding {len(cart)} individual parts',
+                increment_total=True
+            )
+
+            # Get storage
+            from .set_list import BrickSetStorageList, BrickSetPurchaseLocationList, BrickSetOwnerList, BrickSetTagList
+            storage = BrickSetStorageList.get(
+                data.get('storage', ''),
+                allow_none=True
+            )
+            storage_id = storage.fields.id if storage else None
+
+            # Get purchase location
+            purchase_location = BrickSetPurchaseLocationList.get(
+                data.get('purchase_location', ''),
+                allow_none=True
+            )
+            purchase_location_id = purchase_location.fields.id if purchase_location else None
+
+            # Get purchase info
+            purchase_date = data.get('purchase_date', None)
+            purchase_price = data.get('purchase_price', None)
+
+            # Get owners and tags
+            owners: list[str] = list(data.get('owners', []))
+            tags: list[str] = list(data.get('tags', []))
+
+            # Add all parts from cart
+            parts_added = 0
+            for idx, cart_item in enumerate(cart):
+                part_num = cart_item.get('part')
+                color_id = cart_item.get('color_id')
+                quantity = cart_item.get('quantity', 1)
+                color_info = cart_item.get('color_info', {})
+
+                socket.auto_progress(
+                    message=f'Adding part {idx + 1}/{len(cart)}: {part_num} in {cart_item.get("color_name", "unknown color")}',
+                    increment_total=True
+                )
+
+                # Create individual part with no lot_id
+                part_uuid = str(uuid4())
+
+                # Ensure color exists and get full color info (including RGB)
+                full_color_info = IndividualPart.get_or_fetch_color(color_id)
+
+                # Insert the part
+                sql = BrickSQL()
+
+                # Ensure part/color combination exists in rebrickable_parts (same as lot creation)
+                try:
+                    # Check if part exists
+                    result = sql.fetchone('rebrickable_parts/check_exists', parameters={'part': part_num, 'color_id': color_id})
+                    exists = result[0] > 0
+
+                    if not exists:
+                        # Insert part data
+                        part_name = cart_item.get('part_name', '')
+                        color_name = cart_item.get('color_name', '')
+                        image_url = color_info.get('part_img_url', '')
+
+                        # Extract image_id from element_ids or URL
+                        element_ids = color_info.get('elements', [])
+                        if element_ids and len(element_ids) > 0:
+                            image_id = str(element_ids[0])
+                        elif image_url:
+                            image_id, _ = os.path.splitext(os.path.basename(urlparse(image_url).path))
+                        else:
+                            image_id = None
+
+                        # Use full_color_info for RGB and transparency data (same as single-part add)
+                        sql.execute('rebrickable_parts/insert_part_color', parameters={
+                            'part': part_num,
+                            'name': part_name,
+                            'color_id': color_id,
+                            'color_name': color_name,
+                            'color_rgb': full_color_info.get('rgb') if full_color_info else '',
+                            'color_transparent': full_color_info.get('is_trans') if full_color_info else False,
+                            'image': image_url,
+                            'image_id': image_id,
+                            'url': current_app.config['REBRICKABLE_LINK_PART_PATTERN'].format(part=part_num, color=color_id),
+                            'bricklink_color_id': full_color_info.get('bricklink_color_id') if full_color_info else None,
+                            'bricklink_color_name': full_color_info.get('bricklink_color_name') if full_color_info else None
+                        })
+                except Exception as e:
+                    logger.warning('Could not ensure part data for {part_num}/{color_id}: {error}'.format(
+                        part_num=part_num,
+                        color_id=color_id,
+                        error=e
+                    ))
+
+                # Insert individual part
+                sql.execute(
+                    'individual_part/insert',
+                    parameters={
+                        'id': part_uuid,
+                        'part': part_num,
+                        'color': color_id,
+                        'quantity': quantity,
+                        'lot_id': None,  # No lot - this is bulk individual parts mode
+                        'storage': storage_id,
+                        'purchase_location': purchase_location_id,
+                        'purchase_date': purchase_date,
+                        'purchase_price': purchase_price,
+                        'description': None,
+                        'missing': 0,
+                        'damaged': 0,
+                        'checked': False
+                    }
+                )
+
+                # Add owners
+                for owner_id in owners:
+                    owner = BrickSetOwnerList.get(owner_id)
+                    if owner:
+                        sql.execute(
+                            'individual_part/metadata/owner/insert',
+                            parameters={
+                                'part_id': part_uuid,
+                                'owner_id': owner_id
+                            }
+                        )
+
+                # Add tags
+                for tag_id in tags:
+                    tag = BrickSetTagList.get(tag_id)
+                    if tag:
+                        sql.execute(
+                            'individual_part/metadata/tag/insert',
+                            parameters={
+                                'part_id': part_uuid,
+                                'tag_id': tag_id
+                            }
+                        )
+
+                # Download part image if available
+                image_url = color_info.get('part_img_url', '')
+                if image_url:
+                    try:
+                        self.download_image(image_url)
+                    except Exception as e:
+                        # Don't fail the whole operation if image download fails
+                        logger.warning('Could not download image for part {part_num} color {color_id}: {error}'.format(
+                            part_num=part_num,
+                            color_id=color_id,
+                            error=e
+                        ))
+
+                parts_added += 1
+
+            # Commit all changes
+            sql = BrickSQL()
+            sql.commit()
+
+            socket.auto_progress(
+                message=f'Successfully added {parts_added} individual parts',
+                increment_total=True
+            )
+
+            # Generate link to individual parts list
+            from flask import url_for
+            parts_url = url_for('individual_part.list')
+
+            # Send completion with message and link
+            socket.complete(
+                message='Successfully added {count} individual parts. <a href="{url}">View individual parts</a>'.format(
+                    count=parts_added,
+                    url=parts_url
+                ),
+                parts_added=parts_added
+            )
+            return True
+
+        except ErrorException as error:
+            socket.fail(message=str(error))
+            return False
+        except Exception as error:
+            logger.error('Failed to create bulk individual parts: {error}'.format(error=error))
+            logger.error(traceback.format_exc())
+            socket.fail(message='Failed to add individual parts: {error}'.format(error=str(error)))
+            return False
+
+    # Update a field
+    def update_field(self, field: str, value: Any, /) -> Self:
+        setattr(self.fields, field, value)
+
+        # Use a specific update query for each field
+        sql = BrickSQL()
+        sql.execute_and_commit('individual_part/update/field', parameters={
+            'id': self.fields.id,
+            'value': value
+        }, field=field)
+
+        return self
+
+    # Update problem count (missing/damaged)
+    def update_problem(self, problem: str, data: dict[str, Any], /) -> int:
+        # Handle both 'value' key and 'amount' key
+        amount: str | int = data.get('value', data.get('amount', ''))  # type: ignore
+
+        # We need a positive integer
+        try:
+            if amount == '':
+                amount = 0
+
+            amount = int(amount)
+
+            if amount < 0:
+                amount = 0
+        except Exception:
+            raise ErrorException(f'"{amount}" is not a valid integer')
+
+        if amount < 0:
+            raise ErrorException('Cannot set a negative amount')
+
+        setattr(self.fields, problem, amount)
+
+        BrickSQL().execute_and_commit(
+            f'individual_part/update/{problem}',
+            parameters={
+                'id': self.fields.id,
+                problem: amount
+            }
+        )
+
+        return amount
+
+    # Update checked status
+    def update_checked(self, data: dict[str, Any], /) -> bool:
+        # Handle both direct 'checked' key and changer.js 'value' key format
+        if data:
+            checked = data.get('checked', data.get('value', False))
+        else:
+            checked = False
+
+        checked = bool(checked)
+        self.fields.checked = 1 if checked else 0
+
+        BrickSQL().execute_and_commit(
+            'individual_part/update/checked',
+            parameters={
+                'id': self.fields.id,
+                'checked': self.fields.checked
+            }
+        )
+
+        return checked

bricktracker/individual_part_list.py

@@ -0,0 +1,100 @@
+import logging
+from typing import Self, TYPE_CHECKING
+
+from .record_list import BrickRecordList
+from .individual_part import IndividualPart
+
+if TYPE_CHECKING:
+    from .set_storage import BrickSetStorage
+
+logger = logging.getLogger(__name__)
+
+
+# List of individual parts
+class IndividualPartList(BrickRecordList):
+    # Queries
+    list_query: str = 'individual_part/list/all'
+    by_part_query: str = 'individual_part/list/by_part'
+    by_color_query: str = 'individual_part/list/by_color'
+    by_part_and_color_query: str = 'individual_part/list/by_part_and_color'
+    by_storage_query: str = 'individual_part/list/by_storage'
+    using_storage_query: str = 'individual_part/list/using_storage'
+    using_purchase_location_query: str = 'individual_part/list/using_purchase_location'
+    without_storage_query: str = 'individual_part/list/without_storage'
+    problem_query: str = 'individual_part/list/problem'
+
+    # Get all individual parts
+    def all(self, /) -> Self:
+        self.list(override_query=self.list_query)
+        return self
+
+    # Get individual parts by part number
+    def by_part(self, part: str, /) -> Self:
+        self.fields.part = part
+        self.list(override_query=self.by_part_query)
+        return self
+
+    # Get individual parts by color
+    def by_color(self, color_id: int, /) -> Self:
+        self.fields.color = color_id
+        self.list(override_query=self.by_color_query)
+        return self
+
+    # Get individual parts by part number and color
+    def by_part_and_color(self, part: str, color_id: int, /) -> Self:
+        self.fields.part = part
+        self.fields.color = color_id
+        self.list(override_query=self.by_part_and_color_query)
+        return self
+
+    # Get individual parts by storage location
+    def by_storage(self, storage: 'BrickSetStorage', /) -> Self:
+        self.fields.storage = storage.fields.id
+        self.list(override_query=self.by_storage_query)
+        return self
+
+    # Get individual parts using a specific storage location
+    def using_storage(self, storage: 'BrickSetStorage', /) -> Self:
+        self.fields.storage = storage.fields.id
+        self.list(override_query=self.using_storage_query)
+        return self
+
+    # Get individual parts using a specific purchase location
+    def using_purchase_location(self, purchase_location: 'BrickSetPurchaseLocation', /) -> Self:
+        self.fields.purchase_location = purchase_location.fields.id
+        self.list(override_query=self.using_purchase_location_query)
+        return self
+
+    # Get individual parts without storage
+    def without_storage(self, /) -> Self:
+        self.list(override_query=self.without_storage_query)
+        return self
+
+    # Get individual parts with problems (missing or damaged)
+    def with_problems(self, /) -> Self:
+        self.list(override_query=self.problem_query)
+        return self
+
+    # Base individual part list
+    def list(
+        self,
+        /,
+        *,
+        override_query: str | None = None,
+        order: str | None = None,
+        limit: int | None = None,
+        **context,
+    ) -> None:
+        # Load the individual parts from the database
+        for record in super().select(
+            override_query=override_query,
+            order=order,
+            limit=limit,
+            **context
+        ):
+            individual_part = IndividualPart(record=record)
+            self.records.append(individual_part)
+
+    # Set the record class
+    def set_record_class(self, /) -> None:
+        self.record_class = IndividualPart

bricktracker/individual_part_lot.py

@@ -0,0 +1,302 @@
+import logging
+import os
+import traceback
+from datetime import datetime
+from typing import Any, Self, TYPE_CHECKING
+from urllib.parse import urlparse
+from uuid import uuid4
+
+from flask import (
+    current_app,
+    url_for,
+)
+
+from .exceptions import NotFoundException, DatabaseException, ErrorException
+from .individual_part import IndividualPart
+from .record import BrickRecord, format_timestamp
+from .set_owner_list import BrickSetOwnerList
+from .set_purchase_location_list import BrickSetPurchaseLocationList
+from .set_storage_list import BrickSetStorageList
+from .set_tag_list import BrickSetTagList
+from .sql import BrickSQL
+
+if TYPE_CHECKING:
+    from .socket import BrickSocket
+
+logger = logging.getLogger(__name__)
+
+
+# Individual part lot (collection/batch of individual parts added together)
+class IndividualPartLot(BrickRecord):
+    # Queries
+    select_query: str = 'individual_part_lot/select/by_id'
+    insert_query: str = 'individual_part_lot/insert'
+
+    def __init__(
+        self,
+        /,
+        *,
+        record: Any | None = None
+    ):
+        super().__init__()
+
+        # Ingest the record if it has one
+        if record is not None:
+            self.ingest(record)
+
+    # Select a specific lot by UUID
+    def select_by_id(self, id: str, /) -> Self:
+        from .set_owner_list import BrickSetOwnerList
+        from .set_tag_list import BrickSetTagList
+
+        self.fields.id = id
+        if not self.select(
+            override_query=self.select_query,
+            owners=BrickSetOwnerList.as_columns(),
+            tags=BrickSetTagList.as_columns(),
+            # Note: Part lots don't have statuses (by design)
+            # Statuses are meant for tracking set completion/verification, which doesn't apply
+            # to loose part collections. Individual parts within lots can still be marked as
+            # missing/damaged/checked through the parts inventory system.
+        ):
+            raise NotFoundException(
+                'Individual part lot with id "{id}" not found'.format(id=id)
+            )
+        return self
+
+    # Delete a lot and all its parts
+    def delete(self, /) -> None:
+        BrickSQL().executescript(
+            'individual_part_lot/delete',
+            id=self.fields.id
+        )
+
+    # Get the URL for this lot
+    def url(self, /) -> str:
+        return url_for('individual_part.lot_details', lot_id=self.fields.id)
+
+    # String representation for debugging
+    def __repr__(self, /) -> str:
+        name = getattr(self.fields, 'name', 'Unnamed') or 'Unnamed'
+        lot_id = getattr(self.fields, 'id', 'unknown')
+        # Try to get part_count if available (from optimized query)
+        part_count = getattr(self.fields, 'part_count', '?')
+        return f'<IndividualPartLot "{name}" ({part_count} parts) id:{lot_id[:8]}...>'
+
+    # Format created date
+    def created_date_formatted(self, /) -> str:
+        return format_timestamp(self.fields.created_date)
+
+    # Format purchase date
+    def purchase_date_formatted(self, /) -> str:
+        return format_timestamp(self.fields.purchase_date)
+
+    # Format purchase price
+    def purchase_price(self, /) -> str:
+        from flask import current_app
+        if self.fields.purchase_price is not None:
+            return '{price}{currency}'.format(
+                price=self.fields.purchase_price,
+                currency=current_app.config['PURCHASE_CURRENCY']
+            )
+        else:
+            return ''
+
+    # Get all parts in this lot
+    def parts(self, /) -> list['IndividualPart']:
+        sql = BrickSQL()
+        parts_data = sql.fetchall('individual_part_lot/list/parts', parameters={'lot_id': self.fields.id})
+
+        # Convert to list of IndividualPart objects using ingest()
+        return [IndividualPart(record=record) for record in parts_data]
+
+    # Get total quantity of all parts in this lot
+    def total_quantity(self, /) -> int:
+        parts = self.parts()
+        return sum(part.fields.quantity for part in parts)
+
+    # Create a new lot with parts from cart
+    def create(self, socket: 'BrickSocket', data: dict[str, Any], /) -> bool:
+        """
+        Create a new individual part lot with multiple parts.
+
+        Expected data format:
+        {
+            'cart': [
+                {
+                    'part': '3001',
+                    'part_name': 'Brick 2 x 4',
+                    'color_id': 1,
+                    'color_name': 'White',
+                    'quantity': 10,
+                    'color_info': {...}
+                },
+                ...
+            ],
+            'name': 'Optional lot name',
+            'description': 'Optional lot description',
+            'storage': 'storage_id',
+            'purchase_location': 'purchase_location_id',
+            'purchase_date': timestamp,
+            'purchase_price': 0.0,
+            'owners': ['owner_id1', ...],
+            'tags': ['tag_id1', ...]
+        }
+        """
+        try:
+            # Validate cart data
+            cart = data.get('cart', [])
+            if not cart or not isinstance(cart, list):
+                raise ErrorException('Cart is empty or invalid')
+
+            socket.auto_progress(
+                message=f'Creating lot with {len(cart)} parts',
+                increment_total=True
+            )
+
+            # Generate UUID for the lot
+            lot_id = str(uuid4())
+            self.fields.id = lot_id
+
+            # Set lot metadata
+            self.fields.name = data.get('name', None)
+            self.fields.description = data.get('description', None)
+            self.fields.created_date = datetime.now().timestamp()
+
+            # Get storage
+            storage = BrickSetStorageList.get(
+                data.get('storage', ''),
+                allow_none=True
+            )
+            self.fields.storage = storage.fields.id if storage else None
+
+            # Get purchase location
+            purchase_location = BrickSetPurchaseLocationList.get(
+                data.get('purchase_location', ''),
+                allow_none=True
+            )
+            self.fields.purchase_location = purchase_location.fields.id if purchase_location else None
+
+            # Set purchase info
+            self.fields.purchase_date = data.get('purchase_date', None)
+            self.fields.purchase_price = data.get('purchase_price', None)
+
+            # Insert the lot record
+            socket.auto_progress(
+                message='Inserting lot into database',
+                increment_total=True
+            )
+            self.insert(commit=False)
+
+            # Commit the lot so parts can reference it
+            sql = BrickSQL()
+            sql.commit()
+
+            # Save owners using the metadata update methods
+            owners: list[str] = list(data.get('owners', []))
+            for owner_id in owners:
+                owner = BrickSetOwnerList.get(owner_id)
+                if owner:
+                    owner.update_individual_part_lot_state(self, state=True, commit=False)
+
+            # Save tags using the metadata update methods
+            tags: list[str] = list(data.get('tags', []))
+            for tag_id in tags:
+                tag = BrickSetTagList.get(tag_id)
+                if tag:
+                    tag.update_individual_part_lot_state(self, state=True, commit=False)
+
+            # Add all parts from cart
+            socket.auto_progress(
+                message=f'Adding {len(cart)} parts to lot',
+                increment_total=True
+            )
+
+            for idx, cart_item in enumerate(cart):
+                part_num = cart_item.get('part')
+                color_id = cart_item.get('color_id')
+                quantity = cart_item.get('quantity', 1)
+                color_info = cart_item.get('color_info', {})
+
+                socket.auto_progress(
+                    message=f'Adding part {idx + 1}/{len(cart)}: {part_num} in {cart_item.get("color_name", "unknown color")}',
+                    increment_total=True
+                )
+
+                # Create individual part with lot_id
+                part_uuid = str(uuid4())
+                sql = BrickSQL()
+
+                # Ensure color and part/color combination exist in rebrickable tables
+                IndividualPart.get_or_fetch_color(color_id)
+                part_name = cart_item.get('part_name', '')
+                color_name = cart_item.get('color_name', '')
+                image_url = color_info.get('part_img_url', '')
+
+                # Extract image_id from element_ids or URL
+                element_ids = color_info.get('elements', [])
+                if element_ids and len(element_ids) > 0:
+                    image_id = str(element_ids[0])
+                elif image_url:
+                    image_id, _ = os.path.splitext(os.path.basename(urlparse(image_url).path))
+                else:
+                    image_id = None
+
+                sql.execute('rebrickable_parts/insert_part_color', parameters={
+                    'part': part_num,
+                    'name': part_name,
+                    'color_id': color_id,
+                    'color_name': color_name,
+                    'color_rgb': color_info.get('rgb', ''),
+                    'color_transparent': color_info.get('is_trans', False),
+                    'image': image_url,
+                    'image_id': image_id,
+                    'url': current_app.config['REBRICKABLE_LINK_PART_PATTERN'].format(part=part_num, color=color_id),
+                    'bricklink_color_id': color_info.get('bricklink_color_id', None),
+                    'bricklink_color_name': color_info.get('bricklink_color_name', None)
+                })
+                # Commit so the foreign key constraint can be satisfied
+                sql.commit()
+
+                # Now insert the part with lot_id (NO individual metadata - inherited from lot)
+                sql.execute('individual_part/insert_with_lot', parameters={
+                    'id': part_uuid,
+                    'part': part_num,
+                    'color': color_id,
+                    'quantity': quantity,
+                    'lot_id': lot_id
+                })
+
+            # Commit all changes
+            socket.auto_progress(
+                message='Committing changes to database',
+                increment_total=True
+            )
+            sql.commit()
+
+            socket.auto_progress(
+                message=f'Lot created successfully with {len(cart)} parts',
+                increment_total=True
+            )
+
+            # Complete with success message and lot URL
+            lot_url = self.url()
+            socket.complete(
+                message=f'Successfully created lot with {len(cart)} parts. <a href="{lot_url}">View lot</a>',
+                data={
+                    'lot_id': lot_id,
+                    'lot_url': lot_url
+                }
+            )
+
+            return True
+
+        except ErrorException as e:
+            socket.fail(message=str(e))
+            logger.error('Error creating lot: {error}'.format(error=e))
+            return False
+        except Exception as e:
+            socket.fail(message='Unexpected error creating lot: {error}'.format(error=str(e)))
+            logger.error('Unexpected error creating lot: {error}'.format(error=e))
+            logger.error(traceback.format_exc())
+            return False

bricktracker/individual_part_lot_list.py

@@ -0,0 +1,86 @@
+import logging
+from typing import Self, TYPE_CHECKING
+
+from .record_list import BrickRecordList
+from .individual_part_lot import IndividualPartLot
+
+if TYPE_CHECKING:
+    from .set_storage import BrickSetStorage
+
+logger = logging.getLogger(__name__)
+
+
+# List of individual part lots
+class IndividualPartLotList(BrickRecordList):
+    # Queries
+    list_query: str = 'individual_part_lot/list/all'
+    by_part_and_color_query: str = 'individual_part_lot/list/by_part_and_color'
+    by_storage_query: str = 'individual_part_lot/list/by_storage'
+    using_storage_query: str = 'individual_part_lot/list/using_storage'
+    using_purchase_location_query: str = 'individual_part_lot/list/using_purchase_location'
+    without_storage_query: str = 'individual_part_lot/list/without_storage'
+    problem_query: str = 'individual_part_lot/list/problem'
+
+    # Get all individual part lots
+    def all(self, /) -> Self:
+        self.list(override_query=self.list_query)
+        return self
+
+    # Base individual part lot list
+    def list(
+        self,
+        /,
+        *,
+        override_query: str | None = None,
+        order: str | None = None,
+        limit: int | None = None,
+        **context,
+    ) -> None:
+        # Load the individual part lots from the database
+        for record in super().select(
+            override_query=override_query,
+            order=order,
+            limit=limit,
+            **context
+        ):
+            lot = IndividualPartLot(record=record)
+            self.records.append(lot)
+
+    # Set the record class
+    def set_record_class(self, /) -> None:
+        self.record_class = IndividualPartLot
+
+    # Get individual part lots containing a specific part and color
+    def by_part_and_color(self, part: str, color_id: int, /) -> Self:
+        self.fields.part = part
+        self.fields.color = color_id
+        self.list(override_query='individual_part_lot/list/by_part_and_color')
+        return self
+
+    # Get individual part lots by storage location
+    def by_storage(self, storage: 'BrickSetStorage', /) -> Self:
+        self.fields.storage = storage.fields.id
+        self.list(override_query=self.by_storage_query)
+        return self
+
+    # Get individual part lots using a specific storage location
+    def using_storage(self, storage: 'BrickSetStorage', /) -> Self:
+        self.fields.storage = storage.fields.id
+        self.list(override_query=self.using_storage_query)
+        return self
+
+    # Get individual part lots using a specific purchase location
+    def using_purchase_location(self, purchase_location: 'BrickSetPurchaseLocation', /) -> Self:
+        self.fields.purchase_location = purchase_location.fields.id
+        self.list(override_query=self.using_purchase_location_query)
+        return self
+
+    # Get individual part lots without storage
+    def without_storage(self, /) -> Self:
+        self.list(override_query=self.without_storage_query)
+        return self
+
+    # Get individual part lots with problems (containing parts with missing or damaged items)
+    def with_problems(self, /) -> Self:
+        self.list(override_query=self.problem_query)
+        return self

bricktracker/instructions.py

@@ -75,10 +75,11 @@ class BrickInstructions(object):
 
             splits = normalized.split('-', 2)
 
-            if len(splits) >= 2:
+            if len(splits) >= 2 and splits[0]:
                 try:
-                    # Trying to make sense of each part as integers
-                    int(splits[0])
+                    # The set number can be alphanumeric (e.g. Pick-a-Brick
+                    # builds like "EG00029-1"); only the version suffix has
+                    # to be an integer
                     int(splits[1])
 
                     self.set = '-'.join(splits[:2])

bricktracker/metadata.py

@@ -9,6 +9,8 @@ from .exceptions import DatabaseException, ErrorException, NotFoundException
 from .record import BrickRecord
 from .sql import BrickSQL
 if TYPE_CHECKING:
+    from .individual_minifigure import IndividualMinifigure
+    from .individual_part import IndividualPart
     from .set import BrickSet
 
 logger = logging.getLogger(__name__)
@@ -106,6 +108,26 @@ class BrickMetadata(BrickRecord):
             metadata_id=self.fields.id
         )
 
+    # URL to change the selected state of this metadata item for an individual part
+    def url_for_individual_part_state(self, part_id: str, /) -> str:
+        # Replace 'set' with 'individual_part' in the endpoint name
+        endpoint = self.set_state_endpoint.replace('set.', 'individual_part.')
+        return url_for(
+            endpoint,
+            id=part_id,
+            metadata_id=self.fields.id
+        )
+
+    # URL to change the selected state of this metadata item for an individual minifigure
+    def url_for_individual_minifigure_state(self, minifigure_id: str, /) -> str:
+        # Replace 'set' with 'individual_minifigure' in the endpoint name
+        endpoint = self.set_state_endpoint.replace('set.', 'individual_minifigure.')
+        return url_for(
+            endpoint,
+            id=minifigure_id,
+            metadata_id=self.fields.id
+        )
+
     # Select a specific metadata (with an id)
     def select_specific(self, id: str, /) -> Self:
         # Save the parameters to the fields
@@ -182,7 +204,8 @@ class BrickMetadata(BrickRecord):
         /,
         *,
         json: Any | None = None,
-        state: Any | None = None
+        state: Any | None = None,
+        commit: bool = True
     ) -> Any:
         if state is None and json is not None:
             state = json.get('value', False)
@@ -191,13 +214,22 @@ class BrickMetadata(BrickRecord):
         parameters['set_id'] = brickset.fields.id
         parameters['state'] = state
 
-        rows, _ = BrickSQL().execute_and_commit(
-            self.update_set_state_query,
-            parameters=parameters,
-            name=self.as_column(),
-        )
+        if commit:
+            rows, _ = BrickSQL().execute_and_commit(
+                self.update_set_state_query,
+                parameters=parameters,
+                name=self.as_column(),
+            )
+        else:
+            rows, _ = BrickSQL().execute(
+                self.update_set_state_query,
+                parameters=parameters,
+                defer=True,
+                name=self.as_column(),
+            )
 
-        if rows != 1:
+        # When deferred, rows will be -1, so skip the check
+        if commit and rows != 1:
             raise DatabaseException('Could not update the {kind} state for set {set} ({id})'.format(
                 kind=self.kind,
                 set=brickset.fields.set,
@@ -260,3 +292,156 @@ class BrickMetadata(BrickRecord):
         ))
 
         return value
+
+    # Update the selected state of this metadata item for an individual part
+    def update_individual_part_state(
+        self,
+        individual_part: 'IndividualPart',
+        /,
+        *,
+        json: Any | None = None,
+        state: Any | None = None,
+        commit: bool = True
+    ) -> Any:
+        if state is None and json is not None:
+            state = json.get('value', False)
+
+        parameters = self.sql_parameters()
+        parameters['set_id'] = individual_part.fields.id  # set_id parameter accepts any entity id
+        parameters['state'] = state
+
+        # Use the same set query (bricktracker_set_owners/tags/statuses tables accept any entity id)
+        query_name = self.update_set_state_query
+
+        if commit:
+            rows, _ = BrickSQL().execute_and_commit(
+                query_name,
+                parameters=parameters,
+                name=self.as_column(),
+            )
+        else:
+            rows, _ = BrickSQL().execute(
+                query_name,
+                parameters=parameters,
+                defer=True,
+                name=self.as_column(),
+            )
+
+        # When deferred, rows will be -1, so skip the check
+        if commit and rows != 1:
+            raise DatabaseException('Could not update the {kind} state for individual part {part_id}'.format(
+                kind=self.kind,
+                part_id=individual_part.fields.id,
+            ))
+
+        # Info
+        logger.info('{kind} "{name}" state changed to "{state}" for individual part {part_id}'.format(
+            kind=self.kind,
+            name=self.fields.name,
+            state=state,
+            part_id=individual_part.fields.id,
+        ))
+
+        return state
+
+    # Update the selected state of this metadata item for an individual minifigure
+    def update_individual_minifigure_state(
+        self,
+        individual_minifigure: 'IndividualMinifigure',
+        /,
+        *,
+        json: Any | None = None,
+        state: Any | None = None,
+        commit: bool = True
+    ) -> Any:
+        if state is None and json is not None:
+            state = json.get('value', False)
+
+        parameters = self.sql_parameters()
+        parameters['set_id'] = individual_minifigure.fields.id  # set_id parameter accepts any entity id
+        parameters['state'] = state
+
+        # Use the same set query (bricktracker_set_owners/tags/statuses tables accept any entity id)
+        query_name = self.update_set_state_query
+
+        if commit:
+            rows, _ = BrickSQL().execute_and_commit(
+                query_name,
+                parameters=parameters,
+                name=self.as_column(),
+            )
+        else:
+            rows, _ = BrickSQL().execute(
+                query_name,
+                parameters=parameters,
+                defer=True,
+                name=self.as_column(),
+            )
+
+        # When deferred, rows will be -1, so skip the check
+        if commit and rows != 1:
+            raise DatabaseException('Could not update the {kind} state for individual minifigure {minifigure_id}'.format(
+                kind=self.kind,
+                minifigure_id=individual_minifigure.fields.id,
+            ))
+
+        # Info
+        logger.info('{kind} "{name}" state changed to "{state}" for individual minifigure {minifigure_id}'.format(
+            kind=self.kind,
+            name=self.fields.name,
+            state=state,
+            minifigure_id=individual_minifigure.fields.id,
+        ))
+
+        return state
+
+    # Update the selected state of this metadata item for an individual part lot
+    def update_individual_part_lot_state(
+        self,
+        individual_part_lot: 'IndividualPartLot',
+        /,
+        *,
+        json: Any | None = None,
+        state: Any | None = None,
+        commit: bool = True
+    ) -> Any:
+        if state is None and json is not None:
+            state = json.get('value', False)
+
+        parameters = self.sql_parameters()
+        parameters['set_id'] = individual_part_lot.fields.id  # set_id parameter accepts any entity id
+        parameters['state'] = state
+
+        # Use the same set query (bricktracker_set_owners/tags tables accept any entity id)
+        query_name = self.update_set_state_query
+
+        if commit:
+            rows, _ = BrickSQL().execute_and_commit(
+                query_name,
+                parameters=parameters,
+                name=self.as_column(),
+            )
+        else:
+            rows, _ = BrickSQL().execute(
+                query_name,
+                parameters=parameters,
+                defer=True,
+                name=self.as_column(),
+            )
+
+        # When deferred, rows will be -1, so skip the check
+        if commit and rows != 1:
+            raise DatabaseException('Could not update the {kind} state for individual part lot {lot_id}'.format(
+                kind=self.kind,
+                lot_id=individual_part_lot.fields.id,
+            ))
+
+        # Info
+        logger.info('{kind} "{name}" state changed to "{state}" for individual part lot {lot_id}'.format(
+            kind=self.kind,
+            name=self.fields.name,
+            state=state,
+            lot_id=individual_part_lot.fields.id,
+        ))
+
+        return state

bricktracker/metadata_list.py

@@ -6,6 +6,7 @@ from flask import url_for
 from .exceptions import ErrorException, NotFoundException
 from .fields import BrickRecordFields
 from .record_list import BrickRecordList
+from .set_custom_field import BrickSetCustomField
 from .set_owner import BrickSetOwner
 from .set_purchase_location import BrickSetPurchaseLocation
 from .set_status import BrickSetStatus
@@ -17,6 +18,7 @@ logger = logging.getLogger(__name__)
 
 T = TypeVar(
     'T',
+    BrickSetCustomField,
     BrickSetOwner,
     BrickSetPurchaseLocation,
     BrickSetStatus,
@@ -184,3 +186,23 @@ class BrickMetadataList(BrickRecordList[T]):
             cls.set_value_endpoint,
             id=id,
         )
+
+    # URL to change the selected value of this metadata item for an individual part
+    @classmethod
+    def url_for_individual_part_value(cls, part_id: str, /) -> str:
+        # Replace 'set' with 'individual_part' in the endpoint name
+        endpoint = cls.set_value_endpoint.replace('set.', 'individual_part.')
+        return url_for(
+            endpoint,
+            id=part_id,
+        )
+
+    # URL to change the selected value of this metadata item for an individual minifigure
+    @classmethod
+    def url_for_individual_minifigure_value(cls, minifigure_id: str, /) -> str:
+        # Replace 'set' with 'individual_minifigure' in the endpoint name
+        endpoint = cls.set_value_endpoint.replace('set.', 'individual_minifigure.')
+        return url_for(
+            endpoint,
+            id=minifigure_id,
+        )

bricktracker/migrations/0027.py

@@ -0,0 +1,88 @@
+"""
+Migration 0027: Consolidate metadata tables - remove FK constraints from set metadata tables
+
+This migration removes foreign key constraints from bricktracker_set_owners, _tags, and _statuses
+so they can accept any entity ID (sets, individual parts, individual minifigures, individual part lots).
+
+Since these tables have dynamically added columns, we need to read the schema and recreate the tables
+with all existing columns but without the foreign key constraints.
+"""
+import logging
+from typing import Any, TYPE_CHECKING
+
+if TYPE_CHECKING:
+    from ..sql import BrickSQL
+
+logger = logging.getLogger(__name__)
+
+
+def migration_0027(sql: 'BrickSQL') -> dict[str, Any]:
+    """
+    Remove foreign key constraints from set metadata junction tables.
+
+    This allows the tables to store metadata for any entity type, not just sets.
+    """
+
+    tables_to_migrate = [
+        'bricktracker_set_owners',
+        'bricktracker_set_tags',
+        'bricktracker_set_statuses'
+    ]
+
+    for table_name in tables_to_migrate:
+        logger.info('Migrating {table_name} to remove foreign key constraint'.format(
+            table_name=table_name
+        ))
+
+        # Get the current table schema
+        cursor = sql.cursor.execute(f"PRAGMA table_info({table_name})")
+        columns = cursor.fetchall()
+
+        # Build column definitions for new table (without FK constraint)
+        column_defs = []
+        column_names = []
+        for col in columns:
+            col_name = col[1]
+            col_type = col[2]
+            col_not_null = col[3]
+            col_default = col[4]
+            col_pk = col[5]
+
+            column_names.append(f'"{col_name}"')
+
+            col_def = f'"{col_name}" {col_type}'
+            if col_pk:
+                col_def += ' PRIMARY KEY'
+            if col_not_null and not col_pk:
+                if col_default is not None:
+                    col_def += f' NOT NULL DEFAULT {col_default}'
+                else:
+                    col_def += ' NOT NULL'
+            elif col_default is not None:
+                col_def += f' DEFAULT {col_default}'
+
+            column_defs.append(col_def)
+
+        # Create new table without foreign key constraint
+        new_table_name = f'{table_name}_new'
+        create_sql = f'CREATE TABLE "{new_table_name}" ({", ".join(column_defs)})'
+        logger.debug('Creating new table: {sql}'.format(sql=create_sql))
+        sql.cursor.execute(create_sql)
+
+        # Copy all data
+        column_list = ', '.join(column_names)
+        copy_sql = f'INSERT INTO "{new_table_name}" ({column_list}) SELECT {column_list} FROM "{table_name}"'
+        logger.debug('Copying data: {sql}'.format(sql=copy_sql))
+        sql.cursor.execute(copy_sql)
+
+        # Drop old table
+        sql.cursor.execute(f'DROP TABLE "{table_name}"')
+
+        # Rename new table to old name
+        sql.cursor.execute(f'ALTER TABLE "{new_table_name}" RENAME TO "{table_name}"')
+
+        logger.info('Successfully migrated {table_name}'.format(table_name=table_name))
+
+    logger.info('Migration 0027 complete - all set metadata tables now accept any entity ID')
+
+    return {}

bricktracker/minifigure_list.py

@@ -20,8 +20,8 @@ class BrickMinifigureList(BrickRecordList[BrickMinifigure]):
     order: str
 
     # Queries
-    all_query: str = 'minifigure/list/all'
-    all_by_owner_query: str = 'minifigure/list/all_by_owner'
+    all_query: str = 'minifigure/list/all_unified'
+    all_by_owner_query: str = 'minifigure/list/all_by_owner_unified'
     damaged_part_query: str = 'minifigure/list/damaged_part'
     last_query: str = 'minifigure/list/last'
     missing_part_query: str = 'minifigure/list/missing_part'
@@ -44,7 +44,7 @@ class BrickMinifigureList(BrickRecordList[BrickMinifigure]):
         return self
 
     # Load all minifigures with problems filter
-    def all_filtered(self, /, owner_id: str | None = None, problems_filter: str = 'all', theme_id: str = 'all', year: str = 'all') -> Self:
+    def all_filtered(self, /, owner_id: str | None = None, problems_filter: str = 'all', theme_id: str = 'all', year: str = 'all', individuals_filter: str = 'all') -> Self:
         # Save the owner_id parameter
         if owner_id is not None:
             self.fields.owner_id = owner_id
@@ -56,6 +56,8 @@ class BrickMinifigureList(BrickRecordList[BrickMinifigure]):
             context['theme_id'] = theme_id
         if year and year != 'all':
             context['year'] = year
+        if individuals_filter and individuals_filter != 'all':
+            context['individuals_filter'] = individuals_filter
 
         # Choose query based on whether owner filtering is needed
         if owner_id and owner_id != 'all':
@@ -77,7 +79,7 @@ class BrickMinifigureList(BrickRecordList[BrickMinifigure]):
         return self
 
     # Load all minifigures by owner with problems filter
-    def all_by_owner_filtered(self, /, owner_id: str | None = None, problems_filter: str = 'all', theme_id: str = 'all', year: str = 'all') -> Self:
+    def all_by_owner_filtered(self, /, owner_id: str | None = None, problems_filter: str = 'all', theme_id: str = 'all', year: str = 'all', individuals_filter: str = 'all') -> Self:
         # Save the owner_id parameter
         self.fields.owner_id = owner_id
 
@@ -88,6 +90,8 @@ class BrickMinifigureList(BrickRecordList[BrickMinifigure]):
             context['theme_id'] = theme_id
         if year and year != 'all':
             context['year'] = year
+        if individuals_filter and individuals_filter != 'all':
+            context['individuals_filter'] = individuals_filter
 
         # Load the minifigures from the database
         self.list(override_query=self.all_by_owner_query, **context)
@@ -101,6 +105,7 @@ class BrickMinifigureList(BrickRecordList[BrickMinifigure]):
         problems_filter: str = 'all',
         theme_id: str = 'all',
         year: str = 'all',
+        individuals_filter: str = 'all',
         search_query: str | None = None,
         page: int = 1,
         per_page: int = 50,
@@ -127,10 +132,13 @@ class BrickMinifigureList(BrickRecordList[BrickMinifigure]):
         if year and year != 'all':
             filter_context['year'] = year
 
-        # Field mapping for sorting
+        if individuals_filter and individuals_filter != 'all':
+            filter_context['individuals_filter'] = individuals_filter
+
+        # Field mapping for sorting (using column names from the unified query)
         field_mapping = {
-            'name': '"rebrickable_minifigures"."name"',
-            'parts': '"rebrickable_minifigures"."number_of_parts"',
+            'name': '"name"',
+            'parts': '"number_of_parts"',
             'quantity': '"total_quantity"',
             'missing': '"total_missing"',
             'damaged': '"total_damaged"',

bricktracker/parser.py

@@ -17,7 +17,7 @@ def parse_set(set: str, /) -> str:
     if version == '':
         version = '1'
 
-    # Version must be a positive integer
+    # Version must be a valid number (but preserve leading zeros for minifigures)
     try:
         version_int = int(version)
     except Exception:
@@ -30,4 +30,30 @@ def parse_set(set: str, /) -> str:
             version=version,
         ))
 
-    return '{number}-{version}'.format(number=number, version=version_int)
+    # Preserve original version string to keep leading zeros (important for minifigures like fig-000484)
+    return '{number}-{version}'.format(number=number, version=version)
+
+
+# Make sense of string supposed to contain a minifigure ID
+def parse_minifig(figure: str, /) -> str:
+    # Minifigure format is typically fig-XXXXXX
+    # We'll accept with or without the 'fig-' prefix
+    figure = figure.strip()
+
+    if not figure.startswith('fig-'):
+        # Try to add the prefix if it's just numbers
+        if figure.isdigit():
+            figure = 'fig-{figure}'.format(figure=figure.zfill(6))
+        else:
+            raise ErrorException('Minifigure "{figure}" must start with "fig-"'.format(
+                figure=figure,
+            ))
+
+    # Validate format: fig-XXXXXX where X can be digits or letters
+    parts = figure.split('-')
+    if len(parts) != 2 or parts[0] != 'fig':
+        raise ErrorException('Invalid minifigure format "{figure}". Expected format: fig-XXXXXX'.format(
+            figure=figure,
+        ))
+
+    return figure

bricktracker/part.py

@@ -9,6 +9,7 @@ from .exceptions import ErrorException, NotFoundException
 from .rebrickable_part import RebrickablePart
 from .sql import BrickSQL
 if TYPE_CHECKING:
+    from .individual_minifigure import IndividualMinifigure
     from .minifigure import BrickMinifigure
     from .set import BrickSet
     from .socket import BrickSocket
@@ -23,6 +24,7 @@ class BrickPart(RebrickablePart):
 
     # Queries
     insert_query: str = 'part/insert'
+    update_on_refresh_query: str = 'part/update_on_refresh'
     generic_query: str = 'part/select/generic'
     select_query: str = 'part/select/specific'
 
@@ -32,6 +34,7 @@ class BrickPart(RebrickablePart):
         *,
         brickset: 'BrickSet | None' = None,
         minifigure: 'BrickMinifigure | None' = None,
+        individual_minifigure: 'IndividualMinifigure | None' = None,
         record: Row | dict[str, Any] | None = None
     ):
         super().__init__(
@@ -40,7 +43,12 @@ class BrickPart(RebrickablePart):
             record=record
         )
 
-        if self.minifigure is not None:
+        self.individual_minifigure = individual_minifigure
+
+        if self.individual_minifigure is not None:
+            self.identifier = self.individual_minifigure.fields.id
+            self.kind = 'Individual Minifigure'
+        elif self.minifigure is not None:
             self.identifier = self.minifigure.fields.figure
             self.kind = 'Minifigure'
         elif self.brickset is not None:
@@ -66,7 +74,28 @@ class BrickPart(RebrickablePart):
             # This must happen before inserting into bricktracker_parts due to FK constraint
             self.insert_rebrickable()
 
-            if not refresh:
+            if refresh:
+                params = self.sql_parameters()
+
+                # Track this part in the refresh temp table (for orphan cleanup later)
+                BrickSQL().execute(
+                    'part/track_refresh_part',
+                    parameters=params,
+                    defer=False
+                )
+
+                # Try to update existing part first (preserves checked, missing, and damaged states)
+                # Note: Cannot defer this because we need to check if rows were affected
+                rows, _ = BrickSQL().execute(
+                    self.update_on_refresh_query,
+                    parameters=params,
+                    defer=False
+                )
+
+                # If no rows were updated, the part doesn't exist yet, so insert it
+                if rows == 0:
+                    self.insert(commit=False)
+            else:
                 # Insert into bricktracker_parts database (child record)
                 self.insert(commit=False)
 
@@ -160,6 +189,33 @@ class BrickPart(RebrickablePart):
 
         return self
 
+    # Select a specific part from an individual minifigure instance
+    def select_specific_individual_minifigure(
+        self,
+        individual_minifigure: 'IndividualMinifigure',
+        part: str,
+        color: int,
+        spare: int,
+        /,
+    ) -> Self:
+        # Save the parameters to the fields
+        self.individual_minifigure = individual_minifigure
+        self.fields.part = part
+        self.fields.color = color
+        self.fields.spare = spare
+
+        if not self.select(override_query='individual_minifigure/part/select/specific'):
+            raise NotFoundException(
+                'Part {part} with color {color} (spare: {spare}) from individual minifigure {id} was not found in the database'.format(  # noqa: E501
+                    part=self.fields.part,
+                    color=self.fields.color,
+                    spare=self.fields.spare,
+                    id=individual_minifigure.fields.id,
+                ),
+            )
+
+        return self
+
     # Update checked state for part walkthrough
     def update_checked(self, json: Any | None, /) -> bool:
         # Handle both direct 'checked' key and changer.js 'value' key format
@@ -180,22 +236,56 @@ class BrickPart(RebrickablePart):
 
         return checked
 
+    # Update checked state for individual minifigure part
+    def update_checked_individual_minifigure(self, json: Any | None, /) -> bool:
+        # Handle both direct 'checked' key and changer.js 'value' key format
+        if json:
+            checked = json.get('checked', json.get('value', False))
+        else:
+            checked = False
+
+        checked = bool(checked)
+
+        self.fields.checked = checked
+
+        BrickSQL().execute_and_commit(
+            'individual_minifigure/part/update/checked',
+            parameters=self.sql_parameters()
+        )
+
+        return checked
+
     # Compute the url for updating checked state
     def url_for_checked(self, /) -> str:
-        # Different URL for a minifigure part
-        if self.minifigure is not None:
-            figure = self.minifigure.fields.figure
+        # Different URL for individual minifigure part
+        if self.individual_minifigure is not None:
+            return url_for(
+                'individual_minifigure.checked_part',
+                id=self.individual_minifigure.fields.id,
+                part=self.fields.part,
+                color=self.fields.color,
+                spare=self.fields.spare,
+            )
+        # Different URL for a set minifigure part
+        elif self.minifigure is not None:
+            return url_for(
+                'set.checked_part',
+                id=self.fields.id,
+                figure=self.minifigure.fields.figure,
+                part=self.fields.part,
+                color=self.fields.color,
+                spare=self.fields.spare,
+            )
+        # Set part
         else:
-            figure = None
-
-        return url_for(
-            'set.checked_part',
-            id=self.fields.id,
-            figure=figure,
-            part=self.fields.part,
-            color=self.fields.color,
-            spare=self.fields.spare,
-        )
+            return url_for(
+                'set.checked_part',
+                id=self.fields.id,
+                figure=None,
+                part=self.fields.part,
+                color=self.fields.color,
+                spare=self.fields.spare,
+            )
 
     # Update a problematic part
     def update_problem(self, problem: str, json: Any | None, /) -> int:
@@ -227,20 +317,67 @@ class BrickPart(RebrickablePart):
 
         return amount
 
+    # Update a problematic part for individual minifigure
+    def update_problem_individual_minifigure(self, problem: str, json: Any | None, /) -> int:
+        amount: str | int = json.get('value', '')  # type: ignore
+
+        # We need a positive integer
+        try:
+            if amount == '':
+                amount = 0
+
+            amount = int(amount)
+
+            if amount < 0:
+                amount = 0
+        except Exception:
+            raise ErrorException('"{amount}" is not a valid integer'.format(
+                amount=amount
+            ))
+
+        if amount < 0:
+            raise ErrorException('Cannot set a negative amount')
+
+        setattr(self.fields, problem, amount)
+
+        BrickSQL().execute_and_commit(
+            'individual_minifigure/part/update/{problem}'.format(problem=problem),
+            parameters=self.sql_parameters()
+        )
+
+        return amount
+
     # Compute the url for problematic part
     def url_for_problem(self, problem: str, /) -> str:
-        # Different URL for a minifigure part
-        if self.minifigure is not None:
-            figure = self.minifigure.fields.figure
+        # Different URL for individual minifigure part
+        if self.individual_minifigure is not None:
+            return url_for(
+                'individual_minifigure.problem_part',
+                id=self.individual_minifigure.fields.id,
+                part=self.fields.part,
+                color=self.fields.color,
+                spare=self.fields.spare,
+                problem=problem,
+            )
+        # Different URL for set minifigure part
+        elif self.minifigure is not None:
+            return url_for(
+                'set.problem_part',
+                id=self.fields.id,
+                figure=self.minifigure.fields.figure,
+                part=self.fields.part,
+                color=self.fields.color,
+                spare=self.fields.spare,
+                problem=problem,
+            )
+        # Set part
         else:
-            figure = None
-
-        return url_for(
-            'set.problem_part',
-            id=self.fields.id,
-            figure=figure,
-            part=self.fields.part,
-            color=self.fields.color,
-            spare=self.fields.spare,
-            problem=problem,
+            return url_for(
+                'set.problem_part',
+                id=self.fields.id,
+                figure=None,
+                part=self.fields.part,
+                color=self.fields.color,
+                spare=self.fields.spare,
+                problem=problem,
         )

bricktracker/part_list.py

@@ -19,6 +19,7 @@ logger = logging.getLogger(__name__)
 class BrickPartList(BrickRecordList[BrickPart]):
     brickset: 'BrickSet | None'
     minifigure: 'BrickMinifigure | None'
+    individual_minifigure: 'IndividualMinifigure | None'
     order: str
 
     # Queries
@@ -57,8 +58,8 @@ class BrickPartList(BrickRecordList[BrickPart]):
 
         return self
 
-    # Load all parts with filters (owner, color, theme, year)
-    def all_filtered(self, owner_id: str | None = None, color_id: str | None = None, theme_id: str | None = None, year: str | None = None, /) -> Self:
+    # Load all parts with filters (owner, color, theme, year, individuals)
+    def all_filtered(self, owner_id: str | None = None, color_id: str | None = None, theme_id: str | None = None, year: str | None = None, individuals_filter: str | None = None, /) -> Self:
         # Save the filter parameters
         if owner_id is not None:
             self.fields.owner_id = owner_id
@@ -80,6 +81,8 @@ class BrickPartList(BrickRecordList[BrickPart]):
             context['theme_id'] = theme_id
         if year and year != 'all':
             context['year'] = year
+        if individuals_filter and individuals_filter == 'only':
+            context['individuals_filter'] = True
 
         # Load the parts from the database
         self.list(override_query=query, **context)
@@ -93,6 +96,7 @@ class BrickPartList(BrickRecordList[BrickPart]):
         color_id: str | None = None,
         theme_id: str | None = None,
         year: str | None = None,
+        individuals_filter: str | None = None,
         search_query: str | None = None,
         page: int = 1,
         per_page: int = 50,
@@ -113,6 +117,8 @@ class BrickPartList(BrickRecordList[BrickPart]):
             filter_context['theme_id'] = theme_id
         if year and year != 'all':
             filter_context['year'] = year
+        if individuals_filter and individuals_filter == 'only':
+            filter_context['individuals_filter'] = True
         if search_query:
             filter_context['search_query'] = search_query
         # Hide spare parts from display if configured
@@ -165,6 +171,11 @@ class BrickPartList(BrickRecordList[BrickPart]):
         else:
             minifigure = None
 
+        if hasattr(self, 'individual_minifigure'):
+            individual_minifigure = self.individual_minifigure
+        else:
+            individual_minifigure = None
+
         # Prepare template context for filtering
         context_vars = {}
         if hasattr(self.fields, 'owner_id') and self.fields.owner_id is not None:
@@ -188,6 +199,7 @@ class BrickPartList(BrickRecordList[BrickPart]):
             part = BrickPart(
                 brickset=brickset,
                 minifigure=minifigure,
+                individual_minifigure=individual_minifigure,
                 record=record,
             )
 
@@ -234,6 +246,24 @@ class BrickPartList(BrickRecordList[BrickPart]):
 
         return self
 
+    # Load parts from an individual minifigure instance
+    def from_individual_minifigure(
+        self,
+        individual_minifigure: 'IndividualMinifigure',
+        /,
+    ) -> Self:
+        from .individual_minifigure import IndividualMinifigure
+
+        # Save the individual minifigure reference
+        self.individual_minifigure = individual_minifigure
+
+        # Load the parts for this individual minifigure instance
+        self.list(
+            override_query='individual_minifigure/part/list/from_instance'
+        )
+
+        return self
+
     # Load generic parts from a print
     def from_print(
         self,
@@ -254,6 +284,23 @@ class BrickPartList(BrickRecordList[BrickPart]):
 
         return self
 
+    # Last added parts
+    def last(self, /, *, limit: int = 6) -> Self:
+        if current_app.config['RANDOM']:
+            order = 'RANDOM()'
+        else:
+            # Since bricktracker_parts has a composite primary key, it doesn't have a rowid
+            # Order by id DESC (which are UUIDs with timestamps) to get recent parts
+            order = '"combined"."id" DESC, "combined"."part" ASC'
+
+        context = {}
+        if current_app.config.get('HIDE_SPARE_PARTS', False):
+            context['skip_spare_parts'] = True
+
+        self.list(override_query=self.last_query, order=order, limit=limit, **context)
+
+        return self
+
     # Load problematic parts
     def problem(self, /) -> Self:
         self.list(override_query=self.problem_query)
@@ -354,6 +401,10 @@ class BrickPartList(BrickRecordList[BrickPart]):
         if self.brickset is not None:
             parameters['id'] = self.brickset.fields.id
 
+        # Use the individual minifigure ID if present
+        if hasattr(self, 'individual_minifigure') and self.individual_minifigure is not None:
+            parameters['id'] = self.individual_minifigure.fields.id
+
         # Use the minifigure number if present,
         if self.minifigure is not None:
             parameters['figure'] = self.minifigure.fields.figure

bricktracker/rebrickable_image.py

@@ -55,7 +55,7 @@ class RebrickableImage(object):
 
         # Get the URL (this handles nil images via url() method)
         url = self.url()
-        if url is None:
+        if not url:
             return
 
         # Grab the image
@@ -88,7 +88,7 @@ class RebrickableImage(object):
                 return self.part.fields.image_id
 
         if self.minifigure is not None:
-            if self.minifigure.fields.image is None:
+            if not self.minifigure.fields.image:
                 return RebrickableImage.nil_minifigure_name()
             else:
                 return self.minifigure.fields.figure
@@ -113,13 +113,13 @@ class RebrickableImage(object):
     # Return the url depending on the objects provided
     def url(self, /) -> str:
         if self.part is not None:
-            if self.part.fields.image is None:
+            if not self.part.fields.image:
                 return current_app.config['REBRICKABLE_IMAGE_NIL']
             else:
                 return self.part.fields.image
 
         if self.minifigure is not None:
-            if self.minifigure.fields.image is None:
+            if not self.minifigure.fields.image:
                 return current_app.config['REBRICKABLE_IMAGE_NIL_MINIFIGURE']
             else:
                 return self.minifigure.fields.image

bricktracker/rebrickable_minifigure.py

@@ -14,7 +14,6 @@ if TYPE_CHECKING:
 class RebrickableMinifigure(BrickRecord):
     brickset: 'BrickSet | None'
 
-    # Queries
     select_query: str = 'rebrickable/minifigure/select'
     insert_query: str = 'rebrickable/minifigure/insert'
 
@@ -27,10 +26,8 @@ class RebrickableMinifigure(BrickRecord):
     ):
         super().__init__()
 
-        # Save the brickset
         self.brickset = brickset
 
-        # Ingest the record if it has one
         if record is not None:
             self.ingest(record)
 
@@ -62,7 +59,6 @@ class RebrickableMinifigure(BrickRecord):
 
         return parameters
 
-    # Self url
     def url(self, /) -> str:
         return url_for(
             'minifigure.details',
@@ -89,17 +85,24 @@ class RebrickableMinifigure(BrickRecord):
         if current_app.config['REBRICKABLE_LINKS']:
             try:
                 return current_app.config['REBRICKABLE_LINK_MINIFIGURE_PATTERN'].format(  # noqa: E501
-                    number=self.fields.figure,
+                    figure=self.fields.figure,
                 )
             except Exception:
                 pass
 
         return ''
 
+    # Compute the url for the bricklink page
+    # Note: BrickLink uses different minifigure IDs than Rebrickable (e.g., 'adv010' vs 'fig-000359')
+    # Rebrickable API doesn't provide BrickLink minifigure IDs, so we can't generate valid links
+    def url_for_bricklink(self, /) -> str:
+        # BrickLink links disabled for minifigures - no ID mapping available
+        # Left function for later, if I find a way to implement it. 
+        return ''
+
     # Normalize from Rebrickable
     @staticmethod
     def from_rebrickable(data: dict[str, Any], /, **_) -> dict[str, Any]:
-        # Extracting  number
         number = int(str(data['set_num'])[5:])
 
         return {
@@ -107,5 +110,5 @@ class RebrickableMinifigure(BrickRecord):
             'number': int(number),
             'name': str(data['set_name']),
             'quantity': int(data['quantity']),
-            'image': data['set_img_url'],
+            'image': str(data['set_img_url']) if data['set_img_url'] else None,
         }

bricktracker/rebrickable_part.py

@@ -67,8 +67,11 @@ class RebrickablePart(BrickRecord):
     def sql_parameters(self, /) -> dict[str, Any]:
         parameters = super().sql_parameters()
 
+        # Individual minifigure id takes precedence
+        if hasattr(self, 'individual_minifigure') and self.individual_minifigure is not None:
+            parameters['id'] = self.individual_minifigure.fields.id
         # Set id
-        if self.brickset is not None:
+        elif self.brickset is not None:
             parameters['id'] = self.brickset.fields.id
 
         # Use the minifigure number if present,
@@ -178,8 +181,14 @@ class RebrickablePart(BrickRecord):
             'color_rgb': data['color']['rgb'],
             'color_transparent': data['color']['is_trans'],
             'bricklink_color_id': None,
-            'bricklink_color_name': None,
-            'bricklink_part_num': None,
+            # Empty-string sentinel (not NULL) means "fetched from Rebrickable,
+            # but no BrickLink mapping exists upstream". need_refresh.sql flags
+            # only NULL (never fetched), so confirmed-missing parts/colors stop
+            # reappearing on the refresh list forever (#163). color_id stays
+            # NULL since it is an INTEGER column and is excluded from the
+            # refresh trigger instead.
+            'bricklink_color_name': '',
+            'bricklink_part_num': '',
             'name': data['part']['name'],
             'category': data['part']['part_cat_id'],
             'image': data['part']['part_img_url'],

bricktracker/rebrickable_set.py

@@ -95,6 +95,18 @@ class RebrickableSet(BrickRecord):
             socket.auto_progress(message='Parsing set number')
             set = parse_set(str(data['set']))
 
+            # Check if this is actually a minifigure (starts with fig-)
+            # If so, redirect to the minifigure handler
+            if set.startswith('fig-'):
+                from .individual_minifigure import IndividualMinifigure
+                # Transform data: minifigure handler expects 'figure' key instead of 'set'
+                minifig_data = data.copy()
+                minifig_data['figure'] = minifig_data.pop('set')
+                if from_download:
+                    return IndividualMinifigure().download(socket, minifig_data)
+                else:
+                    return IndividualMinifigure().load(socket, minifig_data)
+
             socket.auto_progress(
                 message='Set {set}: loading from Rebrickable'.format(
                     set=set,

bricktracker/record.py

@@ -1,3 +1,4 @@
+from datetime import datetime
 from sqlite3 import Row
 from typing import Any, ItemsView
 
@@ -5,6 +6,26 @@ from .fields import BrickRecordFields
 from .sql import BrickSQL
 
 
+def format_timestamp(timestamp: float | str | None, format_key: str = 'PURCHASE_DATE_FORMAT') -> str:
+    if timestamp is not None:
+        from flask import current_app
+
+        # Handle legacy string dates stored in database (convert to numeric timestamp)
+        if isinstance(timestamp, str):
+            try:
+                # Try parsing as date string first
+                time = datetime.strptime(timestamp, '%Y/%m/%d')
+            except ValueError:
+                # If that fails, return the string as-is (shouldn't happen but safe fallback)
+                return timestamp
+        else:
+            # Normal case: numeric timestamp
+            time = datetime.fromtimestamp(timestamp)
+
+        return time.strftime(current_app.config.get(format_key, '%Y/%m/%d'))
+    return ''
+
+
 # SQLite record
 class BrickRecord(object):
     select_query: str

bricktracker/reload.py

@@ -1,5 +1,6 @@
 from .instructions_list import BrickInstructionsList
 from .retired_list import BrickRetiredList
+from .set_custom_field_list import BrickSetCustomFieldList
 from .set_owner_list import BrickSetOwnerList
 from .set_purchase_location_list import BrickSetPurchaseLocationList
 from .set_status_list import BrickSetStatusList
@@ -31,6 +32,9 @@ def reload() -> None:
         # Reload the set tags
         BrickSetTagList.new(force=True)
 
+        # Reload the set custom fields
+        BrickSetCustomFieldList.new(force=True)
+
         # Reload retired sets
         BrickRetiredList(force=True)
 

bricktracker/set.py

@@ -10,6 +10,7 @@ from .exceptions import NotFoundException, DatabaseException, ErrorException
 from .minifigure_list import BrickMinifigureList
 from .part_list import BrickPartList
 from .rebrickable_set import RebrickableSet
+from .set_custom_field_list import BrickSetCustomFieldList
 from .set_owner_list import BrickSetOwnerList
 from .set_purchase_location_list import BrickSetPurchaseLocationList
 from .set_status_list import BrickSetStatusList
@@ -30,6 +31,7 @@ class BrickSet(RebrickableSet):
     insert_query: str = 'set/insert'
     update_purchase_date_query: str = 'set/update/purchase_date'
     update_purchase_price_query: str = 'set/update/purchase_price'
+    update_description_query: str = 'set/update/description'
 
     # Delete a set
     def delete(self, /) -> None:
@@ -56,13 +58,33 @@ class BrickSet(RebrickableSet):
             # Grabbing the refresh flag
             refresh: bool = bool(data.get('refresh', False))
 
-            # Generate an UUID for self
-            self.fields.id = str(uuid4())
+            # Generate an UUID for self (or use existing ID if refreshing)
+            if refresh:
+                # Find the existing set by set number to get its ID
+                result = BrickSQL().raw_execute(
+                    'SELECT "id" FROM "bricktracker_sets" WHERE "set" = :set',
+                    {'set': self.fields.set}
+                ).fetchone()
+
+                if result:
+                    # Use existing set ID
+                    self.fields.id = result['id']
+                else:
+                    # If set doesn't exist in database, treat as new import
+                    refresh = False
+                    self.fields.id = str(uuid4())
+            else:
+                self.fields.id = str(uuid4())
 
             # Insert the rebrickable set into database FIRST
             # This must happen before inserting bricktracker_sets due to FK constraint
             self.insert_rebrickable()
 
+            # Optionally override the freshly downloaded Rebrickable cover with
+            # the sidecar box art (best-effort; falls back silently to the
+            # Rebrickable image when unavailable).
+            self.apply_sidecar_box_art(data)
+
             if not refresh:
                 # Save the storage
                 storage = BrickSetStorageList.get(
@@ -78,23 +100,66 @@ class BrickSet(RebrickableSet):
                 )
                 self.fields.purchase_location = purchase_location.fields.id
 
+                # Save the purchase date
+                purchase_date = data.get('purchase_date', None)
+                if purchase_date == '':
+                    purchase_date = None
+                if purchase_date is not None:
+                    try:
+                        purchase_date = datetime.strptime(
+                            purchase_date, '%Y/%m/%d'
+                        ).timestamp()
+                    except Exception:
+                        purchase_date = None
+                self.fields.purchase_date = purchase_date
+
+                # Save the purchase price
+                purchase_price = data.get('purchase_price', None)
+                if purchase_price == '':
+                    purchase_price = None
+                if purchase_price is not None:
+                    try:
+                        purchase_price = float(purchase_price)
+                    except Exception:
+                        purchase_price = None
+                self.fields.purchase_price = purchase_price
+
+                # Save the description/notes
+                description = data.get('description', None)
+                if description == '':
+                    description = None
+                self.fields.description = description
+
                 # Insert into database (deferred - will execute at final commit)
                 # All operations are atomic - if anything fails, nothing is committed
                 self.insert(commit=False)
 
-                # Save the owners
+                # Save the owners (deferred - will execute at final commit)
                 owners: list[str] = list(data.get('owners', []))
 
                 for id in owners:
                     owner = BrickSetOwnerList.get(id)
-                    owner.update_set_state(self, state=True)
+                    owner.update_set_state(self, state=True, commit=False)
 
-                # Save the tags
+                # Save the statuses (deferred - will execute at final commit)
+                statuses: list[str] = list(data.get('statuses', []))
+
+                for id in statuses:
+                    status = BrickSetStatusList.get(id)
+                    status.update_set_state(self, state=True, commit=False)
+
+                # Save the tags (deferred - will execute at final commit)
                 tags: list[str] = list(data.get('tags', []))
 
                 for id in tags:
                     tag = BrickSetTagList.get(id)
-                    tag.update_set_state(self, state=True)
+                    tag.update_set_state(self, state=True, commit=False)
+
+            # If refreshing, prepare temp table for tracking parts across both set and minifigs
+            if refresh:
+                sql = BrickSQL()
+                sql.execute('part/create_temp_refresh_tracking_table', defer=False)
+                sql.execute('part/clear_temp_refresh_tracking_table', defer=False)
 
             # Load the inventory
             if not BrickPartList.download(socket, self, refresh=refresh):
@@ -104,6 +169,15 @@ class BrickSet(RebrickableSet):
             if not BrickMinifigureList.download(socket, self, refresh=refresh):
                 return False
 
+            # If refreshing, clean up orphaned parts after all parts have been processed
+            if refresh:
+                # Delete orphaned parts (parts that weren't in the API response)
+                BrickSQL().execute(
+                    'part/delete_untracked_parts',
+                    parameters={'id': self.fields.id},
+                    defer=False
+                )
+
             # Commit the transaction to the database
             socket.auto_progress(
                 message='Set {set}: writing to the database'.format(
@@ -157,6 +231,42 @@ class BrickSet(RebrickableSet):
 
         return True
 
+    # Override the cover with sidecar box art when requested during add.
+    # Never raises: a sidecar miss or failure leaves the Rebrickable cover.
+    def apply_sidecar_box_art(self, data: dict[str, Any], /) -> bool:
+        if not data.get('box_art'):
+            return False
+
+        try:
+            from .sidecar import BrickSidecar
+
+            if not BrickSidecar.enabled():
+                return False
+
+            ref = self.fields.set
+            # Honour SIDECAR_DEFAULT_COVER ('box' or 'set'); anything else
+            # (e.g. 'rebrickable') falls back to the reliable box image. The
+            # *_large variants almost never exist, so use the normal resolution.
+            cover = str(current_app.config.get('SIDECAR_DEFAULT_COVER', 'box'))
+            image_type = cover if cover in ('box', 'set') else 'box'
+            saved = BrickSidecar.save_cover_override(ref, image_type)
+
+            if saved:
+                logger.info('Set {ref}: cover set from sidecar {type} art'.format(
+                    ref=ref,
+                    type=image_type,
+                ))
+            else:
+                logger.info('Set {ref}: no sidecar {type} art, kept Rebrickable cover'.format(  # noqa: E501
+                    ref=ref,
+                    type=image_type,
+                ))
+
+            return saved
+        except Exception:
+            logger.debug('sidecar box art override failed', exc_info=True)
+            return False
+
     # Purchase date
     def purchase_date(self, /, *, standard: bool = False) -> str:
         if self.fields.purchase_date is not None:
@@ -225,6 +335,7 @@ class BrickSet(RebrickableSet):
 
         # Load from database
         if not self.select(
+            custom_fields=BrickSetCustomFieldList.as_columns(),
             owners=BrickSetOwnerList.as_columns(),
             statuses=BrickSetStatusList.as_columns(all=True),
             tags=BrickSetTagList.as_columns(),
@@ -355,3 +466,60 @@ class BrickSet(RebrickableSet):
     # Update purchase price url
     def url_for_purchase_price(self, /) -> str:
         return url_for('set.update_purchase_price', id=self.fields.id)
+
+    # Update description
+    def update_description(self, json: Any | None, /) -> Any:
+        value = json.get('value', None)  # type: ignore
+
+        if value == '':
+            value = None
+
+        self.fields.description = value
+
+        rows, _ = BrickSQL().execute_and_commit(
+            self.update_description_query,
+            parameters=self.sql_parameters()
+        )
+
+        if rows != 1:
+            raise DatabaseException('Could not update the description for set {set} ({id})'.format(  # noqa: E501
+                set=self.fields.set,
+                id=self.fields.id,
+            ))
+
+        # Info
+        logger.info('Description changed to "{value}" for set {set} ({id})'.format(  # noqa: E501
+            value=value,
+            set=self.fields.set,
+            id=self.fields.id,
+        ))
+
+        return value
+
+    # Update description url
+    def url_for_description(self, /) -> str:
+        return url_for('set.update_description', id=self.fields.id)
+
+    # Cover override url (image_type: 'box' or 'set')
+    def url_for_cover(self, image_type: str, /) -> str:
+        return url_for(
+            'set.cover_override',
+            id=self.fields.id,
+            image_type=image_type,
+        )
+
+    # Cover override url for a Brickset additional image (0-indexed)
+    def url_for_cover_additional(self, index: int, /) -> str:
+        return url_for(
+            'set.cover_override_additional',
+            id=self.fields.id,
+            index=index,
+        )
+
+    # Restore the Rebrickable cover url
+    def url_for_cover_restore(self, /) -> str:
+        return url_for('set.cover_restore', id=self.fields.id)
+
+    # Refresh the BrickLink market value url
+    def url_for_value_refresh(self, /) -> str:
+        return url_for('set.value_refresh', id=self.fields.id)

bricktracker/set_custom_field.py

@@ -0,0 +1,99 @@
+import logging
+from typing import Any, Self
+
+from .exceptions import DatabaseException, ErrorException
+from .metadata import BrickMetadata
+from .sql import BrickSQL
+
+logger = logging.getLogger(__name__)
+
+# Allowed custom field types. The type only drives the input widget and light
+# validation; the value is always stored as TEXT.
+CUSTOM_FIELD_TYPES = ('text', 'number', 'date')
+
+
+# Lego set custom field metadata (a typed, admin-defined per-set value)
+class BrickSetCustomField(BrickMetadata):
+    kind: str = 'custom field'
+
+    # Set value endpoint
+    set_state_endpoint: str = 'set.update_custom_field'
+
+    # Queries
+    delete_query: str = 'set/metadata/custom_field/delete'
+    insert_query: str = 'set/metadata/custom_field/insert'
+    select_query: str = 'set/metadata/custom_field/select'
+    update_field_query: str = 'set/metadata/custom_field/update/field'
+    update_set_value_query: str = 'set/metadata/custom_field/update/value'
+
+    # SQL column name uses an underscore (kind has a space), so override the
+    # default which would produce "custom-field_{id}".
+    def as_column(self, /) -> str:
+        return 'custom_field_{id}'.format(id=self.fields.id)
+
+    # Grab data from a form (name + type)
+    def from_form(self, form: dict[str, str], /) -> Self:
+        super().from_form(form)
+
+        field_type = form.get('type', 'text')
+
+        if field_type not in CUSTOM_FIELD_TYPES:
+            raise ErrorException(
+                'Unknown custom field type: {type}'.format(type=field_type)
+            )
+
+        self.fields.type = field_type
+
+        return self
+
+    # Insert into database (carry the type along)
+    def insert(self, /, **_) -> None:
+        super().insert(
+            type=self.fields.type
+        )
+
+    # Update the per-set value of this custom field. Unlike the base
+    # update_set_value (which targets a fixed column), the column name is
+    # dynamic, so it is injected into the query like update_set_state does.
+    def update_value(
+        self,
+        brickset: Any,
+        /,
+        *,
+        json: Any | None = None,
+        value: Any | None = None,
+    ) -> Any:
+        if value is None and json is not None:
+            value = json.get('value', '')
+
+        if value == '':
+            value = None
+
+        parameters = self.sql_parameters()
+        parameters['set_id'] = brickset.fields.id
+        parameters['value'] = value
+
+        rows, _ = BrickSQL().execute_and_commit(
+            self.update_set_value_query,
+            parameters=parameters,
+            name=self.as_column(),
+        )
+
+        if rows != 1:
+            raise DatabaseException(
+                'Could not update the custom field value for set {set} ({id})'.format(  # noqa: E501
+                    set=brickset.fields.set,
+                    id=brickset.fields.id,
+                )
+            )
+
+        logger.info(
+            'Custom field "{name}" value changed to "{value}" for set {set} ({id})'.format(  # noqa: E501
+                name=self.fields.name,
+                value=value,
+                set=brickset.fields.set,
+                id=brickset.fields.id,
+            )
+        )
+
+        return value

bricktracker/set_custom_field_list.py

@@ -0,0 +1,24 @@
+from typing import Self
+
+from .metadata_list import BrickMetadataList
+from .set_custom_field import BrickSetCustomField
+
+
+# Lego sets custom field list
+class BrickSetCustomFieldList(BrickMetadataList[BrickSetCustomField]):
+    kind: str = 'set custom fields'
+
+    # Database
+    table: str = 'bricktracker_set_custom_fields'
+    order: str = '"bricktracker_metadata_custom_fields"."name"'
+
+    # Queries
+    select_query: str = 'set/metadata/custom_field/list'
+
+    # Set value endpoint
+    set_value_endpoint: str = 'set.update_custom_field'
+
+    # Instantiate the list with the proper class
+    @classmethod
+    def new(cls, /, *, force: bool = False) -> Self:
+        return cls(BrickSetCustomField, force=force)

bricktracker/set_list.py

@@ -3,6 +3,7 @@ from typing import Any, Self, Union
 from flask import current_app
 
 from .record_list import BrickRecordList
+from .set_custom_field_list import BrickSetCustomFieldList
 from .set_owner import BrickSetOwner
 from .set_owner_list import BrickSetOwnerList
 from .set_purchase_location import BrickSetPurchaseLocation
@@ -36,6 +37,7 @@ class BrickSetList(BrickRecordList[BrickSet]):
     using_minifigure_query: str = 'set/list/using_minifigure'
     using_part_query: str = 'set/list/using_part'
     using_storage_query: str = 'set/list/using_storage'
+    using_purchase_location_query: str = 'set/list/using_purchase_location'
 
     def __init__(self, /):
         super().__init__()
@@ -51,6 +53,7 @@ class BrickSetList(BrickRecordList[BrickSet]):
     def all(self, /) -> Self:
         # Load the sets from the database with metadata context for filtering
         filter_context = {
+            'custom_fields': BrickSetCustomFieldList.as_columns(),
             'owners': BrickSetOwnerList.as_columns(),
             'statuses': BrickSetStatusList.as_columns(),
             'tags': BrickSetTagList.as_columns(),
@@ -63,6 +66,7 @@ class BrickSetList(BrickRecordList[BrickSet]):
     def all_consolidated(self, /) -> Self:
         # Load the sets from the database using consolidated query with metadata context
         filter_context = {
+            'custom_fields_dict': BrickSetCustomFieldList.as_column_mapping(),
             'owners_dict': BrickSetOwnerList.as_column_mapping(),
             'statuses_dict': BrickSetStatusList.as_column_mapping(),
             'tags_dict': BrickSetTagList.as_column_mapping(),
@@ -92,7 +96,15 @@ class BrickSetList(BrickRecordList[BrickSet]):
         # Convert theme name to theme ID for filtering
         theme_id_filter = None
         if theme_filter:
-            theme_id_filter = self._theme_name_to_id(theme_filter)
+            # Check if this is a NOT filter
+            if theme_filter.startswith('-'):
+                # Extract the actual theme value without the "-" prefix
+                actual_theme = theme_filter[1:]
+                theme_id = self._theme_name_to_id(actual_theme)
+                # Re-add the "-" prefix to the theme ID
+                theme_id_filter = f'-{theme_id}' if theme_id else None
+            else:
+                theme_id_filter = self._theme_name_to_id(theme_filter)
 
         # Check if any filters are applied
         has_filters = any([status_filter, theme_id_filter, owner_filter, purchase_location_filter, storage_filter, tag_filter, year_filter, duplicate_filter])
@@ -108,9 +120,11 @@ class BrickSetList(BrickRecordList[BrickSet]):
             'tag_filter': tag_filter,
             'year_filter': year_filter,
             'duplicate_filter': duplicate_filter,
+            'custom_fields': BrickSetCustomFieldList.as_columns(),
             'owners': BrickSetOwnerList.as_columns(),
             'statuses': BrickSetStatusList.as_columns(),
             'tags': BrickSetTagList.as_columns(),
+            'custom_fields_dict': BrickSetCustomFieldList.as_column_mapping(),
             'owners_dict': BrickSetOwnerList.as_column_mapping(),
             'statuses_dict': BrickSetStatusList.as_column_mapping(),
             'tags_dict': BrickSetTagList.as_column_mapping(),
@@ -670,6 +684,16 @@ class BrickSetList(BrickRecordList[BrickSet]):
 
         return self
 
+    # Sets using a purchase location
+    def using_purchase_location(self, purchase_location: BrickSetPurchaseLocation, /) -> Self:
+        # Save the parameters to the fields
+        self.fields.purchase_location = purchase_location.fields.id
+
+        # Load the sets from the database
+        self.list(override_query=self.using_purchase_location_query)
+
+        return self
+
 
 # Helper to build the metadata lists
 def set_metadata_lists(
@@ -686,6 +710,7 @@ def set_metadata_lists(
     ]
 ]:
     return {
+        'brickset_custom_fields': BrickSetCustomFieldList.list(),
         'brickset_owners': BrickSetOwnerList.list(),
         'brickset_purchase_locations': BrickSetPurchaseLocationList.list(as_class=as_class),  # noqa: E501
         'brickset_storages': BrickSetStorageList.list(as_class=as_class),

bricktracker/set_purchase_location.py

@@ -1,5 +1,7 @@
 from .metadata import BrickMetadata
 
+from flask import url_for
+
 
 # Lego set purchase location metadata
 class BrickSetPurchaseLocation(BrickMetadata):
@@ -11,3 +13,10 @@ class BrickSetPurchaseLocation(BrickMetadata):
     select_query: str = 'set/metadata/purchase_location/select'
     update_field_query: str = 'set/metadata/purchase_location/update/field'
     update_set_value_query: str = 'set/metadata/purchase_location/update/value'
+
+    # Self url
+    def url(self, /) -> str:
+        return url_for(
+            'purchase_location.details',
+            id=self.fields.id,
+        )

bricktracker/set_storage.py

@@ -1,4 +1,6 @@
 from .metadata import BrickMetadata
+from .exceptions import ErrorException
+from .sql import BrickSQL
 
 from flask import url_for
 
@@ -13,6 +15,7 @@ class BrickSetStorage(BrickMetadata):
     select_query: str = 'set/metadata/storage/select'
     update_field_query: str = 'set/metadata/storage/update/field'
     update_set_value_query: str = 'set/metadata/storage/update/value'
+    count_usage_query: str = 'set/metadata/storage/count_usage'
 
     # Self url
     def url(self, /) -> str:
@@ -20,3 +23,52 @@ class BrickSetStorage(BrickMetadata):
             'storage.details',
             id=self.fields.id,
         )
+
+    # Delete from database - check if storage is in use first
+    def delete(self, /) -> None:
+        # Check if storage is being used
+        sql = BrickSQL()
+        result = sql.fetchone(self.count_usage_query, parameters={'id': self.fields.id})
+
+        if result:
+            sets_count = result[0]
+            minifigures_count = result[1]
+            parts_count = result[2]
+            lots_count = result[3]
+
+            total_count = sets_count + minifigures_count + parts_count + lots_count
+
+            if total_count > 0:
+                # Build error message with counts and link
+                error_parts = []
+                if sets_count > 0:
+                    error_parts.append('{count} set{plural}'.format(
+                        count=sets_count,
+                        plural='s' if sets_count != 1 else ''
+                    ))
+                if minifigures_count > 0:
+                    error_parts.append('{count} individual minifigure{plural}'.format(
+                        count=minifigures_count,
+                        plural='s' if minifigures_count != 1 else ''
+                    ))
+                if parts_count > 0:
+                    error_parts.append('{count} individual part{plural}'.format(
+                        count=parts_count,
+                        plural='s' if parts_count != 1 else ''
+                    ))
+                if lots_count > 0:
+                    error_parts.append('{count} part lot{plural}'.format(
+                        count=lots_count,
+                        plural='s' if lots_count != 1 else ''
+                    ))
+
+                error_message = 'Cannot delete storage location "{name}". You need to remove {items} from this storage before it can be deleted. <a href="{url}">View storage details</a>'.format(
+                    name=self.fields.name,
+                    items=', '.join(error_parts),
+                    url=self.url()
+                )
+
+                raise ErrorException(error_message)
+
+        # If not in use, proceed with deletion
+        super().delete()

bricktracker/sidecar.py

@@ -0,0 +1,580 @@
+import json
+import logging
+import os
+import time
+from typing import Any, Final
+
+from flask import current_app, url_for
+import requests
+
+logger = logging.getLogger(__name__)
+
+# The four LEGO.com retail price regions the sidecar exposes. The sidecar
+# returns each of these as a JSON *string* (not a nested object), so we parse
+# them eagerly in get_set().
+RETAIL_REGIONS: Final[list[str]] = ['US', 'UK', 'CA', 'DE']
+
+# Currency code per retail region, used to label MSRP.
+REGION_CURRENCY: Final[dict[str, str]] = {
+    'US': 'USD',
+    'UK': 'GBP',
+    'CA': 'CAD',
+    'DE': 'EUR',
+}
+
+# Maps a currency *symbol* (as a user might set BK_PURCHASE_CURRENCY) to the ISO
+# code(s) it can stand for, so '$' is treated as the same currency as 'USD' and
+# 'kr' as 'DKK'. Symbols are inherently ambiguous (several currencies share one),
+# so we map to a set and treat two values as the same currency when their code
+# sets overlap. Keys are normalized (lowercased, trailing dots stripped).
+CURRENCY_SYMBOLS: Final[dict[str, set[str]]] = {
+    '$': {'USD', 'CAD', 'AUD', 'NZD', 'HKD', 'SGD', 'MXN', 'BRL'},
+    'us$': {'USD'},
+    'ca$': {'CAD'},
+    'c$': {'CAD'},
+    'a$': {'AUD'},
+    'nz$': {'NZD'},
+    '€': {'EUR'},
+    '£': {'GBP'},
+    '¥': {'JPY', 'CNY'},
+    '￥': {'JPY', 'CNY'},
+    'kr': {'DKK', 'SEK', 'NOK', 'ISK'},
+    'zł': {'PLN'},
+    '₽': {'RUB'},
+    '₹': {'INR'},
+    'r$': {'BRL'},
+    '₩': {'KRW'},
+    '฿': {'THB'},
+    '₪': {'ILS'},
+    'chf': {'CHF'},
+    'fr': {'CHF'},
+}
+
+# Valid BrickLink image types served by the sidecar image proxy.
+IMAGE_TYPES: Final[list[str]] = [
+    'box', 'box_large',
+    'set', 'set_large',
+    'instruction', 'instruction_large',
+]
+
+# How long (seconds) to trust a cached /health result so the UI never blocks
+# on a dead sidecar.
+HEALTH_CACHE_TTL: Final[int] = 30
+
+
+# Best-effort client for the brickset-sidecar (BrickData) container.
+#
+# Design rule: a missing or broken sidecar must NEVER break an existing page.
+# Every public method returns None / False on any failure and logs at debug.
+# Nothing in here is allowed to raise into a request handler. Guard call sites
+# with BrickSidecar.enabled().
+class BrickSidecar(object):
+    # Cache for the health probe: (checked_at, healthy)
+    _health_cache: tuple[float, bool] | None = None
+
+    # --- Configuration helpers ------------------------------------------
+
+    # Normalized base URL (no trailing slash), or '' when unset.
+    @staticmethod
+    def base_url() -> str:
+        return str(current_app.config.get('SIDECAR_URL', '') or '').rstrip('/')
+
+    # True only when a sidecar URL is configured (the feature toggle).
+    @staticmethod
+    def enabled() -> bool:
+        return BrickSidecar.base_url() != ''
+
+    @staticmethod
+    def timeout() -> int:
+        return int(current_app.config.get('SIDECAR_TIMEOUT', 5))
+
+    # --- Low level ------------------------------------------------------
+
+    # Perform a GET and return the parsed JSON, or None on any failure.
+    @staticmethod
+    def _get_json(path: str, /, *, params: dict[str, Any] | None = None) -> Any:
+        if not BrickSidecar.enabled():
+            return None
+
+        url = '{base}{path}'.format(base=BrickSidecar.base_url(), path=path)
+
+        try:
+            response = requests.get(
+                url,
+                params=params,
+                timeout=BrickSidecar.timeout(),
+            )
+
+            if not response.ok:
+                logger.debug(
+                    'sidecar GET %s -> HTTP %s', url, response.status_code,
+                )
+                return None
+
+            return response.json()
+        except (requests.RequestException, ValueError) as exception:
+            # ValueError covers JSON decode errors. Never propagate.
+            logger.debug('sidecar GET %s failed: %s', url, exception)
+            return None
+
+    # --- Health ---------------------------------------------------------
+
+    # Cached short-TTL health probe. Safe to call on every render.
+    @staticmethod
+    def healthy() -> bool:
+        if not BrickSidecar.enabled():
+            return False
+
+        now = time.monotonic()
+        cache = BrickSidecar._health_cache
+        if cache is not None and (now - cache[0]) < HEALTH_CACHE_TTL:
+            return cache[1]
+
+        payload = BrickSidecar._get_json('/health')
+        healthy = bool(payload) and payload.get('status') == 'ok'
+
+        BrickSidecar._health_cache = (now, healthy)
+        return healthy
+
+    # --- Sets -----------------------------------------------------------
+
+    # GET /sets/{ref}. Returns the single set dict (the sidecar wraps it in a
+    # {"sets": [...]} list) with the legoCom* JSON-string fields parsed into
+    # dicts, or None if missing. When price=True, the BrickLink price block is
+    # merged in under 'bricklink_price'.
+    #
+    # Metadata is read from the persistent cache first (effectively permanent);
+    # pass refresh=True to force a network fetch and rewrite the cache.
+    @staticmethod
+    def get_set(
+        ref: str,
+        /,
+        *,
+        price: bool = False,
+        refresh: bool = False,
+        use_cache: bool = True,
+    ) -> dict[str, Any] | None:
+        # The sidecar (brickdata) is the single source of truth and cache: it
+        # serves from its own DB and only hits Brickset on its own staleness
+        # rules. We always read live from it — no second cache here. (use_cache
+        # is kept for signature compatibility but no longer caches locally.)
+        params: dict[str, Any] = {}
+        if refresh:
+            params['refresh'] = 'true'
+
+        payload = BrickSidecar._get_json(
+            '/sets/{ref}'.format(ref=ref),
+            params=params or None,
+        )
+
+        if not payload:
+            return None
+
+        sets = payload.get('sets') or []
+        if not sets:
+            return None
+
+        data = sets[0]
+
+        # The sidecar serializes legoCom* as JSON strings; parse them so call
+        # sites get a dict (or None) instead of a raw string.
+        for region in RETAIL_REGIONS:
+            key = 'legoCom{region}'.format(region=region)
+            data[key] = BrickSidecar._parse_json_field(data.get(key))
+
+        if price:
+            data = dict(data)
+            data['bricklink_price'] = BrickSidecar.get_price(ref, refresh=refresh)
+
+        return data
+
+    # BrickLink market value for a set, or None. The sidecar owns price caching
+    # (and its own TTL): the normal path returns its cached price (fetching once
+    # if its cache expired), while refresh=True forces a live re-fetch via the
+    # dedicated /price endpoint. No local caching here.
+    @staticmethod
+    def get_price(
+        ref: str,
+        /,
+        *,
+        refresh: bool = False,
+        use_cache: bool = True,
+    ) -> dict[str, Any] | None:
+        currency = str(current_app.config.get('SIDECAR_CURRENCY', '') or '').strip()
+
+        # Forced refresh (explicit user action): the dedicated endpoint
+        # re-fetches from BrickLink and rewrites the sidecar's price cache.
+        if refresh:
+            params: dict[str, Any] = {'refresh': 'true'}
+            if currency:
+                params['currency'] = currency
+            return BrickSidecar._get_json(
+                '/sets/{ref}/price'.format(ref=ref),
+                params=params,
+            )
+
+        # Normal path: the sidecar returns its cached price, or fetches once if
+        # its own TTL expired. The price rides along on the set payload under
+        # bricklink_price. No local caching — the sidecar is the cache.
+        params = {'price': 'true'}
+        if currency:
+            params['currency'] = currency
+
+        payload = BrickSidecar._get_json(
+            '/sets/{ref}'.format(ref=ref),
+            params=params,
+        )
+
+        if payload:
+            sets = payload.get('sets') or []
+            if sets:
+                return sets[0].get('bricklink_price') or None
+
+        return None
+
+    # GET /sets/bulk -> cached metadata (+ cached price) for many sets at once,
+    # keyed by ref. The sidecar serves this from its own DB with no live
+    # Brickset/BrickLink calls, so it is cheap enough for collection-wide
+    # aggregation (stats) without a local cache. Requests are chunked to keep
+    # the query string within sane limits.
+    @staticmethod
+    def get_sets_bulk(
+        refs: list[str],
+        /,
+        *,
+        price: bool = True,
+        chunk_size: int = 200,
+    ) -> dict[str, dict[str, Any]]:
+        wanted = [r for r in refs if r]
+        if not wanted:
+            return {}
+
+        currency = str(current_app.config.get('SIDECAR_CURRENCY', '') or '').strip()
+        out: dict[str, dict[str, Any]] = {}
+
+        for start in range(0, len(wanted), chunk_size):
+            chunk = wanted[start:start + chunk_size]
+            params: dict[str, Any] = {'refs': ','.join(chunk)}
+            if price:
+                params['price'] = 'true'
+            if currency:
+                params['currency'] = currency
+
+            payload = BrickSidecar._get_json('/sets/bulk', params=params)
+            if not payload:
+                continue
+
+            for s in payload.get('sets') or []:
+                ref = '{n}-{v}'.format(
+                    n=s.get('number'), v=s.get('numberVariant'),
+                )
+                for region in RETAIL_REGIONS:
+                    key = 'legoCom{region}'.format(region=region)
+                    s[key] = BrickSidecar._parse_json_field(s.get(key))
+                out[ref] = s
+
+        return out
+
+    # Convenience: MSRP for the configured retail region (SIDECAR_RETAIL_REGION),
+    # read from an already-fetched get_set() payload. None when unavailable.
+    @staticmethod
+    def retail_price(set_data: dict[str, Any], /) -> float | None:
+        region = BrickSidecar.retail_region()
+        block = set_data.get('legoCom{region}'.format(region=region))
+
+        if not isinstance(block, dict):
+            return None
+
+        value = block.get('retailPrice')
+        try:
+            return float(value) if value is not None else None
+        except (TypeError, ValueError):
+            return None
+
+    # Configured retail region (US/UK/CA/DE), defaulting to US.
+    @staticmethod
+    def retail_region() -> str:
+        region = str(current_app.config.get('SIDECAR_RETAIL_REGION', 'US')).upper()
+        return region if region in REGION_CURRENCY else 'US'
+
+    # Currency code for the configured retail region.
+    @staticmethod
+    def retail_currency() -> str:
+        return REGION_CURRENCY[BrickSidecar.retail_region()]
+
+    # The ISO code(s) a currency value can represent. An ISO code maps to itself;
+    # a known symbol maps to its candidate set (see CURRENCY_SYMBOLS); anything
+    # else falls back to its own uppercased form.
+    @staticmethod
+    def currency_codes(value: Any, /) -> set[str]:
+        text = str(value or '').strip()
+        if not text:
+            return set()
+
+        key = text.rstrip('.').lower()
+        if key in CURRENCY_SYMBOLS:
+            return set(CURRENCY_SYMBOLS[key])
+
+        return {text.upper()}
+
+    # Whether two currency values (symbol or ISO code) denote the same currency.
+    # Empty values are treated as "unknown" -> compatible, so we never warn just
+    # because one side is unset.
+    @staticmethod
+    def same_currency(a: Any, b: Any, /) -> bool:
+        codes_a = BrickSidecar.currency_codes(a)
+        codes_b = BrickSidecar.currency_codes(b)
+
+        if not codes_a or not codes_b:
+            return True
+
+        return bool(codes_a & codes_b)
+
+    # Read a price WITHOUT triggering a live BrickLink fetch: ask the sidecar
+    # for a cache-only price (cached_only=true), so a render never blocks.
+    # Returns (price_dict, fetched_at) or (None, None).
+    @staticmethod
+    def cached_price(ref: str, /) -> tuple[dict[str, Any] | None, Any]:
+        params: dict[str, Any] = {'price': 'true', 'cached_only': 'true'}
+        currency = str(current_app.config.get('SIDECAR_CURRENCY', '') or '').strip()
+        if currency:
+            params['currency'] = currency
+
+        payload = BrickSidecar._get_json(
+            '/sets/{ref}'.format(ref=ref),
+            params=params,
+        )
+
+        price: dict[str, Any] | None = None
+        if payload:
+            sets = payload.get('sets') or []
+            if sets:
+                price = sets[0].get('bricklink_price') or None
+
+        if price is None:
+            return None, None
+
+        return price, price.get('fetched_at')
+
+    # --- Images ---------------------------------------------------------
+
+    # GET /sets/{ref}/images -> {'brickset': {...}, 'bricklink': {...}}, or None.
+    @staticmethod
+    def get_images(ref: str, /) -> dict[str, Any] | None:
+        return BrickSidecar._get_json('/sets/{ref}/images'.format(ref=ref))
+
+    # Build a BrickTracker-served proxy URL for an <img src>. The browser hits
+    # BrickTracker (same origin), which fetches the image from the sidecar
+    # server-side. This works even when the sidecar is only reachable on the
+    # internal Docker network (e.g. http://brickdata:3335), which the browser
+    # cannot resolve. Returns None when disabled or the type is unknown.
+    @staticmethod
+    def image_proxy_url(image_type: str, ref: str, /) -> str | None:
+        if image_type not in IMAGE_TYPES or not BrickSidecar.enabled():
+            return None
+
+        return url_for('sidecar.image', image_type=image_type, ref=ref)
+
+    # The real sidecar image URL, used server-side to fetch the bytes.
+    @staticmethod
+    def _remote_image_url(image_type: str, ref: str, /) -> str | None:
+        if image_type not in IMAGE_TYPES or not BrickSidecar.enabled():
+            return None
+
+        return '{base}/images/bricklink/{type}/{ref}.png'.format(
+            base=BrickSidecar.base_url(),
+            type=image_type,
+            ref=ref,
+        )
+
+    # Build a BrickTracker-served proxy URL for one Brickset additional image
+    # (0-indexed) for use in an <img src>. Like image_proxy_url, the browser
+    # talks only to BrickTracker, which fetches from the sidecar server-side.
+    # Pass thumbnail=True for the smaller variant. Returns None when disabled.
+    @staticmethod
+    def additional_image_url(
+        ref: str,
+        index: int,
+        /,
+        *,
+        thumbnail: bool = False,
+    ) -> str | None:
+        if not BrickSidecar.enabled():
+            return None
+
+        return url_for(
+            'sidecar.additional_image',
+            ref=ref,
+            index=index,
+            thumbnail=1 if thumbnail else None,
+        )
+
+    # The real sidecar additional-image URL, used server-side to fetch bytes.
+    # Unlike box/instruction art these come from Brickset, not the BrickLink
+    # image proxy.
+    @staticmethod
+    def _remote_additional_image_url(
+        ref: str,
+        index: int,
+        /,
+        *,
+        thumbnail: bool = False,
+    ) -> str | None:
+        if not BrickSidecar.enabled():
+            return None
+
+        url = '{base}/sets/{ref}/additional-images/{index}'.format(
+            base=BrickSidecar.base_url(),
+            ref=ref,
+            index=index,
+        )
+
+        if thumbnail:
+            url = '{url}?size=thumbnail'.format(url=url)
+
+        return url
+
+    # Fetch the raw bytes of one Brickset additional image (used when saving it
+    # as the cover). Returns None on any failure or a 404.
+    @staticmethod
+    def fetch_additional_image_bytes(
+        ref: str,
+        index: int,
+        /,
+        *,
+        thumbnail: bool = False,
+    ) -> bytes | None:
+        url = BrickSidecar._remote_additional_image_url(
+            ref, index, thumbnail=thumbnail,
+        )
+        if url is None:
+            return None
+
+        return BrickSidecar._fetch_bytes(url)
+
+    # Fetch the raw bytes of a proxied image (used when saving box art locally).
+    # Returns None on any failure or a 404 (BrickLink has no such image).
+    @staticmethod
+    def fetch_image_bytes(image_type: str, ref: str, /) -> bytes | None:
+        url = BrickSidecar._remote_image_url(image_type, ref)
+        if url is None:
+            return None
+
+        return BrickSidecar._fetch_bytes(url)
+
+    # Download the raw bytes at a sidecar image URL, returning None on any
+    # failure or a non-OK status (e.g. a 404 when the image does not exist).
+    @staticmethod
+    def _fetch_bytes(url: str, /) -> bytes | None:
+        try:
+            response = requests.get(url, timeout=BrickSidecar.timeout())
+
+            if not response.ok:
+                logger.debug(
+                    'sidecar image %s -> HTTP %s', url, response.status_code,
+                )
+                return None
+
+            return response.content
+        except requests.RequestException as exception:
+            logger.debug('sidecar image %s failed: %s', url, exception)
+            return None
+
+    # --- Cover override -------------------------------------------------
+
+    # Absolute path of the locally stored cover for a set ref, mirroring the
+    # logic in RebrickableImage.path() (everything is stored as .jpg).
+    @staticmethod
+    def cover_path(set_ref: str, /) -> str:
+        folder: str = current_app.config['SETS_FOLDER']
+
+        if folder.startswith('/'):
+            base_path = folder
+        else:
+            base_path = os.path.join(current_app.root_path, folder)
+
+        return os.path.join(base_path, '{ref}.jpg'.format(ref=set_ref))
+
+    # Backup path used so a box-art override is reversible.
+    @staticmethod
+    def cover_backup_path(set_ref: str, /) -> str:
+        return BrickSidecar.cover_path(set_ref).replace('.jpg', '.bak.jpg')
+
+    # Override the local cover image with a sidecar image (box / set). Backs up
+    # the existing cover the first time so it can be restored. Returns True on
+    # success, False if the sidecar has no such image or anything fails.
+    @staticmethod
+    def save_cover_override(set_ref: str, image_type: str, /) -> bool:
+        data = BrickSidecar.fetch_image_bytes(image_type, set_ref)
+        return BrickSidecar._write_cover(set_ref, data)
+
+    # Override the local cover with one Brickset additional image (0-indexed).
+    # Same backup/restore semantics as save_cover_override.
+    @staticmethod
+    def save_cover_override_from_additional(set_ref: str, index: int, /) -> bool:
+        data = BrickSidecar.fetch_additional_image_bytes(set_ref, index)
+        return BrickSidecar._write_cover(set_ref, data)
+
+    # Write image bytes as the local cover, backing up the original Rebrickable
+    # cover once so it stays restorable. Returns False when data is None (the
+    # sidecar had no such image) or on any filesystem error.
+    @staticmethod
+    def _write_cover(set_ref: str, data: bytes | None, /) -> bool:
+        if data is None:
+            return False
+
+        path = BrickSidecar.cover_path(set_ref)
+        backup = BrickSidecar.cover_backup_path(set_ref)
+
+        try:
+            # Preserve the original Rebrickable cover once.
+            if os.path.exists(path) and not os.path.exists(backup):
+                os.replace(path, backup)
+                # os.replace moved the file; re-create it below from bytes.
+
+            os.makedirs(os.path.dirname(path), exist_ok=True)
+            with open(path, 'wb') as handle:
+                handle.write(data)
+
+            return True
+        except OSError as exception:
+            logger.debug('cover override for %s failed: %s', set_ref, exception)
+            return False
+
+    # Restore a previously backed-up Rebrickable cover. Returns True if a backup
+    # existed and was restored, False otherwise (caller may re-download instead).
+    @staticmethod
+    def restore_cover(set_ref: str, /) -> bool:
+        path = BrickSidecar.cover_path(set_ref)
+        backup = BrickSidecar.cover_backup_path(set_ref)
+
+        if not os.path.exists(backup):
+            return False
+
+        try:
+            os.replace(backup, path)
+            return True
+        except OSError as exception:
+            logger.debug('cover restore for %s failed: %s', set_ref, exception)
+            return False
+
+    # --- Internal -------------------------------------------------------
+
+    # Parse a field the sidecar returns as a JSON string into a dict, tolerating
+    # None / empty / malformed values.
+    @staticmethod
+    def _parse_json_field(value: Any) -> dict[str, Any] | None:
+        if not value:
+            return None
+
+        if isinstance(value, dict):
+            return value
+
+        if isinstance(value, str):
+            try:
+                parsed = json.loads(value)
+                return parsed if isinstance(parsed, dict) else None
+            except ValueError:
+                return None
+
+        return None

bricktracker/sidecar_set.py

@@ -0,0 +1,294 @@
+import logging
+import re
+from datetime import datetime, timezone
+from html.parser import HTMLParser
+from typing import Any
+
+from flask import current_app
+from markupsafe import Markup, escape
+
+from .sidecar import BrickSidecar
+
+logger = logging.getLogger(__name__)
+
+# Brickset descriptions ship as HTML (<p>, <ul>, <li>, <i>, ...). Keep only a
+# small allowlist of formatting tags and drop every attribute so the markup can
+# be rendered as-is without opening an XSS hole.
+_ALLOWED_TAGS = frozenset({
+    'p', 'br', 'ul', 'ol', 'li', 'b', 'strong', 'i', 'em', 'u',
+    'h3', 'h4', 'h5', 'h6', 'small', 'sub', 'sup',
+})
+_VOID_TAGS = frozenset({'br'})
+
+
+# Build a normalized, template-friendly summary of the sidecar data for a set:
+# enrichment (description, dimensions, retired, ...) plus the three-way price
+# comparison (paid / retail MSRP / BrickLink market value).
+#
+# Metadata comes from the persistent cache (a local read, effectively
+# permanent). The BrickLink
+# market value is read from cache only here (no network) so a page render never
+# blocks; refreshing it is an explicit user action. Returns None when the
+# sidecar is disabled or has no data for the set.
+def summarize(
+    set_ref: str,
+    /,
+    *,
+    purchase_price: float | None = None,
+    fetch_price: bool = False,
+) -> dict[str, Any] | None:
+    if not BrickSidecar.enabled():
+        return None
+
+    data = BrickSidecar.get_set(set_ref)
+    if data is None:
+        return None
+
+    summary: dict[str, Any] = {
+        'description': _description(data.get('description')),
+        'pieces': data.get('pieces'),
+        'minifigs': data.get('minifigs'),
+        'year': data.get('year'),
+        'theme': _clean_str(data.get('theme')),
+        'subtheme': _clean_str(data.get('subtheme')),
+        # Set designer(s); web-scraped, the API does not expose it.
+        'designer': _clean_str(data.get('designer')),
+        'dimensions': _dimensions(data),
+        'weight': _weight(data),
+        'instructions_count': data.get('instructionsCount'),
+        'additional_image_count': _to_int(data.get('additionalImageCount')),
+        # Carousel + extra cover sources are opt-in; off keeps the plain cover.
+        'additional_images_enabled': bool(
+            current_app.config['SIDECAR_ADDITIONAL_IMAGES']
+        ),
+        'tags': _tags(data.get('tags')),
+        # Brickset "Notes" blurb (web-scraped; contains light HTML). Sanitised
+        # like the description so it renders safely.
+        'notes': _description(data.get('notes')),
+    }
+
+    # Retired status from the exit date. exit_date is the full formatted date
+    # (BrickData/Brickset has it even for sets retired long ago, which the
+    # upcoming-retirements CSV does not cover, see #37).
+    retired, exit_year = _retired(data.get('exitDate'))
+    summary['retired'] = retired
+    summary['exit_year'] = exit_year
+    summary['exit_date'] = _format_date(data.get('exitDate'))
+    summary['launch_year'] = _year(data.get('launchDate'))
+
+    # --- Prices: paid / retail / market --------------------------------
+    msrp = BrickSidecar.retail_price(data)
+
+    # When auto-fetch is on, hit the TTL-aware path (network only on a cache
+    # miss/expiry); otherwise stay cache-only so the render never blocks.
+    if fetch_price:
+        price_payload = BrickSidecar.get_price(set_ref)
+        price_fetched_at = None
+    else:
+        price_payload, price_fetched_at = BrickSidecar.cached_price(set_ref)
+
+    market_new = _to_float(price_payload.get('new_avg')) if price_payload else None
+    market_used = _to_float(price_payload.get('used_avg')) if price_payload else None
+    paid = _to_float(purchase_price)
+
+    prices: dict[str, Any] = {
+        'paid': paid,
+        'msrp': msrp,
+        'msrp_currency': BrickSidecar.retail_currency(),
+        # Inflation-adjusted RRP, web-scraped by the sidecar (single value in
+        # its own currency, independent of the configured retail region).
+        'msrp_inflated': _to_float(data.get('rrpInflated')),
+        'msrp_inflated_currency': _clean_str(data.get('rrpInflatedCurrency')),
+        'market_new': market_new,
+        'market_used': market_used,
+        'market_min': _to_float(price_payload.get('new_min')) if price_payload else None,  # noqa: E501
+        'market_max': _to_float(price_payload.get('new_max')) if price_payload else None,  # noqa: E501
+        'market_used_min': _to_float(price_payload.get('used_min')) if price_payload else None,  # noqa: E501
+        'market_used_max': _to_float(price_payload.get('used_max')) if price_payload else None,  # noqa: E501
+        'market_currency': (price_payload or {}).get('currency_code'),
+        'market_fetched_at': (price_payload or {}).get('fetched_at') or price_fetched_at,  # noqa: E501
+        'has_market': price_payload is not None,
+    }
+
+    # Savings vs MSRP (positive = paid less than retail).
+    if msrp is not None and paid is not None:
+        prices['savings_vs_msrp'] = round(msrp - paid, 2)
+
+    # Value movement vs what was paid. SIDECAR_PRICE_BASIS picks which market
+    # average to compare against ('used' or 'new'); fall back to the other when
+    # the preferred one is missing.
+    basis = str(current_app.config.get('SIDECAR_PRICE_BASIS', 'used')).lower()
+    if basis == 'new':
+        market_ref = market_new if market_new is not None else market_used
+    else:
+        market_ref = market_used if market_used is not None else market_new
+    prices['market_basis'] = basis
+    if market_ref is not None and paid is not None:
+        prices['gain_vs_paid'] = round(market_ref - paid, 2)
+
+    summary['prices'] = prices
+
+    return summary
+
+
+# --- Helpers ------------------------------------------------------------
+
+def _clean_str(value: Any) -> str | None:
+    if value is None:
+        return None
+    text = str(value).strip()
+    return text or None
+
+
+# Rebuild an HTML fragment keeping only the allowlisted formatting tags, with
+# all attributes stripped and all text escaped. convert_charrefs=True turns
+# entities (&reg;, &#39;, ...) into real characters before they reach the data
+# handler, so they render correctly.
+class _DescriptionSanitizer(HTMLParser):
+    def __init__(self) -> None:
+        super().__init__(convert_charrefs=True)
+        self.parts: list[str] = []
+
+    def handle_starttag(self, tag: str, attrs: Any) -> None:
+        if tag in _ALLOWED_TAGS:
+            self.parts.append(f'<{tag}>')
+
+    def handle_startendtag(self, tag: str, attrs: Any) -> None:
+        if tag in _ALLOWED_TAGS:
+            self.parts.append(f'<{tag}>')
+
+    def handle_endtag(self, tag: str) -> None:
+        if tag in _ALLOWED_TAGS and tag not in _VOID_TAGS:
+            self.parts.append(f'</{tag}>')
+
+    def handle_data(self, data: str) -> None:
+        self.parts.append(str(escape(data)))
+
+
+def _description(value: Any) -> Markup | None:
+    text = _clean_str(value)
+    if text is None:
+        return None
+    parser = _DescriptionSanitizer()
+    parser.feed(text)
+    parser.close()
+    cleaned = ''.join(parser.parts)
+    # Brickset pads descriptions with empty spacer paragraphs (<p>&nbsp;</p>);
+    # drop those so the blocks sit flush instead of leaving big gaps. \s also
+    # matches the &nbsp; (\xa0) the parser leaves behind.
+    cleaned = re.sub(r'<p>\s*</p>', '', cleaned).strip()
+    return Markup(cleaned) if cleaned else None
+
+
+def _to_float(value: Any) -> float | None:
+    if value is None or value == '':
+        return None
+    try:
+        return float(value)
+    except (TypeError, ValueError):
+        return None
+
+
+def _to_int(value: Any) -> int | None:
+    if value is None or value == '':
+        return None
+    try:
+        return int(value)
+    except (TypeError, ValueError):
+        return None
+
+
+def _dimensions(data: dict[str, Any], /) -> str | None:
+    height = _to_float(data.get('height'))
+    width = _to_float(data.get('width'))
+    depth = _to_float(data.get('depth'))
+
+    if height is None and width is None and depth is None:
+        return None
+
+    parts = [
+        '{0:g}'.format(round(value, 1)) if value is not None else '?'
+        for value in (height, width, depth)
+    ]
+    return '{0} cm'.format(' × '.join(parts))
+
+
+def _weight(data: dict[str, Any], /) -> str | None:
+    weight = _to_float(data.get('weight'))
+    if weight is None:
+        return None
+    return '{0:g} kg'.format(round(weight, 2))
+
+
+def _tags(value: Any) -> list[str]:
+    if not value:
+        return []
+    return [tag.strip() for tag in str(value).split(',') if tag.strip()]
+
+
+def _parse_date(value: Any) -> datetime | None:
+    if not value:
+        return None
+    text = str(value).replace('Z', '+00:00')
+    try:
+        parsed = datetime.fromisoformat(text)
+    except ValueError:
+        return None
+    if parsed.tzinfo is None:
+        parsed = parsed.replace(tzinfo=timezone.utc)
+    return parsed
+
+
+def _year(value: Any) -> int | None:
+    parsed = _parse_date(value)
+    return parsed.year if parsed is not None else None
+
+
+def _format_date(value: Any) -> str | None:
+    parsed = _parse_date(value)
+    if parsed is None:
+        return None
+    return parsed.strftime(current_app.config['PURCHASE_DATE_FORMAT'])
+
+
+def _retired(exit_date: Any) -> tuple[bool, int | None]:
+    parsed = _parse_date(exit_date)
+    if parsed is None:
+        return False, None
+    return parsed < datetime.now(timezone.utc), parsed.year
+
+
+# Bulk retirement lookup for a list of set refs (e.g. the whole wishlist) in a
+# single cached BrickData call. Returns {ref: {'date': str|None,
+# 'retired': bool}} keyed by both "number-variant" and the bare number so the
+# caller can match whichever form it stores. Empty dict when the sidecar is
+# disabled or unreachable, so callers degrade to the CSV (see #37).
+def retirement_dates(
+    set_refs: Any,
+    /,
+) -> dict[str, dict[str, Any]]:
+    if not BrickSidecar.enabled():
+        return {}
+
+    refs = list({str(ref) for ref in set_refs if ref})
+    if not refs:
+        return {}
+
+    try:
+        bulk = BrickSidecar.get_sets_bulk(refs, price=False)
+    except Exception as exception:
+        logger.debug('sidecar retirement bulk fetch failed: %s', exception)
+        return {}
+
+    out: dict[str, dict[str, Any]] = {}
+    for ref, data in bulk.items():
+        retired, _ = _retired(data.get('exitDate'))
+        info = {
+            'date': _format_date(data.get('exitDate')),
+            'retired': retired,
+        }
+        out[ref] = info
+        number, _, _ = ref.partition('-')
+        out.setdefault(number, info)
+
+    return out

bricktracker/socket.py

@@ -18,15 +18,25 @@ logger = logging.getLogger(__name__)
 MESSAGES: Final[dict[str, str]] = {
     'COMPLETE': 'complete',
     'CONNECT': 'connect',
+    'CREATE_LOT': 'create_lot',
+    'CREATE_BULK_INDIVIDUAL_PARTS': 'create_bulk_individual_parts',
     'DISCONNECT': 'disconnect',
     'DOWNLOAD_INSTRUCTIONS': 'download_instructions',
     'DOWNLOAD_PEERON_PAGES': 'download_peeron_pages',
     'FAIL': 'fail',
+    'IMPORT_MINIFIGURE': 'import_minifigure',
     'IMPORT_SET': 'import_set',
+    'LOAD_MINIFIGURE': 'load_minifigure',
+    'LOAD_PART': 'load_part',
+    'LOAD_PART_COLORS': 'load_part_colors',
     'LOAD_PEERON_PAGES': 'load_peeron_pages',
     'LOAD_SET': 'load_set',
+    'MINIFIGURE_LOADED': 'minifigure_loaded',
+    'PART_COLORS_LOADED': 'part_colors_loaded',
+    'PART_LOADED': 'part_loaded',
     'PROGRESS': 'progress',
     'SET_LOADED': 'set_loaded',
+    'VALUE_ALL_SETS': 'value_all_sets',
 }
 
 
@@ -65,6 +75,8 @@ class BrickSocket(object):
         )
 
         # Inject CORS if a domain is defined
+        # Note: For reverse proxy deployments, leave BK_DOMAIN_NAME empty to allow all origins
+        # When empty, Socket.IO defaults to permissive CORS which works with reverse proxies
         if app.config['DOMAIN_NAME'] != '':
             kwargs['cors_allowed_origins'] = app.config['DOMAIN_NAME']
 
@@ -75,6 +87,8 @@ class BrickSocket(object):
             **kwargs,
             path=app.config['SOCKET_PATH'],
             async_mode='gevent',
+            # Enable detailed logging in debug mode for troubleshooting
+            logger=app.config['DEBUG'],
             # Ping/pong settings for mobile network resilience
             ping_timeout=30,  # Wait 30s for pong response before disconnecting
             ping_interval=25,  # Send ping every 25s to keep connection alive
@@ -224,6 +238,130 @@ class BrickSocket(object):
 
             BrickSet().load(self, data)
 
+        @self.socket.on(MESSAGES['IMPORT_MINIFIGURE'], namespace=self.namespace)
+        @rebrickable_socket(self)
+        def import_minifigure(data: dict[str, Any], /) -> None:
+            logger.debug('Socket: IMPORT_MINIFIGURE={data} (from: {fr})'.format(
+                data=data,
+                fr=request.sid,  # type: ignore
+            ))
+
+            from .individual_minifigure import IndividualMinifigure
+            IndividualMinifigure().download(self, data)
+
+        @self.socket.on(MESSAGES['LOAD_MINIFIGURE'], namespace=self.namespace)
+        def load_minifigure(data: dict[str, Any], /) -> None:
+            logger.debug('Socket: LOAD_MINIFIGURE={data} (from: {fr})'.format(
+                data=data,
+                fr=request.sid,  # type: ignore
+            ))
+
+            from .individual_minifigure import IndividualMinifigure
+            IndividualMinifigure().load(self, data)
+
+        @self.socket.on(MESSAGES['LOAD_PART'], namespace=self.namespace)
+        def load_part(data: dict[str, Any], /) -> None:
+            logger.debug('Socket: LOAD_PART={data} (from: {fr})'.format(
+                data=data,
+                fr=request.sid,  # type: ignore
+            ))
+
+            from .individual_part import IndividualPart
+            IndividualPart().add(self, data)
+
+        @self.socket.on(MESSAGES['LOAD_PART_COLORS'], namespace=self.namespace)
+        def load_part_colors(data: dict[str, Any], /) -> None:
+            logger.debug('Socket: LOAD_PART_COLORS={data} (from: {fr})'.format(
+                data=data,
+                fr=request.sid,  # type: ignore
+            ))
+
+            from .individual_part import IndividualPart
+            IndividualPart().load_colors(self, data)
+
+        @self.socket.on(MESSAGES['CREATE_LOT'], namespace=self.namespace)
+        @rebrickable_socket(self)
+        def create_lot(data: dict[str, Any], /) -> None:
+            logger.debug('Socket: CREATE_LOT (from: {fr})'.format(
+                fr=request.sid,  # type: ignore
+            ))
+
+            from .individual_part_lot import IndividualPartLot
+            IndividualPartLot().create(self, data)
+
+        @self.socket.on(MESSAGES['CREATE_BULK_INDIVIDUAL_PARTS'], namespace=self.namespace)
+        @rebrickable_socket(self)
+        def create_bulk_individual_parts(data: dict[str, Any], /) -> None:
+            logger.debug('Socket: CREATE_BULK_INDIVIDUAL_PARTS (from: {fr})'.format(
+                fr=request.sid,  # type: ignore
+            ))
+
+            from .individual_part import IndividualPart
+            IndividualPart().create_bulk(self, data)
+
+        @self.socket.on(MESSAGES['VALUE_ALL_SETS'], namespace=self.namespace)
+        @authenticated_socket(self)
+        def value_all_sets(data: dict[str, Any], /) -> None:
+            logger.debug('Socket: VALUE_ALL_SETS (from: {fr})'.format(
+                fr=request.sid,  # type: ignore
+            ))
+
+            from .sidecar import BrickSidecar
+            from .sql import BrickSQL
+
+            if not BrickSidecar.enabled():
+                self.fail(message='The sidecar is not configured')
+                return
+
+            # Distinct set numbers in the collection (reuses the instructions
+            # statistics query).
+            try:
+                rows = BrickSQL().fetchall('statistics/set_numbers')
+            except Exception:
+                rows = []
+
+            refs = [row['set'] for row in rows]
+
+            self.progress_count = 0
+            self.update_total(len(refs))
+
+            priced = 0    # sets that ended up with a value (cached or fetched)
+            fetched = 0   # sets that required a live fetch this run
+            missing = 0   # sets with no value available at all
+            for ref in refs:
+                self.auto_progress(
+                    message='Valuing set {ref}'.format(ref=ref),
+                )
+
+                # Does the sidecar already hold a cached price for this set? If
+                # so, get_price() returns it without a live BrickLink fetch.
+                cached, fetched_at = BrickSidecar.cached_price(ref)
+                was_fresh = cached is not None
+
+                try:
+                    price = BrickSidecar.get_price(ref)
+                except Exception:
+                    price = None
+
+                if price is not None:
+                    priced += 1
+                    if not was_fresh:
+                        fetched += 1
+                else:
+                    missing += 1
+
+                # Only pause when we actually hit the network, so re-runs over an
+                # already-cached collection stay fast.
+                if not was_fresh:
+                    self.socket.sleep(0.2)
+
+            self.complete(
+                message=(
+                    '{priced} set(s) now have a value ({fetched} newly fetched); '
+                    '{missing} had none available.'
+                ).format(priced=priced, fetched=fetched, missing=missing),
+            )
+
     # Update the progress auto-incrementing
     def auto_progress(
         self,

bricktracker/sql/individual_minifigure/delete.sql

@@ -0,0 +1,24 @@
+-- A bit unsafe as it does not use a prepared statement but it
+-- should not be possible to inject anything through the {{ id }} context
+
+BEGIN TRANSACTION;
+
+-- Delete associated parts first
+DELETE FROM "bricktracker_individual_minifigure_parts"
+WHERE "id" IS NOT DISTINCT FROM '{{ id }}';
+
+-- Delete metadata from consolidated tables
+DELETE FROM "bricktracker_set_owners"
+WHERE "id" IS NOT DISTINCT FROM '{{ id }}';
+
+DELETE FROM "bricktracker_set_statuses"
+WHERE "id" IS NOT DISTINCT FROM '{{ id }}';
+
+DELETE FROM "bricktracker_set_tags"
+WHERE "id" IS NOT DISTINCT FROM '{{ id }}';
+
+-- Delete the individual minifigure itself
+DELETE FROM "bricktracker_individual_minifigures"
+WHERE "id" IS NOT DISTINCT FROM '{{ id }}';
+
+COMMIT;

bricktracker/sql/individual_minifigure/insert.sql

@@ -0,0 +1,19 @@
+INSERT OR IGNORE INTO "bricktracker_individual_minifigures" (
+    "id",
+    "figure",
+    "quantity",
+    "description",
+    "storage",
+    "purchase_location",
+    "purchase_date",
+    "purchase_price"
+) VALUES (
+    :id,
+    :figure,
+    :quantity,
+    :description,
+    :storage,
+    :purchase_location,
+    :purchase_date,
+    :purchase_price
+)

bricktracker/sql/individual_minifigure/list/all.sql

@@ -0,0 +1,45 @@
+-- List all individual minifigures
+SELECT
+    "bricktracker_individual_minifigures"."id",
+    "bricktracker_individual_minifigures"."figure",
+    "bricktracker_individual_minifigures"."quantity",
+    "bricktracker_individual_minifigures"."description",
+    "bricktracker_individual_minifigures"."storage",
+    "bricktracker_individual_minifigures"."purchase_location",
+    "bricktracker_individual_minifigures"."purchase_date",
+    "bricktracker_individual_minifigures"."purchase_price",
+    "rebrickable_minifigures"."number",
+    "rebrickable_minifigures"."name",
+    "rebrickable_minifigures"."image",
+    "rebrickable_minifigures"."number_of_parts",
+    0 AS "total_missing",
+    0 AS "total_damaged"{% if owners %},
+    {{ owners }}{% endif %}{% if statuses %},
+    {{ statuses }}{% endif %}{% if tags %},
+    {{ tags }}{% endif %}
+FROM "bricktracker_individual_minifigures"
+
+INNER JOIN "rebrickable_minifigures"
+    ON "bricktracker_individual_minifigures"."figure" = "rebrickable_minifigures"."figure"
+
+-- LEFT JOINs for metadata (owners, statuses, tags use separate dynamic column tables)
+LEFT JOIN "bricktracker_set_owners"
+    ON "bricktracker_individual_minifigures"."id" = "bricktracker_set_owners"."id"
+
+LEFT JOIN "bricktracker_set_statuses"
+    ON "bricktracker_individual_minifigures"."id" = "bricktracker_set_statuses"."id"
+
+LEFT JOIN "bricktracker_set_tags"
+    ON "bricktracker_individual_minifigures"."id" = "bricktracker_set_tags"."id"
+
+{% if order %}
+ORDER BY {{ order }}
+{% endif %}
+
+{% if limit %}
+LIMIT {{ limit }}
+{% endif %}
+
+{% if offset %}
+OFFSET {{ offset }}
+{% endif %}

bricktracker/sql/individual_minifigure/list/using_purchase_location.sql

@@ -0,0 +1,50 @@
+-- Get all individual minifigure instances for a specific purchase location
+SELECT
+    "bricktracker_individual_minifigures"."id",
+    "bricktracker_individual_minifigures"."figure",
+    "bricktracker_individual_minifigures"."quantity",
+    "bricktracker_individual_minifigures"."description",
+    "bricktracker_individual_minifigures"."storage",
+    "bricktracker_individual_minifigures"."purchase_location",
+    "bricktracker_individual_minifigures"."purchase_date",
+    "bricktracker_individual_minifigures"."purchase_price",
+    "rebrickable_minifigures"."number",
+    "rebrickable_minifigures"."name",
+    "rebrickable_minifigures"."image",
+    "rebrickable_minifigures"."number_of_parts",
+    "storage_meta"."name" AS "storage_name",
+    "purchase_meta"."name" AS "purchase_location_name",
+    IFNULL("problem_join"."total_missing", 0) AS "total_missing",
+    IFNULL("problem_join"."total_damaged", 0) AS "total_damaged"
+FROM "bricktracker_individual_minifigures"
+
+INNER JOIN "rebrickable_minifigures"
+ON "bricktracker_individual_minifigures"."figure" = "rebrickable_minifigures"."figure"
+
+LEFT JOIN "bricktracker_metadata_storages" AS "storage_meta"
+ON "bricktracker_individual_minifigures"."storage" = "storage_meta"."id"
+
+LEFT JOIN "bricktracker_metadata_purchase_locations" AS "purchase_meta"
+ON "bricktracker_individual_minifigures"."purchase_location" = "purchase_meta"."id"
+
+LEFT JOIN (
+    SELECT
+        "bricktracker_individual_minifigure_parts"."id",
+        SUM("bricktracker_individual_minifigure_parts"."missing") AS "total_missing",
+        SUM("bricktracker_individual_minifigure_parts"."damaged") AS "total_damaged"
+    FROM "bricktracker_individual_minifigure_parts"
+    GROUP BY "bricktracker_individual_minifigure_parts"."id"
+) "problem_join"
+ON "bricktracker_individual_minifigures"."id" = "problem_join"."id"
+
+WHERE "bricktracker_individual_minifigures"."purchase_location" IS NOT DISTINCT FROM :purchase_location
+
+{% if order %}
+ORDER BY {{ order }}
+{% else %}
+ORDER BY "bricktracker_individual_minifigures"."rowid" DESC
+{% endif %}
+
+{% if limit %}
+LIMIT {{ limit }}
+{% endif %}

bricktracker/sql/individual_minifigure/list/using_storage.sql

@@ -0,0 +1,50 @@
+-- Get all individual minifigure instances for a specific storage location
+SELECT
+    "bricktracker_individual_minifigures"."id",
+    "bricktracker_individual_minifigures"."figure",
+    "bricktracker_individual_minifigures"."quantity",
+    "bricktracker_individual_minifigures"."description",
+    "bricktracker_individual_minifigures"."storage",
+    "bricktracker_individual_minifigures"."purchase_location",
+    "bricktracker_individual_minifigures"."purchase_date",
+    "bricktracker_individual_minifigures"."purchase_price",
+    "rebrickable_minifigures"."number",
+    "rebrickable_minifigures"."name",
+    "rebrickable_minifigures"."image",
+    "rebrickable_minifigures"."number_of_parts",
+    "storage_meta"."name" AS "storage_name",
+    "purchase_meta"."name" AS "purchase_location_name",
+    IFNULL("problem_join"."total_missing", 0) AS "total_missing",
+    IFNULL("problem_join"."total_damaged", 0) AS "total_damaged"
+FROM "bricktracker_individual_minifigures"
+
+INNER JOIN "rebrickable_minifigures"
+ON "bricktracker_individual_minifigures"."figure" = "rebrickable_minifigures"."figure"
+
+LEFT JOIN "bricktracker_metadata_storages" AS "storage_meta"
+ON "bricktracker_individual_minifigures"."storage" = "storage_meta"."id"
+
+LEFT JOIN "bricktracker_metadata_purchase_locations" AS "purchase_meta"
+ON "bricktracker_individual_minifigures"."purchase_location" = "purchase_meta"."id"
+
+LEFT JOIN (
+    SELECT
+        "bricktracker_individual_minifigure_parts"."id",
+        SUM("bricktracker_individual_minifigure_parts"."missing") AS "total_missing",
+        SUM("bricktracker_individual_minifigure_parts"."damaged") AS "total_damaged"
+    FROM "bricktracker_individual_minifigure_parts"
+    GROUP BY "bricktracker_individual_minifigure_parts"."id"
+) "problem_join"
+ON "bricktracker_individual_minifigures"."id" = "problem_join"."id"
+
+WHERE "bricktracker_individual_minifigures"."storage" IS NOT DISTINCT FROM :storage
+
+{% if order %}
+ORDER BY {{ order }}
+{% else %}
+ORDER BY "bricktracker_individual_minifigures"."rowid" DESC
+{% endif %}
+
+{% if limit %}
+LIMIT {{ limit }}
+{% endif %}

bricktracker/sql/individual_minifigure/list/without_storage.sql

@@ -0,0 +1,50 @@
+-- Get all individual minifigure instances without storage
+SELECT
+    "bricktracker_individual_minifigures"."id",
+    "bricktracker_individual_minifigures"."figure",
+    "bricktracker_individual_minifigures"."quantity",
+    "bricktracker_individual_minifigures"."description",
+    "bricktracker_individual_minifigures"."storage",
+    "bricktracker_individual_minifigures"."purchase_location",
+    "bricktracker_individual_minifigures"."purchase_date",
+    "bricktracker_individual_minifigures"."purchase_price",
+    "rebrickable_minifigures"."number",
+    "rebrickable_minifigures"."name",
+    "rebrickable_minifigures"."image",
+    "rebrickable_minifigures"."number_of_parts",
+    "storage_meta"."name" AS "storage_name",
+    "purchase_meta"."name" AS "purchase_location_name",
+    IFNULL("problem_join"."total_missing", 0) AS "total_missing",
+    IFNULL("problem_join"."total_damaged", 0) AS "total_damaged"
+FROM "bricktracker_individual_minifigures"
+
+INNER JOIN "rebrickable_minifigures"
+ON "bricktracker_individual_minifigures"."figure" = "rebrickable_minifigures"."figure"
+
+LEFT JOIN "bricktracker_metadata_storages" AS "storage_meta"
+ON "bricktracker_individual_minifigures"."storage" = "storage_meta"."id"
+
+LEFT JOIN "bricktracker_metadata_purchase_locations" AS "purchase_meta"
+ON "bricktracker_individual_minifigures"."purchase_location" = "purchase_meta"."id"
+
+LEFT JOIN (
+    SELECT
+        "bricktracker_individual_minifigure_parts"."id",
+        SUM("bricktracker_individual_minifigure_parts"."missing") AS "total_missing",
+        SUM("bricktracker_individual_minifigure_parts"."damaged") AS "total_damaged"
+    FROM "bricktracker_individual_minifigure_parts"
+    GROUP BY "bricktracker_individual_minifigure_parts"."id"
+) "problem_join"
+ON "bricktracker_individual_minifigures"."id" = "problem_join"."id"
+
+WHERE "bricktracker_individual_minifigures"."storage" IS NULL
+
+{% if order %}
+ORDER BY {{ order }}
+{% else %}
+ORDER BY "bricktracker_individual_minifigures"."rowid" DESC
+{% endif %}
+
+{% if limit %}
+LIMIT {{ limit }}
+{% endif %}

bricktracker/sql/individual_minifigure/part/insert.sql

@@ -0,0 +1,23 @@
+INSERT OR IGNORE INTO "bricktracker_individual_minifigure_parts" (
+    "id",
+    "part",
+    "color",
+    "spare",
+    "quantity",
+    "element",
+    "rebrickable_inventory",
+    "missing",
+    "damaged",
+    "checked"
+) VALUES (
+    :id,
+    :part,
+    :color,
+    :spare,
+    :quantity,
+    :element,
+    :rebrickable_inventory,
+    0,
+    0,
+    0
+)

bricktracker/sql/individual_minifigure/part/list/from_instance.sql

@@ -0,0 +1,38 @@
+-- Query parts for a specific individual minifigure instance
+SELECT
+    "bricktracker_individual_minifigure_parts"."id",
+    "bricktracker_individual_minifigures"."figure",
+    "bricktracker_individual_minifigure_parts"."part",
+    "bricktracker_individual_minifigure_parts"."color",
+    "bricktracker_individual_minifigure_parts"."spare",
+    "bricktracker_individual_minifigure_parts"."quantity",
+    "bricktracker_individual_minifigure_parts"."element",
+    "bricktracker_individual_minifigure_parts"."missing" AS "total_missing",
+    "bricktracker_individual_minifigure_parts"."damaged" AS "total_damaged",
+    "bricktracker_individual_minifigure_parts"."checked",
+    "rebrickable_parts"."color_name",
+    "rebrickable_parts"."color_rgb",
+    "rebrickable_parts"."color_transparent",
+    "rebrickable_parts"."bricklink_color_id",
+    "rebrickable_parts"."bricklink_color_name",
+    "rebrickable_parts"."bricklink_part_num",
+    "rebrickable_parts"."name",
+    "rebrickable_parts"."image",
+    "rebrickable_parts"."image_id",
+    "rebrickable_parts"."url",
+    "rebrickable_parts"."print",
+    NULL AS "total_quantity",
+    NULL AS "total_spare",
+    NULL AS "total_sets",
+    NULL AS "total_minifigures"
+FROM "bricktracker_individual_minifigure_parts"
+INNER JOIN "bricktracker_individual_minifigures"
+ON "bricktracker_individual_minifigure_parts"."id" = "bricktracker_individual_minifigures"."id"
+INNER JOIN "rebrickable_parts"
+ON "bricktracker_individual_minifigure_parts"."part" = "rebrickable_parts"."part"
+AND "bricktracker_individual_minifigure_parts"."color" = "rebrickable_parts"."color_id"
+WHERE "bricktracker_individual_minifigure_parts"."id" IS NOT DISTINCT FROM :id
+
+{% if order %}
+ORDER BY {{ order | replace('"combined"', '"bricktracker_individual_minifigure_parts"') | replace('"bricktracker_parts"', '"bricktracker_individual_minifigure_parts"') }}
+{% endif %}

bricktracker/sql/individual_minifigure/part/select/specific.sql

@@ -0,0 +1,33 @@
+-- Select a specific part from an individual minifigure instance
+SELECT
+    "bricktracker_individual_minifigure_parts"."id",
+    "bricktracker_individual_minifigures"."figure",
+    "bricktracker_individual_minifigure_parts"."part",
+    "bricktracker_individual_minifigure_parts"."color",
+    "bricktracker_individual_minifigure_parts"."spare",
+    "bricktracker_individual_minifigure_parts"."quantity",
+    "bricktracker_individual_minifigure_parts"."element",
+    "bricktracker_individual_minifigure_parts"."missing",
+    "bricktracker_individual_minifigure_parts"."damaged",
+    "bricktracker_individual_minifigure_parts"."checked",
+    "rebrickable_parts"."color_name",
+    "rebrickable_parts"."color_rgb",
+    "rebrickable_parts"."color_transparent",
+    "rebrickable_parts"."bricklink_color_id",
+    "rebrickable_parts"."bricklink_color_name",
+    "rebrickable_parts"."bricklink_part_num",
+    "rebrickable_parts"."name",
+    "rebrickable_parts"."image",
+    "rebrickable_parts"."image_id",
+    "rebrickable_parts"."url",
+    "rebrickable_parts"."print"
+FROM "bricktracker_individual_minifigure_parts"
+INNER JOIN "bricktracker_individual_minifigures"
+ON "bricktracker_individual_minifigure_parts"."id" = "bricktracker_individual_minifigures"."id"
+INNER JOIN "rebrickable_parts"
+ON "bricktracker_individual_minifigure_parts"."part" = "rebrickable_parts"."part"
+AND "bricktracker_individual_minifigure_parts"."color" = "rebrickable_parts"."color_id"
+WHERE "bricktracker_individual_minifigure_parts"."id" IS NOT DISTINCT FROM :id
+AND "bricktracker_individual_minifigure_parts"."part" IS NOT DISTINCT FROM :part
+AND "bricktracker_individual_minifigure_parts"."color" IS NOT DISTINCT FROM :color
+AND "bricktracker_individual_minifigure_parts"."spare" IS NOT DISTINCT FROM :spare

bricktracker/sql/individual_minifigure/part/update/checked.sql

@@ -0,0 +1,6 @@
+UPDATE "bricktracker_individual_minifigure_parts"
+SET "checked" = :checked
+WHERE "bricktracker_individual_minifigure_parts"."id" IS NOT DISTINCT FROM :id
+AND "bricktracker_individual_minifigure_parts"."part" IS NOT DISTINCT FROM :part
+AND "bricktracker_individual_minifigure_parts"."color" IS NOT DISTINCT FROM :color
+AND "bricktracker_individual_minifigure_parts"."spare" IS NOT DISTINCT FROM :spare

bricktracker/sql/individual_minifigure/part/update/damaged.sql

@@ -0,0 +1,6 @@
+UPDATE "bricktracker_individual_minifigure_parts"
+SET "damaged" = :damaged
+WHERE "bricktracker_individual_minifigure_parts"."id" IS NOT DISTINCT FROM :id
+AND "bricktracker_individual_minifigure_parts"."part" IS NOT DISTINCT FROM :part
+AND "bricktracker_individual_minifigure_parts"."color" IS NOT DISTINCT FROM :color
+AND "bricktracker_individual_minifigure_parts"."spare" IS NOT DISTINCT FROM :spare

bricktracker/sql/individual_minifigure/part/update/missing.sql

@@ -0,0 +1,6 @@
+UPDATE "bricktracker_individual_minifigure_parts"
+SET "missing" = :missing
+WHERE "bricktracker_individual_minifigure_parts"."id" IS NOT DISTINCT FROM :id
+AND "bricktracker_individual_minifigure_parts"."part" IS NOT DISTINCT FROM :part
+AND "bricktracker_individual_minifigure_parts"."color" IS NOT DISTINCT FROM :color
+AND "bricktracker_individual_minifigure_parts"."spare" IS NOT DISTINCT FROM :spare

bricktracker/sql/individual_minifigure/select/by_id.sql

@@ -0,0 +1,52 @@
+-- Get a specific individual minifigure instance by ID
+SELECT
+    "bricktracker_individual_minifigures"."id",
+    "bricktracker_individual_minifigures"."figure",
+    "bricktracker_individual_minifigures"."quantity",
+    "bricktracker_individual_minifigures"."description",
+    "bricktracker_individual_minifigures"."storage",
+    "bricktracker_individual_minifigures"."purchase_location",
+    "bricktracker_individual_minifigures"."purchase_date",
+    "bricktracker_individual_minifigures"."purchase_price",
+    "rebrickable_minifigures"."number",
+    "rebrickable_minifigures"."name",
+    "rebrickable_minifigures"."image",
+    "rebrickable_minifigures"."number_of_parts",
+    "storage_meta"."name" AS "storage_name",
+    "purchase_meta"."name" AS "purchase_location_name",
+    IFNULL("problem_join"."total_missing", 0) AS "total_missing",
+    IFNULL("problem_join"."total_damaged", 0) AS "total_damaged"{% if owners %},
+    {{ owners }}{% endif %}{% if statuses %},
+    {{ statuses }}{% endif %}{% if tags %},
+    {{ tags }}{% endif %}
+FROM "bricktracker_individual_minifigures"
+
+INNER JOIN "rebrickable_minifigures"
+ON "bricktracker_individual_minifigures"."figure" = "rebrickable_minifigures"."figure"
+
+LEFT JOIN "bricktracker_metadata_storages" AS "storage_meta"
+ON "bricktracker_individual_minifigures"."storage" = "storage_meta"."id"
+
+LEFT JOIN "bricktracker_metadata_purchase_locations" AS "purchase_meta"
+ON "bricktracker_individual_minifigures"."purchase_location" = "purchase_meta"."id"
+
+LEFT JOIN "bricktracker_set_owners"
+ON "bricktracker_individual_minifigures"."id" IS NOT DISTINCT FROM "bricktracker_set_owners"."id"
+
+LEFT JOIN "bricktracker_set_statuses"
+ON "bricktracker_individual_minifigures"."id" IS NOT DISTINCT FROM "bricktracker_set_statuses"."id"
+
+LEFT JOIN "bricktracker_set_tags"
+ON "bricktracker_individual_minifigures"."id" IS NOT DISTINCT FROM "bricktracker_set_tags"."id"
+
+LEFT JOIN (
+    SELECT
+        "bricktracker_individual_minifigure_parts"."id",
+        SUM("bricktracker_individual_minifigure_parts"."missing") AS "total_missing",
+        SUM("bricktracker_individual_minifigure_parts"."damaged") AS "total_damaged"
+    FROM "bricktracker_individual_minifigure_parts"
+    GROUP BY "bricktracker_individual_minifigure_parts"."id"
+) "problem_join"
+ON "bricktracker_individual_minifigures"."id" = "problem_join"."id"
+
+WHERE "bricktracker_individual_minifigures"."id" = :id

bricktracker/sql/individual_minifigure/select/instances_by_figure.sql

@@ -0,0 +1,54 @@
+-- Get all individual minifigure instances for a specific figure
+SELECT
+    "bricktracker_individual_minifigures"."id",
+    "bricktracker_individual_minifigures"."figure",
+    "bricktracker_individual_minifigures"."quantity",
+    "bricktracker_individual_minifigures"."description",
+    "bricktracker_individual_minifigures"."storage",
+    "bricktracker_individual_minifigures"."purchase_location",
+    "bricktracker_individual_minifigures"."purchase_date",
+    "bricktracker_individual_minifigures"."purchase_price",
+    "rebrickable_minifigures"."number",
+    "rebrickable_minifigures"."name",
+    "rebrickable_minifigures"."image",
+    "rebrickable_minifigures"."number_of_parts",
+    "storage_meta"."name" AS "storage_name",
+    "purchase_meta"."name" AS "purchase_location_name",
+    {{ owners }},
+    {{ statuses }},
+    {{ tags }},
+    IFNULL("problem_join"."total_missing", 0) AS "total_missing",
+    IFNULL("problem_join"."total_damaged", 0) AS "total_damaged"
+FROM "bricktracker_individual_minifigures"
+
+INNER JOIN "rebrickable_minifigures"
+ON "bricktracker_individual_minifigures"."figure" = "rebrickable_minifigures"."figure"
+
+LEFT JOIN "bricktracker_metadata_storages" AS "storage_meta"
+ON "bricktracker_individual_minifigures"."storage" = "storage_meta"."id"
+
+LEFT JOIN "bricktracker_metadata_purchase_locations" AS "purchase_meta"
+ON "bricktracker_individual_minifigures"."purchase_location" = "purchase_meta"."id"
+
+LEFT JOIN "bricktracker_set_owners"
+ON "bricktracker_individual_minifigures"."id" = "bricktracker_set_owners"."id"
+
+LEFT JOIN "bricktracker_set_statuses"
+ON "bricktracker_individual_minifigures"."id" = "bricktracker_set_statuses"."id"
+
+LEFT JOIN "bricktracker_set_tags"
+ON "bricktracker_individual_minifigures"."id" = "bricktracker_set_tags"."id"
+
+LEFT JOIN (
+    SELECT
+        "bricktracker_individual_minifigure_parts"."id",
+        SUM("bricktracker_individual_minifigure_parts"."missing") AS "total_missing",
+        SUM("bricktracker_individual_minifigure_parts"."damaged") AS "total_damaged"
+    FROM "bricktracker_individual_minifigure_parts"
+    GROUP BY "bricktracker_individual_minifigure_parts"."id"
+) "problem_join"
+ON "bricktracker_individual_minifigures"."id" = "problem_join"."id"
+
+WHERE "bricktracker_individual_minifigures"."figure" = :figure
+
+ORDER BY "bricktracker_individual_minifigures"."rowid" DESC

bricktracker/sql/individual_minifigure/update.sql

@@ -0,0 +1,9 @@
+UPDATE "bricktracker_individual_minifigures"
+SET
+    "quantity" = :quantity,
+    "description" = :description,
+    "storage" = :storage,
+    "purchase_location" = :purchase_location,
+    "purchase_date" = :purchase_date,
+    "purchase_price" = :purchase_price
+WHERE "id" = :id

bricktracker/sql/individual_part/delete.sql

@@ -0,0 +1,17 @@
+-- A bit unsafe as it does not use a prepared statement but it
+-- should not be possible to inject anything through the {{ id }} context
+
+BEGIN TRANSACTION;
+
+-- Delete metadata from consolidated tables
+DELETE FROM "bricktracker_set_owners"
+WHERE "id" IS NOT DISTINCT FROM '{{ id }}';
+
+DELETE FROM "bricktracker_set_tags"
+WHERE "id" IS NOT DISTINCT FROM '{{ id }}';
+
+-- Delete the individual part itself
+DELETE FROM "bricktracker_individual_parts"
+WHERE "id" IS NOT DISTINCT FROM '{{ id }}';
+
+COMMIT;

bricktracker/sql/individual_part/insert.sql

@@ -0,0 +1,30 @@
+-- Insert a new individual part
+INSERT INTO "bricktracker_individual_parts" (
+    "id",
+    "part",
+    "color",
+    "quantity",
+    "missing",
+    "damaged",
+    "checked",
+    "description",
+    "lot_id",
+    "storage",
+    "purchase_location",
+    "purchase_date",
+    "purchase_price"
+) VALUES (
+    :id,
+    :part,
+    :color,
+    :quantity,
+    :missing,
+    :damaged,
+    :checked,
+    :description,
+    :lot_id,
+    :storage,
+    :purchase_location,
+    :purchase_date,
+    :purchase_price
+)

bricktracker/sql/individual_part/insert_with_lot.sql

@@ -0,0 +1,30 @@
+-- Insert an individual part that belongs to a lot
+INSERT INTO "bricktracker_individual_parts" (
+    "id",
+    "part",
+    "color",
+    "quantity",
+    "missing",
+    "damaged",
+    "checked",
+    "description",
+    "storage",
+    "purchase_location",
+    "purchase_date",
+    "purchase_price",
+    "lot_id"
+) VALUES (
+    :id,
+    :part,
+    :color,
+    :quantity,
+    0,
+    0,
+    0,
+    NULL,
+    NULL,
+    NULL,
+    NULL,
+    NULL,
+    :lot_id
+)

bricktracker/sql/individual_part/list/all.sql

@@ -0,0 +1,42 @@
+-- List all individual parts
+SELECT
+    "bricktracker_individual_parts"."id",
+    "bricktracker_individual_parts"."part",
+    "bricktracker_individual_parts"."color",
+    "bricktracker_individual_parts"."quantity",
+    "bricktracker_individual_parts"."missing",
+    "bricktracker_individual_parts"."damaged",
+    "bricktracker_individual_parts"."checked",
+    "bricktracker_individual_parts"."description",
+    "bricktracker_individual_parts"."lot_id",
+    "bricktracker_individual_parts"."storage",
+    "bricktracker_individual_parts"."purchase_location",
+    "bricktracker_individual_parts"."purchase_date",
+    "bricktracker_individual_parts"."purchase_price",
+    "rebrickable_parts"."name" AS "part_name",
+    "rebrickable_parts"."color_name",
+    "rebrickable_parts"."color_rgb",
+    "rebrickable_parts"."color_transparent",
+    "rebrickable_parts"."category",
+    "rebrickable_parts"."image",
+    "rebrickable_parts"."image_id",
+    "rebrickable_parts"."url" AS "part_url",
+    "rebrickable_parts"."bricklink_part_num",
+    "rebrickable_parts"."bricklink_color_id",
+    "rebrickable_parts"."bricklink_color_name"
+FROM "bricktracker_individual_parts"
+INNER JOIN "rebrickable_parts"
+    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
+    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
+
+{% if order %}
+ORDER BY {{ order }}
+{% endif %}
+
+{% if limit %}
+LIMIT {{ limit }}
+{% endif %}
+
+{% if offset %}
+OFFSET {{ offset }}
+{% endif %}

bricktracker/sql/individual_part/list/by_color.sql

@@ -0,0 +1,31 @@
+SELECT
+    "bricktracker_individual_parts"."id",
+    "bricktracker_individual_parts"."part",
+    "bricktracker_individual_parts"."color",
+    "bricktracker_individual_parts"."quantity",
+    "bricktracker_individual_parts"."missing",
+    "bricktracker_individual_parts"."damaged",
+    "bricktracker_individual_parts"."checked",
+    "bricktracker_individual_parts"."description",
+    "bricktracker_individual_parts"."storage",
+    "bricktracker_individual_parts"."purchase_location",
+    "bricktracker_individual_parts"."purchase_date",
+    "bricktracker_individual_parts"."purchase_price",
+    "rebrickable_parts"."name",
+    "rebrickable_parts"."color_name",
+    "rebrickable_parts"."color_rgb",
+    "rebrickable_parts"."color_transparent",
+    "rebrickable_parts"."image",
+    "rebrickable_parts"."url",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
+FROM "bricktracker_individual_parts"
+INNER JOIN "rebrickable_parts"
+    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
+    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+WHERE "bricktracker_individual_parts"."color" = :color
+ORDER BY "bricktracker_individual_parts"."part"

bricktracker/sql/individual_part/list/by_part.sql

@@ -0,0 +1,31 @@
+SELECT
+    "bricktracker_individual_parts"."id",
+    "bricktracker_individual_parts"."part",
+    "bricktracker_individual_parts"."color",
+    "bricktracker_individual_parts"."quantity",
+    "bricktracker_individual_parts"."missing",
+    "bricktracker_individual_parts"."damaged",
+    "bricktracker_individual_parts"."checked",
+    "bricktracker_individual_parts"."description",
+    "bricktracker_individual_parts"."storage",
+    "bricktracker_individual_parts"."purchase_location",
+    "bricktracker_individual_parts"."purchase_date",
+    "bricktracker_individual_parts"."purchase_price",
+    "rebrickable_parts"."name",
+    "rebrickable_parts"."color_name",
+    "rebrickable_parts"."color_rgb",
+    "rebrickable_parts"."color_transparent",
+    "rebrickable_parts"."image",
+    "rebrickable_parts"."url",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
+FROM "bricktracker_individual_parts"
+INNER JOIN "rebrickable_parts"
+    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
+    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+WHERE "bricktracker_individual_parts"."part" = :part
+ORDER BY "bricktracker_individual_parts"."color"

bricktracker/sql/individual_part/list/by_part_and_color.sql

@@ -0,0 +1,34 @@
+SELECT
+    "bricktracker_individual_parts"."id",
+    "bricktracker_individual_parts"."part",
+    "bricktracker_individual_parts"."color",
+    "bricktracker_individual_parts"."quantity",
+    "bricktracker_individual_parts"."missing",
+    "bricktracker_individual_parts"."damaged",
+    "bricktracker_individual_parts"."checked",
+    "bricktracker_individual_parts"."description",
+    "bricktracker_individual_parts"."lot_id",
+    "bricktracker_individual_parts"."storage",
+    "bricktracker_individual_parts"."purchase_location",
+    "bricktracker_individual_parts"."purchase_date",
+    "bricktracker_individual_parts"."purchase_price",
+    "rebrickable_parts"."name",
+    "rebrickable_parts"."color_name",
+    "rebrickable_parts"."color_rgb",
+    "rebrickable_parts"."color_transparent",
+    "rebrickable_parts"."image",
+    "rebrickable_parts"."url",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
+FROM "bricktracker_individual_parts"
+INNER JOIN "rebrickable_parts"
+    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
+    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+WHERE "bricktracker_individual_parts"."part" = :part
+    AND "bricktracker_individual_parts"."color" = :color
+    AND "bricktracker_individual_parts"."lot_id" IS NULL
+ORDER BY "bricktracker_individual_parts"."id"

bricktracker/sql/individual_part/list/by_storage.sql

@@ -0,0 +1,31 @@
+SELECT
+    "bricktracker_individual_parts"."id",
+    "bricktracker_individual_parts"."part",
+    "bricktracker_individual_parts"."color",
+    "bricktracker_individual_parts"."quantity",
+    "bricktracker_individual_parts"."missing",
+    "bricktracker_individual_parts"."damaged",
+    "bricktracker_individual_parts"."checked",
+    "bricktracker_individual_parts"."description",
+    "bricktracker_individual_parts"."storage",
+    "bricktracker_individual_parts"."purchase_location",
+    "bricktracker_individual_parts"."purchase_date",
+    "bricktracker_individual_parts"."purchase_price",
+    "rebrickable_parts"."name",
+    "rebrickable_parts"."color_name",
+    "rebrickable_parts"."color_rgb",
+    "rebrickable_parts"."color_transparent",
+    "rebrickable_parts"."image",
+    "rebrickable_parts"."url",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
+FROM "bricktracker_individual_parts"
+INNER JOIN "rebrickable_parts"
+    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
+    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+WHERE "bricktracker_individual_parts"."storage" = :storage
+ORDER BY "bricktracker_individual_parts"."part", "bricktracker_individual_parts"."color"

bricktracker/sql/individual_part/list/problem.sql

@@ -0,0 +1,32 @@
+SELECT
+    "bricktracker_individual_parts"."id",
+    "bricktracker_individual_parts"."part",
+    "bricktracker_individual_parts"."color",
+    "bricktracker_individual_parts"."quantity",
+    "bricktracker_individual_parts"."missing",
+    "bricktracker_individual_parts"."damaged",
+    "bricktracker_individual_parts"."checked",
+    "bricktracker_individual_parts"."description",
+    "bricktracker_individual_parts"."storage",
+    "bricktracker_individual_parts"."purchase_location",
+    "bricktracker_individual_parts"."purchase_date",
+    "bricktracker_individual_parts"."purchase_price",
+    "rebrickable_parts"."name",
+    "rebrickable_parts"."color_name",
+    "rebrickable_parts"."color_rgb",
+    "rebrickable_parts"."color_transparent",
+    "rebrickable_parts"."image",
+    "rebrickable_parts"."url",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
+FROM "bricktracker_individual_parts"
+INNER JOIN "rebrickable_parts"
+    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
+    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+WHERE "bricktracker_individual_parts"."missing" > 0
+   OR "bricktracker_individual_parts"."damaged" > 0
+ORDER BY "bricktracker_individual_parts"."part", "bricktracker_individual_parts"."color"

bricktracker/sql/individual_part/list/using_purchase_location.sql

@@ -0,0 +1,32 @@
+SELECT
+    "bricktracker_individual_parts"."id",
+    "bricktracker_individual_parts"."part",
+    "bricktracker_individual_parts"."color",
+    "bricktracker_individual_parts"."quantity",
+    "bricktracker_individual_parts"."missing",
+    "bricktracker_individual_parts"."damaged",
+    "bricktracker_individual_parts"."checked",
+    "bricktracker_individual_parts"."description",
+    "bricktracker_individual_parts"."storage",
+    "bricktracker_individual_parts"."purchase_location",
+    "bricktracker_individual_parts"."purchase_date",
+    "bricktracker_individual_parts"."purchase_price",
+    "rebrickable_parts"."name",
+    "rebrickable_parts"."color_name",
+    "rebrickable_parts"."color_rgb",
+    "rebrickable_parts"."color_transparent",
+    "rebrickable_parts"."image",
+    "rebrickable_parts"."url",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
+FROM "bricktracker_individual_parts"
+INNER JOIN "rebrickable_parts"
+    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
+    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+WHERE "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM :purchase_location
+AND "bricktracker_individual_parts"."lot_id" IS NULL
+ORDER BY "bricktracker_individual_parts"."part", "bricktracker_individual_parts"."color"

bricktracker/sql/individual_part/list/using_storage.sql

@@ -0,0 +1,31 @@
+SELECT
+    "bricktracker_individual_parts"."id",
+    "bricktracker_individual_parts"."part",
+    "bricktracker_individual_parts"."color",
+    "bricktracker_individual_parts"."quantity",
+    "bricktracker_individual_parts"."missing",
+    "bricktracker_individual_parts"."damaged",
+    "bricktracker_individual_parts"."checked",
+    "bricktracker_individual_parts"."description",
+    "bricktracker_individual_parts"."storage",
+    "bricktracker_individual_parts"."purchase_location",
+    "bricktracker_individual_parts"."purchase_date",
+    "bricktracker_individual_parts"."purchase_price",
+    "rebrickable_parts"."name",
+    "rebrickable_parts"."color_name",
+    "rebrickable_parts"."color_rgb",
+    "rebrickable_parts"."color_transparent",
+    "rebrickable_parts"."image",
+    "rebrickable_parts"."url",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
+FROM "bricktracker_individual_parts"
+INNER JOIN "rebrickable_parts"
+    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
+    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+WHERE "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM :storage
+ORDER BY "bricktracker_individual_parts"."part", "bricktracker_individual_parts"."color"

bricktracker/sql/individual_part/list/without_storage.sql

@@ -0,0 +1,31 @@
+SELECT
+    "bricktracker_individual_parts"."id",
+    "bricktracker_individual_parts"."part",
+    "bricktracker_individual_parts"."color",
+    "bricktracker_individual_parts"."quantity",
+    "bricktracker_individual_parts"."missing",
+    "bricktracker_individual_parts"."damaged",
+    "bricktracker_individual_parts"."checked",
+    "bricktracker_individual_parts"."description",
+    "bricktracker_individual_parts"."storage",
+    "bricktracker_individual_parts"."purchase_location",
+    "bricktracker_individual_parts"."purchase_date",
+    "bricktracker_individual_parts"."purchase_price",
+    "rebrickable_parts"."name",
+    "rebrickable_parts"."color_name",
+    "rebrickable_parts"."color_rgb",
+    "rebrickable_parts"."color_transparent",
+    "rebrickable_parts"."image",
+    "rebrickable_parts"."url",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
+FROM "bricktracker_individual_parts"
+INNER JOIN "rebrickable_parts"
+    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
+    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_parts"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_parts"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+WHERE "bricktracker_individual_parts"."storage" IS NULL
+ORDER BY "bricktracker_individual_parts"."part", "bricktracker_individual_parts"."color"

bricktracker/sql/individual_part/select/by_id.sql

@@ -0,0 +1,44 @@
+-- Select a specific individual part by UUID
+SELECT
+    "bricktracker_individual_parts"."id",
+    "bricktracker_individual_parts"."part",
+    "bricktracker_individual_parts"."color",
+    "bricktracker_individual_parts"."quantity",
+    "bricktracker_individual_parts"."missing",
+    "bricktracker_individual_parts"."damaged",
+    "bricktracker_individual_parts"."checked",
+    "bricktracker_individual_parts"."description",
+    "bricktracker_individual_parts"."lot_id",
+    "bricktracker_individual_parts"."storage",
+    "bricktracker_individual_parts"."purchase_location",
+    "bricktracker_individual_parts"."purchase_date",
+    "bricktracker_individual_parts"."purchase_price",
+    "rebrickable_parts"."name" AS "part_name",
+    "rebrickable_parts"."color_name",
+    "rebrickable_parts"."color_rgb",
+    "rebrickable_parts"."color_transparent",
+    "rebrickable_parts"."category",
+    "rebrickable_parts"."image",
+    "rebrickable_parts"."image_id",
+    "rebrickable_parts"."url",
+    "rebrickable_parts"."bricklink_part_num",
+    "rebrickable_parts"."bricklink_color_id",
+    "rebrickable_parts"."bricklink_color_name"
+    {% if owners %},{{ owners }}{% endif %}
+    {% if statuses %},{{ statuses }}{% endif %}
+    {% if tags %},{{ tags }}{% endif %}
+FROM "bricktracker_individual_parts"
+INNER JOIN "rebrickable_parts"
+    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
+    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
+
+LEFT JOIN "bricktracker_set_owners"
+ON "bricktracker_individual_parts"."id" IS NOT DISTINCT FROM "bricktracker_set_owners"."id"
+
+LEFT JOIN "bricktracker_set_statuses"
+ON "bricktracker_individual_parts"."id" IS NOT DISTINCT FROM "bricktracker_set_statuses"."id"
+
+LEFT JOIN "bricktracker_set_tags"
+ON "bricktracker_individual_parts"."id" IS NOT DISTINCT FROM "bricktracker_set_tags"."id"
+
+WHERE "bricktracker_individual_parts"."id" = :id;

bricktracker/sql/individual_part/update/checked.sql

@@ -0,0 +1,3 @@
+UPDATE "bricktracker_individual_parts"
+SET "checked" = :checked
+WHERE "id" = :id

bricktracker/sql/individual_part/update/damaged.sql

@@ -0,0 +1,3 @@
+UPDATE "bricktracker_individual_parts"
+SET "damaged" = :damaged
+WHERE "id" = :id

bricktracker/sql/individual_part/update/description.sql

@@ -0,0 +1,4 @@
+-- Update description for an individual part
+UPDATE "bricktracker_individual_parts"
+SET "description" = :description
+WHERE "id" = :id;

bricktracker/sql/individual_part/update/field.sql

@@ -0,0 +1,4 @@
+-- Update a specific field in bricktracker_individual_parts
+UPDATE "bricktracker_individual_parts"
+SET "{{ field }}" = :value
+WHERE "id" = :id

bricktracker/sql/individual_part/update/missing.sql

@@ -0,0 +1,3 @@
+UPDATE "bricktracker_individual_parts"
+SET "missing" = :missing
+WHERE "id" = :id

bricktracker/sql/individual_part/update/quantity.sql

@@ -0,0 +1,4 @@
+-- Update quantity for an individual part
+UPDATE "bricktracker_individual_parts"
+SET "quantity" = :quantity
+WHERE "id" = :id;

bricktracker/sql/individual_part/update_full.sql

@@ -0,0 +1,9 @@
+UPDATE "bricktracker_individual_parts"
+SET
+    "quantity" = :quantity,
+    "description" = :description,
+    "storage" = :storage,
+    "purchase_location" = :purchase_location,
+    "purchase_date" = :purchase_date,
+    "purchase_price" = :purchase_price
+WHERE "id" = :id

bricktracker/sql/individual_part_lot/delete.sql

@@ -0,0 +1,22 @@
+-- A bit unsafe as it does not use a prepared statement but it
+-- should not be possible to inject anything through the {{ id }} context
+
+BEGIN TRANSACTION;
+
+-- Delete all individual parts associated with this lot
+DELETE FROM "bricktracker_individual_parts"
+WHERE "lot_id" IS NOT DISTINCT FROM '{{ id }}';
+
+-- Delete lot owners (using consolidated metadata table)
+DELETE FROM "bricktracker_set_owners"
+WHERE "id" IS NOT DISTINCT FROM '{{ id }}';
+
+-- Delete lot tags (using consolidated metadata table)
+DELETE FROM "bricktracker_set_tags"
+WHERE "id" IS NOT DISTINCT FROM '{{ id }}';
+
+-- Delete the lot itself
+DELETE FROM "bricktracker_individual_part_lots"
+WHERE "id" IS NOT DISTINCT FROM '{{ id }}';
+
+COMMIT;

bricktracker/sql/individual_part_lot/insert.sql

@@ -0,0 +1,19 @@
+INSERT INTO "bricktracker_individual_part_lots" (
+    "id",
+    "name",
+    "description",
+    "created_date",
+    "storage",
+    "purchase_location",
+    "purchase_date",
+    "purchase_price"
+) VALUES (
+    :id,
+    :name,
+    :description,
+    :created_date,
+    :storage,
+    :purchase_location,
+    :purchase_date,
+    :purchase_price
+)

bricktracker/sql/individual_part_lot/list/all.sql

@@ -0,0 +1,21 @@
+SELECT
+    "bricktracker_individual_part_lots"."id",
+    "bricktracker_individual_part_lots"."name",
+    "bricktracker_individual_part_lots"."description",
+    "bricktracker_individual_part_lots"."created_date",
+    "bricktracker_individual_part_lots"."storage",
+    "bricktracker_individual_part_lots"."purchase_location",
+    "bricktracker_individual_part_lots"."purchase_date",
+    "bricktracker_individual_part_lots"."purchase_price",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name",
+    COUNT("bricktracker_individual_parts"."id") AS "part_count"
+FROM "bricktracker_individual_part_lots"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_part_lots"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_part_lots"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+LEFT JOIN "bricktracker_individual_parts"
+    ON "bricktracker_individual_part_lots"."id" = "bricktracker_individual_parts"."lot_id"
+GROUP BY "bricktracker_individual_part_lots"."id"
+ORDER BY "bricktracker_individual_part_lots"."created_date" DESC

bricktracker/sql/individual_part_lot/list/by_part_and_color.sql

@@ -0,0 +1,23 @@
+SELECT DISTINCT
+    "bricktracker_individual_part_lots"."id",
+    "bricktracker_individual_part_lots"."name",
+    "bricktracker_individual_part_lots"."description",
+    "bricktracker_individual_part_lots"."created_date",
+    "bricktracker_individual_part_lots"."storage",
+    "bricktracker_individual_part_lots"."purchase_location",
+    "bricktracker_individual_part_lots"."purchase_date",
+    "bricktracker_individual_part_lots"."purchase_price",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name",
+    COUNT("bricktracker_individual_parts"."id") AS "part_count"
+FROM "bricktracker_individual_part_lots"
+INNER JOIN "bricktracker_individual_parts"
+    ON "bricktracker_individual_part_lots"."id" = "bricktracker_individual_parts"."lot_id"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_part_lots"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_part_lots"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+WHERE "bricktracker_individual_parts"."part" = :part
+    AND "bricktracker_individual_parts"."color" = :color
+GROUP BY "bricktracker_individual_part_lots"."id"
+ORDER BY "bricktracker_individual_part_lots"."created_date" DESC

bricktracker/sql/individual_part_lot/list/by_storage.sql

@@ -0,0 +1,22 @@
+SELECT
+    "bricktracker_individual_part_lots"."id",
+    "bricktracker_individual_part_lots"."name",
+    "bricktracker_individual_part_lots"."description",
+    "bricktracker_individual_part_lots"."created_date",
+    "bricktracker_individual_part_lots"."storage",
+    "bricktracker_individual_part_lots"."purchase_location",
+    "bricktracker_individual_part_lots"."purchase_date",
+    "bricktracker_individual_part_lots"."purchase_price",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name",
+    COUNT("bricktracker_individual_parts"."id") AS "part_count"
+FROM "bricktracker_individual_part_lots"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_part_lots"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_part_lots"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+LEFT JOIN "bricktracker_individual_parts"
+    ON "bricktracker_individual_part_lots"."id" = "bricktracker_individual_parts"."lot_id"
+WHERE "bricktracker_individual_part_lots"."storage" = :storage
+GROUP BY "bricktracker_individual_part_lots"."id"
+ORDER BY "bricktracker_individual_part_lots"."created_date" DESC

bricktracker/sql/individual_part_lot/list/parts.sql

@@ -0,0 +1,26 @@
+SELECT
+    "bricktracker_individual_parts"."id",
+    "bricktracker_individual_parts"."part",
+    "bricktracker_individual_parts"."color",
+    "bricktracker_individual_parts"."quantity",
+    "bricktracker_individual_parts"."missing",
+    "bricktracker_individual_parts"."damaged",
+    "bricktracker_individual_parts"."checked",
+    "bricktracker_individual_parts"."description",
+    "bricktracker_individual_parts"."storage",
+    "bricktracker_individual_parts"."purchase_location",
+    "bricktracker_individual_parts"."purchase_date",
+    "bricktracker_individual_parts"."purchase_price",
+    "bricktracker_individual_parts"."lot_id",
+    "rebrickable_parts"."name",
+    "rebrickable_parts"."color_name",
+    "rebrickable_parts"."color_rgb",
+    "rebrickable_parts"."color_transparent",
+    "rebrickable_parts"."image",
+    "rebrickable_parts"."url"
+FROM "bricktracker_individual_parts"
+INNER JOIN "rebrickable_parts"
+    ON "bricktracker_individual_parts"."part" = "rebrickable_parts"."part"
+    AND "bricktracker_individual_parts"."color" = "rebrickable_parts"."color_id"
+WHERE "bricktracker_individual_parts"."lot_id" = :lot_id
+ORDER BY "rebrickable_parts"."name" ASC, "bricktracker_individual_parts"."color" ASC

bricktracker/sql/individual_part_lot/list/problem.sql

@@ -0,0 +1,23 @@
+SELECT
+    "bricktracker_individual_part_lots"."id",
+    "bricktracker_individual_part_lots"."name",
+    "bricktracker_individual_part_lots"."description",
+    "bricktracker_individual_part_lots"."created_date",
+    "bricktracker_individual_part_lots"."storage",
+    "bricktracker_individual_part_lots"."purchase_location",
+    "bricktracker_individual_part_lots"."purchase_date",
+    "bricktracker_individual_part_lots"."purchase_price",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name",
+    COUNT("bricktracker_individual_parts"."id") AS "part_count"
+FROM "bricktracker_individual_part_lots"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_part_lots"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_part_lots"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+INNER JOIN "bricktracker_individual_parts"
+    ON "bricktracker_individual_part_lots"."id" = "bricktracker_individual_parts"."lot_id"
+WHERE "bricktracker_individual_parts"."missing" > 0
+   OR "bricktracker_individual_parts"."damaged" > 0
+GROUP BY "bricktracker_individual_part_lots"."id"
+ORDER BY "bricktracker_individual_part_lots"."created_date" DESC

bricktracker/sql/individual_part_lot/list/using_purchase_location.sql

@@ -0,0 +1,22 @@
+SELECT
+    "bricktracker_individual_part_lots"."id",
+    "bricktracker_individual_part_lots"."name",
+    "bricktracker_individual_part_lots"."description",
+    "bricktracker_individual_part_lots"."created_date",
+    "bricktracker_individual_part_lots"."storage",
+    "bricktracker_individual_part_lots"."purchase_location",
+    "bricktracker_individual_part_lots"."purchase_date",
+    "bricktracker_individual_part_lots"."purchase_price",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name",
+    COUNT("bricktracker_individual_parts"."id") AS "part_count"
+FROM "bricktracker_individual_part_lots"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_part_lots"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_part_lots"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+LEFT JOIN "bricktracker_individual_parts"
+    ON "bricktracker_individual_part_lots"."id" = "bricktracker_individual_parts"."lot_id"
+WHERE "bricktracker_individual_part_lots"."purchase_location" IS NOT DISTINCT FROM :purchase_location
+GROUP BY "bricktracker_individual_part_lots"."id"
+ORDER BY "bricktracker_individual_part_lots"."created_date" DESC

bricktracker/sql/individual_part_lot/list/using_storage.sql

@@ -0,0 +1,22 @@
+SELECT
+    "bricktracker_individual_part_lots"."id",
+    "bricktracker_individual_part_lots"."name",
+    "bricktracker_individual_part_lots"."description",
+    "bricktracker_individual_part_lots"."created_date",
+    "bricktracker_individual_part_lots"."storage",
+    "bricktracker_individual_part_lots"."purchase_location",
+    "bricktracker_individual_part_lots"."purchase_date",
+    "bricktracker_individual_part_lots"."purchase_price",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name",
+    COUNT("bricktracker_individual_parts"."id") AS "part_count"
+FROM "bricktracker_individual_part_lots"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_part_lots"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_part_lots"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+LEFT JOIN "bricktracker_individual_parts"
+    ON "bricktracker_individual_part_lots"."id" = "bricktracker_individual_parts"."lot_id"
+WHERE "bricktracker_individual_part_lots"."storage" IS NOT DISTINCT FROM :storage
+GROUP BY "bricktracker_individual_part_lots"."id"
+ORDER BY "bricktracker_individual_part_lots"."created_date" DESC

bricktracker/sql/individual_part_lot/list/without_storage.sql

@@ -0,0 +1,22 @@
+SELECT
+    "bricktracker_individual_part_lots"."id",
+    "bricktracker_individual_part_lots"."name",
+    "bricktracker_individual_part_lots"."description",
+    "bricktracker_individual_part_lots"."created_date",
+    "bricktracker_individual_part_lots"."storage",
+    "bricktracker_individual_part_lots"."purchase_location",
+    "bricktracker_individual_part_lots"."purchase_date",
+    "bricktracker_individual_part_lots"."purchase_price",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name",
+    COUNT("bricktracker_individual_parts"."id") AS "part_count"
+FROM "bricktracker_individual_part_lots"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_part_lots"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_part_lots"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+LEFT JOIN "bricktracker_individual_parts"
+    ON "bricktracker_individual_part_lots"."id" = "bricktracker_individual_parts"."lot_id"
+WHERE "bricktracker_individual_part_lots"."storage" IS NULL
+GROUP BY "bricktracker_individual_part_lots"."id"
+ORDER BY "bricktracker_individual_part_lots"."created_date" DESC

bricktracker/sql/individual_part_lot/select/by_id.sql

@@ -0,0 +1,28 @@
+SELECT
+    "bricktracker_individual_part_lots"."id",
+    "bricktracker_individual_part_lots"."name",
+    "bricktracker_individual_part_lots"."description",
+    "bricktracker_individual_part_lots"."created_date",
+    "bricktracker_individual_part_lots"."storage",
+    "bricktracker_individual_part_lots"."purchase_location",
+    "bricktracker_individual_part_lots"."purchase_date",
+    "bricktracker_individual_part_lots"."purchase_price",
+    "bricktracker_metadata_storages"."name" AS "storage_name",
+    "bricktracker_metadata_purchase_locations"."name" AS "purchase_location_name"
+    {% if owners %},{{ owners }}{% endif %}
+    {% if tags %},{{ tags }}{% endif %}
+FROM "bricktracker_individual_part_lots"
+LEFT JOIN "bricktracker_metadata_storages"
+    ON "bricktracker_individual_part_lots"."storage" IS NOT DISTINCT FROM "bricktracker_metadata_storages"."id"
+LEFT JOIN "bricktracker_metadata_purchase_locations"
+    ON "bricktracker_individual_part_lots"."purchase_location" IS NOT DISTINCT FROM "bricktracker_metadata_purchase_locations"."id"
+
+LEFT JOIN "bricktracker_set_owners"
+ON "bricktracker_individual_part_lots"."id" IS NOT DISTINCT FROM "bricktracker_set_owners"."id"
+
+-- Note: Part lots don't have statuses, only owners and tags
+
+LEFT JOIN "bricktracker_set_tags"
+ON "bricktracker_individual_part_lots"."id" IS NOT DISTINCT FROM "bricktracker_set_tags"."id"
+
+WHERE "bricktracker_individual_part_lots"."id" = :id

bricktracker/sql/individual_part_lot/update/description.sql

@@ -0,0 +1,4 @@
+-- Update individual part lot description
+UPDATE "bricktracker_individual_part_lots"
+SET "description" = :description
+WHERE "id" = :id

bricktracker/sql/individual_part_lot/update/name.sql

@@ -0,0 +1,4 @@
+-- Update individual part lot name
+UPDATE "bricktracker_individual_part_lots"
+SET "name" = :name
+WHERE "id" = :id

bricktracker/sql/individual_part_lot/update/purchase_date.sql

@@ -0,0 +1,4 @@
+-- Update individual part lot purchase date
+UPDATE "bricktracker_individual_part_lots"
+SET "purchase_date" = :purchase_date
+WHERE "id" = :id

bricktracker/sql/individual_part_lot/update/purchase_location.sql

@@ -0,0 +1,4 @@
+-- Update individual part lot purchase location
+UPDATE "bricktracker_individual_part_lots"
+SET "purchase_location" = :purchase_location
+WHERE "id" = :id

bricktracker/sql/individual_part_lot/update/purchase_price.sql

@@ -0,0 +1,4 @@
+-- Update individual part lot purchase price
+UPDATE "bricktracker_individual_part_lots"
+SET "purchase_price" = :purchase_price
+WHERE "id" = :id

bricktracker/sql/individual_part_lot/update/storage.sql

@@ -0,0 +1,4 @@
+-- Update individual part lot storage
+UPDATE "bricktracker_individual_part_lots"
+SET "storage" = :storage
+WHERE "id" = :id

bricktracker/sql/migrations/0021.sql

@@ -0,0 +1,88 @@
+-- description: Add individual minifigures and individual parts tables
+
+-- Individual minifigures table - tracks individual minifigures not associated with sets
+CREATE TABLE IF NOT EXISTS "bricktracker_individual_minifigures" (
+    "id" TEXT NOT NULL,
+    "figure" TEXT NOT NULL,
+    "quantity" INTEGER NOT NULL DEFAULT 1,
+    "description" TEXT,
+    "storage" TEXT, -- Storage bin location
+    "purchase_date" REAL, -- Purchase date
+    "purchase_location" TEXT, -- Purchase location
+    "purchase_price" REAL, -- Purchase price
+    PRIMARY KEY("id"),
+    FOREIGN KEY("figure") REFERENCES "rebrickable_minifigures"("figure"),
+    FOREIGN KEY("storage") REFERENCES "bricktracker_metadata_storages"("id"),
+    FOREIGN KEY("purchase_location") REFERENCES "bricktracker_metadata_purchase_locations"("id")
+);
+
+-- Metadata for individual minifigures: use bricktracker_set_owners, bricktracker_set_tags, bricktracker_set_statuses tables
+
+-- Parts table for individual minifigures - tracks constituent parts
+CREATE TABLE IF NOT EXISTS "bricktracker_individual_minifigure_parts" (
+    "id" TEXT NOT NULL,
+    "part" TEXT NOT NULL,
+    "color" INTEGER NOT NULL,
+    "spare" BOOLEAN NOT NULL,
+    "quantity" INTEGER NOT NULL,
+    "element" INTEGER,
+    "rebrickable_inventory" INTEGER NOT NULL,
+    "missing" INTEGER NOT NULL DEFAULT 0,
+    "damaged" INTEGER NOT NULL DEFAULT 0,
+    "checked" BOOLEAN DEFAULT 0,
+    PRIMARY KEY("id", "part", "color", "spare"),
+    FOREIGN KEY("id") REFERENCES "bricktracker_individual_minifigures"("id"),
+    FOREIGN KEY("part", "color") REFERENCES "rebrickable_parts"("part", "color_id")
+);
+
+-- Individual parts table - tracks individual parts not associated with sets
+CREATE TABLE IF NOT EXISTS "bricktracker_individual_parts" (
+    "id" TEXT NOT NULL,
+    "part" TEXT NOT NULL,
+    "color" INTEGER NOT NULL,
+    "quantity" INTEGER NOT NULL DEFAULT 1,
+    "description" TEXT,
+    "storage" TEXT, -- Storage bin location
+    "purchase_date" REAL, -- Purchase date
+    "purchase_location" TEXT, -- Purchase location
+    "purchase_price" REAL, -- Purchase price
+    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")
+);
+
+-- Metadata for individual parts: use bricktracker_set_owners, bricktracker_set_tags, bricktracker_set_statuses tables
+
+-- Indexes for individual minifigures
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_minifigures_figure
+ON bricktracker_individual_minifigures(figure);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_minifigures_storage
+ON bricktracker_individual_minifigures(storage);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_minifigures_purchase_location
+ON bricktracker_individual_minifigures(purchase_location);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_minifigures_purchase_date
+ON bricktracker_individual_minifigures(purchase_date);
+
+-- Indexes for individual minifigure parts
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_minifigure_parts_id_missing_damaged
+ON bricktracker_individual_minifigure_parts(id, missing, damaged);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_minifigure_parts_part_color
+ON bricktracker_individual_minifigure_parts(part, color);
+
+-- Indexes for individual parts
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_parts_part_color
+ON bricktracker_individual_parts(part, color);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_parts_storage
+ON bricktracker_individual_parts(storage);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_parts_purchase_location
+ON bricktracker_individual_parts(purchase_location);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_parts_purchase_date
+ON bricktracker_individual_parts(purchase_date);

bricktracker/sql/migrations/0022.sql

@@ -0,0 +1,91 @@
+-- description: Add individual part lots system for bulk/cart adding of parts
+
+BEGIN TRANSACTION;
+
+-- Create individual part lots table
+CREATE TABLE IF NOT EXISTS "bricktracker_individual_part_lots" (
+    "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") ON DELETE SET NULL,
+    FOREIGN KEY("purchase_location") REFERENCES "bricktracker_metadata_purchase_locations"("id") ON DELETE SET NULL
+);
+
+-- Create index for faster lookups
+CREATE INDEX IF NOT EXISTS "idx_individual_part_lots_created_date"
+ON "bricktracker_individual_part_lots"("created_date");
+
+-- Add missing/damaged/checked fields to individual parts table
+ALTER TABLE "bricktracker_individual_parts"
+ADD COLUMN "missing" INTEGER NOT NULL DEFAULT 0;
+
+ALTER TABLE "bricktracker_individual_parts"
+ADD COLUMN "damaged" INTEGER NOT NULL DEFAULT 0;
+
+ALTER TABLE "bricktracker_individual_parts"
+ADD COLUMN "checked" BOOLEAN NOT NULL DEFAULT 0;
+
+-- Add lot_id column to individual parts table with foreign key constraint
+-- Note: SQLite doesn't support ALTER TABLE ADD CONSTRAINT for FK, so we need to recreate the table
+
+-- Create new table with FK constraint
+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,
+    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 (set lot_id to NULL for all existing parts)
+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";
+
+-- Drop old table
+DROP TABLE "bricktracker_individual_parts";
+
+-- Rename new table
+ALTER TABLE "bricktracker_individual_parts_new" RENAME TO "bricktracker_individual_parts";
+
+-- Recreate existing indexes
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_parts_part_color
+ON bricktracker_individual_parts(part, color);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_parts_storage
+ON bricktracker_individual_parts(storage);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_parts_purchase_location
+ON bricktracker_individual_parts(purchase_location);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_individual_parts_purchase_date
+ON bricktracker_individual_parts(purchase_date);
+
+-- Create lot_id index
+CREATE INDEX IF NOT EXISTS "idx_individual_parts_lot_id"
+ON "bricktracker_individual_parts"("lot_id");
+
+-- Metadata for individual part lots: use bricktracker_set_owners and bricktracker_set_tags tables
+-- Note: Part lots don't have statuses, only owners and tags
+
+COMMIT;

bricktracker/sql/migrations/0023.sql

@@ -0,0 +1,13 @@
+-- description: Add missing indexes for individual part lots optimization
+
+BEGIN TRANSACTION;
+
+-- Add storage index for lots table (for filtering by storage)
+CREATE INDEX IF NOT EXISTS "idx_individual_part_lots_storage"
+ON "bricktracker_individual_part_lots"("storage");
+
+-- Add purchase location index for lots table (for filtering by purchase location)
+CREATE INDEX IF NOT EXISTS "idx_individual_part_lots_purchase_location"
+ON "bricktracker_individual_part_lots"("purchase_location");
+
+COMMIT;

bricktracker/sql/migrations/0024.sql

@@ -0,0 +1,16 @@
+-- description: Create rebrickable_colors translation table for BrickLink color ID mapping
+-- This table caches color information from Rebrickable API to avoid repeated API calls
+-- and provides mapping between Rebrickable and BrickLink color IDs
+
+CREATE TABLE IF NOT EXISTS "rebrickable_colors" (
+    "color_id" INTEGER PRIMARY KEY,
+    "name" TEXT NOT NULL,
+    "rgb" TEXT,
+    "is_trans" BOOLEAN,
+    "bricklink_color_id" INTEGER,
+    "bricklink_color_name" TEXT
+);
+
+-- Create index for faster lookups
+CREATE INDEX IF NOT EXISTS "idx_rebrickable_colors_bricklink"
+ON "rebrickable_colors"("bricklink_color_id");

bricktracker/sql/migrations/0025.sql

@@ -0,0 +1,17 @@
+-- description: Add performance indexes for individual parts and minifigure parts
+
+BEGIN TRANSACTION;
+
+-- Composite index for lot part listing (common query: list parts in a lot)
+CREATE INDEX IF NOT EXISTS idx_individual_parts_lot_id_part_color
+ON bricktracker_individual_parts(lot_id, part, color);
+
+-- Problem tracking index for individual parts (common query: find parts with problems)
+CREATE INDEX IF NOT EXISTS idx_individual_parts_missing_damaged
+ON bricktracker_individual_parts(missing, damaged);
+
+-- Checked state index for individual minifigure parts (common query: find unchecked parts)
+CREATE INDEX IF NOT EXISTS idx_individual_minifigure_parts_checked
+ON bricktracker_individual_minifigure_parts(id, checked);
+
+COMMIT;

bricktracker/sql/migrations/0026.sql

@@ -0,0 +1,41 @@
+-- description: Standardize ON DELETE behavior for foreign keys (use RESTRICT everywhere)
+
+BEGIN TRANSACTION;
+
+-- Recreate bricktracker_individual_part_lots without ON DELETE SET NULL
+-- This makes FK behavior consistent: prevent deletion of metadata if referenced
+
+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
+INSERT INTO "bricktracker_individual_part_lots_new"
+SELECT * FROM "bricktracker_individual_part_lots";
+
+-- Drop old table
+DROP TABLE "bricktracker_individual_part_lots";
+
+-- Rename new table
+ALTER TABLE "bricktracker_individual_part_lots_new" RENAME TO "bricktracker_individual_part_lots";
+
+-- Recreate indexes
+CREATE INDEX IF NOT EXISTS "idx_individual_part_lots_created_date"
+ON "bricktracker_individual_part_lots"("created_date");
+
+CREATE INDEX IF NOT EXISTS "idx_individual_part_lots_storage"
+ON "bricktracker_individual_part_lots"("storage");
+
+CREATE INDEX IF NOT EXISTS "idx_individual_part_lots_purchase_location"
+ON "bricktracker_individual_part_lots"("purchase_location");
+
+COMMIT;

bricktracker/sql/migrations/0027.sql

@@ -0,0 +1,6 @@
+-- description: Remove foreign key constraints from consolidated metadata tables
+
+-- This migration is implemented entirely in Python (see migrations/0027.py)
+-- The Python code dynamically recreates bricktracker_set_owners, bricktracker_set_tags,
+-- and bricktracker_set_statuses without foreign key constraints so they can accept
+-- UUIDs from any entity type (sets, individual parts, individual minifigures, part lots)

bricktracker/sql/migrations/0028.sql

@@ -0,0 +1,17 @@
+-- description: Add sidecar_set_cache table for brickset-sidecar metadata and price caching
+
+BEGIN TRANSACTION;
+
+-- Caches responses from the optional brickset-sidecar so we never hammer it.
+-- payload        : JSON of GET /sets/{ref} (effectively permanent, refresh on demand)
+-- price_payload  : JSON of GET /sets/{ref}/price (honours SIDECAR_PRICE_CACHE_HOURS TTL)
+-- *_fetched_at   : epoch seconds (REAL) of when each payload was stored
+CREATE TABLE IF NOT EXISTS "sidecar_set_cache" (
+    "set_ref" TEXT NOT NULL PRIMARY KEY,
+    "payload" TEXT,
+    "price_payload" TEXT,
+    "fetched_at" REAL,
+    "price_fetched_at" REAL
+);
+
+COMMIT;

bricktracker/sql/migrations/0029.sql

@@ -0,0 +1,23 @@
+-- description: Add typed custom fields (admin-defined per-set fields)
+
+BEGIN TRANSACTION;
+
+-- Defines each custom field: an id, a name and a type (text / number / date).
+-- The type only drives the input widget + light validation; the per-set value
+-- is always stored as TEXT in a dynamic column on bricktracker_set_custom_fields.
+CREATE TABLE "bricktracker_metadata_custom_fields" (
+    "id" TEXT NOT NULL,
+    "name" TEXT NOT NULL,
+    "type" TEXT NOT NULL DEFAULT 'text',
+    PRIMARY KEY("id")
+);
+
+-- Holds the per-set value of each custom field. A "custom_field_{id}" TEXT
+-- column is added for every field created (mirroring the tag/status pattern).
+CREATE TABLE "bricktracker_set_custom_fields" (
+    "id" TEXT NOT NULL,
+    PRIMARY KEY("id"),
+    FOREIGN KEY("id") REFERENCES "bricktracker_sets"("id")
+);
+
+COMMIT;

bricktracker/sql/minifigure/base/base.sql

@@ -17,10 +17,10 @@ SELECT
     {% block total_sets %}
     NULL AS "total_sets" -- dummy for order: total_sets
     {% endblock %}
-FROM "bricktracker_minifigures"
+FROM "rebrickable_minifigures"
 
-INNER JOIN "rebrickable_minifigures"
-ON "bricktracker_minifigures"."figure" IS NOT DISTINCT FROM "rebrickable_minifigures"."figure"
+LEFT JOIN "bricktracker_minifigures"
+ON "rebrickable_minifigures"."figure" IS NOT DISTINCT FROM "bricktracker_minifigures"."figure"
 
 {% block join %}{% endblock %}