Build a fully-searchable SQLite database from your Apple Photos library by combining Apple's computed metadata, EXIF data, and AI descriptions generated by a local vision model. Everything runs on your machine — no cloud, no API keys, no subscription.
Apple Photos search is good, but locked in. You can't query it programmatically, export the intelligence it has built up, or combine it with your own metadata. With a large library spanning decades, finding specific memories often requires remembering exactly when or where something happened.
This project builds a fully searchable database from your Apple Photos library by combining three data sources: what Apple has already computed, raw EXIF metadata from the image files, and AI-generated descriptions from a local vision model running entirely on your own hardware.
A SQLite database covering your entire photo library that you can query like this:
-- Find all beach photos with a specific person from 2019
SELECT original_filename, date_created, gemma_description
FROM photos
WHERE named_people LIKE '%Alex%'
AND apple_scene_top = 'beach'
AND year = 2019;
-- Full-text search across all AI descriptions
SELECT * FROM photos_fts WHERE photos_fts MATCH 'stone bridge old city';
-- Photos taken in Italy based on GPS
SELECT * FROM photos
WHERE latitude BETWEEN 36.6 AND 47.1
AND longitude BETWEEN 6.6 AND 18.5;
-- Find all photos where text is visible (signs, menus, documents)
SELECT original_filename, vision_text_content
FROM photos
WHERE vision_text_detected = 1;Plus a Flask-based web UI (src/photo_web.py) for browsing and search via the database.
Everything runs locally. No photos leave your machine. No cloud API. No subscription.
Most people don't realize how much AI work Apple has already done on their library. By reading the Photos database directly (read-only, via a temp copy) you can extract:
- GPS coordinates for many photos
- Named people from face recognition you've already trained in Photos
- Scene classifications — beach, mountain, city, food, outdoor, portrait, etc.
- Apple AI descriptions — already written for a subset of photos
- Photo quality scores — aesthetic, curation, and iconic scores Apple computes internally
- Album membership
- Timestamps and timezone data
- Camera metadata
This data is sitting in Photos Library.photoslibrary/database/Photos.sqlite and has never required any processing on your part — Apple's Neural Engine built it silently over time.
Raw metadata embedded in each image file: camera, lens, aperture, shutter speed, ISO, flash, GPS, and original capture timestamp.
A vision-capable model running entirely on your own hardware. For each photo it generates:
- Natural language description — 2-3 sentences describing what's in the photo
- Scene type and setting
- Tags — a structured list of descriptive keywords
- Location guess — inferred from visual clues like landmarks, signage, and architecture
- Time of day and weather
- People count and activity
- Any visible text (OCR)
Gemma 4 (e4b variant, ~9.6GB) runs well on Apple Silicon unified memory via Ollama.
| Component | Tool |
|---|---|
| Platform | Mac with Apple Silicon |
| Photos DB reader | Python + sqlite3 |
| EXIF extraction | exiftool |
| Additional vision | Apple Vision.framework (pyobjc) |
| Local AI model | Ollama + Gemma 4 |
| Output database | SQLite with FTS5 |
| Web UI | Flask |
| Query CLI | src/query_photos.py |
Everything is open source or built into macOS.
Run scripts/explore_photos_db.py to inspect what Apple has already computed for your specific library. Schema varies by macOS version.
src/phase1_build_db.py reads all assets from Photos.sqlite, resolves file paths, extracts EXIF data, pulls scene labels and named people, and writes everything to a SQLite database with a full-text search index. Runs as a background job overnight.
At completion you already have a fully searchable database — by person, date, GPS, scene type, camera, and album — with no AI processing required.
src/phase2_gemma.py sends each photo to Gemma 4 running locally via Ollama, receives structured JSON descriptions, and writes them back to the database. At ~13-14 seconds per photo on an M2 Mac Mini, a large library takes weeks. The practical approach:
- Favorites first — highest value, smallest set
- No-GPS photos — where AI location guessing adds the most value
- Everything else — long-running background job over time
You can search photos that have been processed while the job continues in the background.
Rather than running Phase 2 manually, use macOS launchd to run enrichment automatically. See launchd/README.md. The job is fully resumable — if the machine reboots or the job is interrupted, it picks up exactly where it left off.
Monitor progress any time:
# How many photos have been described so far
sqlite3 ~/photos_meta.db "SELECT COUNT(*) FROM photos WHERE gemma_processed=1;"
# Watch the log
tail -f phase2.log- Mac with Apple Silicon (M2 or better recommended; M1 works but slower)
- Apple Photos library stored locally (not optimized/iCloud-only)
- macOS — required for Apple Vision framework
- Ollama 0.20.5 — see docs/GOTCHAS.md; newer versions broke Gemma MoE models in testing
- Python 3.10+
- exiftool —
brew install exiftool - Disk space — temp space during Phase 1 (can be many GB; route to the library's volume if external), plus a few GB for the final database
- Time — ~7 hours for Phase 1 on a large library, weeks for full Phase 2 (background)
git clone https://github.com/YOU/photo-intelligence.git
cd photo-intelligence
python3 -m venv venv && source venv/bin/activate
pip install -r requirements.txt
# Phase 0 — peek at your library
python3 scripts/explore_photos_db.py
# Phase 1 — build the metadata DB (edit paths in scripts/run_phase1.sh first)
./scripts/run_phase1.sh
# Phase 2 — start enrichment (edit scripts/run_phase2.sh first)
./scripts/run_phase2.sh
# Browse via the web UI
python3 src/photo_web.py --db ~/photos_meta.dbFor background processing via launchd, see launchd/README.md.
The web UI does not copy or duplicate any of your photos. Image data stays inside the Apple Photos library bundle for the entire lifetime of the system.
Apple Photos Library bundle (originals on disk)
│
▼
photos_meta.db stores the absolute path to each file
│
▼
Flask routes read from that path on demand
│
▼
Thumbnails generated and cached in RAM only
Phase 1 walks the library's originals/<first-char-UUID>/<UUID>.<ext> directory and stores the resolved absolute path in photos.file_path. Phase 2 reads bytes from that path to send to Ollama. The Flask app's /thumb/<id> route generates a JPEG thumbnail in memory (cached in a Python dict, not persisted), and /original/<id> calls send_file() directly on the stored path.
Implications:
- No duplicate storage. The DB stores paths, not image data. If you delete a photo from Apple Photos, the web UI will 404 on that ID until you rerun Phase 1.
- The thumbnail cache is in-memory only. It resets when the Flask process restarts. There is no eviction policy yet, so for very large libraries under heavy browsing the cache can grow without bound.
- The library must be mounted. If your library lives on an external SSD that disconnects or sleeps, expect 404s until it's back.
- Read-only by design. The UI never writes to the library and cannot corrupt it.
/original/<id> calls send_file() on whatever absolute path is in photos.file_path. Phase 1 only writes paths inside the library, so in normal operation this is safe. If you ever extend the schema or import paths from another source, add a sandbox check (Path(p).resolve().is_relative_to(library_root)) before serving — otherwise a malformed file_path row could read arbitrary files. The current code includes a FIXME comment at the relevant route.
Built and tested against a real library of ~191,000 photos spanning 1890–2026 across many countries and cameras.
| Metric | Result |
|---|---|
| Photos processed | ~191,000 |
| With GPS coordinates | ~62% |
| With Apple scene labels | ~70% |
| With named people | ~24% |
| With Apple AI description | ~2% |
| With camera model | ~67% |
| Phase 1 processing time | ~7 hours |
| Phase 2 speed (M2 Mac Mini) | ~13-14 sec/photo |
Photos.sqlite is undocumented. Apple does not publish the schema and changes it with macOS updates. The scripts probe for column names defensively and should survive minor updates, but a major macOS version bump may require adjustments.
Read-only access only. The scripts work from a temp copy of the database. Never write to Photos.sqlite directly — it will corrupt your library.
iCloud-shared photos won't have local files. Photos shared with you by others appear in Photos.sqlite but "Download Originals" doesn't apply to them. They get metadata but no AI descriptions.
AI accuracy varies. Scene classification and tagging are reliable. Location guessing works well for distinctive landmarks but is speculative for generic scenes. Named people come from Apple's face recognition, not Gemma — those are highly accurate since you've trained them yourself.
Running Gemma 4 locally via Ollama rather than a cloud API means:
- Privacy — your photos never leave your machine
- No cost — no API fees regardless of how many photos you process
- No rate limits — run as fast as your hardware allows
- Offline — no account or internet required
- Ownership — the model and all outputs are yours
The tradeoff is speed. Cloud APIs would be faster but at $0.001-0.003 per image, processing 130,000+ photos would cost $130-400. Running locally is free but takes longer.
photo-intelligence/
├── README.md
├── requirements.txt
├── src/
│ ├── phase1_build_db.py # Phase 1: metadata extraction
│ ├── phase2_gemma.py # Phase 2: AI enrichment
│ ├── photo_web.py # Flask web UI
│ └── query_photos.py # CLI query tool
├── scripts/
│ ├── explore_photos_db.py # Phase 0: schema inspection
│ ├── run_phase1.sh # Phase 1 wrapper
│ ├── run_phase2.sh # Phase 2 wrapper (also used by launchd)
│ └── clean_people_tags.py # Maintenance: clean up tag pollution
├── launchd/
│ ├── com.photointelligence.phase2.plist
│ └── README.md
└── docs/
├── INSTALL.md
├── GOTCHAS.md # Hard-won lessons; read this
├── CONFIG.md
└── SCHEMA.md
MIT — see LICENSE.
- Core Logic: Python / SQLite
- AI Vision: Ollama + Gemma
- Development Assistant: This project was developed in collaboration with Claude Code.