A DB schema to do simple accounting that is HIPAA compliant

So this starts with me putting together a spreadsheet over 10 years ago for a person to keep track of their sole practitioner medical practice. This was before I did my MBA and before I passed the CPA exam. This spreadsheet breaks a few times a year and then I duct tape it back together. The spreadsheet is really simple and most notable for what it doesn’t do — double entry accounting, splits between payments (e.g. Paypal charges,) create invoices or receipts, and most importantly, separately encrypt Personally Identifiable Information per HIPAA. Now, it is technically HIPAA compliant because it’s saved with a password and only one person generally has access to it. Technically compliant and good are two different things, though. I’m generally not satisfied with the former.

So I’m writing a webapp to replace the spreadsheet. This is mostly an educational exercise for me. I could turn it into a business and I’ve made a few design choices to make that easier. However, the webapp’s primary purpose is to replace a spreadsheet that one person uses.

The stack I’m using is pretty simple — Python, Flask, SQLAlchemy, Bootstrap. Initially, I’ll use sqlite. Everything is going to run on a laptop, so sqlite is good enough. One final thing — I didn’t generate the schema in the DB. I first used a design tool and then wrote all the SQLAlchemy models. This has been debugged to create actual entries into every table.

Whole Schema:

That’s not so helpful, but I wanted to get the whole enchilada out there.

We’ll break this down by subsections.

First up, Entity:

Entity Table and its FKs

Entity is the main design decision that would allow me to take this app multi-enterprise. This way we can have multiple customers in the same database. Of course, this is just going to have one customer to start. Let’s go through the fields:

• id : this is the primary key
• uuid: we’re going to generate a uuid for every row in every table. This may seem kind of weird, but it’s cheap and it’s going to make pulling up things like lastEdited easier. It will also allow for easier security audits, if that’s ever needed. I’m also doing this because it will help me get used to how I need to think for IPv6.
• name :The name of the entity, duh.
• id_Sogeum: This is a foreign key to random salts. We’re going to use this when we encrypt PII. Look at the Sogeum table and you’ll see that each one also gets a UUID and it gets a status, too. We’re going to do key rotation and retirement, so we need to keep track of key status which means we need to keep track of salt status. When a key is retired, we will actually purge it from the DB. We will probably put it in the logs, though. Notice that their is no FK back to Entity on Sogeum. If you were able to get a copy of this table, you couldn’t tell what salts belonged to what entity. PII is going to very difficult for administrators to access — the encryption keys are never actually stored. They are generated at storage and access time. So, let’s say, if your DBA wanted to read it, he would have to write code to generate the key
• ein :Employer Identification Number. This is the same as a social security number, but for a business.
• id_Address: We normalize all the addresses into this table. This does slightly complicate the Entity creation code as we’ll have to insert an address first, then go back and update its id_Entity field after the Entity is created.
• id_PhoneNumber: just like address.
• id_EntityType: There are a bunch of tables where you have a type. Instead of having a separate table for each that‘s’ very static, we have two tables, TypesUniversal and TypesForEntity. So universal means across the whole app while entity means specific to an entity. Then we have a TypeFlavor table. For example, there will an Entity flavor. Then there will be entries for LLC, Sole Proprietorship, S Corp, LLP, etc.
• createDate: We’re going to have a log table where we log each CRUD action. But this table will be truncated periodically to only hold one entry for each UUID. So in the table rows themselves we go ahead and put in the created date. This seems to be the most efficient way to keep track of it.

After Entity is User:

User is pretty straight forward

• id: Primary Key
• createDate: just like with Entity.
• uuid: Every object gets a UUID.
• id_Person: Every object that needs a person will normalize into the Person table. Its fields are easy to understand. If the particular person row is a client (AKA patient), this data will actually be encrypted. So if someone managed to get a dump of the Person table, most rows would be encrypted with different keys. Yeah, have fun with that if you manage to get a dump from the table. One other thing to point out is that all this normalization makes it much easier to comply with privacy regulation. We don’t have any PII hiding in random places so we can control access to it easily.
• emailAddress: Exactly what you would expect
• id_Address: Normalized to keep all the addresses together.
• id_PhoneNumber: Just like id_Address.
• username: This can be the same as the email address or it can be something else. It’s up to the user.
• pwHash: We don’t actually store passwords, we store the hashes. We’ll hash them with Argon2 and salt them of course. These hashes will have nothing to do with the Sogeum table. Before we salt the hashes, we will check them against the most popular passwords and forbid them.
• id_Entity: Every user has to tie to an entity.

Next is Client:

Client is normalized as well.

• id: The primary key. It autoincrements.
• createDate: Just like all the others. We record it at row creation.
• uuid: Yep, another one. I don’t think this needs to be encrypted.
• emailAddress: Exactly what it says. We’ll use some regex to check its validity. It will be encrypted so you’ll see gibberish if you do a straight select.
• id_Person, id_Address, id_PhoneNumber: Like everything else, they’re normalized into separate tables. Person will be encrypted.
• rate1, rate2, rate3, rate4: So the user can set a series of default rates. There is a rate table, but it is not FK’ed to this. We’ll use that in the UI to make things cool in the UI, but these are not normalized fields.
• id_Entity: Yeah, every client record is tied to an entity.

Let’s do Rates now since we mentioned it just now.

• id: The primary key.
• createDate: Done at creation.
• uuid: Yep, it gets one. This will all make sense later.
• name: Obvious.
• amount: Obvious.
• comment: Obvious.
• id_Entity: Yeah, each row is specific to an entity. We’re not going to encrypt anything here, though.

Now it will get interesting. Let’s look at ClientJournal.

Before we get to the fields, we need some context. HIPAA makes it very clear that billing data and treatment logs are sensitive, personally identifiable data. So the idea is to store all treatment data separate from all other financial transactions. That’s why this exists.

Next, I toyed with the idea of a combined journal / ledger table. That way is the more technically elegant way to go. But we’d end up with really funky looking data because accounting systems are periodic in nature. You periodically flush transactions, but keep the balances alive in the ledger. We can do this on a combined table, but it’s going to feel really awkward to someone who actually understands accounting. So we have a situation where domain expectation is not matching with technical orthodoxy. I pick domain expectation.

So let’s talk about the fields in ClientJournal:

• id: The primary key.
• createDate: Here it is again. I’ll attach the model code at the end. Then you can see I didn’t keep typing this over and over.
• uuid: This is a little bit different than other tables. We’re going to use this UUID as a transaction id. Transactions will often have multiple rows. For example, if someone pays with Paypal we will credit revenue, debit your Paypal account (asset), and debit Paypall fees (expense). So this is going to be two rows, both of which will have the same UUID.
• id_Client: Of course every entry here will be specific to a client. The data here is just a number, but the data in the Client table will nearly all be encrypted. Because we’ve normalized the data, we don’t actually need to encrypt this table.
• id_TransType: The user will be able to designate what kind of transaction this is.
• id_Diagnosis: Medical-specific. There is an editable diagnosis table. Every ClientJournal entry can tie to just one.
• date: Date of the transaction. This is usually the date of the client session or payment.
• creditAmt: Amount for the credit account.
• debitAmt: Amount for the debit account. Note that the sums of creditAmt and debitAmt have to equal each other across the transaction. We don’t check this with the DB, but this kind of error checking is key to accounting.
• id_ChartOfAccounts_Debit, id_ChartOfAccounts_Credit: FKs back to the Chart of Accounts. We’ll talk about that later on.
• identifier: Most payments will have an identifier. For checks, it is check number. It may be transaction number. When we talk about PaymentType, this will make complete sense.
• note, desc: Yep, the user can add a note or description
• id_Entity: Yes, it’s all specific to an entity.

Next up is ClientLedger:

So ClientLedger is where we will actually see how much money comes in and when we do billing, most of the data is going to come from here. First, a couple of things. Every client is essentially a subaccount of a row in the Chart of Accounts, Client Revenue. So when you create a new client, we will also create an entry into the ClientJournal and also into ClientLedger. When this client has a session or some other service and pays immediately with cash, the journal entry will be CR for the patient and DR for Cash (or Cash for Deposit.) The corresponding ClientLedger entry will be the same, but we will update the balance for both Cash and the patient. We will also go make entries into the General Ledger, but instead of DR to the client account, it will tie to the superaccount, ClientRevenue. With other payments, it’s a little more complicated, but the principles are the same. Let’s go to the fields:

• id: The primary key.
• createDate: Here it is again. I’ll attach the model code at the end. Then you can see I didn’t keep typing this over and over.
• uuid: this will agree exactly with the entry in the ClientJournal. I’m not sure if it’s always true so I’m holding back on an FK relationship. We’ll see how it goes in testing. Because of splits, multiple rows will often have identical UUID.
• id_Client: Almost every transaction will have one of these. Exceptions will be things like starting balance, balance forward, correcting entries, etc.
• creditAmt, debitAmt: Obvious what this is for.
• id_ChartOfAccounts_Debit: The payment types will automate many of these entries.
• id_ChartOfAccounts_Credit: There will be some variety on entries here, but most are going to be ClientRevenue.
• debitBalance, creditBalance: These are hugely important so extra care needs to be taken to make sure they are correct. To calculate it, you have to grab the latest entry and then add the new transaction. So we need to check that what you grab is the latest with at least two methods.
• note, desc: They’re here. We’ll see how much they’re used.
• id_EntryType: There will be a taxonomy of entry types. I’ll have to review some of my accounting texts to come up with it.
• id_Entity: of course.
• date: I actually forgot this and had to rev the schema to get it in there. Software!

We’ll do the GeneralJournal now:

Very similar to the ClientJournal

This is very similar to the ClientJournal so I’m not going to go every field.

• There is no tie to client here. Instead we have TransEntity. Let’s say you pay the electric bill. Well, you can store their address and phone number if you want. Or let’s say it’s your malpractice insurance. You can also include a contact person.
• For this kind of business, this should be a fairly light table. We’re not duplicating ClientJournal entries so this is going to be the humdrum business transactions like rent, insurance, utilities…

Onto GeneralLedger:

Simpler than ClientLedger

Once again, this one is very similar to ClientLedger. So I won’t go field by field.

• An important thing is that ClientLedger entries will actually be posted here as well. Essentially, they’ll have the PII stripped away just leaving the financials.
• uuid refers to the transaction. So you will see the same uuid in ClientJournal, ClientLedger, and GeneralLedger.

Let’s look at ChartOfAccounts:

accounting goodness

The Chart of Accounts is central to modern accounting. I don’t want to teach an accounting class so I’m not going to through every field.

• parent_id: This allows us to do subaccounts. Most of these are going to be user produced. For example, I will prepopulate Utilities as an Expense account. The user will create the electric company subaccount.
• id_Entity: Every entity will have a different chart of Accounts.
• ScheduleCField: The idea is that each account ties to a Schedule C (if you don’t know what this is, then you’re lucky) line. Then you can click a button in April and have everything ready for your taxes.

Audit Trail is pretty important in accounting. So I have that built into the schema:

Are you excited? Yeah, I didn’t think you would be.

So this is basically a structured log. Every time we make a change, we put a row in here. Now you see why I have uuid all over the place — it makes this easier. We could beef it up and put a row in every time someone looked at something. Even for me, I think that’s overkill. We’ll just put that in the logs.

But why do we do this? I didn’t want to clutter up every table with fields like lastEditedBy, lastEditDate, lastAction. Also, doing it that way means we only have the last entry. You can’t show any history. With this, I can show history and still use the same ORM. I just do a simple query with uuid. If space becomes a problem, we can manage that by truncating this table.

I’ve written this largely as documentation for myself. If you see it, you’re free to comment and make suggestions. If this makes you want to give me a job, well, you can figure out how to find me. I always listen to inquiries though I’m not always immediately available.

That’s it for now. Here is the model code (Medium is going to make it look bad because I code with 132 width, but oh well):

import uuid
import sys
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.ext.declarative import declared_attr, declarative_base
Base = declarative_base()
db = SQLAlchemy()
class BaseModel(db.Model):
    # This is an abstract for our models that we will subclass for the actual tables. Every table will have these fields so
    # this is the right way to do it.
    __abstract__ = True
    id = db.Column(db.Integer, primary_key=True)
    createDate = db.Column(db.DateTime)
    uuid = db.Column(db.String(32))
class EntityMixin(object):
    # This mixin will create an id_Entity column with an FK to the Entity model.
    @declared_attr
    def id_Entity(cls):
        return db.Column(db.Integer, db.ForeignKey('Entity.id'))
@declared_attr
    def entity(cls):
        return db.relationship('Entity', primaryjoin='%s.id_Entity==Entity.id' % cls.__name__, remote_side='Entity.id')
class NameMixin(object):
    # This is a mixin to make the table name match the model name exactly.
    @declared_attr
    def __tablename__(cls):
        return cls.__name__
class AuditTrail(NameMixin, db.Model):
    # You could think of this as the application's "master journal" or temporary log
    # So we throw data in here every time we modify or create an object.
    # The table will be truncated periodically. That's why we have a create date in models for every object.
    # The create date may not actually be available here. But having this table will actually allow us to
    # build a UI where you can look at some history.
    id = db.Column(db.Integer, primary_key=True)
    objUUID = db.Column(db.String(32), nullable=False)
    model = db.Column(db.String(100), nullable=False)  #This will usually be the table name for the object
    id_User = db.Column(db.Integer, db.ForeignKey('User.id'), index=True)
    user = db.relationship("User", foreign_keys=[id_User])
    uuid_User = db.Column(db.String(32), db.ForeignKey('User.uuid'))
    userUUID = db.relationship("User", foreign_keys=[uuid_User])
    id_Entity = db.Column(db.Integer, db.ForeignKey('Entity.id'), index=True)
    entity = db.relationship("Entity", foreign_keys=[id_Entity])
    uuid_Entity = db.Column(db.String(32), db.ForeignKey('Entity.uuid'))
    entityUUID = db.relationship("Entity", foreign_keys=[uuid_Entity])
    action = db.Column(db.String(25), nullable=False)
    timestamp = db.Column(db.DateTime)
    comment = db.Column(db.String(255))
class Sogeum(NameMixin, BaseModel):
    # ipso factotum, learn Korean and you'll know what this model is about
    sogeum = db.Column(db.String(128), nullable=False)
    status = db.Column(db.Integer, nullable=False)
class Entity(NameMixin, db.Model):
    # This model makes SAAS possible. Without it, we have to create a new DB for each subscriber company
    id = db.Column(db.Integer, primary_key=True)
    uuid = db.Column(db.String(32))
    name = db.Column(db.String(255), index=True, nullable=False)
    id_Sogeum = db.Column(db.Integer, db.ForeignKey('Sogeum.id'))
    sogeum = db.relationship("Sogeum", foreign_keys=[id_Sogeum])
    ein = db.Column(db.String(10))
    id_Address = db.Column(db.Integer, db.ForeignKey('Address.id'))
    address = db.relationship("Address", foreign_keys=[id_Address])
    id_PhoneNumber = db.Column(db.Integer, db.ForeignKey('PhoneNumber.id'))
    phoneNumber = db.relationship("PhoneNumber", foreign_keys=[id_PhoneNumber])
    id_EntityType = db.Column(db.Integer, db.ForeignKey('TypesUniversal.id'), nullable=False)
    entityType = db.relationship("TypesUniversal", foreign_keys=[id_EntityType])
    createDate = db.Column(db.DateTime)
class Client(NameMixin, EntityMixin, BaseModel):
    # In general accounting, this would be customer. But this app is custom to psychiatrists and similar practices
    #entity = db.relationship("Entity", foreign_keys=[id_Entity])
    emailAddress = db.Column(db.String(254), index=True)
    id_Person = db.Column(db.Integer, db.ForeignKey('Person.id'), nullable=False)
    person = db.relationship("Person", foreign_keys=[id_Person])
    id_Address = db.Column(db.Integer, db.ForeignKey('Address.id'))
    address = db.relationship("Address", foreign_keys=[id_Address])
    id_PhoneNumber = db.Column(db.Integer, db.ForeignKey('PhoneNumber.id'))
    phoneNumber = db.relationship("PhoneNumber", foreign_keys=[id_PhoneNumber])
    rate1 = db.Column(db.Float)
    rate2 = db.Column(db.Float)
    rate3 = db.Column(db.Float)
    rate4 = db.Column(db.Float)
class ChartOfAccounts(NameMixin, EntityMixin, BaseModel):
    # Pretty straight forward. This will be prepopulated with a bunch and then the entity will have to add a lot
    code = db.Column(db.String(5), nullable=False, index=True)
    name = db.Column(db.String(50), nullable=False)
    desc = db.Column(db.String(255))
    parent_id = db.Column(db.Integer)
    id_AccountTypes = db.Column(db.Integer, db.ForeignKey('TypesUniversal.id'))
    accountType = db.relationship("TypesUniversal", foreign_keys=[id_AccountTypes])
    id_ScheduleCField = db.Column(db.Integer, db.ForeignKey('ScheduleCField.id'))
    scheduleCField = db.relationship("ScheduleCField", foreign_keys=[id_ScheduleCField])
class ClientJournal(NameMixin, EntityMixin, BaseModel):
    # This holds the journal for all the practitioner's client sessions. It's separate from the GL because it must be HIPAA compliant
    # uuid is going to be per transaction and not per row
    id_Client = db.Column(db.Integer, db.ForeignKey('Client.id'))
    client = db.relationship("Client", foreign_keys=[id_Client])
    id_TransType = db.Column(db.Integer, db.ForeignKey('TypesForEntity.id'))
    transType = db.relationship("TypesForEntity", foreign_keys=[id_TransType])
    id_Diagnosis = db.Column(db.Integer, db.ForeignKey('Diagnosis.id'))
    diagnosis = db.relationship("Diagnosis", foreign_keys=[id_Diagnosis])
    date = db.Column(db.DateTime, nullable=False)
    creditAmt = db.Column(db.Float)
    debitAmt = db.Column(db.Float)
    id_ChartOfAccounts_Debit = db.Column(db.Integer, db.ForeignKey('ChartOfAccounts.id'))
    acctDebit = db.relationship("ChartOfAccounts", foreign_keys=[id_ChartOfAccounts_Debit])
    id_ChartOfAccounts_Credit = db.Column(db.Integer, db.ForeignKey('ChartOfAccounts.id'))
    acctCredit = db.relationship("ChartOfAccounts", foreign_keys=[id_ChartOfAccounts_Credit])
    identifier = db.Column(db.String(255))
    note = db.Column(db.String(255))
    desc = db.Column(db.String(255))
class ClientLedger(NameMixin, EntityMixin, BaseModel):
    # We break out the ledger from the journal to make maintenance easier.
    # In the ledger, we're focusing on account balances. There is more detail on the transaction in the journals.
    # We could put it all together, but closing out periods would be trickier.
    # To check balances for clients, we'll have to come to this table.
    # uuid will usually not be generated specifically for this table, but will come from the journal.
    id_Client = db.Column(db.Integer, db.ForeignKey('Client.id'))
    client = db.relationship("Client", foreign_keys=[id_Client])
    creditAmt = db.Column(db.Float)
    debitAmt = db.Column(db.Float)
    id_ChartOfAccounts_Debit = db.Column(db.Integer, db.ForeignKey('ChartOfAccounts.id'))
    acctDebit = db.relationship("ChartOfAccounts", foreign_keys=[id_ChartOfAccounts_Debit])
    id_ChartOfAccounts_Credit = db.Column(db.Integer, db.ForeignKey('ChartOfAccounts.id'))
    acctCredit = db.relationship("ChartOfAccounts", foreign_keys=[id_ChartOfAccounts_Credit])
    debitBalance = db.Column(db.Integer, nullable=False)
    creditBalance = db.Column(db.Integer, nullable=False)
    note = db.Column(db.String(255))
    desc = db.Column(db.String(255))
    id_EntryType = db.Column(db.Integer, db.ForeignKey('TypesUniversal.id'))
    entryType = db.relationship("TypesUniversal", foreign_keys=[id_EntryType])
    date = db.Column(db.DateTime, index=True)
class User(NameMixin, EntityMixin, BaseModel):
    # Pretty obvious what this is for
    id_Person = db.Column(db.Integer, db.ForeignKey('Person.id'), nullable=False)
    person = db.relationship("Person", foreign_keys=[id_Person])
    emailAddress = db.Column(db.String(254), index=True)
    id_Address = db.Column(db.Integer, db.ForeignKey('Address.id'))
    address = db.relationship("Address", foreign_keys=[id_Address])
    id_PhoneNumber = db.Column(db.Integer, db.ForeignKey('PhoneNumber.id'))
    phoneNumber = db.relationship("PhoneNumber", foreign_keys=[id_PhoneNumber])
    username = db.Column(
        db.String(254), nullable=False,
        index=True)  # you can duplicate your email address as the username so this can be 254 characters
    pwHash = db.Column(db.String(128), nullable=False)  # This is likely larger than we need, but so what
    roles = db.relationship("UserRoleJunction")
class Person(NameMixin, EntityMixin, BaseModel):
    # This is essentially a normalization table. Most of the entries will be encrypted as they are clients
    firstName = db.Column(db.String(100))
    middleName = db.Column(db.String(100))
    lastName = db.Column(db.String(100), index=True, nullable=False)
    suffix = db.Column(db.String(50))
class Address(NameMixin, EntityMixin, BaseModel):
    # Another normalization table to put addresses all in one place; addresses are not PII so we don't need to encrypt them
    addrLine1 = db.Column(db.String(100), index=True, nullable=False)
    addrLine2 = db.Column(db.String(100), default=None)
    city = db.Column(db.String(100), nullable=False, index=True)
    stateOrProvince = db.Column(db.String(100), nullable=False, index=True)
    country = db.Column(db.String(100), nullable=False, default='USA')
class PhoneNumber(NameMixin, EntityMixin, BaseModel):
    # Another normalization table. Instead of integers we use strings to support alphanumerics
    countryCode = db.Column(db.String(4), default='01', nullable=False)
    areaOrCityCode = db.Column(db.String(4))
    number = db.Column(db.String(12), nullable=False)
class Rates(NameMixin, EntityMixin, BaseModel):
    # This is a simple model to hold default rates. It's not FK'ed to to the client model so it's easy to override.
    # In other words, this will will exclusively benefit the UI
    name = db.Column(db.String(50), nullable=False)
    amount = db.Column(db.Float, nullable=False)
    comment = db.Column(db.String(255), default=None)
class GeneralJournal(NameMixin, EntityMixin, BaseModel):
    '''
    After a lot of thinking, I've decided to do actual double entry.
    Double entry is going to make closing accounting periods much easier.
    Also, it will correspond more directly to accounting as it is taught so all this will be easier to maintain.
    Transactions are first put into the journal table. Then (probably simultaneously), balances will be calculated in
    the General Ledger.
    uuid is per transaction and will be shared across the ledger and journal.
    '''
    date = db.Column(db.DateTime, nullable=False, index=True)
    debitAmt = db.Column(db.Float)  # This can be null because of splits
    id_ChartOfAccounts_Debit = db.Column(db.Integer, db.ForeignKey('ChartOfAccounts.id'))
    acctDebit = db.relationship("ChartOfAccounts", foreign_keys=[id_ChartOfAccounts_Debit])
    creditAmt = db.Column(db.Float)  # This can be null because of splits
    id_ChartOfAccounts_Credit = db.Column(db.Integer, db.ForeignKey('ChartOfAccounts.id'))
    acctCredit = db.relationship("ChartOfAccounts", foreign_keys=[id_ChartOfAccounts_Credit])
    transName = db.Column(db.String(100))
    note = db.Column(db.String(255))
    desc = db.Column(db.String(255))
    id_TransEntity = db.Column(db.Integer, db.ForeignKey('TransEntity.id'))
    transEntity = db.relationship("TransEntity", foreign_keys=[id_TransEntity])
    id_TransType = db.Column(db.Integer, db.ForeignKey('TypesForEntity.id'))
    transType = db.relationship("TypesForEntity", foreign_keys=[id_TransType])
class GeneralLedger(NameMixin, EntityMixin, BaseModel):
    # This one is for tracking balances. We're going to
    debitBalance = db.Column(db.Integer, nullable=False)
    creditBalance = db.Column(db.Integer, nullable=False)
    debitAmt = db.Column(db.Float)  # This can be null because of splits
    id_ChartOfAccounts_Debit = db.Column(db.Integer, db.ForeignKey('ChartOfAccounts.id'))
    acctDebit = db.relationship("ChartOfAccounts", foreign_keys=[id_ChartOfAccounts_Debit])
    creditAmt = db.Column(db.Float)  # This can be null because of splits
    id_ChartOfAccounts_Credit = db.Column(db.Integer, db.ForeignKey('ChartOfAccounts.id'))
    acctCredit = db.relationship("ChartOfAccounts", foreign_keys=[id_ChartOfAccounts_Credit])
    note = db.Column(db.String(255))
    desc = db.Column(db.String(255))
    id_EntryType = db.Column(db.Integer, db.ForeignKey('TypesUniversal.id'))
    entryType = db.relationship("TypesUniversal", foreign_keys=[id_EntryType])
class TransEntity(NameMixin, EntityMixin, BaseModel):
    # Our customers will do business with other entities like the phone company, insurance companies, etc.
    # This model is to hold the metadata for these counterparties.
    name = db.Column(db.String(100), nullable=False, index=True)
    id_Person = db.Column(db.Integer, db.ForeignKey('Person.id'), nullable=False)  # A contact person
    person = db.relationship("Person", foreign_keys=[id_Person])
    id_Address = db.Column(db.Integer, db.ForeignKey('Address.id'))
    address = db.relationship("Address", foreign_keys=[id_Address])
    id_PhoneNumber = db.Column(db.Integer, db.ForeignKey('PhoneNumber.id'))
    phoneNumber = db.relationship("PhoneNumber", foreign_keys=[id_PhoneNumber])
    emailAddress = db.Column(db.String(254))
class PaymentTypeAccountJunction(NameMixin, EntityMixin, BaseModel):
    # This is the junction table between payment type and sessing ledger
    # In the UI, the user will pick a type and based on what's here, the ledger will end up with the correct splits
    id_PaymentType = db.Column(db.Integer, db.ForeignKey('TypesForEntity.id'), nullable=False, primary_key=True)
    paymentType = db.relationship("TypesForEntity", foreign_keys=[id_PaymentType])
    id_ChartOfAccounts_Credit = db.Column(db.Integer, db.ForeignKey('ChartOfAccounts.id'))
    acctCredit = db.relationship("ChartOfAccounts", foreign_keys=[id_ChartOfAccounts_Credit])
    percentCredit = db.Column(db.Float)
    id_ChartOfAccounts_Debit = db.Column(db.Integer, db.ForeignKey('ChartOfAccounts.id'))
    acctDebit = db.relationship("ChartOfAccounts", foreign_keys=[id_ChartOfAccounts_Debit])
    percentDebit = db.Column(db.Float)
class TypesForEntity(NameMixin, EntityMixin, BaseModel):
    # We were going to have a bunch of different type models.
    # But, it seems more extensible if we have a generic type model with flavors from another model
    # Then we can add new ones later without a schema change
    # This one is for types specific to entities
    name = db.Column(db.String(100), nullable=False, index=True)
    desc = db.Column(db.String(254), default=None)
    identifierType = db.Column(
        db.String(100))  # this field allows the user to designate an identifier like check number
    id_TypeFlavor = db.Column(db.Integer, db.ForeignKey('TypeFlavor.id'), nullable=False)
    typeFlavor = db.relationship("TypeFlavor", foreign_keys=[id_TypeFlavor])
class TypesUniversal(NameMixin, BaseModel):
    # This is like TypesForEntity, but these are universal
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False, index=True)
    desc = db.Column(db.String(254), default=None)
    identifierType = db.Column(
        db.String(100))  # this field allows the user to designate an identifier like check number
    id_TypeFlavor = db.Column(db.Integer, db.ForeignKey('TypeFlavor.id'), nullable=False)
    flavor = db.relationship("TypeFlavor", foreign_keys=[id_TypeFlavor])
class TypeFlavor(NameMixin, BaseModel):
    # See comment on Types. Initial flavors include payment, session, account, entity
    # This model uses the same data application wide so there is no entity field
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False, index=True)
    desc = db.Column(db.String(254), default=None)
class ScheduleCField(NameMixin, BaseModel):
    # This one will go application wide, too, so no entity field
    # It will be the source of an FK relatioship into Chart of Accounts
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False, index=True)
    desc = db.Column(db.String(254), default=None)
class Diagnosis(NameMixin, EntityMixin, BaseModel):
    # This name should be generalized so that we can service more than health care providers
    name = db.Column(db.String(100), nullable=False, index=True)
    desc = db.Column(db.String(254), default=None)
class Role(NameMixin, BaseModel):
    # We have to do authorizations. So there will be roles. Users can have more than one role so we'll also have a junction table
    # We are not going to make the roles configurable by end users just yet.
    name = db.Column(db.String(100), nullable=False)
    desc = db.Column(db.String(255), default=None)
class UserRoleJunction(NameMixin, EntityMixin, BaseModel):
    # In later versions, we may add fields to this so we can have scope
    id_User = db.Column(db.Integer, db.ForeignKey('User.id'), primary_key=True)
    user = db.relationship("User", foreign_keys=[id_User])
    id_Role = db.Column(db.Integer, db.ForeignKey('Role.id'), primary_key=True)
    role = db.relationship("Role", foreign_keys=[id_Role])