Database Guide

Avni uses PostgreSQL as the database. It particularly depends on a couple of features quite heavily - JSONB data type and row-level security. JSONB allows for user-defined schema and row-level security for achieving multi-tenancy. If you planning to do Avni server-side, product development then reading up on these two concepts is highly recommended. But if you plan to use Avni to implement for your organisation then these two concepts are not necessary reading.

Avni's database schema documentation is available here, but if you are here for the first time then do read the rest of the documentation here before browsing through the schema documentation.

Avni database can be logically broken down into a smaller-cohesive group of tables - which for our purposes of understanding could be called modules (to reiterate, this is only for understanding purposes - Avni database doesn't have these modules in the database in any form). The functions of the tables and key-columns are provided in dbdocs.

An explanation for a few columns which are repeated across the tables

  • organisation_id: This column indicates the organisation to which a row belongs (since Avni uses row-level multi-tenancy.
  • is_voided or active: This is used to perform soft delete of data
  • id: the primary key of a table
  • uuid: identifier via which an externally integrating system can refer to records in Avni. id should not be used for this purpose.
  • version: although this column is present, please ignore it because this column is not used as the functionality around it has not been developed fully.

Foundational modules

Framework

Organisation

Avni is multi-tenant with multiple organisation's data residing within the same schema - protected by row-level security. Avni also supports a group of organisations with one master organisation.

Work area

For more about work-area please refer to this.

Master data tables

For few commonly required entities recognised, hence recognised by the platform.

User-defined data model

These tables allow the user of the platform (aka implementer) to define their data model. These tables could be further sub-grouped into form, concept and types tables. Concept tables describe your data independent of how it has been captured. Form tables describe how the data should be captured. *Types tables allow you to define the high-level relationship between your data entities, as explained in section 2 and 4 here.


Transaction data

Transaction data


Functional modules

Note that here tables for transaction and meta/master data are grouped together.

Identifiers

Identifiers have been documented here.

Checklist


Application behaviour

Rules

User

Application settings

Access Control


Others

Translations

Account

Telemetry and logs