ghstshdw
62 lines
3.6 KiB
SQL
62 lines
3.6 KiB
SQL
-- PostgreSQL PHI Seed Script
|
|
-- Run against target PostgreSQL instance to populate test data
|
|
|
|
-- Drop tables if they exist for clean re-seeding
|
|
DROP TABLE IF EXISTS "BreachLog" CASCADE;
|
|
DROP TABLE IF EXISTS "Claims" CASCADE;
|
|
DROP TABLE IF EXISTS "Patients" CASCADE;
|
|
|
|
CREATE TABLE "Patients" (
|
|
"PatientID" SERIAL PRIMARY KEY,
|
|
"FullName" VARCHAR(100),
|
|
"SSN" VARCHAR(11),
|
|
"DateOfBirth" DATE,
|
|
"MedicalRecordNumber" VARCHAR(20),
|
|
"Phone" VARCHAR(14),
|
|
"Email" VARCHAR(100),
|
|
"Address" VARCHAR(200),
|
|
"InsurancePolicyNumber" VARCHAR(30),
|
|
"Diagnosis" VARCHAR(200)
|
|
);
|
|
|
|
INSERT INTO "Patients" ("PatientID", "FullName", "SSN", "DateOfBirth", "MedicalRecordNumber", "Phone", "Email", "Address", "InsurancePolicyNumber", "Diagnosis") VALUES
|
|
(10001, 'John M. Whitfield', '573-44-9281', '1983-07-15', 'MRN-77441', '(414) 555-0174', 'jwhitfield@email.com', '4421 Elm Street Milwaukee WI 53202', 'Aetna-A992881', 'Type 2 Diabetes'),
|
|
(10002, 'Dr. Sarah K. Chen', '819-77-3341', '1971-03-22', 'MRN-33018', '(312) 555-9912', 'schen@midwestmed.org', '1010 Lake Shore Dr Chicago IL 60611', 'Cigna-448172', 'Hypertension'),
|
|
(10003, 'James O. O''Brien', '819-77-3341', '1955-11-08', 'MRN-66109', '(513) 555-2288', 'jobrien@gmail.com', '892 Vine Street Cincinnati OH 45202', 'UnitedHealth-77223', 'Chronic Back Pain'),
|
|
(10004, 'Maria Elena Rodriguez', '622-11-0099', '1990-04-30', 'MRN-91503', '(214) 555-6610', 'mrodriguez@texashealth.net', '7715 Oak Park Ave Dallas TX 75235', 'BCBS-338190', 'Anxiety Disorder'),
|
|
(10005, 'Robert D. Washington', '441-28-7763', '1967-09-14', 'MRN-55221', '(313) 555-0044', 'rwashington@email.com', '2034 Michigan Ave Detroit MI 48226', 'Medicaid-119204', 'COPD'),
|
|
(10006, 'Lisa Ann Nakamura', '441-28-7763', '1988-01-03', 'MRN-88712', '(206) 555-3390', 'lnakamura@uwnmed.org', '556 Pine Rd Seattle WA 98101', 'Premera-661733', 'Asthma'),
|
|
(10007, 'Michael T. O''Sullivan', '733-90-1122', '1979-06-19', 'MRN-22909', '(503) 555-8711', 'msullivan@hrcare.org', '188 Crescent Bay Dr Portland OR 97201', 'Regence-442180', 'Arthritis'),
|
|
(10008, 'Angela R. Petit', '662-41-0039', '1962-12-25', 'MRN-77660', '(615) 555-2200', 'apetit@nhsmail.org', '9900 Broadway Nashville TN 37203', 'Humana-993371', 'Breast Cancer Stage IIA');
|
|
|
|
CREATE TABLE "Claims" (
|
|
"ClaimID" VARCHAR(20) PRIMARY KEY,
|
|
"PatientID" INTEGER REFERENCES "Patients"("PatientID"),
|
|
"ServiceDate" DATE,
|
|
"BilledAmount" DECIMAL(10,2),
|
|
"DiagnosisCode" VARCHAR(10),
|
|
"ProviderNPI" VARCHAR(10),
|
|
"PolicyNumber" VARCHAR(30),
|
|
"SSN" VARCHAR(11)
|
|
);
|
|
|
|
INSERT INTO "Claims" ("ClaimID", "PatientID", "ServiceDate", "BilledAmount", "DiagnosisCode", "ProviderNPI", "PolicyNumber", "SSN") VALUES
|
|
('CLM-2026-001881', 10001, '2026-01-15', 2840.00, 'E11.9', '1154998722', 'AET-772-441-0091', '573-44-9281'),
|
|
('CLM-2026-001882', 10002, '2026-01-22', 550.00, 'M54.5', '1154998722', 'UHG-992-448-1177', '819-77-3341'),
|
|
('CLM-2026-001883', 10003, '2026-02-03', 1275.00, 'F41.1', '1154998722', 'BCBS-TX-772-441-9914', '622-11-0099'),
|
|
('CLM-2026-001884', 10004, '2026-02-18', 320.00, 'J45.20', '1154998722', 'PREM-WS-992-441-8817', '441-28-7763');
|
|
|
|
-- HIPAA Breach Log
|
|
CREATE TABLE "BreachLog" (
|
|
"LogID" SERIAL PRIMARY KEY,
|
|
"BreachDate" TIMESTAMP,
|
|
"PatientID" INTEGER REFERENCES "Patients"("PatientID"),
|
|
"Description" VARCHAR(500),
|
|
"SSN" VARCHAR(11),
|
|
"ReportedTo" VARCHAR(50)
|
|
);
|
|
|
|
INSERT INTO "BreachLog" ("BreachDate", "PatientID", "Description", "SSN", "ReportedTo") VALUES
|
|
('2026-03-01 14:22:00', 10003, 'Unauthorized access to patient record by staff member', '819-77-3341', 'HHS OCR'),
|
|
('2026-03-15 09:45:00', 10001, 'Email containing unencrypted PHI sent to wrong recipient', '573-44-9281', 'HHS OCR');
|