Data Backup Service

The data backup service is an ETL pipeline designed to extract data from the Woodcore CBA MySQL database, transform it through table joins, table and column renaming, and load it into a destination PostgreSQL database. This process is executed once a day, precisely at 1:05 a.m. every day.

Repository Name: atlas-etl

There are two ways backups are sent:

  1. Default Backup - This is called default because it requires no input from the tenant, this backup is immediately activated at 'go-live' after permission is approved. The tenant's data is extracted and loaded into an internal Woodcore Postgres database, and the dump file is sent to the tenant's email address daily.
    The tenant must extract the data dump from the most recent email to get the latest backup.
  2. Specialized Backup - In this setup, the data extracted is seamlessly uploaded to the remote database managed by the tenant on a daily basis. Woodcore requires write access to this database to ensure the smooth running of this process. Tenants are required to provide their backup credentials via a form linked to the backup service. At the time of submission, Woodcore verifies if write access is granted; if not, the submission will not be successful.

How To Decrypt Backups (Default)

Check your inbox for the most recent email from [email protected], which looks like this:

Step 1

Create a new folder on your system in your desired location (e.g., desktop, documents).

Step 2

Download the SQL backup file and Decryption keys and move the files to the new folder created in Step 1.

Step 3

Open up your terminal/command prompt and navigate to the new folder created in Step 1.

Step 4

Run the following command:

openssl smime -decrypt -in [sql backup filename] -binary -inform DEM -inkey [decryption key filename] -out WoodCoreInc.sql.gz

📘

Substitute the command with the correct file names where appropriate.

Step 5

Extract the decrypted file using a file extractor that supports a .gz extension like Gzip or WinRarData.

👍

At this point, you have successfully extracted the data. You can load it into your database.

Understanding the Data

The data extracted in this process range from configuration to account and transaction information. The table below shows the list of tables available in the backup.

ERD (Entity Relation Diagram) to help with the database understanding.

Tables
wc::cashierActionRequest
wc::cashiers
wc::cashierTransactions
wc::charges
wc::customerAddress
wc::customers
wc::depositProductTermAndPreclosure
wc::depositTransactionOnHold
wc::employees
wc::glAccounts
wc::journalEntries
wc::loanAccount
wc::loanAccountCharge
wc::loanAccountTransaction
wc::loanChargePaidBy
wc::loanGuarantor
wc::loanInArrears
wc::loanOfficerAssignHistory
wc::loanPaidInAdvance
wc::loanProductCharges
wc::loanProducts
wc::loanRepaymentSchedule
wc::loanRepaymetScheduleHistory
wc::loanRescheduleRequest
wc::savingsAccount
wc::savingsAccountTransactions
wc::savingsCharge
wc::savingsProduct
wc::systemUsers