ACUITY DB overview

General info

By default, ACUITY PostgreSQL database name in acuity_db; it is possible to use a different name if you need, though. It contains four different schemas:

• acuity - main schema containing all business data; the current article describes mostly this one
• maintenance_tasks - an empty schema, probably unused; if so, it will be deleted soon
• oracle - schema used by orafce extension; it contains emulations of some Oracle DB features for PostgreSQL (in past, ACUITY used Oracle and then was migrated to PostgreSQL). orafce is a third party solution, not part of ACUITY.
• acuity_utils - schema containing several custom utility functions All three main ACUITY applications (VAHub, VASecurity and AdminUI) use the same database.

acuity schema tables

The database is quite big and complicated, so to make it more readable the list of table is logically separated in sections. Technically, the sections are not isolated from other tables, of course, but each section relates to some particular task or group of neighbor tasks, so it's easier to review them in such a way.

Some tables in this schema contain static data that is initialized when the system is being deployed (in ACUITY, such operations are performed by Flyway) and (normally) never updated in any way during the ACUITY applications work. Other tables are for dynamic data that can be uploaded, updated or deleted when applications are working. Tables with static data will be mentioned as such below; other table should be considered as containing dynamic data.

DB description pages

In concern of readability, database description is separated into several pages:

• Result data tables — these tables store resulting data of ETL process performed by AdminUI. That's what is presented by VAHub. There is no static data in them.
• Mapping data tables — these tables store all the service information required for AdminUI to map correctly the source files data to result tables data during the ETL process. It's the most complicated part of the DB. There is static as well as dynamic data in them.
• Third party solution tables — these tables are used (and maybe created) by some libraries we use in ACUITY for their service purposes. Their names and connections are normally not defined by us, and ACUITY code mostly does not manipulate with them directly.
• Other data tables — here is the description of all other useful tables of acuity schema.
• Tables to delete - these tables are supposedly useless and should be removed in time.