Open pri2si17-1997 opened 7 years ago
@aethelwulffe @teryhill
Questions :
Why we are storing logs
in table? It can be stored in log file. And who are going to use this log?
In transactions, if we select type as referral
, then we have two tabs, Referral
and Counter-Referral
, but Counter-Referral
form can't be submitted as a single unit. So according to me both forms should be in same layout. What you say?
:smile:
For 1 : I would prefer a log file. And its use case should be limited to developers. Why would any clinical person require logs? If there occurs any exception or error, he would tell that system is failing at this point, and we can ask for the logs and correct it. A non-technical person will surely not have any point to use database logs. This is my point.
For 2 : I think I should work considering it one (as I was assuming earlier) and later on we can change the UI.
I certainly think we can do a better job with logging that would support a cleaner, lower profile, less resource intensive model and the ability to search and recover. Perhaps standard logging, or NOSQL storage.
Hello @tmccormi , can you explain it a bit more with some example. It will help me a lot.
It's a concept not a design. I don't have any specifics, just a desire to make this simpler.
I would be inclined to store the "summary" columns in the log table:
Then store the specific details of the SQL either in text log, NOSQL DB or even using MySQL native transaction logging, in some fashion.
Ok. Will update you regarding this.. :slightly_smiling_face: Right now these log related tables are of no use, in new schema (can be added if no better solution found).
This is the kind of thing we need to do with the log file sometimes and it should be easier LibreEHR/contrib/util/undelete_from_log/convert_logcomments.pl
See the README.TXT for instructions.
Hello @aethelwulffe
Attached is the structure for transactions
and referral_transactions
table.
Note
referral_transactions
stores information only for referrals. I have made some assumptions like Prescriptions/Referrals
related to prescriptions
table, Documents
related to documents
table. It may/will need UI change.
transactions
stores all other transactions except referrals. It can be assumed to be a combination of previous transactions
and lbt_data
. Example for new transactions
table :
[
{
type : patient_request,
data : <body content>,
<user_created_field> : <value>,
..........
..........
},
.............
.............
]
Please check it and let me know if anything incorrect or any improvements required.
Hello @aethelwulffe
For gacl_*
related tables, can you please tell me the use of gacl_aro_seq
, gacl_aro_sections_seq
, gacl_aco_sections_seq
, gacl_aco_seq
. They have only one field id
and I can't find its use.
Also from the README:
By the way, values in the "Order" columns do not seem to be important.
I just plug in "10" for everything. Names are cosmetic but should be
meaningful to you. What really matters is the Value column.
Probably "Order" is order_value
(gacl_aco
table). So should I drop this field. Rest I can't find anything to change in gacl_*
related tables structure. Please tell me if you want something to get changed.
I don't think we need to re-factor GAcL that's a stand-alone tool we use for all of our security management and it's not something that we developed
Tony McCormick
On Jul 27, 2017 1:27 PM, "Priyanshu Sinha" notifications@github.com wrote:
Hello @aethelwulffe https://github.com/aethelwulffe
For gacl_* related tables, can you please tell me the use of gacl_aro_seq, gacl_aro_sections_seq, gacl_aco_sections_seq, gacl_aco_seq. They have only one field id and I can't find its use.
Also from the README:
By the way, values in the "Order" columns do not seem to be important. I just plug in "10" for everything. Names are cosmetic but should be meaningful to you. What really matters is the Value column.
Probably "Order" is order_value (gaclaco table). So should I drop this field. Rest I can't find anything to change in gacl* related tables structure. Please tell me if you want something to get changed.
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/LibreHealthIO/LibreEHR/issues/647#issuecomment-318476944, or mute the thread https://github.com/notifications/unsubscribe-auth/AARci6DcnOCBQCyqh-7N6jCK__uGnIs0ks5sSPK8gaJpZM4OkKhY .
-- Please be aware that e-mail communication can be intercepted in transmission or misdirected. Please consider communicating any sensitive information by telephone. The information contained in this message may be privileged and confidential. If you are NOT the intended recipient, please notify the sender immediately with a copy to hipaa-security@mrsb-ltd.com and destroy this message.
Ok but what about that Order
column mentioned in README?
Order is a list-priority item. That means "list these ACO groups in the following order". This may have no bearing on our application, but as GACL will be either re-implemented or replaced at some point, it is not worth digging too far. If you can drop a field and verify everything still works with no errors, that is fine. Otherwise we have to modify code, and we have a lot of code to modify to fit your new scheme as it is.
Hello @aethelwulffe
Please provide billing related tables details.
@pri2si17-1997 Finally. Here you go. I listed all this stuff in the chat at one point. That was stupid to even bother with. If you need, I can give you a set of records from all the below tables that show a single real-world set of entries in a completed state. These are take from real world stuff, but are not all the same incident. They were rather chosen because they represent the data as it is initially entered, or otherwise show the variations that happen, vs what all the tables and rows look like when the whole process is complete. SO:
billing
: Line items from the "Fee Sheet" UI form. These items are linked to a date of service via the encounter
. encounter
is from form_encounter
table.
INSERT INTO `billing` (`id`, `date`, `code_type`, `code`, `pid`,
`provider_id`, `user`, `groupname`, `authorized`, `encounter`, `code_text`, `billed`, `activity`,
`payer_id`, `bill_process`, `bill_date`, `process_date`, `process_file`, `modifier`, `units`,
`fee`, `justify`) VALUES
('1','20160107191728','CPT4','99244','1','1952522161','1952522161','Default','1','1','','0','1','1','0','NULL','NULL','NULL','','','253.00','R10.33'),
('2','20160107191728','ICD10','R10.33','1','1952522161','1952522161','Default','1','1','','0','1','1','0','NULL','NULL','NULL','','1','0',''),
In the above, row 1 is a procedure code...meaning what was done. The provider_id and user_id are the same. In this example, the integer is large, because I personally use the NPI UUID for the user
table row indexes. The last entry in row 1 is the 'justification' for the procedure, which is the code from row 2 which is a diagnosis code. Note that diagnosis codes do not have fees attached to them (generally) such as the '253.00' from row 1.
form_encounter
has a lot of information that links all these items together. Also data from the tables I will talk about below are added to these field as the billing process goes on, such payer_id
, bill_process
, bill_date
and others.
Next, there is the claims
table.
When you use the 'Billing Manager' and create a claim batch file, it records the particulars of the file creation (name of file, time, etc...) in this table for each form_encounter
row that has been included in the batch. It also records a row when you print to a paper insurance claim form. It records what file that claim was added to, what "version" it was etc... There might be a long-standing issue with this, as it seems to always create two rows per item; some investigation is probably in order. It may be that these extra rows are actually records for a patient invoice form that never actually gets printed. I have never investigated this @teryhill
Next is ar_session. Here are some rows from the table:
12689 ,7799 ,223 ,0 ,063017ECA-HILL ,2017-07-19 ,2017-07-21 ,144.00 ,2017-07-21 15:02:17 ,2017-07-21 15:02:17 ,0.00 ,insurance ,Community Alternatives,insurance_payment ,2017-07-21 ,0 ,check_payment
12690 ,26756 ,223 ,0 ,4-26415506 ,2017-07-17 ,2017-07-21 ,14.00 ,2017-07-21 15:04:42 ,2017-07-21 15:04:42 ,0.00 ,insurance ,State of Florida,insurance_payment ,2017-07-21 ,0 ,check_payment
12691 ,0 ,223 ,0 ,,2017-07-24 ,2017-07-24 ,60.00 ,2017-07-24 11:14:11 ,0000-00-00 00:00:00 ,0.00 ,patient ,Wanda Wonderful ,patient_payment ,2017-07-24 ,3619 ,credit_card
12694 ,1829 ,223 ,0 ,ePay - 017072111403532 ,2017-07-25 ,2017-07-25 ,1673.70 ,2017-07-25 13:36:17 ,2017-07-25 13:40:40 ,0.00 ,insurance ,,insurance_payment ,2017-07-25 ,0 ,electronic
ar_session records payment transactions. When a payment record comes in, it is listed here. That is so that payment record can be linked to the ar_activity
table. One row in this table represents a single transfer of funds, which may be payment against many claim items, and even for many different patients. These rows may be from user entries of patient payments, entering of checks received by mail, or parsed out of the 835 ERA files that I will talk about below.
the ar_activity
table is where you distribute parts of each big check listed in ar_session
.
491 ,5012 ,1 ,H2019 ,HR ,1 ,2010-12-15 13:33:39 ,8 ,1 ,199912 ,80.64 ,0.00 ,2011-01-26 13:50:48 ,,,IPP ,NULL
491 ,5012 ,2 ,H2019 ,HR ,1 ,2010-12-15 13:33:39 ,8 ,1 ,Ins adjust Ins1 ,0.00 ,9.36 ,2011-01-26 13:50:48 ,,,IA ,NULL
The above are two rows from the ar_activity table. These are payments distributed from the ar_session rows to items in the billing
table. It records payments and adjustments (additions and subtractions). It also records the adjustment reasons. The source for these rows are either direct entries from the payments UI, or are generated by parsing the ERA files (Electronic Remittance Advice) which are delimited files in the ANSI x12 835 format. Those ERA's are downloaded from a insurance company or payment clearinghouse, then uploaded to the EHR by the user and parsed for this content. They are stored in the /sites directory.
Ok.. and have you looked at the structure of tables in https://github.com/LibreHealthIO/LibreEHR/issues/647#issuecomment-318410259 ?
I know little about the transactions table. In the office I am most familiar with it is mostly just used direct from the UI to record disclosure of information type stuff. eRX seems to not interact with it. It would take me a while to figure out things that it is supposed to do. If you have been through it, and have a good idea of everywhere the table is used, then go for it. I find that trying to hunt down something stupidly named "transactions" is very difficult. It needs a more appropriate descriptive name, like "patient_disclosures" or something.
Well if you are talking about Disclosures
on patient dashboard, then it has nothing to do with transactions
table. They are stored in extended_log
table. Probably that name should be renamed.
Hello @aethelwulffe @teryhill
Can you please explain payments
. What i can collect is it is used to make payment for patients (self/insurance).
I think there is need of change in payments
UI. Reason why I'm saying this is :
When we select payment method as cash, then also there is field for cheque number(although it is not mandatory field.)
If i'm not wrong, layout remains same in all case when we select type as cash
, cheque payment
, credit card
, and net banking(authorize.net)
. But it should be dynamic. Correct me if i'm wrong.
It presents with button Generate Invoice
. It may be Make Payment
and invoice should be generated automatically.
payments
and payment_gateway_details
are two tables related to this. I can't find any info from dump of nhance.librehealth.io/download/20170629.sql
.
It will be great help, if you tell me how payments are supposed to work? Irrespective of present UI.
I can think of some this sort of UI :
This is my point. There can be many improvements to it.
@nileshprasad137 can look up to this, and may get a better UI.
Well, first, in this thingie, we are not actually TAKING payments, but rather recording them manually, then distributing the payments manually, or reviewing automated payment distributions imported by 835 files or by (possibly) the patient portal stuff, BUT: Yes, the Payments system...and there are THREE payment entry systems...need TOTAL REVISION. Yes, Yes, and Yes.
For this project, the developer must have good working knowledge of this stuff, and have a test database with data that has existing (complete and incomplete) records to process payments and billing with.
Some folks use the check number on cash payments to record a small note.
we are not actually TAKING payments, but rather recording them manually, then distributing the payments manually.
If this is the case then, there i think that there will be no use of payment_gateway_details
. But can we extend it to make payment? Or is it useless in this scenario?
there are TWO payment entry systems
Probably you mean billing stuffs?
Some folks use the check number on cash payments to record a small note.
But it should not happen.
Not...exactly.
What I am saying is that this system doesn't process credit card payments and the like (much as the UI example you posted) it is just manual entry. it is a scenario thing.
It will also take me a while to do a write-up explaining all the different bits and pieces...but Terry and I have been looking towards re-doing the whole billing and accounting bit for a long time.
On 2017-07-31 16:08, Priyanshu Sinha wrote:
we are not actually TAKING payments, but rather recording them manually, then distributing the payments manually.
If this is the case then, there i think that there will be no use of |payment_gateway_details|. But can we extend it to make payment? Or is it useless in this scenario?
there are TWO payment entry systems
Probably you mean billing stuffs?
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/LibreHealthIO/LibreEHR/issues/647#issuecomment-319181699, or mute the thread https://github.com/notifications/unsubscribe-auth/AAhzF2_cnYhLmWSinem2ultWkgQg2q2eks5sTjRbgaJpZM4OkKhY.
Yups @aethelwulffe . I thought we are doing payment, as we have one table to store gateway details. So should I leave it for now? As we will revise the module, then only we can design it.
My next approach is to seed the created tables . As majority of the main tables are covered. Those which are not covered include acl_*
on the major portion and some other table which we can process as we will encounter its query. If you have some better plan for me then please let me know.
Yeah, instead of a payment gateway, this is a (strange) accounting system. I am not totally sure what "seed the tables" is, but please, move on with your plans.
@aethelwulffe "seed the tables" is the terminology used by Laravel and others to mean "create the database/tables and load the initial values"
Meaning "populate the database". That is the exact term used by the various database engine documentation texts. Sounds like some more folks inventing and popularizing new terms instead of bothering to learn the real ones (see "on-boarding"). Seeding is usually referring to artificial randomization in computer science.
Yeah @aethelwulffe You are correct. Basically what I will do is fill database tables with random data. It will help us in testing our query(new which I'm supposed to do) and also to generate test data.
You are just looking for an excuse to use ORM Faker...you know it!:-D
On 2017-08-01 15:31, Priyanshu Sinha wrote:
Yeah @aethelwulffe https://github.com/aethelwulffe You are correct. Basically what I will do is fill database tables with random data. It will help us in testing our query(new which I'm supposed to do) and also to generate test data.
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/LibreHealthIO/LibreEHR/issues/647#issuecomment-319472837, or mute the thread https://github.com/notifications/unsubscribe-auth/AAhzF-jRRpUbdnQ4krirrbWC3jc0I1C_ks5sT30ogaJpZM4OkKhY.
Haha... yes.. :smile:
Hello @aethelwulffe @teryhill
First of all, sorry for being inactive for last couple of days. I was not able to do much work because of college schedule.
Please look at the following addresses table data. This type of fake data we will be able to make.
Plus four is somewhat not real.
Is there a reason peoples names are in the addresses table?
Tony McCormick, CTO www.mi-squared.com Support: 866-735-0897, Direct: 713-574-6709 My Calendar: http://bit.ly/XznvDo -Verba volant, scripta manent
On Thu, Aug 3, 2017 at 2:23 PM, Priyanshu Sinha notifications@github.com wrote:
Hello @aethelwulffe https://github.com/aethelwulffe @teryhill https://github.com/teryhill
First of all, sorry for being inactive for last couple of days. I was not able to do much work because of college schedule.
Please look at the following addresses table data. This type of fake data we will be able to make.
[image: screenshot from 2017-08-04 02-49-29] https://user-images.githubusercontent.com/12952169/28944380-f0b30f44-78bf-11e7-9f44-d413ee8c12e8.png
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/LibreHealthIO/LibreEHR/issues/647#issuecomment-320094074, or mute the thread https://github.com/notifications/unsubscribe-auth/AARcixvLqaRml16v-b7vC3KhqxWSni5Iks5sUjpogaJpZM4OkKhY .
-- Please be aware that e-mail communication can be intercepted in transmission or misdirected. Please consider communicating any sensitive information by telephone. The information contained in this message may be privileged and confidential. If you are NOT the intended recipient, please notify the sender immediately with a copy to hipaa-security@mrsb-ltd.com and destroy this message.
@tmccormi we have streets name annie way , those look like legit street names to me Rachael Drive is like Hill Lane. so may be they are just a little close to names or they might even be.
Got it.
Tony McCormick, CTO www.mi-squared.com Support: 866-735-0897, Direct: 713-574-6709 My Calendar: http://bit.ly/XznvDo -Verba volant, scripta manent
On Thu, Aug 3, 2017 at 5:29 PM, Terry Hill notifications@github.com wrote:
@tmccormi https://github.com/tmccormi we have streets name annie way , those look like legit street names to me Rachael Drive is like Hill Lane.
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/LibreHealthIO/LibreEHR/issues/647#issuecomment-320124831, or mute the thread https://github.com/notifications/unsubscribe-auth/AARci-57hqO8_ejceB_I4oSgOSWAkqKYks5sUmXugaJpZM4OkKhY .
-- Please be aware that e-mail communication can be intercepted in transmission or misdirected. Please consider communicating any sensitive information by telephone. The information contained in this message may be privileged and confidential. If you are NOT the intended recipient, please notify the sender immediately with a copy to hipaa-security@mrsb-ltd.com and destroy this message.
Hello @aethelwulffe
In clinical_rules
table, there is a field pid
, which has default value as 0 meaning that it is applicable for all patient, but it should refer to any registered patient?
Can you please explain me this feature? I am not sure about its working. It is from Administration -> Rules
. There are other tables also like clinical_plans
which work like this only.
Am I clear with my question?
Clear enough. PID - 0 means it is not just a rule specific to a single patient...meaning a custom rule made for handling a single case. The use of custom rules is actually too clumsy for real-world use, but that is the mechanism.
The fun part is, I have a PR in that greatly changes tables related to these. Explaining the clinical rules stuff well, I will make a try, but the whole process and interface for running these rules needs to get revamped in a number of ways.
These clinical rules are run when you go to patient demographics. There, it check for active rules as they apply to that patient. The rule for Tobacco Assessment for instance, checks the LifeStyles section of the patient history table for a value in a field. The system and interface for doing all this is only informational, and does not actually control workflow. The whole system was designed to pass a single test against a single set of test data. It is weak, easily broken, and basically a failed attempt due to the designer not really looking at the big picture. It is also needlessly complex. That is totally whacko for the following reasons: In all cases, where we have a (for this simple example) a value where the Tobacco assessment result is "patient is a smoker, patient has received smoking cessation counseling" there is a medical billing code. That means when we store this value some place, it sits in the patient history in a custom-purpose field, and just...sits there. The actual need is for this Tobacco Assessment status to get sent as an unpaid billing code to the insurance company as a QA measure. These codes also can sit right next to things like the medical diagnosis and the like right in the billing table. The same applies for lab values and just about everything else. Instead of having code that looks all over the place, making multiple-table joins to fields that are user configurable already, the whole patient status of everything that has ever happened to them (that needs to be tracked) can be stored as a unique value in the billing table as a universally recognized code. This makes QA reporting, workflow management and everything else not only easier to implement, but also reduces about a dozen JOIN statements out of the required queries.
The whole approach to this system was wrong. It is being worked on. I have a PR on these tables. You may pass it by for now.
Ok @aethelwulffe I'm leaving it for now.
But I would like to present my thought. What I think is there is many to many relationship between rules and patients as one rule is applicable to many patients (default case) and one patient can have many rules. So rules table should be single table responsible for storing only clinical rules. There can be one more table to have M2M relationship between patients and rules. I'm not sure about whole working so this may not be appropriate, but it may have some use.
Please correct me if I'm wrong.
Hello @aethelwulffe , below is some fake data for patient_contacts
table. I remember you told me that it is generic contacts table and not just specific for patients. I haven't changed its name yet.
You please look at above data and let me know if anything incorrect.
I have left tables such as payments
, rules
, acl_
, etc. as they require revamping and I'm not sure what the new structure will be. It can be added any time once we are sure of new structure.
Now I'm seeding the database, and completed patients_*
table. I will soon push my changes.
Thanks
Hello @aethelwulffe , Here is user_secures table with seeded data :
If you find anything incorrect or some more improvements, then please let me know. You haven't replied to previous comments. If there is something wrong, which I'm doing, then please let me know. :worried:
Hello @aethelwulffe
Regarding Link
script_firstname.txt
, script_girls.txt
, script_lastname.txt
are for firstname and lastname of patients(Or users). I have included this using $faker->firstName($gender = null|'male'|'female')
and $faker->lastName
.
script_state.txt
, script_town.txt
are for addresses. I have used $faker->state
and $faker->city
. I was not able to figure out separate plus_four
(as AFAIK it is the first four digits of pincode.), so used a random string.
script_icompany.txt
, for company name, there is $faker->company
& $faker->companySuffix
. I think they are more suitable. It also has tax id, $faker->vat
, which I used in facilities
table.
I will let you know, as I will seed other tables. Somewhere like for phone number
, I have used different country formatters, but as it is fake data, so it will not have much impact.
Hello @aethelwulffe @teryhill
Can you please provide me with some data of insurance, like : Policy Number, Group Number, Plan Name, Copay and plan type. It will be a great help for me.
Sure. I will get you a de-identified database sample with a variety of such stuff.
On 2017-08-14 10:22, Priyanshu Sinha wrote:
Hello @aethelwulffe https://github.com/aethelwulffe @teryhill https://github.com/teryhill
Can you please provide me with some data of insurance, like : Policy Number, Group Number, Plan Name, Copay and plan type. It will be a great help for me.
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/LibreHealthIO/LibreEHR/issues/647#issuecomment-322203794, or mute the thread https://github.com/notifications/unsubscribe-auth/AAhzF711SKyeIKBA7dqq4IntHOTf6XbEks5sYFg6gaJpZM4OkKhY.
Ok. Thanks... :slightly_smiling_face:
Out of curiosity, if you have gotten that random patient generator going, it will give you valid data for all these tables.
On 2017-08-14 14:07, Priyanshu Sinha wrote:
Ok. Thanks... 🙂
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/LibreHealthIO/LibreEHR/issues/647#issuecomment-322264489, or mute the thread https://github.com/notifications/unsubscribe-auth/AAhzF97zei7vgSOZsrQBbhZZuiNuNGThks5sYIzZgaJpZM4OkKhY.
Yes @aethelwulffe I got that working. It is really nice... :slightly_smiling_face: I got some sample data related to insurance which I asked earlier. I will continue it once the docs and demo get ready. :slightly_smiling_face:
Hello @aethelwulffe @teryhill
Probably this type of EER diagram will be helpful. You please have a look at a it and tell me if it is correct or not.
looks good to me and as long as it is readable by us old geezers it should be good
Yep, good ole standard stuff.
Hello @aethelwulffe @teryhill
I have added documentation in pdf format and removed useless DirectoryStructure
file. Also please check this link https://wordpress.com/post/pri2si17.wordpress.com/535
Is it ok to submit? or any changes required?
Hello @aethelwulffe @teryhill @tmccormi
This is in continuation of #552 .