The cameras watch and the recogniser names, but the system’s actual product is memory, who passed, when, which door, with the image to prove it, searchable months later. This post is the logging layer, SQLite as the app’s single source of history, the schema, the queries behind the interface’s search and filters, and the CSV export that lets the data leave politely.
The database module owns one file and two tables, and the schema is the whole design:
self.cursor.execute('''
CREATE TABLE IF NOT EXISTS logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
camera TEXT NOT NULL,
timestamp TEXT NOT NULL,
image_path TEXT
)
''')
self.cursor.execute('''
CREATE TABLE IF NOT EXISTS known_faces (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL,
image_path TEXT NOT NULL,
added_at TEXT NOT NULL
)
''')
self.conn.commit()
Familiar disciplines from the shop software return with new reasons. Every write commits immediately, a logger that loses entries on a crash has failed its only job. The image_path column stores where the captured frame lives on disk rather than the image bytes themselves, files belong in folders and paths belong in databases, keeping the database small and the images ordinary files a human can browse. And known_faces makes the admin tab real, adding a person writes their name and photo here and refreshes the recogniser’s encodings, the database is the roster, the folder is the evidence.
The interface’s search and filters are parameterised queries over the logs, name matching and date ranges, the same LIKE-plus-placeholders pattern every project since the shop software has used:
def search_logs(self, name='', date_from='', date_to=''):
q = 'SELECT name, camera, timestamp, image_path FROM logs WHERE 1=1'
params = []
if name:
q += ' AND name LIKE ?'
params.append('%' + name + '%')
if date_from:
q += ' AND timestamp >= ?'
params.append(date_from)
if date_to:
q += ' AND timestamp <= ?'
params.append(date_to + ' 23:59:59')
q += ' ORDER BY timestamp DESC'
return self.cursor.execute(q, params).fetchall()
The WHERE 1=1 idiom lets each filter append cleanly, and every user value travels as a placeholder, never glued into the string. Export is the last honest feature, attendance data ultimately lives in someone’s spreadsheet, and Python’s csv module turns any search result into a file, the current filters included, so export what I am looking at is one button. One privacy note belongs in the same breath as the feature list, this database is a record of people’s movements, local by design, and worth treating with the respect that sentence implies, the retention broom that clears old rows and images is a one-liner, and running it is an operator’s duty, not an afterthought.
A few things people ask me about this
Should captured images go in the database as blobs? Paths in the database, files on disk. The database stays small and fast, the images stay browsable, and backups can treat the two by different policies.
How do I let users combine optional filters cleanly? Start the query with WHERE 1=1 and append AND clauses per active filter, each with a placeholder. The SQL composes cleanly and the inputs stay parameterised.
Next
That completes the system, eyes, judgment, and memory. The finale weighs the whole build, and what a project at the edge of my ability taught me.