Database Schema

Aetix Computer uses 18 MySQL tables. All tables are created via aetix_computer.sql using CREATE TABLE IF NOT EXISTS.

Quick Navigation
Calls • Call Notes • Call Units • Call Timeline • Warrants • BOLOs • Reports • Citations • Citation Charges • Person Flags • Person Notes • Vehicle Flags • Gangs • Gang Members • Gang Activity • Messages • SMT • Mugshots

computer_calls

Active and closed dispatch calls.

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Unique call ID
code	VARCHAR(20)		Dispatch code (e.g., '10-31')
title	VARCHAR(255)		Call title / description
description	TEXT		Detailed description
priority	TINYINT		Priority level (1=Low, 2=Medium, 3=High)
status	VARCHAR(20)	IDX	PENDING, ACTIVE, CLOSED
location	VARCHAR(255)		Street name + area
coords	JSON		{x, y, z} coordinates
blip	INT		Map blip sprite ID
faction	VARCHAR(50)	IDX	Faction that owns this call
created_by	VARCHAR(50)		Officer citizenid who created
created_by_name	VARCHAR(100)		Officer name
created_at	TIMESTAMP		Creation timestamp
updated_at	TIMESTAMP		Last update timestamp
closed_at	TIMESTAMP		When the call was closed

computer_call_notes

Notes attached to dispatch calls.

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Note ID
call_id	INT	FK → computer_calls.id	Parent call
content	TEXT		Note content
author_id	VARCHAR(50)		Author citizenid
author_name	VARCHAR(100)		Author display name
created_at	TIMESTAMP		Timestamp

computer_call_units

Officers/units assigned to dispatch calls.

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Assignment ID
call_id	INT	FK → computer_calls.id	Parent call
citizenid	VARCHAR(50)		Assigned officer
name	VARCHAR(100)		Officer name
callsign	VARCHAR(20)		Unit callsign
assigned_at	TIMESTAMP		When assigned

computer_call_timeline

Automatic timeline entries for call events.

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Timeline entry ID
call_id	INT	FK → computer_calls.id	Parent call
type	VARCHAR(50)		Event type: created, status_change, unit_assigned, unit_unassigned, note_added
content	TEXT		Event description
author_name	VARCHAR(100)		Who triggered the event
created_at	TIMESTAMP		Event timestamp

computer_warrants

Arrest, search, bench, wiretap, and seizure warrants.

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Warrant ID
type	VARCHAR(30)	IDX	Warrant type ID (arrest, search, bench, wiretap, seizure)
status	VARCHAR(20)	IDX	PENDING, APPROVED, ACTIVE, SERVED, CANCELLED
citizenid	VARCHAR(50)	IDX	Target person
citizen_name	VARCHAR(100)		Target name
reason	TEXT		Reason / justification
faction	VARCHAR(50)		Issuing faction
issued_by	VARCHAR(50)		Issuing officer citizenid
issued_by_name	VARCHAR(100)		Issuing officer name
approved_by	VARCHAR(50)		Approving judge citizenid
approved_by_name	VARCHAR(100)		Judge name
served_by	VARCHAR(50)		Serving officer citizenid
served_by_name	VARCHAR(100)		Serving officer name
created_at	TIMESTAMP		Created
updated_at	TIMESTAMP		Last updated
expires_at	TIMESTAMP		Expiration (nullable)

computer_bolos

Be On the Lookout bulletins.

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	BOLO ID
title	VARCHAR(255)		BOLO title
description	TEXT		Full description
status	VARCHAR(20)	IDX	active, expired, cancelled
person_name	VARCHAR(100)		Suspect name (if applicable)
person_description	TEXT		Physical description
vehicle_plate	VARCHAR(20)	IDX	Vehicle plate (used for ALPR matching)
vehicle_model	VARCHAR(100)		Vehicle model
vehicle_color	VARCHAR(50)		Vehicle color
faction	VARCHAR(50)		Issuing faction
created_by	VARCHAR(50)		Creator citizenid
created_by_name	VARCHAR(100)		Creator name
created_at	TIMESTAMP		Created
expires_at	TIMESTAMP		Expiration (nullable)

computer_reports

Incident, arrest, use-of-force, traffic, and field interview reports.

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Report ID
type	VARCHAR(30)	IDX	Report type
title	VARCHAR(255)		Report title
body	LONGTEXT		Report narrative (rich text)
status	VARCHAR(20)	IDX	DRAFT, SUBMITTED, APPROVED, REJECTED
call_id	INT	FK → computer_calls.id	Linked dispatch call (nullable)
incident_id	VARCHAR(50)	IDX	External incident ID for cross-referencing
faction	VARCHAR(50)		Author's faction
author_id	VARCHAR(50)		Author citizenid
author_name	VARCHAR(100)		Author name
approved_by	VARCHAR(50)		Approver citizenid
approved_by_name	VARCHAR(100)		Approver name
created_at	TIMESTAMP		Created
updated_at	TIMESTAMP		Last updated

computer_citations

Issued citations (tickets).

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Citation ID
citizenid	VARCHAR(50)	IDX	Offender citizenid
citizen_name	VARCHAR(100)		Offender name
total_fine	INT		Total fine amount ($)
total_time	INT		Total jail time (months)
notes	TEXT		Officer notes
faction	VARCHAR(50)		Issuing faction
issued_by	VARCHAR(50)		Issuing officer
issued_by_name	VARCHAR(100)		Officer name
created_at	TIMESTAMP		Issued date

computer_citation_charges

Individual charges on a citation.

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Charge entry ID
citation_id	INT	FK → computer_citations.id	Parent citation
charge_id	INT		Charge ID from config
title	VARCHAR(255)		Charge title
type	VARCHAR(30)		Infraction / Misdemeanor / Felony
fine	INT		Fine amount for this charge
time	INT		Jail time for this charge
count	INT DEFAULT 1		Number of counts

computer_person_flags

Active flags on person profiles.

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Flag entry ID
citizenid	VARCHAR(50)	IDX	Target person
flag_id	VARCHAR(50)		Flag identifier from config
added_by	VARCHAR(50)		Officer who added
added_by_name	VARCHAR(100)		Officer name
reason	TEXT		Reason for flag
created_at	TIMESTAMP		When added

computer_person_notes

Officer notes on person profiles.

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Note ID
citizenid	VARCHAR(50)	IDX	Target person
content	TEXT		Note content
author_id	VARCHAR(50)		Author citizenid
author_name	VARCHAR(100)		Author name
created_at	TIMESTAMP		Timestamp

computer_vehicle_flags

Flags on vehicle records (stolen, BOLO, wanted). Used by ALPR/radar integration.

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Flag entry ID
plate	VARCHAR(20)	IDX	Vehicle plate (uppercase, trimmed)
flag_type	VARCHAR(50)		Flag type: stolen, bolo, wanted, custom
status	VARCHAR(20)	IDX	active, cleared
added_by	VARCHAR(50)		Officer citizenid
added_by_name	VARCHAR(100)		Officer name
reason	TEXT		Reason
created_at	TIMESTAMP		When flagged
cleared_at	TIMESTAMP		When cleared (nullable)

computer_gangs

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Gang ID
name	VARCHAR(100)	UNIQUE	Gang name
description	TEXT		Description / notes
territory	VARCHAR(255)		Known territory
threat_level	TINYINT		1-5 threat level
created_at	TIMESTAMP		Created
updated_at	TIMESTAMP		Updated

computer_gang_members

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Membership ID
gang_id	INT	FK → computer_gangs.id	Parent gang
citizenid	VARCHAR(50)		Member citizenid
citizen_name	VARCHAR(100)		Member name
rank	VARCHAR(50)		Rank within gang
added_at	TIMESTAMP		When added

computer_gang_activity

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Activity ID
gang_id	INT	FK → computer_gangs.id	Parent gang
description	TEXT		Activity description
author_name	VARCHAR(100)		Reporting officer
created_at	TIMESTAMP		When logged

computer_messages

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Message ID
faction	VARCHAR(50)	IDX	Faction scope
sender_id	VARCHAR(50)		Sender citizenid
sender_name	VARCHAR(100)		Sender name
sender_badge	VARCHAR(20)		Badge number
content	TEXT		Message text
created_at	TIMESTAMP		Sent at

computer_smt

Supervised Management Team (probation/parole tracking).

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	SMT entry ID
citizenid	VARCHAR(50)	IDX	Supervised person
type	VARCHAR(30)		parole, probation, etc.
conditions	TEXT		Supervision conditions
assigned_by	VARCHAR(50)		Assigning officer
assigned_by_name	VARCHAR(100)		Officer name
status	VARCHAR(20)		active, completed, revoked
created_at	TIMESTAMP		Start date
expires_at	TIMESTAMP		End date (nullable)

computer_mugshots

Column	Type	Key	Description
id	INT AUTO_INCREMENT	PK	Mugshot ID
citizenid	VARCHAR(50)	IDX	Person
url	TEXT		Image URL (base64 or hosted)
taken_by	VARCHAR(50)		Officer who took the photo
taken_by_name	VARCHAR(100)		Officer name
created_at	TIMESTAMP		When taken

Entity Relationships

Key Relationships

computer_calls
  +-- computer_call_notes      (call_id -> calls.id)
  +-- computer_call_units      (call_id -> calls.id)
  +-- computer_call_timeline   (call_id -> calls.id)
  +-- computer_reports         (call_id -> calls.id)

computer_citations
  +-- computer_citation_charges (citation_id -> citations.id)

computer_gangs
  +-- computer_gang_members    (gang_id -> gangs.id)
  +-- computer_gang_activity   (gang_id -> gangs.id)

Person (citizenid) links across:
  - computer_warrants.citizenid
  - computer_person_flags.citizenid
  - computer_person_notes.citizenid
  - computer_citations.citizenid
  - computer_gang_members.citizenid
  - computer_smt.citizenid
  - computer_mugshots.citizenid

Vehicle (plate) links:
  - computer_vehicle_flags.plate
  - computer_bolos.vehicle_plate

Bridge / API Back to Overview