Hospital Patient Records with Row-Level Security
Design a patient-records schema covering patients, visits, prescriptions, and staff access, implementing row-level security so doctors only see their own patients' records, plus an audit-trail table logging every access. Learners practice PostgreSQL row-level security policies, triggers for auditing, and privacy-conscious schema design.
- Estimate
- ~12h
- Steps
- 6
- Completed by
- 0
- Proposed by
- codeseed.app
PostgreSQL · pgAdmin · Row-Level Security
Project roadmap
- 01
Design the schema
~2hCreate patients, visits, prescriptions, and staff tables with doctor-patient assignments.
- 02
Insert sample data
~1.5hPopulate patients, visits, and staff with realistic assignment relationships.
- 03
Implement row-level security policies
~2.5hEnable RLS and write policies restricting doctors to records of their assigned patients.
- 04
Implement an audit-trail trigger
~2.5hWrite a trigger that logs every SELECT/UPDATE access to patient records into an audit table.
- 05
Verify policy enforcement
~2hWrite queries run as different roles to confirm access restrictions behave as expected.
- 06
Build a compliance report
~1.5hWrite a query summarizing access-log activity for compliance review.
Resources
Ready to build this?
Get a GitHub repo and start building. Your AI reviewer checks each step as you go.
Tech stack