Introduction
In today's digital landscape, data privacy and security compliance have become critical for businesses of all sizes. Many companies turn to compliance management software to help them navigate complex regulations like GDPR, CCPA, and HIPAA. However, recent allegations have raised concerns about the reliability of some compliance tools. This tutorial will teach you how to create a basic compliance tracking system using Python and SQLite, helping you understand what makes a compliance tool trustworthy.
This hands-on project will guide you through building a simple compliance management database that tracks regulatory requirements, company status, and audit records. By the end, you'll have a foundational understanding of compliance data management and be able to evaluate whether tools like Delve are providing genuine compliance tracking or misleading customers.
Prerequisites
To follow this tutorial, you'll need:
- A computer with internet access
- Python 3.6 or higher installed
- Basic understanding of databases and programming concepts
- Text editor (like VS Code, Sublime Text, or even Notepad)
No prior experience with compliance software is required, as we'll build everything from scratch.
Step-by-Step Instructions
Step 1: Set up your Python environment
First, we need to ensure we have Python installed and create a project directory for our compliance tracking system.
mkdir compliance_tracker
cd compliance_trackerThis creates a new folder for our project. If you're unsure if Python is installed, run python --version or python3 --version in your terminal to verify.
Step 2: Create the main database structure
We'll start by creating a Python script that sets up our SQLite database with tables for regulations, companies, and compliance status.
import sqlite3
db = sqlite3.connect('compliance.db')
cursor = db.cursor()
# Create tables for our compliance tracking system
create_regulations_table = '''
CREATE TABLE IF NOT EXISTS regulations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
description TEXT,
requirements TEXT,
effective_date DATE
);
'''
cursor.execute(create_regulations_table)
create_companies_table = '''
CREATE TABLE IF NOT EXISTS companies (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
industry TEXT,
contact_email TEXT
);
'''
cursor.execute(create_companies_table)
create_compliance_table = '''
CREATE TABLE IF NOT EXISTS compliance_status (
id INTEGER PRIMARY KEY AUTOINCREMENT,
company_id INTEGER,
regulation_id INTEGER,
status TEXT,
last_updated DATE,
notes TEXT,
FOREIGN KEY (company_id) REFERENCES companies (id),
FOREIGN KEY (regulation_id) REFERENCES regulations (id)
);
'''
cursor.execute(create_compliance_table)
db.commit()
db.close()
print("Database created successfully!")This code creates three interconnected tables: regulations, companies, and compliance_status. The relationships between these tables allow us to track which companies are compliant with which regulations.
Step 3: Add sample data to your database
Now we'll populate our database with some sample data to demonstrate how the system works.
import sqlite3
db = sqlite3.connect('compliance.db')
cursor = db.cursor()
# Add sample regulations
regulations_data = [
('GDPR', 'General Data Protection Regulation', 'Data protection and privacy for individuals within European Union', '2018-05-25'),
('CCPA', 'California Consumer Privacy Act', 'Right to know what personal data is collected', '2020-01-01'),
('HIPAA', 'Health Insurance Portability and Accountability Act', 'Protects sensitive patient health information', '1996-08-21')
]
cursor.executemany('INSERT INTO regulations (name, description, requirements, effective_date) VALUES (?, ?, ?, ?)', regulations_data)
cursor.execute("INSERT INTO companies (name, industry, contact_email) VALUES ('TechCorp', 'Technology', '[email protected]')")
cursor.execute("INSERT INTO companies (name, industry, contact_email) VALUES ('HealthPlus', 'Healthcare', '[email protected]')")
db.commit()
db.close()
print("Sample data added successfully!")These sample entries represent the types of regulations and companies that would typically be tracked in a compliance system. Notice how we're storing both the regulation details and company information.
Step 4: Create a function to check compliance status
Next, we'll build a function that allows us to check whether a company is compliant with specific regulations.
import sqlite3
from datetime import datetime
def check_compliance_status(company_name, regulation_name):
db = sqlite3.connect('compliance.db')
cursor = db.cursor()
# Query to find company and regulation IDs
cursor.execute('''
SELECT c.id as company_id, r.id as regulation_id
FROM companies c, regulations r
WHERE c.name = ? AND r.name = ?
''', (company_name, regulation_name))
result = cursor.fetchone()
if not result:
print(f"Company {company_name} or regulation {regulation_name} not found")
return
company_id, regulation_id = result
# Query to get compliance status
cursor.execute('''
SELECT status, last_updated, notes
FROM compliance_status
WHERE company_id = ? AND regulation_id = ?
''', (company_id, regulation_id))
status_result = cursor.fetchone()
if status_result:
status, last_updated, notes = status_result
print(f"{company_name} compliance status for {regulation_name}:")
print(f"Status: {status}")
print(f"Last updated: {last_updated}")
if notes:
print(f"Notes: {notes}")
else:
print(f"No compliance record found for {company_name} with {regulation_name}")
db.close()
# Test the function
check_compliance_status('TechCorp', 'GDPR')This function demonstrates how a real compliance system would retrieve and display information. It's important to note that in a proper system, this data would be regularly updated and verified.
Step 5: Add a function to update compliance records
Let's create a function that allows us to update compliance status for companies.
import sqlite3
from datetime import datetime
def update_compliance_status(company_name, regulation_name, status, notes=''):
db = sqlite3.connect('compliance.db')
cursor = db.cursor()
# Get company and regulation IDs
cursor.execute('''
SELECT c.id as company_id, r.id as regulation_id
FROM companies c, regulations r
WHERE c.name = ? AND r.name = ?
''', (company_name, regulation_name))
result = cursor.fetchone()
if not result:
print(f"Company {company_name} or regulation {regulation_name} not found")
db.close()
return
company_id, regulation_id = result
# Check if record already exists
cursor.execute('''
SELECT id FROM compliance_status
WHERE company_id = ? AND regulation_id = ?
''', (company_id, regulation_id))
existing_record = cursor.fetchone()
current_date = datetime.now().strftime('%Y-%m-%d')
if existing_record:
# Update existing record
cursor.execute('''
UPDATE compliance_status
SET status = ?, last_updated = ?, notes = ?
WHERE id = ?
''', (status, current_date, notes, existing_record[0]))
print(f"Updated compliance status for {company_name} with {regulation_name}")
else:
# Create new record
cursor.execute('''
INSERT INTO compliance_status (company_id, regulation_id, status, last_updated, notes)
VALUES (?, ?, ?, ?, ?)
''', (company_id, regulation_id, status, current_date, notes))
print(f"Added new compliance record for {company_name} with {regulation_name}")
db.commit()
db.close()
# Test the update function
update_compliance_status('TechCorp', 'GDPR', 'Compliant', 'All data protection measures implemented')
update_compliance_status('HealthPlus', 'HIPAA', 'Non-Compliant', 'Pending security audit')
# Check the updated status
check_compliance_status('TechCorp', 'GDPR')This function shows how important it is to have proper audit trails and documentation in compliance systems. Real systems would require more sophisticated validation and approval processes.
Step 6: Build a simple compliance report generator
Finally, let's create a function that generates a summary report of all compliance statuses.
import sqlite3
def generate_compliance_report():
db = sqlite3.connect('compliance.db')
cursor = db.cursor()
# Query to get all compliance records with company and regulation names
cursor.execute('''
SELECT c.name as company_name, r.name as regulation_name, cs.status, cs.last_updated
FROM compliance_status cs
JOIN companies c ON cs.company_id = c.id
JOIN regulations r ON cs.regulation_id = r.id
ORDER BY c.name, r.name
''')
results = cursor.fetchall()
if not results:
print("No compliance records found")
return
print("\n=== Compliance Status Report ===")
for row in results:
company, regulation, status, last_updated = row
print(f"{company} - {regulation}: {status} (Updated: {last_updated})")
db.close()
# Generate the report
generate_compliance_report()This report function demonstrates how compliance tools should present information clearly to stakeholders. In a real-world scenario, such reports would be automatically generated and shared with relevant parties.
Summary
In this tutorial, you've built a basic compliance tracking system using Python and SQLite. You've learned how to:
- Create database tables for regulations, companies, and compliance status
- Insert sample data to represent real-world compliance scenarios
- Query and retrieve compliance information
- Update compliance records with proper audit trails
- Generate compliance reports
This hands-on experience helps illustrate why tools like Delve are under scrutiny - they must provide accurate, up-to-date, and verifiable compliance information. A good compliance system should be transparent, regularly audited, and provide clear documentation of its processes. This simple system shows the foundation of what such tools should achieve, but real compliance software requires additional features like automated monitoring, advanced reporting, and integration with other business systems.
Remember, when evaluating compliance tools, look for transparency in their processes, regular updates, and verifiable audit trails - not just claims of compliance.
