A DB schema to do simple accounting that is HIPAA compliant

Entity Table and its FKs
User is pretty straight forward
Client is normalized as well.
Very similar to the ClientJournal
Simpler than ClientLedger
accounting goodness
Are you excited? Yeah, I didn’t think you would be.
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])

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store