Database
The PIMS solution currently uses a Microsoft SQL Server hosted within a Linux container. This database provides the primary data storage for the API and contains all Property information.
Microsoft SQL Server 2019
The primary purpose of this datasource is to maintain an inventory of properties (Parcels, Buildings and Projects). User roles and claims are managed by keycloak, but are also contained within this datasource. There are also a number of supporting lists to support the front-end application user experience. MS SQL Server Enterprise edition is being used (this requires a license).
It has been discovered that under load MS SQL Server will run out of memory within a container that has less than 6 GB maximum memory. A container is configured with a minimum and maximum memory setting. The minimum can be as low as you want it (i.e. 100 MB). The maximum however must be 6 GB or more. Presently MS SQL Server doesn't require more than 1 GB of memory, even under load, but for some reason it still requires the 6 GB being available.
The database is backed up daily by a cron job service provided by a pod running in each namespace/environment (dev, test, prod). This service is fully configurable and also provides a way to adhoc backup, verify and restore.
| Link | Description |
|---|---|
| DevOps configuration | How to configure database backup for each environment |
| Container image | Detailed information about the database backup container and configuration |
| Disaster Recovery | How to use the database backup and recovery scripts |
As the current primary purpose of the PIMS DB is to manage inventory of properties, the structure revolves around the the two property objects Parcels and Buildings. A Parcel object represents land, and it can contain many Building objects. Additionally the DB provides a structure to support properties being owned by an Agency, so that the appropriate Users have authority to manage it.
Most objects will also include tracking columns to identify when it was created or updated and who created and updated it. Additionally objects will natively provide optimistic concurrency enforcement, which will ensure data is not overwritten in a multi-user concurrent solution.
The following are the primary objects;
| Object | Description |
|---|---|
| Parcels | Land inventory |
| Buildings | Building inventory |
| Projects | Project inventory (disposal, aquisition) |
| Users | Users accounts within PIMS |
| Roles | Roles that authorize abilities within PIMS |
| Agencies | A ministry, crown corporation or entity that owns properties |
| Addresses | A physical address to a location |
| Workflows | Light workflow engine provide a way to control the lifecycle of projects |
| Notification Templates | Manage notification templates that are used for sending email |
| Notification Queue | A queue containing all notifications that have been generated |
The following provide a way to manage lists or collections within the solution;
| Object | Description |
|---|---|
| PropertyTypes | List of property types [land | building] |
| PropertyClassifications | List of property classifications |
| Cities | List of cities |
| Provinces | List of provinces |
| BuildingConstructionTypes | List of building construction types |
| BuildingPredominateUses | List of building predominate uses |
| BuildingOccupantTypes | List of building occupant types |
| TierLevels | List of project tier levels for projects |
| Project Status | List of project status that represent stages a project will go through |
| Project Risks | List of project risks |
| Tasks | List of tasks to complete a process or stage. These are associated to project status and workflows |