Skip to content

Instantly share code, notes, and snippets.

@dancrumb

dancrumb/Query Plan

Last active November 13, 2019 17:26
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dancrumb/435a6e6f06925803c8b8a47b4422aa24 to your computer and use it in GitHub Desktop.
Save dancrumb/435a6e6f06925803c8b8a47b4422aa24 to your computer and use it in GitHub Desktop.
Download ZIP
Slow Query
Raw
pesky.sql
SELECT "Appointment"."id", "Appointment"."uid", "Appointment"."start", "Appointment"."end", "Appointment"."ooh",
"Appointment"."pcpReviewed", "Appointment"."status", "Appointment"."chiefComplaint",
"Appointment"."healthcareNotes", "Appointment"."fallbackNumber", "Appointment"."attendingGuardian",
"Appointment"."cancellationReason", "Appointment"."notesDownloaded", "Appointment"."statusHistory",
"Appointment"."paymentStatus", "Appointment"."sentToPcp", "Appointment"."lastTransition",
"Appointment"."providerId", "Appointment"."createdAt", "Appointment"."updatedAt", "Appointment"."reviewerId",
"Appointment"."patientId", "patient"."id" AS "patient.id", "patient"."uid" AS "patient.uid",
"patient"."firstName" AS "patient.firstName", "patient"."lastName" AS "patient.lastName",
"patient"."nickName" AS "patient.nickName", "patient"."phone" AS "patient.phone", "patient"."dob" AS "patient.dob",
"patient"."relationships" AS "patient.relationships", "patient"."nonOhanaPcp" AS "patient.nonOhanaPcp",
"patient"."medicalHistoryVersion" AS "patient.medicalHistoryVersion",
"patient"."blockedForNonPayment" AS "patient.blockedForNonPayment", "patient"."createdAt" AS "patient.createdAt",
"patient"."updatedAt" AS "patient.updatedAt", "patient"."medicalHomeId" AS "patient.medicalHomeId",
"patient"."responsiblePersonId" AS "patient.responsiblePersonId", "patient"."providerId" AS "patient.providerId",
"patient->provider"."id" AS "patient.provider.id", "patient->provider"."authId" AS "patient.provider.authId",
"patient->provider"."status" AS "patient.provider.status",
"patient->provider"."firstName" AS "patient.provider.firstName",
"patient->provider"."lastName" AS "patient.provider.lastName",
"patient->provider"."username" AS "patient.provider.username",
"patient->provider"."userType" AS "patient.provider.userType",
"patient->provider"."email" AS "patient.provider.email",
"patient->provider"."passwordToken" AS "patient.provider.passwordToken",
"patient->provider"."passwordCreated" AS "patient.provider.passwordCreated",
"patient->provider"."noEmail" AS "patient.provider.noEmail",
"patient->provider"."lastHeartbeat" AS "patient.provider.lastHeartbeat",
"patient->provider"."onCallNumber" AS "patient.provider.onCallNumber",
"patient->provider"."createdAt" AS "patient.provider.createdAt",
"patient->provider"."updatedAt" AS "patient.provider.updatedAt",
"patient->provider"."medicalHomeId" AS "patient.provider.medicalHomeId",
"patient->provider"."appointmentId" AS "patient.provider.appointmentId",
"patient->provider->medicalHome"."id" AS "patient.provider.medicalHome.id",
"patient->provider->medicalHome"."name" AS "patient.provider.medicalHome.name",
"patient->provider->medicalHome"."address" AS "patient.provider.medicalHome.address",
"patient->provider->medicalHome"."phone" AS "patient.provider.medicalHome.phone",
"patient->provider->medicalHome"."url" AS "patient.provider.medicalHome.url",
"patient->provider->medicalHome"."timeZone" AS "patient.provider.medicalHome.timeZone",
"patient->provider->medicalHome"."openHours" AS "patient.provider.medicalHome.openHours",
"patient->provider->medicalHome"."createdAt" AS "patient.provider.medicalHome.createdAt",
"patient->provider->medicalHome"."updatedAt" AS "patient.provider.medicalHome.updatedAt",
"patient->medicalHome"."id" AS "patient.medicalHome.id",
"patient->medicalHome"."name" AS "patient.medicalHome.name",
"patient->medicalHome"."address" AS "patient.medicalHome.address",
"patient->medicalHome"."phone" AS "patient.medicalHome.phone",
"patient->medicalHome"."url" AS "patient.medicalHome.url",
"patient->medicalHome"."timeZone" AS "patient.medicalHome.timeZone",
"patient->medicalHome"."openHours" AS "patient.medicalHome.openHours",
"patient->medicalHome"."createdAt" AS "patient.medicalHome.createdAt",
"patient->medicalHome"."updatedAt" AS "patient.medicalHome.updatedAt",
"patient->medicalHistories"."id" AS "patient.medicalHistories.id",
"patient->medicalHistories"."versionNumber" AS "patient.medicalHistories.versionNumber",
"patient->medicalHistories"."details" AS "patient.medicalHistories.details",
"patient->medicalHistories"."createdAt" AS "patient.medicalHistories.createdAt",
"patient->medicalHistories"."updatedAt" AS "patient.medicalHistories.updatedAt",
"patient->medicalHistories"."userId" AS "patient.medicalHistories.userId",
"patient->medicalHistories"."patientId" AS "patient.medicalHistories.patientId",
"patient->responsiblePerson"."id" AS "patient.responsiblePerson.id",
"patient->responsiblePerson"."firstName" AS "patient.responsiblePerson.firstName",
"patient->responsiblePerson"."lastName" AS "patient.responsiblePerson.lastName",
"patient->responsiblePerson"."email" AS "patient.responsiblePerson.email",
"patient->responsiblePerson"."phone" AS "patient.responsiblePerson.phone",
"patient->responsiblePerson"."address" AS "patient.responsiblePerson.address",
"patient->responsiblePerson"."dob" AS "patient.responsiblePerson.dob",
"patient->responsiblePerson"."relationships" AS "patient.responsiblePerson.relationships",
"patient->responsiblePerson"."createdAt" AS "patient.responsiblePerson.createdAt",
"patient->responsiblePerson"."updatedAt" AS "patient.responsiblePerson.updatedAt",
"patient->responsiblePerson"."guardianId" AS "patient.responsiblePerson.guardianId",
"patient->guardians"."id" AS "patient.guardians.id", "patient->guardians"."authId" AS "patient.guardians.authId",
"patient->guardians"."status" AS "patient.guardians.status",
"patient->guardians"."firstName" AS "patient.guardians.firstName",
"patient->guardians"."lastName" AS "patient.guardians.lastName",
"patient->guardians"."username" AS "patient.guardians.username",
"patient->guardians"."userType" AS "patient.guardians.userType",
"patient->guardians"."email" AS "patient.guardians.email",
"patient->guardians"."passwordToken" AS "patient.guardians.passwordToken",
"patient->guardians"."passwordCreated" AS "patient.guardians.passwordCreated",
"patient->guardians"."noEmail" AS "patient.guardians.noEmail",
"patient->guardians"."lastHeartbeat" AS "patient.guardians.lastHeartbeat",
"patient->guardians"."createdAt" AS "patient.guardians.createdAt",
"patient->guardians"."updatedAt" AS "patient.guardians.updatedAt",
"patient->guardians"."appointmentId" AS "patient.guardians.appointmentId",
"patient->guardians->GuardianPatient"."guardianId" AS "patient.guardians.GuardianPatient.guardianId",
"patient->guardians->GuardianPatient"."patientId" AS "patient.guardians.GuardianPatient.patientId",
"patient->guardians->GuardianPatient"."createdAt" AS "patient.guardians.GuardianPatient.createdAt",
"patient->guardians->GuardianPatient"."updatedAt" AS "patient.guardians.GuardianPatient.updatedAt",
"patient->guardians->hipaaDeclaration"."id" AS "patient.guardians.hipaaDeclaration.id",
"patient->guardians->hipaaDeclaration"."NPP" AS "patient.guardians.hipaaDeclaration.NPP",
"patient->guardians->hipaaDeclaration"."Telemed" AS "patient.guardians.hipaaDeclaration.Telemed",
"patient->guardians->hipaaDeclaration"."PCFP" AS "patient.guardians.hipaaDeclaration.PCFP",
"patient->guardians->hipaaDeclaration"."createdAt" AS "patient.guardians.hipaaDeclaration.createdAt",
"patient->guardians->hipaaDeclaration"."updatedAt" AS "patient.guardians.hipaaDeclaration.updatedAt",
"patient->guardians->hipaaDeclaration"."guardianId" AS "patient.guardians.hipaaDeclaration.guardianId",
"patient->guardians->responsiblePerson"."id" AS "patient.guardians.responsiblePerson.id",
"patient->guardians->responsiblePerson"."firstName" AS "patient.guardians.responsiblePerson.firstName",
"patient->guardians->responsiblePerson"."lastName" AS "patient.guardians.responsiblePerson.lastName",
"patient->guardians->responsiblePerson"."email" AS "patient.guardians.responsiblePerson.email",
"patient->guardians->responsiblePerson"."phone" AS "patient.guardians.responsiblePerson.phone",
"patient->guardians->responsiblePerson"."address" AS "patient.guardians.responsiblePerson.address",
"patient->guardians->responsiblePerson"."dob" AS "patient.guardians.responsiblePerson.dob",
"patient->guardians->responsiblePerson"."relationships" AS "patient.guardians.responsiblePerson.relationships",
"patient->guardians->responsiblePerson"."createdAt" AS "patient.guardians.responsiblePerson.createdAt",
"patient->guardians->responsiblePerson"."updatedAt" AS "patient.guardians.responsiblePerson.updatedAt",
"patient->guardians->responsiblePerson"."guardianId" AS "patient.guardians.responsiblePerson.guardianId",
"patient->paymentInformation"."id" AS "patient.paymentInformation.id",
"patient->paymentInformation"."hasHealthInsurance" AS "patient.paymentInformation.hasHealthInsurance",
"patient->paymentInformation"."billInsurance" AS "patient.paymentInformation.billInsurance",
"patient->paymentInformation"."hasMedicaid" AS "patient.paymentInformation.hasMedicaid",
"patient->paymentInformation"."insuranceProvider" AS "patient.paymentInformation.insuranceProvider",
"patient->paymentInformation"."insuranceGroup" AS "patient.paymentInformation.insuranceGroup",
"patient->paymentInformation"."insuranceId" AS "patient.paymentInformation.insuranceId",
"patient->paymentInformation"."customerId" AS "patient.paymentInformation.customerId",
"patient->paymentInformation"."haveCardImages" AS "patient.paymentInformation.haveCardImages",
"patient->paymentInformation"."last4" AS "patient.paymentInformation.last4",
"patient->paymentInformation"."createdAt" AS "patient.paymentInformation.createdAt",
"patient->paymentInformation"."updatedAt" AS "patient.paymentInformation.updatedAt",
"patient->paymentInformation"."patientId" AS "patient.paymentInformation.patientId",
"patient->paymentInformation->cardImages"."id" AS "patient.paymentInformation.cardImages.id",
"patient->paymentInformation->cardImages"."face" AS "patient.paymentInformation.cardImages.face",
"patient->paymentInformation->cardImages"."dataUrl" AS "patient.paymentInformation.cardImages.dataUrl",
"patient->paymentInformation->cardImages"."createdAt" AS "patient.paymentInformation.cardImages.createdAt",
"patient->paymentInformation->cardImages"."updatedAt" AS "patient.paymentInformation.cardImages.updatedAt",
"patient->paymentInformation->cardImages"."paymentInformationId" AS "patient.paymentInformation.cardImages.paymentInformationId",
"provider"."id" AS "provider.id", "provider"."authId" AS "provider.authId",
"provider"."status" AS "provider.status", "provider"."firstName" AS "provider.firstName",
"provider"."lastName" AS "provider.lastName", "provider"."username" AS "provider.username",
"provider"."userType" AS "provider.userType", "provider"."email" AS "provider.email",
"provider"."passwordToken" AS "provider.passwordToken", "provider"."passwordCreated" AS "provider.passwordCreated",
"provider"."noEmail" AS "provider.noEmail", "provider"."lastHeartbeat" AS "provider.lastHeartbeat",
"provider"."onCallNumber" AS "provider.onCallNumber", "provider"."createdAt" AS "provider.createdAt",
"provider"."updatedAt" AS "provider.updatedAt", "provider"."medicalHomeId" AS "provider.medicalHomeId",
"provider"."appointmentId" AS "provider.appointmentId", "provider->medicalHome"."id" AS "provider.medicalHome.id",
"provider->medicalHome"."name" AS "provider.medicalHome.name",
"provider->medicalHome"."address" AS "provider.medicalHome.address",
"provider->medicalHome"."phone" AS "provider.medicalHome.phone",
"provider->medicalHome"."url" AS "provider.medicalHome.url",
"provider->medicalHome"."timeZone" AS "provider.medicalHome.timeZone",
"provider->medicalHome"."openHours" AS "provider.medicalHome.openHours",
"provider->medicalHome"."createdAt" AS "provider.medicalHome.createdAt",
"provider->medicalHome"."updatedAt" AS "provider.medicalHome.updatedAt", "participants"."id" AS "participants.id",
"participants"."authId" AS "participants.authId", "participants"."status" AS "participants.status",
"participants"."firstName" AS "participants.firstName", "participants"."lastName" AS "participants.lastName",
"participants"."username" AS "participants.username", "participants"."userType" AS "participants.userType",
"participants"."email" AS "participants.email", "participants"."passwordToken" AS "participants.passwordToken",
"participants"."passwordCreated" AS "participants.passwordCreated",
"participants"."noEmail" AS "participants.noEmail", "participants"."lastHeartbeat" AS "participants.lastHeartbeat",
"participants"."createdAt" AS "participants.createdAt", "participants"."updatedAt" AS "participants.updatedAt",
"participants"."appointmentId" AS "participants.appointmentId",
"visitDocumentation"."id" AS "visitDocumentation.id",
"visitDocumentation"."signing" AS "visitDocumentation.signing",
"visitDocumentation"."reviewOfSystems" AS "visitDocumentation.reviewOfSystems",
"visitDocumentation"."red_flag" AS "visitDocumentation.red_flag",
"visitDocumentation"."red_flag_text" AS "visitDocumentation.red_flag_text",
"visitDocumentation"."hpi" AS "visitDocumentation.hpi",
"visitDocumentation"."chief_complaint" AS "visitDocumentation.chief_complaint",
"visitDocumentation"."plan_followup_other" AS "visitDocumentation.plan_followup_other",
"visitDocumentation"."plan_follow_up" AS "visitDocumentation.plan_follow_up",
"visitDocumentation"."plan_what_now" AS "visitDocumentation.plan_what_now",
"visitDocumentation"."plan_medications_start" AS "visitDocumentation.plan_medications_start",
"visitDocumentation"."plan_medications_continue" AS "visitDocumentation.plan_medications_continue",
"visitDocumentation"."other" AS "visitDocumentation.other",
"visitDocumentation"."family_history" AS "visitDocumentation.family_history",
"visitDocumentation"."past_surgeries" AS "visitDocumentation.past_surgeries",
"visitDocumentation"."past_health_issues" AS "visitDocumentation.past_health_issues",
"visitDocumentation"."exams" AS "visitDocumentation.exams",
"visitDocumentation"."immunizations_up_to_date" AS "visitDocumentation.immunizations_up_to_date",
"visitDocumentation"."immunization_details" AS "visitDocumentation.immunization_details",
"visitDocumentation"."allergies" AS "visitDocumentation.allergies",
"visitDocumentation"."medications" AS "visitDocumentation.medications",
"visitDocumentation"."billing_code" AS "visitDocumentation.billing_code",
"visitDocumentation"."icd10_codes" AS "visitDocumentation.icd10_codes",
"visitDocumentation"."general" AS "visitDocumentation.general",
"visitDocumentation"."locked" AS "visitDocumentation.locked",
"visitDocumentation"."signature" AS "visitDocumentation.signature",
"visitDocumentation"."createdAt" AS "visitDocumentation.createdAt",
"visitDocumentation"."updatedAt" AS "visitDocumentation.updatedAt",
"visitDocumentation"."appointmentId" AS "visitDocumentation.appointmentId",
"visitDocumentation"."providerId" AS "visitDocumentation.providerId",
"visitDocumentation->addendums"."id" AS "visitDocumentation.addendums.id",
"visitDocumentation->addendums"."content" AS "visitDocumentation.addendums.content",
"visitDocumentation->addendums"."createdAt" AS "visitDocumentation.addendums.createdAt",
"visitDocumentation->addendums"."updatedAt" AS "visitDocumentation.addendums.updatedAt",
"visitDocumentation->addendums"."userId" AS "visitDocumentation.addendums.userId",
"visitDocumentation->addendums"."visitDocumentationId" AS "visitDocumentation.addendums.visitDocumentationId",
"visitDocumentation->addendums->author"."id" AS "visitDocumentation.addendums.author.id",
"visitDocumentation->addendums->author"."authId" AS "visitDocumentation.addendums.author.authId",
"visitDocumentation->addendums->author"."status" AS "visitDocumentation.addendums.author.status",
"visitDocumentation->addendums->author"."firstName" AS "visitDocumentation.addendums.author.firstName",
"visitDocumentation->addendums->author"."lastName" AS "visitDocumentation.addendums.author.lastName",
"visitDocumentation->addendums->author"."username" AS "visitDocumentation.addendums.author.username",
"visitDocumentation->addendums->author"."userType" AS "visitDocumentation.addendums.author.userType",
"visitDocumentation->addendums->author"."email" AS "visitDocumentation.addendums.author.email",
"visitDocumentation->addendums->author"."passwordToken" AS "visitDocumentation.addendums.author.passwordToken",
"visitDocumentation->addendums->author"."passwordCreated" AS "visitDocumentation.addendums.author.passwordCreated",
"visitDocumentation->addendums->author"."noEmail" AS "visitDocumentation.addendums.author.noEmail",
"visitDocumentation->addendums->author"."lastHeartbeat" AS "visitDocumentation.addendums.author.lastHeartbeat",
"visitDocumentation->addendums->author"."createdAt" AS "visitDocumentation.addendums.author.createdAt",
"visitDocumentation->addendums->author"."updatedAt" AS "visitDocumentation.addendums.author.updatedAt",
"visitDocumentation->addendums->author"."appointmentId" AS "visitDocumentation.addendums.author.appointmentId",
"visitDocumentation->author"."id" AS "visitDocumentation.author.id",
"visitDocumentation->author"."authId" AS "visitDocumentation.author.authId",
"visitDocumentation->author"."status" AS "visitDocumentation.author.status",
"visitDocumentation->author"."firstName" AS "visitDocumentation.author.firstName",
"visitDocumentation->author"."lastName" AS "visitDocumentation.author.lastName",
"visitDocumentation->author"."username" AS "visitDocumentation.author.username",
"visitDocumentation->author"."userType" AS "visitDocumentation.author.userType",
"visitDocumentation->author"."email" AS "visitDocumentation.author.email",
"visitDocumentation->author"."passwordToken" AS "visitDocumentation.author.passwordToken",
"visitDocumentation->author"."passwordCreated" AS "visitDocumentation.author.passwordCreated",
"visitDocumentation->author"."noEmail" AS "visitDocumentation.author.noEmail",
"visitDocumentation->author"."lastHeartbeat" AS "visitDocumentation.author.lastHeartbeat",
"visitDocumentation->author"."onCallNumber" AS "visitDocumentation.author.onCallNumber",
"visitDocumentation->author"."createdAt" AS "visitDocumentation.author.createdAt",
"visitDocumentation->author"."updatedAt" AS "visitDocumentation.author.updatedAt",
"visitDocumentation->author"."medicalHomeId" AS "visitDocumentation.author.medicalHomeId",
"visitDocumentation->author"."appointmentId" AS "visitDocumentation.author.appointmentId",
"visitDocumentation->author->medicalHome"."id" AS "visitDocumentation.author.medicalHome.id",
"visitDocumentation->author->medicalHome"."name" AS "visitDocumentation.author.medicalHome.name",
"visitDocumentation->author->medicalHome"."address" AS "visitDocumentation.author.medicalHome.address",
"visitDocumentation->author->medicalHome"."phone" AS "visitDocumentation.author.medicalHome.phone",
"visitDocumentation->author->medicalHome"."url" AS "visitDocumentation.author.medicalHome.url",
"visitDocumentation->author->medicalHome"."timeZone" AS "visitDocumentation.author.medicalHome.timeZone",
"visitDocumentation->author->medicalHome"."openHours" AS "visitDocumentation.author.medicalHome.openHours",
"visitDocumentation->author->medicalHome"."createdAt" AS "visitDocumentation.author.medicalHome.createdAt",
"visitDocumentation->author->medicalHome"."updatedAt" AS "visitDocumentation.author.medicalHome.updatedAt",
"files"."id" AS "files.id", "files"."filename" AS "files.filename", "files"."key" AS "files.key",
"files"."contentType" AS "files.contentType", "files"."comment" AS "files.comment",
"files"."deleted" AS "files.deleted", "files"."createdAt" AS "files.createdAt",
"files"."updatedAt" AS "files.updatedAt", "files"."userId" AS "files.userId",
"files"."appointmentId" AS "files.appointmentId", "files"."signoutId" AS "files.signoutId",
"locationAffirmation"."id" AS "locationAffirmation.id",
"locationAffirmation"."state" AS "locationAffirmation.state",
"locationAffirmation"."dateOfAffirmation" AS "locationAffirmation.dateOfAffirmation",
"locationAffirmation"."createdAt" AS "locationAffirmation.createdAt",
"locationAffirmation"."updatedAt" AS "locationAffirmation.updatedAt",
"locationAffirmation"."appointmentId" AS "locationAffirmation.appointmentId"
FROM "appointments" AS "Appointment"
LEFT OUTER JOIN "patients" AS "patient" ON "Appointment"."patientId" = "patient"."id"
LEFT OUTER JOIN "users" AS "patient->provider"
ON "patient"."providerId" = "patient->provider"."id" AND "patient->provider"."userType" = 'Provider'
LEFT OUTER JOIN "medicalHomes" AS "patient->provider->medicalHome"
ON "patient->provider"."medicalHomeId" = "patient->provider->medicalHome"."id"
LEFT OUTER JOIN "medicalHomes" AS "patient->medicalHome"
ON "patient"."medicalHomeId" = "patient->medicalHome"."id"
LEFT OUTER JOIN "medicalHistories" AS "patient->medicalHistories"
ON "patient"."id" = "patient->medicalHistories"."patientId"
LEFT OUTER JOIN "responsiblePeople" AS "patient->responsiblePerson"
ON "patient"."responsiblePersonId" = "patient->responsiblePerson"."id"
LEFT OUTER JOIN ( "GuardianPatient" AS "patient->guardians->GuardianPatient" INNER JOIN "users" AS "patient->guardians" ON
"patient->guardians"."id" = "patient->guardians->GuardianPatient"."guardianId")
ON "patient"."id" = "patient->guardians->GuardianPatient"."patientId" AND "patient->guardians"."userType" = 'Guardian'
LEFT OUTER JOIN "hipaaDeclarations" AS "patient->guardians->hipaaDeclaration"
ON "patient->guardians"."id" = "patient->guardians->hipaaDeclaration"."guardianId"
LEFT OUTER JOIN "responsiblePeople" AS "patient->guardians->responsiblePerson"
ON "patient->guardians"."id" = "patient->guardians->responsiblePerson"."guardianId"
LEFT OUTER JOIN "paymentInformations" AS "patient->paymentInformation"
ON "patient"."id" = "patient->paymentInformation"."patientId"
LEFT OUTER JOIN "cardImages" AS "patient->paymentInformation->cardImages"
ON "patient->paymentInformation"."id" = "patient->paymentInformation->cardImages"."paymentInformationId"
LEFT OUTER JOIN "users" AS "provider"
ON "Appointment"."providerId" = "provider"."id" AND "provider"."userType" = 'Provider'
LEFT OUTER JOIN "medicalHomes" AS "provider->medicalHome"
ON "provider"."medicalHomeId" = "provider->medicalHome"."id"
LEFT OUTER JOIN "users" AS "participants" ON "Appointment"."id" = "participants"."appointmentId"
LEFT OUTER JOIN "visitDocumentations" AS "visitDocumentation"
LEFT OUTER JOIN "addendums" AS "visitDocumentation->addendums" ON
"visitDocumentation"."id" =
"visitDocumentation->addendums"."visitDocumentationId"
LEFT OUTER JOIN "users" AS "visitDocumentation->addendums->author" ON
"visitDocumentation->addendums"."userId" =
"visitDocumentation->addendums->author"."id"
INNER JOIN "users" AS "visitDocumentation->author" ON
"visitDocumentation"."providerId" = "visitDocumentation->author"."id" AND
"visitDocumentation->author"."userType" = 'Provider'
LEFT OUTER JOIN "medicalHomes" AS "visitDocumentation->author->medicalHome" ON
"visitDocumentation->author"."medicalHomeId" = "visitDocumentation->author->medicalHome"."id"
ON "Appointment"."id" = "visitDocumentation"."appointmentId"
LEFT OUTER JOIN "files" AS "files"
ON "Appointment"."id" = "files"."appointmentId" AND "files"."deleted" = FALSE
LEFT OUTER JOIN "locationAffirmations" AS "locationAffirmation"
ON "Appointment"."id" = "locationAffirmation"."appointmentId"
WHERE "Appointment"."patientId" = 82;
Raw
Query Plan
Hash Left Join (cost=111.92..1282.24 rows=22848 width=12127) (actual time=1.445..194.091 rows=34272 loops=1)
Hash Cond: ("Appointment".id = "locationAffirmation"."appointmentId")
-> Hash Left Join (cost=105.62..1007.68 rows=22848 width=12089) (actual time=1.324..154.654 rows=34272 loops=1)
Hash Cond: ("Appointment".id = files."appointmentId")
-> Hash Left Join (cost=104.03..789.47 rows=22848 width=11950) (actual time=1.284..119.666 rows=34272 loops=1)
Hash Cond: ("Appointment".id = participants."appointmentId")
-> Hash Left Join (cost=93.86..693.61 rows=22848 width=10806) (actual time=1.183..85.547 rows=34272 loops=1)
Hash Cond: ("Appointment".id = "visitDocumentation"."appointmentId")
-> Hash Left Join (cost=52.16..540.03 rows=22848 width=6690) (actual time=0.526..48.480 rows=34272 loops=1)
Hash Cond: (patient.id = "patient->guardians->GuardianPatient"."patientId")
-> Hash Left Join (cost=34.27..250.82 rows=5712 width=5157) (actual time=0.351..16.407 rows=5712 loops=1)
Hash Cond: (patient.id = "patient->paymentInformation"."patientId")
-> Hash Left Join (cost=31.19..169.20 rows=5712 width=5050) (actual time=0.305..10.927 rows=5712 loops=1)
Hash Cond: ("Appointment"."providerId" = provider.id)
-> Hash Left Join (cost=19.28..119.48 rows=5712 width=3496) (actual time=0.159..5.168 rows=5712 loops=1)
Hash Cond: ("Appointment"."patientId" = patient.id)
-> Index Scan using appointments_patientid_index on appointments "Appointment" (cost=0.14..35.80 rows=112 width=851) (actual time=0.009..0.150 rows=112 loops=1)
Index Cond: ("patientId" = 82)
-> Hash (cost=18.49..18.49 rows=51 width=2645) (actual time=0.139..0.139 rows=51 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 52kB
-> Merge Left Join (cost=0.85..18.49 rows=51 width=2645) (actual time=0.020..0.082 rows=51 loops=1)
Merge Cond: (patient.id = "patient->medicalHistories"."patientId")
-> Nested Loop Left Join (cost=0.70..8.92 rows=1 width=2267) (actual time=0.013..0.016 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.56..6.75 rows=1 width=2169) (actual time=0.009..0.011 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.42..4.59 rows=1 width=1776) (actual time=0.008..0.010 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.29..4.33 rows=1 width=1383) (actual time=0.005..0.006 rows=1 loops=1)
-> Index Scan using patients_pkey on patients patient (cost=0.14..2.16 rows=1 width=222) (actual time=0.002..0.002 rows=1 loops=1)
Index Cond: (id = 82)
-> Index Scan using users_pkey on users "patient->provider" (cost=0.14..2.17 rows=1 width=1161) (actual time=0.000..0.001 rows=0 loops=1)
Index Cond: (patient."providerId" = id)
Filter: (("userType")::text = 'Provider'::text)
-> Index Scan using "medicalHomes_pkey" on "medicalHomes" "patient->provider->medicalHome" (cost=0.14..0.24 rows=1 width=393) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: ("patient->provider"."medicalHomeId" = id)
-> Index Scan using "medicalHomes_pkey" on "medicalHomes" "patient->medicalHome" (cost=0.14..2.15 rows=1 width=393) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (patient."medicalHomeId" = id)
-> Index Scan using "responsiblePeople_pkey" on "responsiblePeople" "patient->responsiblePerson" (cost=0.14..2.16 rows=1 width=98) (actual time=0.001..0.001 rows=1 loops=1)
Index Cond: (patient."responsiblePersonId" = id)
-> Index Scan using medicalhistories_patientid_index on "medicalHistories" "patient->medicalHistories" (cost=0.14..9.29 rows=51 width=378) (actual time=0.004..0.027 rows=51 loops=1)
Index Cond: ("patientId" = 82)
-> Hash (cost=11.36..11.36 rows=44 width=1554) (actual time=0.141..0.141 rows=44 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 36kB
-> Hash Left Join (cost=2.29..11.36 rows=44 width=1554) (actual time=0.034..0.106 rows=44 loops=1)
Hash Cond: (provider."medicalHomeId" = "provider->medicalHome".id)
-> Seq Scan on users provider (cost=0.00..8.76 rows=44 width=1161) (actual time=0.010..0.047 rows=44 loops=1)
Filter: (("userType")::text = 'Provider'::text)
Rows Removed by Filter: 97
-> Hash (cost=2.13..2.13 rows=13 width=393) (actual time=0.018..0.018 rows=13 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 14kB
-> Seq Scan on "medicalHomes" "provider->medicalHome" (cost=0.00..2.13 rows=13 width=393) (actual time=0.002..0.013 rows=13 loops=1)
-> Hash (cost=3.07..3.07 rows=1 width=107) (actual time=0.042..0.042 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Right Join (cost=1.44..3.07 rows=1 width=107) (actual time=0.038..0.040 rows=1 loops=1)
Hash Cond: ("patient->paymentInformation->cardImages"."paymentInformationId" = "patient->paymentInformation".id)
-> Seq Scan on "cardImages" "patient->paymentInformation->cardImages" (cost=0.00..1.45 rows=45 width=46) (actual time=0.002..0.007 rows=45 loops=1)
-> Hash (cost=1.43..1.43 rows=1 width=61) (actual time=0.010..0.010 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on "paymentInformations" "patient->paymentInformation" (cost=0.00..1.43 rows=1 width=61) (actual time=0.007..0.008 rows=1 loops=1)
Filter: ("patientId" = 82)
Rows Removed by Filter: 33
-> Hash (cost=17.84..17.84 rows=4 width=1533) (actual time=0.170..0.170 rows=6 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Nested Loop Left Join (cost=10.44..17.84 rows=4 width=1533) (actual time=0.047..0.161 rows=6 loops=1)
-> Hash Right Join (cost=10.30..16.96 rows=4 width=1266) (actual time=0.044..0.145 rows=6 loops=1)
Hash Cond: ("patient->guardians->responsiblePerson"."guardianId" = "patient->guardians".id)
-> Seq Scan on "responsiblePeople" "patient->guardians->responsiblePerson" (cost=0.00..5.91 rows=191 width=98) (actual time=0.002..0.051 rows=191 loops=1)
-> Hash (cost=10.25..10.25 rows=4 width=1168) (actual time=0.035..0.035 rows=6 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Nested Loop (cost=0.14..10.25 rows=4 width=1168) (actual time=0.011..0.028 rows=6 loops=1)
-> Seq Scan on "GuardianPatient" "patient->guardians->GuardianPatient" (cost=0.00..2.20 rows=6 width=24) (actual time=0.007..0.011 rows=6 loops=1)
Filter: ("patientId" = 82)
Rows Removed by Filter: 90
-> Index Scan using users_pkey on users "patient->guardians" (cost=0.14..1.33 rows=1 width=1144) (actual time=0.002..0.002 rows=1 loops=6)
Index Cond: (id = "patient->guardians->GuardianPatient"."guardianId")
Filter: (("userType")::text = 'Guardian'::text)
-> Index Scan using hipaadeclarations_guardianid_index on "hipaaDeclarations" "patient->guardians->hipaaDeclaration" (cost=0.14..0.21 rows=1 width=267) (actual time=0.001..0.002 rows=1 loops=6)
Index Cond: ("patient->guardians".id = "guardianId")
-> Hash (cost=41.37..41.37 rows=26 width=4116) (actual time=0.652..0.652 rows=84 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 155kB
-> Hash Left Join (cost=13.62..41.37 rows=26 width=4116) (actual time=0.181..0.441 rows=85 loops=1)
Hash Cond: ("visitDocumentation->author"."medicalHomeId" = "visitDocumentation->author->medicalHome".id)
-> Hash Left Join (cost=11.32..38.90 rows=26 width=3723) (actual time=0.150..0.345 rows=85 loops=1)
Hash Cond: ("visitDocumentation".id = "visitDocumentation->addendums"."visitDocumentationId")
-> Hash Join (cost=9.31..36.70 rows=26 width=2351) (actual time=0.073..0.211 rows=82 loops=1)
Hash Cond: ("visitDocumentation"."providerId" = "visitDocumentation->author".id)
-> Seq Scan on "visitDocumentations" "visitDocumentation" (cost=0.00..26.82 rows=82 width=1190) (actual time=0.001..0.030 rows=82 loops=1)
-> Hash (cost=8.76..8.76 rows=44 width=1161) (actual time=0.066..0.067 rows=44 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 15kB
-> Seq Scan on users "visitDocumentation->author" (cost=0.00..8.76 rows=44 width=1161) (actual time=0.005..0.038 rows=44 loops=1)
Filter: (("userType")::text = 'Provider'::text)
Rows Removed by Filter: 97
-> Hash (cost=1.89..1.89 rows=10 width=1372) (actual time=0.073..0.073 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Merge Right Join (cost=1.41..1.89 rows=10 width=1372) (actual time=0.027..0.055 rows=10 loops=1)
Merge Cond: ("visitDocumentation->addendums->author".id = "visitDocumentation->addendums"."userId")
-> Index Scan using users_pkey on users "visitDocumentation->addendums->author" (cost=0.14..11.26 rows=141 width=1144) (actual time=0.002..0.014 rows=24 loops=1)
-> Sort (cost=1.27..1.29 rows=10 width=228) (actual time=0.022..0.029 rows=10 loops=1)
Sort Key: "visitDocumentation->addendums"."userId"
Sort Method: quicksort Memory: 28kB
-> Seq Scan on addendums "visitDocumentation->addendums" (cost=0.00..1.10 rows=10 width=228) (actual time=0.002..0.005 rows=10 loops=1)
-> Hash (cost=2.13..2.13 rows=13 width=393) (actual time=0.019..0.019 rows=13 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 14kB
-> Seq Scan on "medicalHomes" "visitDocumentation->author->medicalHome" (cost=0.00..2.13 rows=13 width=393) (actual time=0.002..0.005 rows=13 loops=1)
-> Hash (cost=8.41..8.41 rows=141 width=1144) (actual time=0.088..0.088 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
-> Seq Scan on users participants (cost=0.00..8.41 rows=141 width=1144) (actual time=0.003..0.061 rows=141 loops=1)
-> Hash (cost=1.27..1.27 rows=26 width=139) (actual time=0.028..0.028 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on files (cost=0.00..1.27 rows=26 width=139) (actual time=0.002..0.014 rows=26 loops=1)
Filter: (NOT deleted)
Rows Removed by Filter: 1
-> Hash (cost=3.91..3.91 rows=191 width=38) (actual time=0.107..0.107 rows=179 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 22kB
-> Seq Scan on "locationAffirmations" "locationAffirmation" (cost=0.00..3.91 rows=191 width=38) (actual time=0.002..0.030 rows=191 loops=1)
Planning time: 5.263 ms
Execution time: 201.768 ms
Raw
tables.sql
-- we don't know how to generate root <with-no-name> (class Root) :(
create type enum_appointments_status as enum ('created', 'patientArrived', 'patientRoomed', 'providerArrived', 'providerWithPatient', 'completing', 'canceled', 'expired', 'completed', 'providerWithoutPatient', 'roomAbandoned');
alter type enum_appointments_status owner to ahana;
create type "enum_cardImages_face" as enum ('front', 'back');
alter type "enum_cardImages_face" owner to ahana;
create type enum_queues_type as enum ('personal', 'medicalHome', 'other');
alter type enum_queues_type owner to ahana;
create type "periodType" as enum ('primary', 'backup', 'voluntary', 'none');
alter type "periodType" owner to ahana;
create type "openPeriodType" as enum ('recurring', 'oneoff');
alter type "openPeriodType" owner to ahana;
create type "surveyType" as enum ('appointment', 'exit');
alter type "surveyType" owner to ahana;
create table appointments
(
id serial not null
constraint appointments_pkey
primary key,
start timestamp with time zone,
"end" timestamp with time zone,
ooh boolean default false,
"pcpReviewed" boolean default false,
status enum_appointments_status default 'created'::public.enum_appointments_status,
"chiefComplaint" varchar(255) default ''::character varying,
"healthcareNotes" varchar(255) default ''::character varying,
"fallbackNumber" varchar(255),
"attendingGuardian" varchar(255),
"notesDownloaded" boolean default false,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null,
"providerId" integer,
"patientId" integer,
"cancellationReason" text,
"statusHistory" json default '[]'::json not null,
"routingData" json default '{}'::json not null,
uid varchar(32),
"paymentStatus" varchar(50) default 'unprocessed'::character varying,
"reviewerId" integer,
"sentToPcp" boolean default false,
"lastTransition" timestamp with time zone default now() not null
);
alter table appointments owner to ahana;
create index appointments_patientid_index
on appointments ("patientId");
create index appointments_providerid_index
on appointments ("providerId");
create index appointments_reviewerid_index
on appointments ("reviewerId");
create table "connectionTokens"
(
uid uuid default public.uuid_generate_v4() not null
constraint "connectionTokens_pkey"
primary key,
jwt text default ''::text not null,
expires timestamp with time zone not null,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null
);
alter table "connectionTokens" owner to ahana;
create table "locationAffirmations"
(
id serial not null
constraint "locationAffirmations_pkey"
primary key,
state varchar(255),
"dateOfAffirmation" timestamp with time zone,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null,
"appointmentId" integer
constraint "locationAffirmations_appointmentId_fkey"
references appointments
on update cascade on delete set null
);
alter table "locationAffirmations" owner to ahana;
create index locationaffirmations_appointmentid_index
on "locationAffirmations" ("appointmentId");
create table "medicaidFollowups"
(
id serial not null
constraint "medicaidFollowups_pkey"
primary key,
name text default ''::text,
email text default ''::text,
phone text default ''::text,
sent boolean default false,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null
);
alter table "medicaidFollowups" owner to ahana;
create table "medicalHomes"
(
id serial not null
constraint "medicalHomes_pkey"
primary key,
name varchar(255),
address varchar(255),
phone varchar(255),
url varchar(255),
"openHours" json default '{ "days": { "monday": [], "tuesday": [], "wednesday": [], "thursday": [], "friday": [], "saturday": [], "sunday": [] }, "oneoffs": [] }'::json,
"createdAt" timestamp with time zone default now() not null,
"updatedAt" timestamp with time zone default now() not null,
"timeZone" varchar(255) default 'UTC'::character varying not null
);
alter table "medicalHomes" owner to ahana;
create table users
(
id serial not null
constraint users_pkey
primary key,
"firstName" varchar(255),
"lastName" varchar(255),
username varchar(255),
email varchar(255)
constraint users_email_key
unique,
"passwordToken" varchar(255),
"passwordCreated" boolean default true,
"noEmail" boolean default false,
"createdAt" timestamp with time zone default now() not null,
"updatedAt" timestamp with time zone default now() not null,
"authId" varchar(64) default ''::character varying not null,
status varchar(10) default 'CREATED'::character varying not null,
"userType" varchar(255),
"medicalHomeId" integer
constraint users_medicalhomes_id_fk
references "medicalHomes",
"onCallNumber" varchar(15),
"lastHeartbeat" timestamp with time zone,
"appointmentId" integer
constraint users_appointments_id_fk
references appointments
);
alter table users owner to ahana;
alter table appointments
add constraint "appointments_providerId_fkey"
foreign key ("providerId") references users
on update cascade;
alter table appointments
add constraint appointments_users_id_fk
foreign key ("reviewerId") references users;
create table certifications
(
id serial not null
constraint certifications_pkey
primary key,
number varchar(255),
type varchar(255),
start timestamp with time zone,
"end" timestamp with time zone,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null,
"providerId" integer
constraint "certifications_providerId_fkey"
references users
on update cascade on delete set null
);
alter table certifications owner to ahana;
create table "hipaaDeclarations"
(
id serial not null
constraint "hipaaDeclarations_pkey"
primary key,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null,
"guardianId" integer
constraint "hipaaDeclarations_guardianId_fkey"
references users
on update cascade on delete set null,
"NPP" text default ''::text,
"Telemed" text default ''::text,
"PCFP" text default ''::text
);
alter table "hipaaDeclarations" owner to ahana;
create index hipaadeclarations_guardianid_index
on "hipaaDeclarations" ("guardianId");
create table "onCallPeriods"
(
id serial not null
constraint "onCallPeriods_pkey"
primary key,
start timestamp with time zone not null,
"end" timestamp with time zone not null,
"createdAt" timestamp with time zone default now() not null,
"updatedAt" timestamp with time zone default now() not null,
"providerId" integer
constraint "onCallPeriods_providerId_fkey"
references users
on update cascade on delete set null,
type "periodType" default 'primary'::"periodType"
);
alter table "onCallPeriods" owner to ahana;
create table "responsiblePeople"
(
id serial not null
constraint "responsiblePeople_pkey"
primary key,
"createdAt" timestamp with time zone default now() not null,
"updatedAt" timestamp with time zone default now() not null,
"firstName" text default ''::text,
email text default ''::text,
phone text default ''::text,
address json default '{}'::json not null,
dob timestamp,
relationships json default '{}'::json not null,
"guardianId" integer
constraint "responsiblePeople_guardianId_fkey"
references users
on update cascade on delete set null,
"lastName" varchar(255) default ''::character varying
);
alter table "responsiblePeople" owner to ahana;
create table patients
(
id serial not null
constraint patients_pkey
primary key,
"firstName" varchar(255) default ''::character varying,
"lastName" varchar(255) default ''::character varying,
"nickName" varchar(255) default ''::character varying,
dob timestamp with time zone,
relationships json default '{}'::json,
"nonOhanaPcp" json,
"medicalHistoryVersion" integer default 1,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null,
"providerId" integer
constraint "patients_providerId_fkey"
references users
on update cascade on delete set null,
"medicalHomeId" integer
constraint "patients_medicalHomeId_fkey"
references "medicalHomes"
on update cascade on delete set null,
"blockedForNonPayment" boolean default false not null,
"responsiblePersonId" serial
constraint "patients_responsiblePersonId_fkey"
references "responsiblePeople"
on update cascade on delete set null,
uid varchar(32),
phone text default ''::text
);
alter table patients owner to ahana;
create table "GuardianPatient"
(
"createdAt" timestamp with time zone default now() not null,
"updatedAt" timestamp with time zone default now() not null,
"guardianId" integer not null
constraint "GuardianPatient_guardianId_fkey"
references users
on update cascade on delete cascade,
"patientId" integer not null
constraint "GuardianPatient_patientId_fkey"
references patients
on update cascade on delete cascade,
constraint "GuardianPatient_pkey"
primary key ("guardianId", "patientId")
);
alter table "GuardianPatient" owner to ahana;
create index "GuardianPatient_guardianId_index"
on "GuardianPatient" ("guardianId");
create index "GuardianPatient_patientId_index"
on "GuardianPatient" ("patientId");
alter table appointments
add constraint "appointments_patientId_fkey"
foreign key ("patientId") references patients
on update cascade on delete set null;
create table "medicalHistories"
(
id serial not null
constraint "medicalHistories_pkey"
primary key,
"versionNumber" integer default 1 not null,
details json default '{"other":"","medical_history":"","surgeries":"","past_or_ongoing_health_issues":"","environment":"","immunizations_up_to_date":"","allergies":"","medications":""}'::json not null,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null,
"patientId" integer
constraint "medicalHistories_patientId_fkey"
references patients
on update cascade on delete set null,
"userId" integer
constraint "medicalHistories_userId_fkey"
references users
on update cascade on delete set null
);
alter table "medicalHistories" owner to ahana;
create index medicalhistories_patientid_index
on "medicalHistories" ("patientId");
create index medicalhistories_userid_index
on "medicalHistories" ("userId");
create index patients_medicalhomeid_index
on patients ("medicalHomeId");
create index patients_providerid_index
on patients ("providerId");
create index patients_responsiblepersonid_index
on patients ("responsiblePersonId");
create table "paymentInformations"
(
id serial not null
constraint "paymentInformations_pkey"
primary key,
"hasHealthInsurance" boolean default false,
"billInsurance" boolean default false,
"hasMedicaid" boolean default false,
"insuranceProvider" text default ''::text,
"insuranceGroup" text default ''::text,
"insuranceId" text default ''::text,
"customerId" varchar(255) default ''::character varying not null,
"haveCardImages" boolean default false,
last4 varchar(255) default ''::character varying,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null,
"patientId" integer
constraint paymentinformations_patients_id_fk
references patients
);
alter table "paymentInformations" owner to ahana;
create table "cardImages"
(
id serial not null
constraint "cardImages_pkey"
primary key,
face "enum_cardImages_face",
"dataUrl" text default ''::text not null,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null,
"paymentInformationId" integer
constraint "cardImages_paymentInformationId_fkey"
references "paymentInformations"
on update cascade on delete set null
);
alter table "cardImages" owner to ahana;
create index cardimages_paymentinformationid_index
on "cardImages" ("paymentInformationId");
create index paymentinformations_patientid_index
on "paymentInformations" ("patientId");
create table signouts
(
id serial not null
constraint signouts_pkey
primary key,
details text not null,
expiry timestamp with time zone,
history json,
deleted boolean default false,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null,
"patientId" integer
constraint "signouts_patientId_fkey"
references patients
on update cascade on delete set null,
"providerId" integer
constraint "signouts_providerId_fkey"
references users
on update cascade on delete set null,
type varchar(24)
);
alter table signouts owner to ahana;
create table files
(
id serial not null
constraint files_pkey
primary key,
filename varchar(255) not null,
key varchar(255) not null,
"contentType" varchar(255) not null,
comment text default ''::text,
deleted boolean default false not null,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null,
"appointmentId" integer
constraint "files_appointmentId_fkey"
references appointments
on update cascade on delete set null,
"userId" integer
constraint "files_userId_fkey"
references users
on update cascade on delete set null,
"signoutId" integer
constraint "files_signoutId_fkey"
references signouts
on update cascade on delete set null
);
alter table files owner to ahana;
create index files_appointmentid_index
on files ("appointmentId");
create index files_deleted_index
on files (deleted);
create index signouts_patientid_index
on signouts ("patientId");
create index signouts_providerid_index
on signouts ("providerId");
create index users_appointmentid_index
on users ("appointmentId");
create index users_medicalhomeid_index
on users ("medicalHomeId");
create table "visitDocumentations"
(
id serial not null
constraint "visitDocumentations_pkey"
primary key,
details json,
locked boolean default false,
signature json,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null,
"appointmentId" integer
constraint "visitDocumentations_appointmentId_fkey"
references appointments
on update cascade on delete set null,
"providerId" integer
constraint "visitDocumentations_providerId_fkey"
references users
on update cascade on delete set null,
signing boolean default false not null,
"reviewOfSystems" json default '[]'::json not null,
red_flag boolean default false not null,
red_flag_text text default ''::text not null,
hpi text default ''::text not null,
chief_complaint text default ''::text not null,
plan_followup_other text default ''::text not null,
plan_follow_up text default ''::text not null,
plan_what_now text default ''::text not null,
plan_medications_start text default ''::text not null,
plan_medications_continue text default ''::text not null,
other text default ''::text not null,
family_history text default ''::text not null,
past_surgeries text default ''::text not null,
past_health_issues text default ''::text not null,
exams json default '[]'::json not null,
immunizations_up_to_date text default ''::text not null,
immunization_details text default ''::text not null,
allergies text default ''::text not null,
medications text default ''::text not null,
billing_code text default ''::text not null,
icd10_codes json default '[]'::json not null,
general text default ''::text not null
);
alter table "visitDocumentations" owner to ahana;
create table addendums
(
id serial not null
constraint addendums_pkey
primary key,
content text not null,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null,
"userId" integer
constraint "addendums_userId_fkey"
references users
on update cascade on delete set null,
"visitDocumentationId" integer
constraint "addendums_visitDocumentationId_fkey"
references "visitDocumentations"
on update cascade on delete set null
);
alter table addendums owner to ahana;
create index addendums_userid_index
on addendums ("userId");
create index addendums_visitdocumentationid_index
on addendums ("visitDocumentationId");
create index visitdocumentations_appointmentid_index
on "visitDocumentations" ("appointmentId");
create index visitdocumentations_providerid_index
on "visitDocumentations" ("providerId");
create table surveys
(
id serial not null
constraint surveys_pkey
primary key,
"createdAt" timestamp with time zone not null,
"updatedAt" timestamp with time zone not null,
"npsScore" integer,
comments text default ''::text,
"respondentId" integer
constraint "surveys_userId_fkey"
references users
on update cascade on delete set null,
"appointmentId" integer
constraint "surveys_appointmentId_fkey"
references appointments
on update cascade on delete set null,
"surveyType" "surveyType" default 'appointment'::"surveyType" not null,
"visitOccurred" boolean default true not null
);
alter table surveys owner to ahana;
create table "techSupportPeriods"
(
id serial not null
constraint "techSupportPeriod_pKey"
primary key,
start timestamp with time zone default now() not null,
"end" timestamp with time zone default now() not null,
"createdAt" timestamp with time zone default now() not null,
"updatedAt" timestamp with time zone default now() not null,
phone char(12) not null
);
alter table "techSupportPeriods" owner to ahana;
create table "openPeriods"
(
id serial not null
constraint "openPeriods_pkey"
primary key,
"createdAt" timestamp with time zone default now() not null,
"updatedAt" timestamp with time zone default now() not null,
starts timestamp with time zone not null,
ends timestamp with time zone not null,
type "openPeriodType" default 'recurring'::"openPeriodType" not null,
"isClosed" boolean default false not null,
"medicalHomeId" integer
constraint "openPeriods_medicalHomeId_fkey"
references "medicalHomes"
on update cascade on delete set null
);
alter table "openPeriods" owner to ahana;
create table alerts
(
id serial not null
constraint alerts_pkey
primary key,
"createdAt" timestamp with time zone default now() not null,
"updatedAt" timestamp with time zone default now() not null,
"smsNumber" char(12) not null,
message text default ''::text not null,
"messageId" text,
error json,
"appointmentId" integer
constraint "alerts_appointmentId_fkey"
references appointments
on update cascade on delete set null
);
alter table alerts owner to ahana;
create table logouts
(
id serial not null
constraint logouts_pkey
primary key,
"createdAt" timestamp with time zone default now() not null,
"updatedAt" timestamp with time zone default now() not null,
"visitOccurred" boolean default false not null,
"surveySkipped" boolean default false not null,
"timeOnSite" integer default 0 not null,
"userType" varchar(255) not null
);
alter table logouts owner to ahana;
create function uuid_nil() returns uuid
immutable
strict
parallel safe
language c
as $$
begin
-- missing source code
end;
$$;
alter function uuid_nil() owner to rdsadmin;
create function uuid_ns_dns() returns uuid
immutable
strict
parallel safe
language c
as $$
begin
-- missing source code
end;
$$;
alter function uuid_ns_dns() owner to rdsadmin;
create function uuid_ns_url() returns uuid
immutable
strict
parallel safe
language c
as $$
begin
-- missing source code
end;
$$;
alter function uuid_ns_url() owner to rdsadmin;
create function uuid_ns_oid() returns uuid
immutable
strict
parallel safe
language c
as $$
begin
-- missing source code
end;
$$;
alter function uuid_ns_oid() owner to rdsadmin;
create function uuid_ns_x500() returns uuid
immutable
strict
parallel safe
language c
as $$
begin
-- missing source code
end;
$$;
alter function uuid_ns_x500() owner to rdsadmin;
create function uuid_generate_v1() returns uuid
strict
parallel safe
language c
as $$
begin
-- missing source code
end;
$$;
alter function uuid_generate_v1() owner to rdsadmin;
create function uuid_generate_v1mc() returns uuid
strict
parallel safe
language c
as $$
begin
-- missing source code
end;
$$;
alter function uuid_generate_v1mc() owner to rdsadmin;
create function uuid_generate_v3(namespace uuid, name text) returns uuid
immutable
strict
parallel safe
language c
as $$
begin
-- missing source code
end;
$$;
alter function uuid_generate_v3(uuid, text) owner to rdsadmin;
create function uuid_generate_v4() returns uuid
strict
parallel safe
language c
as $$
begin
-- missing source code
end;
$$;
alter function uuid_generate_v4() owner to rdsadmin;
create function uuid_generate_v5(namespace uuid, name text) returns uuid
immutable
strict
parallel safe
language c
as $$
begin
-- missing source code
end;
$$;
alter function uuid_generate_v5(uuid, text) owner to rdsadmin;
create function add_uid_to_appointment() returns trigger
language plpgsql
as $$
BEGIN
NEW."uid" :=
(CONCAT('AHANA-A', concat(to_char(NEW."createdAt", 'YYYYMMDD'), LPAD(NEW."id"::TEXT, 7, '0'))));
RETURN NEW;
END
$$;
alter function add_uid_to_appointment() owner to ahana;
create trigger appointment_insert_add_uid
before insert
on appointments
for each row
execute procedure add_uid_to_appointment();
create function add_uid_to_patient() returns trigger
language plpgsql
as $$
BEGIN
NEW."uid" :=
(CONCAT('AHANA-P', concat(to_char(NEW."createdAt", 'YYYYMMDD'), LPAD(NEW."id"::TEXT, 7, '0'))));
RETURN NEW;
END
$$;
alter function add_uid_to_patient() owner to ahana;
create trigger patient_insert_add_uid
before insert
on patients
for each row
execute procedure add_uid_to_patient();
create function last_saturday(timestamp with time zone) returns timestamp with time zone
immutable
strict
language sql
as $$
SELECT date_trunc('day' ,$1) - (extract(dow from $1)||' days')::interval - '1 day'::interval
$$;
alter function last_saturday(timestamp with time zone) owner to ahana;
create function last_saturday(timestamp with time zone, character) returns timestamp with time zone
immutable
strict
language sql
as $$
SELECT ((date_trunc('day', $1) at time zone 'utc' AT TIME ZONE $2))::timestamptz
- extract(DOW FROM date_trunc('day', $1)) * '24 hours'::INTERVAL - '24 hours'::interval
$$;
alter function last_saturday(timestamp with time zone, char) owner to ahana;
create function last_saturday(timestamp with time zone, character) returns timestamp with time zone
immutable
strict
language sql
as $$
SELECT ((date_trunc('day', $1) at time zone 'utc' AT TIME ZONE $2))::timestamptz
- extract(DOW FROM date_trunc('day', $1)) * '24 hours'::INTERVAL - '24 hours'::interval
$$;
alter function last_saturday(timestamp with time zone, char) owner to ahana;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment