Database Reference¶

DFACS uses SQLite with WAL mode. The database is stored locally on the DFACS device.

Characteristics: - WAL mode for multi-reader concurrency - Foreign key constraints enabled - 30-second transaction timeout - Auto-repair of corrupted schemas on startup - Pessimistic write locking (RLock) - Access log auto-purge after ACCESS_LOG_RETENTION_DAYS (default: 90)

Core Tables¶

`access_logs`¶

Every scan event — granted and denied.

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`timestamp`	TEXT	ISO 8601 timestamp
`barcode`	TEXT	Scanned barcode value
`result`	TEXT	`Allowed` or `Denied`
`reason`	TEXT	Denial reason (null if allowed)
`first_name`	TEXT	Member first name (if PII enabled)
`last_name`	TEXT	Member last name (if PII enabled)
`membership_type`	TEXT	Membership type label
`api_acknowledged`	INTEGER	`1` = sent to API, `0` = queued, `NULL` = not applicable
`image_path`	TEXT	Local Alcatraz image path (if applicable)
`alcatraz_event_id`	TEXT	Linked Alcatraz event
`alcatraz_profile_id`	TEXT	Linked Alcatraz profile
`event_source`	TEXT	Source of the event (scanner, alcatraz, etc.)
`s3_image_key`	TEXT	S3 key for uploaded image

Auto-purges entries older than ACCESS_LOG_RETENTION_DAYS.

`denied_barcodes`¶

Blocklist of barcodes denied access.

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`barcode`	TEXT UNIQUE	Barcode value
`invalidated_at`	TEXT	Timestamp of denial
`status`	TEXT	Denial reason/status

`settings`¶

Key-value configuration store.

Column	Type	Description
`setting_key`	TEXT PK	Config key name
`setting_value`	TEXT	Config value

See Configuration Reference for all valid keys.

`checkin_queue`¶

Offline check-ins waiting to be sent to the API.

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`barcode`	TEXT	Barcode value
`timestamp`	TEXT	Original scan timestamp
`result`	TEXT	Access result
`first_name`	TEXT	Member first name
`last_name`	TEXT	Member last name
`membership_type`	TEXT	Membership type

Queue drains automatically when API connectivity is restored. Retry uses exponential backoff: 5s initial, 300s max.

`checkin_history`¶

Archive of successfully sent check-ins.

`schema_version`¶

Tracks database schema version for migration management.

User Management¶

`users`¶

Local admin accounts (separate from WAN users).

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`username`	TEXT UNIQUE	Login username
`password_hash`	TEXT	bcrypt hash
`role_id`	INTEGER FK → roles	Assigned role
`created_at`	TEXT	Creation timestamp
`last_login`	TEXT	Last login timestamp
`is_active`	INTEGER	`1` = active

`roles`¶

Permission roles for local dashboard access.

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`name`	TEXT UNIQUE	Role name (Superadmin, Administrator, Operator)
`permissions`	TEXT	JSON array of permission keys

Default roles created on first startup: Superadmin, Administrator, Operator.

`role_permissions`¶

Page-level access control.

Column	Type	Description
`role_id`	INTEGER FK → roles	Role
`permission`	TEXT	Permission key (e.g., `configuration`, `access_logs`)

`audit_log`¶

Admin action history.

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`timestamp`	TEXT	Action timestamp
`user`	TEXT	Username
`action`	TEXT	Action performed
`details`	TEXT	Action details/context

`wan_users`¶

Users synced from the DFACSWAN portal (cloud accounts).

Column	Type	Description
`firebase_uid`	TEXT UNIQUE	Firebase user ID
`email`	TEXT	User email
`first_name`	TEXT	First name
`last_name`	TEXT	Last name
`role_name`	TEXT	Role from portal
`permissions`	TEXT	JSON array of permission keys
`password_hash`	TEXT	Local password hash (form login fallback)
`totp_secret_encrypted`	TEXT	Encrypted TOTP secret
`is_active`	INTEGER	`1` = active
`is_internal`	INTEGER	`1` = DragonFly internal user
`last_synced_at`	TEXT	Last sync from portal
`last_activity_at`	TEXT	Last login/activity

Schedules¶

`access_schedules`¶

Member-type-specific time-based access rules.

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`name`	TEXT	Schedule name
`schedule_data`	TEXT	JSON grid (days × hours)
`membership_types`	TEXT	JSON list of applicable membership types
`is_active`	INTEGER	`1` = active
`created_at`	TEXT	Creation timestamp
`updated_at`	TEXT	Last modified timestamp

`door_schedules`¶

Door operation hours (when door is physically accessible).

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`name`	TEXT	Schedule name
`is_active`	INTEGER	`1` = active
`weekly_hours`	TEXT	JSON: `{"monday": [["06:00","22:00"]], ...}`
`created_at`	TEXT	Creation timestamp
`updated_at`	TEXT	Last modified timestamp
`synced_at`	TEXT	Last sync from portal

`holiday_schedules`¶

Override days (closures, modified hours).

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`name`	TEXT	Holiday name
`date`	TEXT	Date (YYYY-MM-DD)
`recurs_annually`	INTEGER	`1` = repeats every year
`override_type`	TEXT	`closed` or `modified`
`start_time`	TEXT	Modified start time (if not closed)
`end_time`	TEXT	Modified end time (if not closed)
`deny_access_control`	INTEGER	`1` = deny all access control
`created_at`	TEXT	Creation timestamp
`updated_at`	TEXT	Last modified timestamp
`synced_at`	TEXT	Last sync from portal

Vendor Barcode Tables¶

Each integration stores member data in its own table. Schema varies by vendor.

`antaris_barcodes`¶

Antaris member barcodes and status.

`paramount_barcodes`¶

Column	Description
`barcode`	Barcode value
`member_num`	Paramount member number
`club_id`	Club identifier
`status`	Membership status
`stoplight`	Paramount stoplight flag
`first_name`, `last_name`	Member name
`membership_type`	Membership type label
`allow_entry`	Boolean access flag

`zenoti_barcodes`¶

Zenoti guest/member records.

`glofox_users`¶

Column	Description
`user_id`	Glofox internal ID
`barcode`	Barcode value
`local_status`	Cached membership status

`fliip_members`¶

Fliip plan holder records.

`standalone_barcodes`¶

Local-only barcodes (standalone CSV mode).

Alcatraz / OSDP¶

`alcatraz_events`¶

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`event_id`	TEXT UNIQUE	Alcatraz platform event ID
`event_type`	TEXT	Event classification
`badge_number`	TEXT	Badge that triggered event
`image_path`	TEXT	Local image file path
`alcatraz_profile_id`	TEXT	Associated profile
`timestamp`	TEXT	Event timestamp
`access_log_id`	INTEGER FK → access_logs	Linked scan record
`processed`	INTEGER	`1` = enrichment complete

`alcatraz_profiles`¶

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`profile_id`	TEXT UNIQUE	Alcatraz platform profile ID
`status`	TEXT	Profile status
`badge_count`	INTEGER	Number of linked badges
`badge_numbers`	TEXT	JSON array of badge numbers
`created_at`, `updated_at`, `last_synced_at`	TEXT	Timestamps

`alcatraz_badge_associations`¶

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`profile_id`	TEXT	Alcatraz profile
`card_id`	TEXT	Card identifier
`badge_number`	TEXT	Badge number
`card_format`	TEXT	Card encoding format
`locked`	INTEGER	`1` = locked/inactive
`last_activity`	TEXT	Last scan timestamp
`member_name`	TEXT	Associated member name

`alcatraz_image_storage`¶

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`file_path`	TEXT	Local file path
`file_size`	INTEGER	File size in bytes
`created_at`	TEXT	Capture timestamp
`access_log_id`	INTEGER FK → access_logs	Linked event
`synced_to_wan`	INTEGER	`1` = uploaded to portal
`s3_image_key`	TEXT	S3 object key

`alcatraz_event_queue`¶

Async enrichment queue — matches Alcatraz events to access log records.

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`barcode`	TEXT	Barcode value
`access_log_id`	INTEGER FK → access_logs	Target record
`queued_at`	TEXT	Queue entry timestamp
`event_timestamp`	TEXT	Original event time
`retry_count`	INTEGER	Number of retries
`last_retry_at`	TEXT	Last retry timestamp
`status`	TEXT	`pending`, `complete`, `failed`
`enrichment_data`	TEXT	JSON enrichment payload

`security_events`¶

OSDP-detected security incidents.

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`event_type`	TEXT	`tailgating_known`, `tailgating_unknown`, `crossing`, `unauthorized_entry`, `2fa_mismatch`
`timestamp`	TEXT	Event timestamp
`associated_credential`	TEXT	Card/badge that triggered event
`denied_credential`	TEXT	Credential that was denied (if applicable)
`description`	TEXT	Human-readable description
`image_path`	TEXT	Associated image (Alcatraz)
`alcatraz_event_id`	TEXT	Linked Alcatraz event
`notification_sent`	INTEGER	`1` = SMS/MQTT alert sent

Enrollment¶

`pending_enrollments`¶

Alcatraz face enrollment codes awaiting capture.

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`enrollment_code`	TEXT UNIQUE	One-time code
`badge_number`	TEXT	Badge to associate after enrolment
`status`	TEXT	`pending`, `complete`, `expired`
`expiration`	TEXT	Expiry timestamp
`created_at`	TEXT	Creation timestamp

`enrollment_queue`¶

Auto-enrollment batch processor queue.

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`barcode`	TEXT	Barcode value
`member_email`	TEXT	Email for Alcatraz lookup
`member_name`	TEXT	Member name
`status`	TEXT	`pending`, `complete`, `failed`
`source`	TEXT	`on_scan`, `on_sync`
`created_at`	TEXT	Queue entry timestamp
`processed_at`	TEXT	Completion timestamp
`retry_count`	INTEGER	Retry attempts

OSDP¶

`wiegand_conversion_templates`¶

Card format parsing rules for Wiegand-to-barcode conversion.

`osdp_pd_configurations`¶

Column	Type	Description
`id`	INTEGER PK	Auto-increment
`address`	INTEGER	OSDP peripheral address
`name`	TEXT	Device name
`secure_channel`	INTEGER	`1` = encrypted channel enabled

Database Reference¶

Core Tables¶

access_logs¶

denied_barcodes¶

settings¶

checkin_queue¶

checkin_history¶

schema_version¶

User Management¶

users¶

roles¶

role_permissions¶

audit_log¶

wan_users¶

Schedules¶

access_schedules¶

door_schedules¶

holiday_schedules¶

Vendor Barcode Tables¶

antaris_barcodes¶

paramount_barcodes¶

zenoti_barcodes¶

glofox_users¶

fliip_members¶

standalone_barcodes¶