Skip to main content

Database Design

Overview​

The Secure Controlled Guns and Ammunition Inventory Management System (SCGAIMS) utilizes a relational PostgreSQL database hosted through Supabase to manage inventory records, transaction histories, audit information, discrepancy reporting, and investigation records.

The database was designed to support accountability, auditability, and traceability throughout the entire inventory lifecycle while maintaining referential integrity and supporting future scalability.

Key design principles include:

  • Data integrity
  • Accountability
  • Auditability
  • Referential integrity
  • Role separation
  • Security governance
  • Scalability

Database Platform

PostgreSQL​

PostgreSQL was selected as the primary database platform due to its:

  • Reliability
  • ACID compliance
  • Relational capabilities
  • Strong integrity enforcement
  • Security features
  • Scalability

The database is hosted using Supabase as a managed cloud service.

Entity Relationship Overview

The SCGAIMS database is comprised of several core entities:

InventoryItem
    |
    |
IssueRecord
    |
    |
Investigation
    |
    |
AuditLog

Shift

These entities collectively support inventory operations, investigations, reporting, and accountability controls.

InventoryItem Entity

Purpose​

The InventoryItem entity stores information relating to firearms, ammunition, and other controlled inventory items.

Each record represents an inventory asset that may be issued, returned, audited, or investigated.

Fields​

inventory_id​

Type: Integer
Purpose: Primary Key

Unique identifier for each inventory item.

item_name​

Type: String

Descriptive name of the inventory item.

Examples:

Glock 17
M4 Carbine
9mm Ammunition
5.56mm Ammunition

serial_number​

Type: String

Unique identifier for serialized weapons.

May be null for bulk ammunition inventories.

quantity​

Type: Integer

Current quantity available for issue.

item_type​

Type: String

Indicates the inventory category.

Examples:

Weapon
Ammunition
Accessory

created_at​

Type: Timestamp

Date and time the inventory item was created.

updated_at​

Type: Timestamp

Date and time the inventory record was last modified.

Shift Entity

Purpose​

The Shift entity supports operational shift-based inventory issuance workflows.

Inventory transactions are associated with a specific operational shift.

Fields​

shift_id​

Type: Integer
Purpose: Primary Key

shift_name​

Type: String

Human-readable shift identifier.

Examples:

Day Shift
Night Shift
Bravo Shift

armory_location​

Type: String

Associated armory location.

status​

Type: String

Possible values:

Active
Closed
Suspended

start_time​

Type: Timestamp

Shift start time.

end_time​

Type: Timestamp

Shift completion time.

IssueRecord Entity

Purpose​

IssueRecord represents inventory issuance and return transactions.

This is one of the most critical entities within SCGAIMS.

Fields​

issue_id​

Type: Integer
Purpose: Primary Key

inventory_id​

Type: Foreign Key
References: InventoryItem

Identifies the inventory item involved.

shift_id​

Type: Foreign Key
References: Shift

Identifies the operational shift.

quantity_issued​

Type: Integer

Quantity issued to personnel.

quantity_returned​

Type: Integer

Quantity returned after use.

issued_to​

Type: String

User or officer receiving inventory.

issued_at​

Type: Timestamp

Issue transaction timestamp.

returned_at​

Type: Timestamp

Return transaction timestamp.

discrepancy_flag​

Type: Boolean

Possible values:

True
False

Automatically set when:

quantity_issued != quantity_returned

Investigation Entity

Purpose​

The Investigation entity supports discrepancy review and accountability processes.

Investigations are generated when inventory discrepancies occur.

Fields​

investigation_id​

Type: Integer
Purpose: Primary Key

issue_record_id​

Type: Foreign Key
References: IssueRecord

Associated transaction.

description​

Type: Text

Details of the discrepancy or investigation.

status​

Type: String

Possible values:

Open
Pending Review
Resolved
Closed

resolution_notes​

Type: Text

Supervisor comments and findings.

created_at​

Type: Timestamp

Investigation creation date.

resolved_at​

Type: Timestamp

Investigation completion date.

AuditLog Entity

Purpose​

Audit logs provide accountability, non-repudiation, and forensic review capabilities.

All significant actions performed within the system are recorded in this table.

Fields​

audit_id​

Type: Integer
Purpose: Primary Key

user_identifier​

Type: String

Identifier for the acting user.

Future versions may utilize:

Auth0 Subject Identifier

action​

Type: String

Examples:

Inventory Issued
Inventory Returned
Investigation Created
Investigation Resolved
Inventory Updated

target_entity​

Type: String

Identifies the affected record.

timestamp​

Type: Timestamp

Server-side transaction timestamp.

details​

Type: Text

Additional context relating to the action.

Referential Integrity

SCGAIMS enforces referential integrity through foreign key relationships.

Examples include:

IssueRecord
    → InventoryItem

IssueRecord
    → Shift

Investigation
    → IssueRecord

Benefits include:

  • Prevention of orphaned records
  • Improved consistency
  • Improved data quality
  • Stronger accountability

Discrepancy Detection Logic

The database supports automated discrepancy detection through evaluation of:

Quantity Issued
vs
Quantity Returned

Logic:

IF Quantity Returned != Quantity Issued

THEN

Discrepancy Flag = True

AND

Investigation Record Created

This functionality supports rapid identification of accountability concerns.

Auditability Considerations

The database was designed with accountability as a primary objective.

Key controls include:

  • Permanent transaction records
  • Audit logging
  • Investigation tracking
  • Timestamp recording
  • Historical transaction retention

These controls assist both operational oversight and investigative activities.

Security Considerations

The SCGAIMS database incorporates several security controls:

Access Control​

Database access is restricted to authorized application services.

Environment Variables​

Sensitive configuration values are stored outside application source code.

Examples:

Database Username
Database Password
Auth0 Credentials

Encrypted Communications​

Connections between Django and PostgreSQL utilize secure communication channels.

Cloud Hosting​

Supabase provides managed infrastructure and centralized administration.

Future Database Enhancements

Planned enhancements include:

  • Multi-armory inventory support
  • Ammunition lot tracking
  • Weapon assignment history
  • Digital chain-of-custody records
  • Evidence management support
  • Automated archival processes
  • SIEM integration

Conclusion

The SCGAIMS database design provides a secure, structured, and auditable foundation for managing firearms and ammunition inventories. Through the use of relational modeling, investigation tracking, audit logging, and discrepancy management, the design supports accountability, transparency, and security governance objectives while remaining scalable for future enhancements.