db: FK constraints silently unenforced on raw sqlite3 connections

[eneskemalergin]

Summary

PRAGMA foreign_keys = ON is only active per-connection. get_or_create_db() sets it correctly, but any caller that opens a raw sqlite3.connect() without going through get_or_create_db will silently accept FK violations — e.g. inserting into study_files for a non-existent accession.

Reproduction

import sqlite3
from pxaudit.db import create_tables, insert_study_files
import pandas as pd

conn = sqlite3.connect(":memory:", isolation_level=None)   # no PRAGMA!
create_tables(conn)
df = pd.DataFrame([{"accession": "MISSING", "file_name": "f.raw",
                    "file_category": "RAW", "file_extension": ".raw",
                    "ftp_location": None, "file_size": None}])
insert_study_files(conn, "MISSING", df)   # no error raised

Root cause

sqlite3 ships with FK enforcement disabled by default for backwards-compatibility.
The PRAGMA must be re-issued on every new connection; it is not persisted in the database file.

Options considered

1. Move PRAGMA foreign_keys = ON into create_tables(): broadens the fix but mutates connection state in a function whose name implies only DDL.
2. Add a helper _configure_conn(conn)called by both get_or_create_db and the test fixture conn -> cleanest separation.
3. Leave as-is, document in docstring: accepted for Phase 1 since all production paths go through get_or_create_db and the test conn fixture sets it manually.

Decision for Phase 1

Left by design. All production code paths go through get_or_create_db. The test fixture explicitly calls PRAGMA foreign_keys = ON. No silent failures in any current call site.

Recommended follow-up (Phase 2)

Introduce _configure_conn(conn) called from both get_or_create_db and create_tables, and add a specific test that raw insert_study_files without the PRAGMA produces the expected FK error, confirming the fix holds.