A full-stack law enforcement intelligence platform modeled after Palantir Gotham — covering subject identity, social media threat monitoring, arrest records, and multi-agency employee management, with a .NET desktop application and complete ETL/BI pipeline.
INFO 3240 Enterprise Information Management, University of Denver — Spring 2021
The goal of this project was to simulate the kind of operational intelligence database that powers platforms like Palantir Gotham — tracking subjects, social media activity, arrest records, agency employees, and clearance levels across five law enforcement agencies. The database was peer-reviewed and earned an A grade.
The full stack included: a normalized SQL Server OLTP database loaded via SSIS, an SSAS multidimensional cube for pivot analysis, Power BI executive dashboards, and a .NET Windows Forms application for day-to-day operational use by both officers and agency administrators.
9-table relational model with enforced foreign keys and check constraints
The schema was designed from scratch with normalization, foreign key integrity, and real-world constraint enforcement — driver's license numbers must be exactly 10 digits, and social media security threat levels must fall between 1 and 5.
Views and constraints powering the .NET application
Three views were created to power the search and reporting features. The SearchResults view joins four tables to support the agency Record Lookup form with dropdown filters:
CREATE VIEW dbo.SearchResults AS
SELECT Agency.AgencyName, Employee.BadgeID, JobType.JobTitle,
JobType.ClearanceLevel, Employee.FirstName, Employee.LastName,
Employee.Address, Employee.Zipcode,
EmployeeHistory.StartDate, EmployeeHistory.HourlyRate
FROM Agency
INNER JOIN Employee ON Agency.AgencyID = Employee.AgencyID
INNER JOIN EmployeeHistory ON Employee.BadgeID = EmployeeHistory.BadgeID
INNER JOIN JobType ON Employee.JobTitleID = JobType.JobTitleIDData integrity was enforced directly in the schema — for example, driver's license numbers must be exactly 10 digits, and social media threat levels must be 1–5:
DriverLiscenseNumber NVARCHAR(10)
CONSTRAINT ck_driver_liscense_number
CHECK ((DriverLiscenseNumber >= '1000000000')
AND (DriverLiscenseNumber <= '9999999999')),
SecurityThreatLevel NVARCHAR(2)
CONSTRAINT ck_security_threat_level
CHECK ((SecurityThreatLevel >= '1')
AND (SecurityThreatLevel <= '5'))Four operational forms for two user roles — officers and agency administrators
Agency search form with dropdown filters for agency name and job title. Queries the SearchResults view to return matching officer records dynamically.
Detail form with embedded subform showing every arrest an officer has made — designed for internal accountability and community interaction review.
Tabular grid of all social media posts with subject names and threat levels. Built to be fed by a live data scraping pipeline for real-time monitoring.
Detail-view form allowing any agency to remotely add new officers to the database, with help tooltips on each required field for validation guidance.
Full pipeline from raw CSV to interactive dashboards
Data was bulk-inserted via SSIS from CSV source files into the OLTP database. An SSAS multidimensional cube was built for Excel pivot-table analysis, and Power BI dashboards delivered executive-level agency reporting. The .NET application was built in Visual Studio with VB.NET and Windows Forms.
Reviewed by Lillian Tobias on 6/7/21: "Project has a combination of useful and well-designed forms (one each for detail, grid, and subform). Practical, functional and creative search form. Aggregations (sum, count, average) used in at least one form. Directory structure is clean, files organized and named accordingly."