Healthcare Patient Management Database

I’m kicking off a new healthcare patient-management system and the first critical piece is a rock-solid, compliant data layer. The entire scope at this stage revolves around database management—designing, building, and documenting a schema that can safely house patient records, appointments, clinical notes, and billing details while meeting HIPAA-level security expectations.

Here’s what I need from you:

• Analyse typical patient-care workflows and translate them into an efficient relational model.
• Deliver an ER diagram, a fully normalised schema, and an indexing strategy suitable for high-volume queries.
• Implement the model in the SQL engine you recommend (PostgreSQL, MySQL, or MS-SQL are all fine if they cover encryption-at-rest, robust backup, and role-based access control).
• Seed the database with representative dummy data and supply migration scripts for future environments.
• Provide clear, developer-friendly documentation so my upcoming UI and integration teams can plug in without friction.

Acceptance criteria:

• Core entities—patients, encounters, clinicians, appointments, audit trail, and billing items—are modelled with clear primary/foreign keys.
• Common queries (retrieve or update a full patient record) execute in under one second on a dataset of at least 100k patients.
• Sensitive fields are encrypted at rest, and access roles follow least-privilege principles.

Once this foundation is solid, follow-on milestones may expand into interface design or third-party integrations, but the immediate deliverable is a secure, performant, and well-documented database ready for the rest of the build.