When the app updates its database

SOP SYS-09 · Technical systems manual

1 Purpose

Know what the app does to the shop’s database when it starts after a change is merged, check that it worked, and put things right if it did not.

2 Applies to

The person responsible for the shop’s database, after merging any change that adds a file to the sql/ folder, and whenever Admin shows The shop’s database could not be brought up to date.

3 Before you begin

The shop’s app is running. See SYS-03 Start, stop and check the shop’s app.

4 What happens when the app starts

The shop’s rules arrive as numbered files in the sql/ folder, one per feature, for example 34_staff.sql. The shop’s database keeps its own list of the files already in it.

Each time the app starts, it compares that list with the sql/ folder:

Nothing new: it does nothing.
A new file: it first takes a backup, named with before-update, in the backups/ folder. See SYS-08 Back up and restore the shop’s data.
It adds each new file in number order. Each file goes in completely, or not at all.
If a file cannot be added, it stops there. The database is exactly as it was before that file. The app still starts, and Admin shows a red box naming the file and the backup.

It never adds a file twice, and it never rebuilds the database or deletes anything.

5 Procedure

5.1 Check an update after merging

Restart the app so it picks up the merged files. In a terminal in the dog-groomer-platform folder, type this command and press Enter:
```
docker compose restart api
```
Wait about 30 seconds, then type this command and press Enter:
```
docker compose logs api --tail 20
```
Or, without a terminal: open Admin and click Database and backups. It shows the same thing, with every section and every backup. See MGR-11 Check the database and backups.
Read the lines that start with [database update]:
- grooming_demo is up to date followed by added and the file names: the new files went in. You are finished.
- grooming_demo is up to date followed by a number of sections, and nothing added: there was nothing new.
- could not be added, so the update stopped there: go to Put a failed update right.

5.2 Put a failed update right

Open Admin. The red box The shop’s database could not be brought up to date names the file that failed and the backup taken first. Click What the database said to see the error.
Do not restore the backup yet. The database is still as it was before the failed file, so nothing has been lost. Only the new feature is missing.
Send the file’s name and the error to whoever wrote the change. The fix is a corrected file, merged like any other change.
When the corrected file is merged, repeat steps 1 to 3. The update tries again, and the red box goes away when it succeeds.

Restore the backup (see SYS-08) only if the shop’s records look wrong after an update that said it succeeded.

6 Result

The log says grooming_demo is up to date, and Admin shows no red box.

7 If something goes wrong

7.1 The backup before the update did not work, so nothing was added.

The update stopped before changing anything, because it could not take a backup first. The most common reason is a full disk. Free some space, or move old backups from backups/ to a USB drive, then repeat steps 1 to 3.

7.2 The backend answered 500 after a merge

The app has not restarted since the merge, so the new files are not in the database yet. Repeat steps 1 to 3.

8 For whoever writes a new section file

Give it the next free number, and start it with SET search_path = groom, public; like the others.
It runs once, in one transaction, on a database that already has data in it. It must work on that database, not only on an empty one.
ALTER TYPE … ADD VALUE works, but the new value cannot be used later in the same file. Put any use of it in the next file.
Never edit a file that has already been merged: the shop’s database will not run it again. Make a new file instead.