DuckDB APFS Extension

Query your macOS file system with SQL. This DuckDB extension provides Windows Everything-like file search and analysis capabilities for macOS, powered by Spotlight index and POSIX fts(3) directory traversal.

Platform: macOS only (requires APFS/HFS+ and Spotlight service)

Functions

apfs_scan(path [, mode] [, since=TIMESTAMP])

Recursively scan a directory, returning file metadata as a table (11 columns).

Parameter	Type	Default	Description
path	VARCHAR	(required)	Directory to scan
mode	VARCHAR	'fts'	'fts' (POSIX traversal, precise) or 'spotlight' (macOS index, fast)
since	TIMESTAMP	(optional)	Incremental scan — only files modified after this timestamp (spotlight mode only)

apfs_search(keyword)

Instant file name search across the entire disk via Spotlight index.

Parameter	Type	Description
keyword	VARCHAR	File name search keyword (case-insensitive, supports wildcards)

apfs_has_changes(path, since)

Scalar function that checks whether any file in a directory has been modified since a given timestamp.

Parameter	Type	Description
path	VARCHAR	Directory path
since	TIMESTAMP	Check for changes after this timestamp
Returns	BOOLEAN	true if changes exist, false otherwise

Return Columns

All three functions share the same 11-column schema:

Column	Type	Description
path	VARCHAR	Full file path
name	VARCHAR	File name
extension	VARCHAR	File extension (without .)
size	BIGINT	File size in bytes
file_type	VARCHAR	file / directory / symlink / other
modified_at	TIMESTAMP	Last modified time
created_at	TIMESTAMP	Creation time
accessed_at	TIMESTAMP	Last accessed time
permissions	VARCHAR	Permission string (e.g., rwxr-xr-x)
owner	VARCHAR	Owner username
depth	INTEGER	Depth relative to scan root

Quick Start

-- Instant search (Spotlight, milliseconds)
SELECT * FROM apfs_search('report') LIMIT 20;

-- Scan a directory
SELECT * FROM apfs_scan('/Users/max/Documents', 'fts');

-- Full disk scan via Spotlight (seconds)
CREATE TABLE files AS SELECT * FROM apfs_scan('/', 'spotlight');

-- Top 20 extensions by disk usage
SELECT extension, COUNT(*) AS cnt,
       SUM(size) / 1024 / 1024 / 1024 AS gb
FROM files
WHERE file_type = 'file'
GROUP BY extension
ORDER BY gb DESC
LIMIT 20;

-- Find large video files (> 1 GB)
SELECT name, path, size / 1024 / 1024 / 1024 AS gb
FROM files
WHERE extension IN ('mp4', 'mkv', 'avi', 'mov')
  AND size > 1024 * 1024 * 1024
ORDER BY size DESC;

-- Find potential duplicate files
SELECT name, size, COUNT(*) AS copies
FROM files
WHERE file_type = 'file'
GROUP BY name, size
HAVING copies > 1
ORDER BY size DESC;

-- Incremental scan: only recently modified files
SELECT * FROM apfs_scan('/', 'spotlight',
    since=TIMESTAMP '2026-04-01 00:00:00');

-- Check if a directory has changed since last scan
SELECT apfs_has_changes('/Users/max/Documents',
    TIMESTAMP '2026-04-01 00:00:00');

Scan Modes

Mode	Engine	Speed	Coverage	Permission
fts	POSIX fts(3)	30-120s (full disk)	All accessible files	Full Disk Access needed for protected dirs
spotlight	macOS MDQuery	1-3s (full disk)	Indexed files only	No extra permission needed

Recommendation: Use spotlight for fast full-disk scans. Use fts when you need 100% coverage or when scanning non-indexed directories like /tmp.

Building

Prerequisites

• macOS 10.15+
• Xcode Command Line Tools (xcode-select --install)
• CMake 3.5+

Build from Source

git clone --recurse-submodules https://github.com/<your-username>/duckdb-apfs.git
cd duckdb-apfs

# Release build (recommended for actual use)
make

# Debug build (for development)
make debug

# Faster build with Ninja
GEN=ninja make

Build Output

Build	DuckDB CLI	Loadable Extension
Release	build/release/duckdb	build/release/extension/apfs/apfs.duckdb_extension
Debug	build/debug/duckdb	build/debug/extension/apfs/apfs.duckdb_extension

The CLI binary has the extension statically linked — just run ./build/release/duckdb and start querying.

Running Tests

# Run all apfs tests (8 test files, 74 assertions)
make test

# Or run directly
build/debug/test/unittest "test/sql/apfs/*"

# Run a single test
build/debug/test/unittest "test/sql/apfs/test_apfs_scan_fts.test"

Loading as Dynamic Extension

If you have a separate DuckDB installation:

SET allow_unsigned_extensions = true;
LOAD '/path/to/apfs.duckdb_extension';

Note: The extension version must match the DuckDB version exactly.

Project Structure

duckdb-apfs/
├── src/                          # C++ extension source
│   ├── apfs_extension.cpp        # Entry point, function registration
│   ├── apfs_fts_scanner.cpp      # POSIX fts(3) traversal engine
│   ├── apfs_spotlight_scanner.cpp # Spotlight MDQuery engine + apfs_search
│   ├── apfs_has_changes.cpp      # Change detection scalar function
│   ├── apfs_utils.cpp            # Utilities (UTF-8, permissions, owner)
│   └── include/                  # Header files
├── test/sql/apfs/                # 8 sqllogictest test files
├── docs/                         # Design doc, build guide
├── duckdb/                       # DuckDB source (git submodule)
├── extension-ci-tools/           # CI tools (git submodule)
├── CMakeLists.txt
├── Makefile
└── extension_config.cmake

macOS Permissions

Function	Permission Required
apfs_search()	None
apfs_scan(path, 'spotlight')	None
apfs_scan(path, 'fts')	Full Disk Access (for protected directories)
apfs_has_changes()	None

To grant Full Disk Access: System Settings → Privacy & Security → Full Disk Access → add your terminal app.

License

MIT