fix(add): fix for FK issue when adding sets and selecting metadata

Reviewed-on: #117

CHANGELOG.md

@@ -1,5 +1,44 @@
 # Changelog
 
+## Upcoming - 1.3.2
+
+### New Features
+
+- **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
+  - Detailed scan results show affected records with counts and descriptions
+- **Database Optimization**
+  - Added "Optimize Database" button to re-create performance indexes
+  - Safe to run after database imports or restores
+  - Re-creates all indexes from migration #19 using `CREATE INDEX IF NOT EXISTS`
+  - Runs `ANALYZE` to rebuild query statistics
+  - 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
+- **Fixed nil image downloads**: Placeholder images for parts and minifigures without images now download correctly
+  - Removed early returns that prevented nil image downloads
+  - Nil images now properly saved to configured folders (e.g., `/app/data/parts/nil.jpg`)
+- **Fixed error logging for missing files**: File not found errors now show actual configured folder paths instead of just URL paths
+  - Added detailed logging showing both file path and configured folder for easier debugging
+- **Fixed minifigure filters in client-side pagination mode**: Owner and other filters now work correctly when server-side pagination is disabled
+  - Aligned filter behavior with parts page (applies filters server-side, then loads filtered data for client-side search)
+
 ## 1.3
 
 ### Breaking Changes 

README.md

@@ -18,8 +18,6 @@ A web application for organizing and tracking LEGO sets, parts, and minifigures.
 
 ## Prefered setup: pre-build docker image
 
-Use the provided [compose.yaml](compose.yaml) file.
-
 See [Quick Start](https://bricktracker.baerentsen.space/quick-start) to get up and running right away.
 
 See [Walk Through](https://bricktracker.baerentsen.space/tutorial-first-steps) for a more detailed guide.

bricktracker/app.py

@@ -104,12 +104,14 @@ def setup_app(app: Flask) -> None:
             level=logging.DEBUG,
             format='[%(asctime)s] {%(filename)s:%(lineno)d} %(levelname)s - %(message)s',  # noqa: E501
         )
+        logging.getLogger().setLevel(logging.DEBUG)
     else:
         logging.basicConfig(
             stream=sys.stdout,
             level=logging.INFO,
             format='[%(asctime)s] %(levelname)s - %(message)s',
         )
+        logging.getLogger().setLevel(logging.INFO)
 
     # Load the navbar
     Navbar(app)

bricktracker/metadata.py

@@ -182,7 +182,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,15 +192,27 @@ class BrickMetadata(BrickRecord):
         parameters['set_id'] = brickset.fields.id
         parameters['state'] = state
 
-        rows, _ = BrickSQL().execute(
-            self.update_set_state_query,
-            parameters=parameters,
-            defer=True,
-            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(),
+            )
 
-        # Note: rows will be -1 when deferred, so we can't validate here
-        # Validation will happen at final commit in set.py
+        # 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,
+                id=brickset.fields.id,
+            ))
 
         # Info
         logger.info('{kind} "{name}" state changed to "{state}" for set {set} ({id})'.format(  # noqa: E501

bricktracker/minifigure.py

@@ -33,11 +33,7 @@ class BrickMinifigure(RebrickableMinifigure):
                 )
             )
 
-            if not refresh:
-                # Insert into database
-                self.insert(commit=False)
-
-            # Load the inventory
+            # Load the inventory (needed to count parts for rebrickable record)
             if not BrickPartList.download(
                 socket,
                 self.brickset,
@@ -46,9 +42,14 @@ class BrickMinifigure(RebrickableMinifigure):
             ):
                 return False
 
-            # Insert the rebrickable set into database (after counting parts)
+            # Insert the rebrickable minifigure into database first (parent record)
+            # This must happen before inserting into bricktracker_minifigures due to FK constraint
             self.insert_rebrickable()
 
+            if not refresh:
+                # Insert into bricktracker_minifigures database (child record)
+                self.insert(commit=False)
+
         except Exception as e:
             socket.fail(
                 message='Error while importing minifigure {figure} from {set}: {error}'.format(  # noqa: E501

bricktracker/minifigure_list.py

@@ -44,7 +44,11 @@ class BrickMinifigureList(BrickRecordList[BrickMinifigure]):
         return self
 
     # Load all minifigures with problems filter
-    def all_filtered(self, /, 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') -> Self:
+        # Save the owner_id parameter
+        if owner_id is not None:
+            self.fields.owner_id = owner_id
+
         context = {}
         if problems_filter and problems_filter != 'all':
             context['problems_filter'] = problems_filter
@@ -53,7 +57,13 @@ class BrickMinifigureList(BrickRecordList[BrickMinifigure]):
         if year and year != 'all':
             context['year'] = year
 
-        self.list(override_query=self.all_query, **context)
+        # Choose query based on whether owner filtering is needed
+        if owner_id and owner_id != 'all':
+            query = self.all_by_owner_query
+        else:
+            query = self.all_query
+
+        self.list(override_query=query, **context)
         return self
 
     # Load all minifigures by owner

bricktracker/part.py

@@ -62,13 +62,14 @@ class BrickPart(RebrickablePart):
                 )
             )
 
-            if not refresh:
-                # Insert into database
-                self.insert(commit=False)
-
-            # Insert the rebrickable set into database
+            # Insert the rebrickable part into database first (parent record)
+            # This must happen before inserting into bricktracker_parts due to FK constraint
             self.insert_rebrickable()
 
+            if not refresh:
+                # Insert into bricktracker_parts database (child record)
+                self.insert(commit=False)
+
         except Exception as e:
             socket.fail(
                 message='Error while importing part {part} from {kind} {identifier}: {error}'.format(  # noqa: E501

bricktracker/rebrickable_image.py

@@ -53,23 +53,7 @@ class RebrickableImage(object):
         if os.path.exists(path):
             return
 
-        # Check if the original image field is null - copy nil placeholder instead
-        if self.part is not None and self.part.fields.image is None:
-            return
-        if self.minifigure is not None and self.minifigure.fields.image is None:
-            return
-        if self.set.fields.image is None:
-            # Copy nil.png from parts folder to sets folder with set number as filename
-            parts_folder = current_app.config['PARTS_FOLDER']
-            if not os.path.isabs(parts_folder):
-                parts_folder = os.path.join(current_app.root_path, parts_folder)
-            nil_source = os.path.join(parts_folder, f"{RebrickableImage.nil_name()}.{self.extension}")
-
-            if os.path.exists(nil_source):
-                import shutil
-                shutil.copy2(nil_source, path)
-            return
-
+        # Get the URL (this handles nil images via url() method)
         url = self.url()
         if url is None:
             return

bricktracker/set.py

@@ -82,19 +82,19 @@ class BrickSet(RebrickableSet):
                 # 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 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)
 
             # Load the inventory
             if not BrickPartList.download(socket, self, refresh=refresh):

bricktracker/sql/schema/integrity_check_summary.sql

@@ -0,0 +1,24 @@
+-- Database integrity check summary
+-- Returns count of each type of integrity issue
+
+SELECT 'orphaned_sets' as issue_type, COUNT(*) as count,
+       'Sets in bricktracker_sets without matching rebrickable_sets record' as description
+FROM bricktracker_sets bs
+WHERE NOT EXISTS (
+    SELECT 1 FROM rebrickable_sets rs WHERE rs."set" = bs."set"
+)
+UNION ALL
+SELECT 'orphaned_parts' as issue_type, COUNT(*) as count,
+       'Parts in bricktracker_parts without matching rebrickable_parts record' as description
+FROM bricktracker_parts bp
+WHERE NOT EXISTS (
+    SELECT 1 FROM rebrickable_parts rp WHERE rp.part = bp.part AND rp.color_id = bp.color
+)
+UNION ALL
+SELECT 'parts_missing_set' as issue_type, COUNT(DISTINCT bp.id) as count,
+       'Parts referencing non-existent sets in bricktracker_sets' as description
+FROM bricktracker_parts bp
+WHERE NOT EXISTS (
+    SELECT 1 FROM bricktracker_sets bs WHERE bs.id = bp.id
+)
+ORDER BY count DESC;

bricktracker/sql/schema/integrity_delete_minifigures_for_orphaned_sets.sql

@@ -0,0 +1,8 @@
+DELETE FROM bricktracker_minifigures
+WHERE id IN (
+    SELECT bs.id
+    FROM bricktracker_sets bs
+    WHERE NOT EXISTS (
+        SELECT 1 FROM rebrickable_sets rs WHERE rs."set" = bs."set"
+    )
+);

bricktracker/sql/schema/integrity_delete_orphaned_parts.sql

@@ -0,0 +1,10 @@
+-- Delete orphaned parts (bricktracker_parts records without parent rebrickable_parts)
+
+DELETE FROM bricktracker_parts
+WHERE rowid IN (
+    SELECT bp.rowid
+    FROM bricktracker_parts bp
+    WHERE NOT EXISTS (
+        SELECT 1 FROM rebrickable_parts rp WHERE rp.part = bp.part AND rp.color_id = bp.color
+    )
+);

bricktracker/sql/schema/integrity_delete_orphaned_sets.sql

@@ -0,0 +1,10 @@
+-- Delete orphaned sets (bricktracker_sets records without parent rebrickable_sets)
+
+DELETE FROM bricktracker_sets
+WHERE "set" IN (
+    SELECT bs."set"
+    FROM bricktracker_sets bs
+    WHERE NOT EXISTS (
+        SELECT 1 FROM rebrickable_sets rs WHERE rs."set" = bs."set"
+    )
+);

bricktracker/sql/schema/integrity_delete_owners_for_orphaned_sets.sql

@@ -0,0 +1,8 @@
+DELETE FROM bricktracker_set_owners
+WHERE id IN (
+    SELECT bs.id
+    FROM bricktracker_sets bs
+    WHERE NOT EXISTS (
+        SELECT 1 FROM rebrickable_sets rs WHERE rs."set" = bs."set"
+    )
+);

bricktracker/sql/schema/integrity_delete_parts_for_orphaned_sets.sql

@@ -0,0 +1,8 @@
+DELETE FROM bricktracker_parts
+WHERE id IN (
+    SELECT bs.id
+    FROM bricktracker_sets bs
+    WHERE NOT EXISTS (
+        SELECT 1 FROM rebrickable_sets rs WHERE rs."set" = bs."set"
+    )
+);

bricktracker/sql/schema/integrity_delete_parts_missing_set.sql

@@ -0,0 +1,10 @@
+-- Delete parts that reference non-existent sets
+
+DELETE FROM bricktracker_parts
+WHERE rowid IN (
+    SELECT bp.rowid
+    FROM bricktracker_parts bp
+    WHERE NOT EXISTS (
+        SELECT 1 FROM bricktracker_sets bs WHERE bs.id = bp.id
+    )
+);

bricktracker/sql/schema/integrity_delete_statuses_for_orphaned_sets.sql

@@ -0,0 +1,8 @@
+DELETE FROM bricktracker_set_statuses
+WHERE id IN (
+    SELECT bs.id
+    FROM bricktracker_sets bs
+    WHERE NOT EXISTS (
+        SELECT 1 FROM rebrickable_sets rs WHERE rs."set" = bs."set"
+    )
+);

bricktracker/sql/schema/integrity_delete_tags_for_orphaned_sets.sql

@@ -0,0 +1,8 @@
+DELETE FROM bricktracker_set_tags
+WHERE id IN (
+    SELECT bs.id
+    FROM bricktracker_sets bs
+    WHERE NOT EXISTS (
+        SELECT 1 FROM rebrickable_sets rs WHERE rs."set" = bs."set"
+    )
+);

bricktracker/sql/schema/integrity_orphaned_parts.sql

@@ -0,0 +1,17 @@
+-- Find orphaned parts (bricktracker_parts records without parent rebrickable_parts)
+
+SELECT
+    bp.id,
+    bp.part,
+    bp.color,
+    bp.quantity,
+    bp.spare,
+    bp.missing,
+    bp.damaged,
+    bs."set" as set_number
+FROM bricktracker_parts bp
+LEFT JOIN bricktracker_sets bs ON bs.id = bp.id
+WHERE NOT EXISTS (
+    SELECT 1 FROM rebrickable_parts rp WHERE rp.part = bp.part AND rp.color_id = bp.color
+)
+ORDER BY bp.id, bp.part, bp.color;

bricktracker/sql/schema/integrity_orphaned_sets.sql

@@ -0,0 +1,15 @@
+-- Find orphaned sets (bricktracker_sets records without parent rebrickable_sets)
+
+SELECT
+    bs."set",
+    bs.id,
+    bs.description,
+    bs.storage,
+    bs.purchase_date,
+    bs.purchase_location,
+    bs.purchase_price
+FROM bricktracker_sets bs
+WHERE NOT EXISTS (
+    SELECT 1 FROM rebrickable_sets rs WHERE rs."set" = bs."set"
+)
+ORDER BY bs."set";

bricktracker/sql/schema/integrity_parts_missing_set.sql

@@ -0,0 +1,15 @@
+-- Find parts referencing non-existent sets
+
+SELECT
+    bp.id,
+    bp.part,
+    bp.color,
+    bp.quantity,
+    bp.spare,
+    bp.missing,
+    bp.damaged
+FROM bricktracker_parts bp
+WHERE NOT EXISTS (
+    SELECT 1 FROM bricktracker_sets bs WHERE bs.id = bp.id
+)
+ORDER BY bp.id, bp.part, bp.color;

bricktracker/sql/schema/optimize.sql

@@ -0,0 +1,39 @@
+-- Optimize database performance
+-- Re-applies performance indexes and runs database maintenance
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_parts_id_missing_damaged
+ON bricktracker_parts(id, missing, damaged);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_parts_part_color_spare
+ON bricktracker_parts(part, color, spare);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_sets_set_storage
+ON bricktracker_sets("set", storage);
+
+CREATE INDEX IF NOT EXISTS idx_rebrickable_sets_name_lower
+ON rebrickable_sets(LOWER(name));
+
+CREATE INDEX IF NOT EXISTS idx_rebrickable_parts_name_lower
+ON rebrickable_parts(LOWER(name));
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_sets_purchase_location
+ON bricktracker_sets(purchase_location);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_parts_quantity
+ON bricktracker_parts(quantity);
+
+CREATE INDEX IF NOT EXISTS idx_rebrickable_sets_year
+ON rebrickable_sets(year);
+
+CREATE INDEX IF NOT EXISTS idx_rebrickable_sets_theme_id
+ON rebrickable_sets(theme_id);
+
+CREATE INDEX IF NOT EXISTS idx_rebrickable_sets_number_version
+ON rebrickable_sets(number, version);
+
+CREATE INDEX IF NOT EXISTS idx_bricktracker_sets_purchase_date
+ON bricktracker_sets(purchase_date);
+
+ANALYZE;
+
+PRAGMA optimize;

bricktracker/sql_integrity.py

@@ -0,0 +1,242 @@
+import logging
+
+from .sql import BrickSQL
+
+logger = logging.getLogger(__name__)
+
+
+class BrickIntegrityIssue(object):
+    issue_type: str
+    count: int
+    description: str
+
+    def __init__(self, issue_type: str, count: int, description: str, /):
+        self.issue_type = issue_type
+        self.count = count
+        self.description = description
+
+
+class BrickOrphanedSet(object):
+    set: str
+    id: str
+    description: str | None
+    storage: str | None
+    purchase_date: float | None
+    purchase_location: str | None
+    purchase_price: float | None
+
+    def __init__(
+        self,
+        set: str,
+        id: str,
+        description: str | None,
+        storage: str | None,
+        purchase_date: float | None,
+        purchase_location: str | None,
+        purchase_price: float | None,
+        /
+    ):
+        self.set = set
+        self.id = id
+        self.description = description
+        self.storage = storage
+        self.purchase_date = purchase_date
+        self.purchase_location = purchase_location
+        self.purchase_price = purchase_price
+
+
+class BrickOrphanedPart(object):
+    id: str
+    part: str
+    color: int
+    quantity: int
+    spare: bool
+    missing: int
+    damaged: int
+    set_number: str | None
+
+    def __init__(
+        self,
+        id: str,
+        part: str,
+        color: int,
+        quantity: int,
+        spare: bool,
+        missing: int,
+        damaged: int,
+        set_number: str | None,
+        /
+    ):
+        self.id = id
+        self.part = part
+        self.color = color
+        self.quantity = quantity
+        self.spare = spare
+        self.missing = missing
+        self.damaged = damaged
+        self.set_number = set_number
+
+
+class BrickPartMissingSet(object):
+    id: str
+    part: str
+    color: int
+    quantity: int
+    spare: bool
+    missing: int
+    damaged: int
+
+    def __init__(
+        self,
+        id: str,
+        part: str,
+        color: int,
+        quantity: int,
+        spare: bool,
+        missing: int,
+        damaged: int,
+        /
+    ):
+        self.id = id
+        self.part = part
+        self.color = color
+        self.quantity = quantity
+        self.spare = spare
+        self.missing = missing
+        self.damaged = damaged
+
+
+class BrickIntegrityCheck(object):
+    def check_summary(self, /) -> list[BrickIntegrityIssue]:
+        sql = BrickSQL()
+        results = sql.fetchall('schema/integrity_check_summary')
+
+        issues: list[BrickIntegrityIssue] = []
+        for row in results:
+            issues.append(BrickIntegrityIssue(
+                row['issue_type'],
+                row['count'],
+                row['description']
+            ))
+
+        return issues
+
+    def get_orphaned_sets(self, /) -> list[BrickOrphanedSet]:
+        sql = BrickSQL()
+        results = sql.fetchall('schema/integrity_orphaned_sets')
+
+        sets: list[BrickOrphanedSet] = []
+        for row in results:
+            sets.append(BrickOrphanedSet(
+                row['set'],
+                row['id'],
+                row['description'],
+                row['storage'],
+                row['purchase_date'],
+                row['purchase_location'],
+                row['purchase_price']
+            ))
+
+        return sets
+
+    def get_orphaned_parts(self, /) -> list[BrickOrphanedPart]:
+        sql = BrickSQL()
+        results = sql.fetchall('schema/integrity_orphaned_parts')
+
+        parts: list[BrickOrphanedPart] = []
+        for row in results:
+            parts.append(BrickOrphanedPart(
+                row['id'],
+                row['part'],
+                row['color'],
+                row['quantity'],
+                row['spare'],
+                row['missing'],
+                row['damaged'],
+                row['set_number']
+            ))
+
+        return parts
+
+    def get_parts_missing_set(self, /) -> list[BrickPartMissingSet]:
+        sql = BrickSQL()
+        results = sql.fetchall('schema/integrity_parts_missing_set')
+
+        parts: list[BrickPartMissingSet] = []
+        for row in results:
+            parts.append(BrickPartMissingSet(
+                row['id'],
+                row['part'],
+                row['color'],
+                row['quantity'],
+                row['spare'],
+                row['missing'],
+                row['damaged']
+            ))
+
+        return parts
+
+    def cleanup_orphaned_sets(self, /) -> int:
+        sql = BrickSQL()
+        orphaned = self.get_orphaned_sets()
+        count = len(orphaned)
+
+        if count > 0:
+            sql.executescript('schema/integrity_delete_parts_for_orphaned_sets')
+            sql.executescript('schema/integrity_delete_minifigures_for_orphaned_sets')
+            sql.executescript('schema/integrity_delete_tags_for_orphaned_sets')
+            sql.executescript('schema/integrity_delete_owners_for_orphaned_sets')
+            sql.executescript('schema/integrity_delete_statuses_for_orphaned_sets')
+            sql.executescript('schema/integrity_delete_orphaned_sets')
+            sql.commit()
+            logger.info(f'Deleted {count} orphaned set(s)')
+
+        return count
+
+    def cleanup_orphaned_parts(self, /) -> int:
+        sql = BrickSQL()
+        orphaned = self.get_orphaned_parts()
+        count = len(orphaned)
+
+        if count > 0:
+            sql.executescript('schema/integrity_delete_orphaned_parts')
+            sql.commit()
+            logger.info(f'Deleted {count} orphaned part(s)')
+
+        return count
+
+    def cleanup_parts_missing_set(self, /) -> int:
+        sql = BrickSQL()
+        orphaned = self.get_parts_missing_set()
+        count = len(orphaned)
+
+        if count > 0:
+            sql.executescript('schema/integrity_delete_parts_missing_set')
+            sql.commit()
+            logger.info(f'Deleted {count} part(s) with missing set references')
+
+        return count
+
+    def cleanup_all(self, /) -> dict[str, int]:
+        orphaned_parts = self.cleanup_orphaned_parts()
+        parts_missing_set = self.cleanup_parts_missing_set()
+        orphaned_sets = self.cleanup_orphaned_sets()
+
+        counts = {
+            'orphaned_parts': orphaned_parts,
+            'parts_missing_set': parts_missing_set,
+            'orphaned_sets': orphaned_sets
+        }
+
+        total = sum(counts.values())
+        logger.info(f'Integrity cleanup complete: {total} total records removed')
+
+        return counts
+
+    def optimize_database(self, /) -> None:
+        sql = BrickSQL()
+        sql.executescript('schema/optimize')
+        sql.commit()
+
+        sql.connection.execute('VACUUM')
+        logger.info('Database optimization complete')

bricktracker/version.py

@@ -1,4 +1,4 @@
 from typing import Final
 
-__version__: Final[str] = '1.3.0'
+__version__: Final[str] = '1.3.1'
 __database_version__: Final[int] = 20

bricktracker/views/admin/database.py

@@ -17,6 +17,7 @@ from werkzeug.wrappers.response import Response
 
 from ..exceptions import exception_handler
 from ...reload import reload
+from ...sql_integrity import BrickIntegrityCheck
 from ...sql_migration_list import BrickSQLMigrationList
 from ...sql import BrickSQL
 from ..upload import upload_helper
@@ -184,3 +185,57 @@ def upgrade() -> str | Response:
         ),
         database_error=request.args.get('database_error')
     )
+
+
+@admin_database_page.route('/integrity/check', methods=['GET'])
+@login_required
+@exception_handler(__file__)
+def integrity_check() -> str:
+    integrity = BrickIntegrityCheck()
+    issues = integrity.check_summary()
+    orphaned_sets = integrity.get_orphaned_sets()
+    orphaned_parts = integrity.get_orphaned_parts()
+    parts_missing_set = integrity.get_parts_missing_set()
+    total_issues = sum(issue.count for issue in issues)
+
+    return render_template(
+        'admin.html',
+        integrity_check=True,
+        integrity_issues=issues,
+        orphaned_sets=orphaned_sets,
+        orphaned_parts=orphaned_parts,
+        parts_missing_set=parts_missing_set,
+        total_issues=total_issues,
+        database_error=request.args.get('database_error')
+    )
+
+
+@admin_database_page.route('/integrity/cleanup', methods=['POST'])
+@login_required
+@exception_handler(
+    __file__,
+    post_redirect='admin_database.integrity_check',
+    error_name='database_error'
+)
+def integrity_cleanup() -> Response:
+    integrity = BrickIntegrityCheck()
+    counts = integrity.cleanup_all()
+    total = sum(counts.values())
+    logger.info(f'Database integrity cleanup: removed {total} orphaned records')
+
+    return redirect(url_for('admin.admin', cleanup_success=total))
+
+
+@admin_database_page.route('/optimize', methods=['POST'])
+@login_required
+@exception_handler(
+    __file__,
+    post_redirect='admin.admin',
+    error_name='database_error'
+)
+def optimize() -> Response:
+    integrity = BrickIntegrityCheck()
+    integrity.optimize_database()
+    logger.info('Database optimization complete')
+
+    return redirect(url_for('admin.admin', optimize_success=1))

bricktracker/views/data.py

@@ -49,7 +49,7 @@ def serve_data_file(folder: str, filename: str):
     # Check if file exists
     file_path = os.path.join(folder_path, safe_filename)
     if not os.path.isfile(file_path):
-        logger.debug(f"File not found: {file_path}")
+        logger.warning(f"File not found: {file_path} (configured folder: {folder_path})")
         abort(404)
 
     # Verify the resolved path is still within the allowed folder (security check)

bricktracker/views/minifigure.py

@@ -42,10 +42,7 @@ def list() -> str:
         pagination_context = build_pagination_context(page, per_page, total_count, is_mobile)
     else:
         # ORIGINAL MODE - Single page with all data for client-side search
-        if owner_id == 'all' or owner_id is None or owner_id == '':
-            minifigures = BrickMinifigureList().all_filtered(problems_filter=problems_filter, theme_id=theme_id, year=year)
-        else:
-            minifigures = BrickMinifigureList().all_by_owner_filtered(owner_id=owner_id, problems_filter=problems_filter, theme_id=theme_id, year=year)
+        minifigures = BrickMinifigureList().all_filtered(owner_id=owner_id, problems_filter=problems_filter, theme_id=theme_id, year=year)
 
         pagination_context = None
 

static/scripts/minifigures.js

@@ -53,15 +53,15 @@ function applyFilters() {
     }
   }
 
-  // Only reset to page 1 when filtering in server-side pagination mode
+  // Reset to page 1 when filtering in server-side pagination mode
   if (isPaginationMode()) {
     currentUrl.searchParams.set('page', '1');
-    // Navigate to updated URL (server-side pagination)
-    window.location.href = currentUrl.toString();
-  } else {
-    // Client-side mode: Update URL without page reload
-    window.history.replaceState({}, '', currentUrl.toString());
   }
+
+  // Navigate to updated URL (reload page with new filters)
+  // This works for both pagination and client-side modes
+  // because backend applies filters even in client-side mode
+  window.location.href = currentUrl.toString();
 }
 
 // Legacy function for compatibility

templates/admin.html

@@ -30,6 +30,8 @@
             {% include 'admin/database/drop.html' %}
             {% elif import_database %}
             {% include 'admin/database/import.html' %}
+            {% elif integrity_check %}
+            {% include 'admin/database/integrity_check.html' %}
             {% elif upgrade_database %}
             {% include 'admin/database/upgrade.html' %}
             {% elif refresh_set %}

templates/admin/database.html

@@ -33,11 +33,72 @@
     </ul>
   </div>
 {% endif %}
+
+<h5 class="border-bottom mt-4">Maintenance</h5>
+{% if request.args.get('optimize_success') %}
+<div class="alert alert-success alert-dismissible fade show" role="alert">
+  <i class="ri-checkbox-circle-line"></i> <strong>Success!</strong> Database optimization complete. Indexes rebuilt and statistics updated.
+  <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
+</div>
+{% endif %}
+{% if request.args.get('cleanup_success') %}
+<div class="alert alert-success alert-dismissible fade show" role="alert">
+  <i class="ri-checkbox-circle-line"></i> <strong>Success!</strong> Removed {{ request.args.get('cleanup_success') }} orphaned record(s) from database.
+  <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
+</div>
+{% endif %}
+<p class="text-muted small">Maintain and optimize your database for best performance.</p>
+<div class="mb-3">
+  <a href="{{ url_for('admin_database.integrity_check') }}" class="btn btn-info" role="button">
+    <i class="ri-shield-check-line"></i> Check Database Integrity
+  </a>
+  <span class="text-muted small">Scan for orphaned records and foreign key violations</span>
+</div>
+<div class="mb-3">
+  <button type="button" class="btn btn-success" data-bs-toggle="modal" data-bs-target="#optimizeConfirmModal">
+    <i class="ri-speed-up-line"></i> Optimize Database
+  </button>
+  <span class="text-muted small">Re-create indexes and rebuild statistics (safe to run anytime)</span>
+</div>
+
+<h5 class="border-bottom mt-4">Danger zone</h5>
+{% if database_error %}<div class="alert alert-danger text-start" role="alert"><strong>Error:</strong> {{ database_error }}.</div>{% endif %}
+<div class="text-end">
+  <a href="{{ url_for('admin_database.upload') }}" class="btn btn-warning" role="button"><i class="ri-upload-line"></i> Import a database file</a>
+  <a href="{{ url_for('admin_database.drop') }}" class="btn btn-danger" role="button"><i class="ri-close-line"></i> Drop the database</a>
+  <a href="{{ url_for('admin_database.delete') }}" class="btn btn-danger" role="button"><i class="ri-delete-bin-2-line"></i> Delete the database file</a>
+</div>
 {{ accordion.footer() }}
 
-{{ accordion.header('Database danger zone', 'database-danger', 'admin', danger=true, class='text-end') }}
-{% if database_error %}<div class="alert alert-danger text-start" role="alert"><strong>Error:</strong> {{ database_error }}.</div>{% endif %}
-<a href="{{ url_for('admin_database.upload') }}" class="btn btn-warning" role="button"><i class="ri-upload-line"></i> Import a database file</a>
-<a href="{{ url_for('admin_database.drop') }}" class="btn btn-danger" role="button"><i class="ri-close-line"></i> Drop the database</a>
-<a href="{{ url_for('admin_database.delete') }}" class="btn btn-danger" role="button"><i class="ri-delete-bin-2-line"></i> Delete the database file</a>
-{{ accordion.footer() }}
+<div class="modal fade" id="optimizeConfirmModal" tabindex="-1" aria-labelledby="optimizeConfirmModalLabel" aria-hidden="true">
+  <div class="modal-dialog modal-dialog-centered">
+    <div class="modal-content">
+      <div class="modal-header bg-success text-white">
+        <h5 class="modal-title" id="optimizeConfirmModalLabel">
+          <i class="ri-speed-up-line"></i> Confirm Database Optimization
+        </h5>
+        <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal" aria-label="Close"></button>
+      </div>
+      <div class="modal-body">
+        <p>Run this to improve speed and reclaim storage space after making changes to your collection.</p>
+        <p><strong>What happens:</strong></p>
+        <ul>
+          <li>Speeds up searches and filters</li>
+          <li>Improves page load times</li>
+          <li>Reduces file size by removing unused space</li>
+        </ul>
+        <p class="text-muted small"><i class="ri-information-line"></i> Safe to run anytime. Takes a few seconds to complete.</p>
+      </div>
+      <div class="modal-footer">
+        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">
+          <i class="ri-close-line"></i> Cancel
+        </button>
+        <form action="{{ url_for('admin_database.optimize') }}" method="post" style="display: inline;">
+          <button type="submit" class="btn btn-success">
+            <i class="ri-speed-up-line"></i> Yes, Optimize Now
+          </button>
+        </form>
+      </div>
+    </div>
+  </div>
+</div>

templates/admin/database/integrity_check.html

@@ -0,0 +1,180 @@
+{% import 'macro/accordion.html' as accordion %}
+
+{{ accordion.header('Database Integrity Check', 'database-integrity', 'admin', expanded=true, icon='shield-check-line') }}
+
+{% if database_error %}
+<div class="alert alert-danger text-start" role="alert"><strong>Error:</strong> {{ database_error }}.</div>
+{% endif %}
+
+<h5 class="border-bottom">Scan Results</h5>
+
+{% if total_issues == 0 %}
+<div class="alert alert-success" role="alert">
+  <i class="ri-checkbox-circle-line"></i> <strong>No integrity issues found!</strong> Your database is healthy.
+</div>
+<div class="text-end">
+  <a class="btn btn-primary" href="{{ url_for('admin.admin') }}" role="button"><i class="ri-arrow-left-long-line"></i> Back to admin</a>
+</div>
+{% else %}
+<div class="alert alert-warning" role="alert">
+  <i class="ri-alert-line"></i> <strong>Found {{ total_issues }} integrity issue(s)</strong> that need attention.
+</div>
+
+<h6 class="mt-3">Summary</h6>
+<table class="table table-sm">
+  <thead>
+    <tr>
+      <th>Issue Type</th>
+      <th>Count</th>
+      <th>Description</th>
+    </tr>
+  </thead>
+  <tbody>
+    {% for issue in integrity_issues %}
+    <tr class="{% if issue.count > 0 %}table-warning{% endif %}">
+      <td><code>{{ issue.issue_type }}</code></td>
+      <td><span class="badge {% if issue.count > 0 %}text-bg-warning{% else %}text-bg-success{% endif %}">{{ issue.count }}</span></td>
+      <td>{{ issue.description }}</td>
+    </tr>
+    {% endfor %}
+  </tbody>
+</table>
+
+{% if orphaned_sets|length > 0 %}
+<h6 class="mt-4">Orphaned Sets ({{ orphaned_sets|length }})</h6>
+<p class="text-muted small">These sets exist in bricktracker_sets but are missing from rebrickable_sets:</p>
+<table class="table table-sm table-striped">
+  <thead>
+    <tr>
+      <th>Set Number</th>
+      <th>ID</th>
+      <th>Storage</th>
+      <th>Purchase Price</th>
+    </tr>
+  </thead>
+  <tbody>
+    {% for set in orphaned_sets %}
+    <tr>
+      <td><code>{{ set.set }}</code></td>
+      <td><code class="small">{{ set.id }}</code></td>
+      <td>{{ set.storage or '-' }}</td>
+      <td>{{ set.purchase_price or '-' }}</td>
+    </tr>
+    {% endfor %}
+  </tbody>
+</table>
+{% endif %}
+
+{% if orphaned_parts|length > 0 %}
+<h6 class="mt-4">Orphaned Parts ({{ orphaned_parts|length }})</h6>
+<p class="text-muted small">These parts exist in bricktracker_parts but are missing from rebrickable_parts:</p>
+<table class="table table-sm table-striped">
+  <thead>
+    <tr>
+      <th>Part</th>
+      <th>Color</th>
+      <th>Set Number</th>
+      <th>Quantity</th>
+      <th>Spare</th>
+    </tr>
+  </thead>
+  <tbody>
+    {% for part in orphaned_parts[:20] %}
+    <tr>
+      <td><code>{{ part.part }}</code></td>
+      <td>{{ part.color }}</td>
+      <td>{{ part.set_number or '-' }}</td>
+      <td>{{ part.quantity }}</td>
+      <td>{{ 'Yes' if part.spare else 'No' }}</td>
+    </tr>
+    {% endfor %}
+    {% if orphaned_parts|length > 20 %}
+    <tr>
+      <td colspan="5" class="text-muted text-center"><em>... and {{ orphaned_parts|length - 20 }} more</em></td>
+    </tr>
+    {% endif %}
+  </tbody>
+</table>
+{% endif %}
+
+{% if parts_missing_set|length > 0 %}
+<h6 class="mt-4">Parts with Missing Set References ({{ parts_missing_set|length }})</h6>
+<p class="text-muted small">These parts reference sets that don't exist in bricktracker_sets:</p>
+<table class="table table-sm table-striped">
+  <thead>
+    <tr>
+      <th>Part</th>
+      <th>Color</th>
+      <th>Set ID</th>
+      <th>Quantity</th>
+    </tr>
+  </thead>
+  <tbody>
+    {% for part in parts_missing_set[:20] %}
+    <tr>
+      <td><code>{{ part.part }}</code></td>
+      <td>{{ part.color }}</td>
+      <td><code class="small">{{ part.id }}</code></td>
+      <td>{{ part.quantity }}</td>
+    </tr>
+    {% endfor %}
+    {% if parts_missing_set|length > 20 %}
+    <tr>
+      <td colspan="4" class="text-muted text-center"><em>... and {{ parts_missing_set|length - 20 }} more</em></td>
+    </tr>
+    {% endif %}
+  </tbody>
+</table>
+{% endif %}
+
+<div class="mt-4 text-end">
+  <a class="btn btn-secondary" href="{{ url_for('admin.admin') }}" role="button"><i class="ri-arrow-left-long-line"></i> Back to admin</a>
+  <button type="button" class="btn btn-danger" data-bs-toggle="modal" data-bs-target="#cleanupConfirmModal">
+    <i class="ri-delete-bin-line"></i> Clean Up Orphaned Records
+  </button>
+</div>
+{% endif %}
+
+{{ accordion.footer() }}
+
+<!-- Cleanup Confirmation Modal -->
+<div class="modal fade" id="cleanupConfirmModal" tabindex="-1" aria-labelledby="cleanupConfirmModalLabel" aria-hidden="true">
+  <div class="modal-dialog modal-dialog-centered">
+    <div class="modal-content">
+      <div class="modal-header bg-danger text-white">
+        <h5 class="modal-title" id="cleanupConfirmModalLabel">
+          <i class="ri-alert-line"></i> Confirm Database Cleanup
+        </h5>
+        <button type="button" class="btn-close btn-close-white" data-bs-dismiss="modal" aria-label="Close"></button>
+      </div>
+      <div class="modal-body">
+        <div class="alert alert-warning" role="alert">
+          <i class="ri-error-warning-line"></i> <strong>Warning!</strong> This action cannot be undone.
+        </div>
+        <p><strong>This will permanently delete:</strong></p>
+        <ul>
+          {% if orphaned_sets|length > 0 %}
+          <li><strong>{{ orphaned_sets|length }}</strong> orphaned set record(s)</li>
+          {% endif %}
+          {% if orphaned_parts|length > 0 %}
+          <li><strong>{{ orphaned_parts|length }}</strong> orphaned part record(s)</li>
+          {% endif %}
+          {% if parts_missing_set|length > 0 %}
+          <li><strong>{{ parts_missing_set|length }}</strong> part(s) with missing set references</li>
+          {% endif %}
+        </ul>
+        <p class="text-muted small"><i class="ri-information-line"></i> <strong>Recommendation:</strong> Download a backup of your database before proceeding.</p>
+      </div>
+      <div class="modal-footer">
+        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">
+          <i class="ri-close-line"></i> Cancel
+        </button>
+        <form action="{{ url_for('admin_database.integrity_cleanup') }}" method="post" style="display: inline;">
+          <button type="submit" class="btn btn-danger">
+            <i class="ri-delete-bin-line"></i> Yes, Clean Up Now
+          </button>
+        </form>
+      </div>
+    </div>
+  </div>
+</div>