This repository presents the reference implementation of a product data server as part of the product data network buildingenvelopedata.org. Before deploying this repository, machine can be used to set up the machine.
The API specification of the product data servers is available in the repository api. There is also a visualization of the API of a product data server.
This repository is deployed as the product data server of TestLab Solar Facades of Fraunhofer ISE.
If you have a question for which you don't find the answer in this repository, please raise a new issue and add the tag question
! All ways to contribute are presented by CONTRIBUTING.md. The basis for our collaboration is decribed by our Code of Conduct.
- Open your favorite shell, for example, good old
Bourne Again SHell, aka,
bash
, the somewhat newer Z shell, aka,zsh
, or shiny newfish
. - Install Git by running
sudo apt install git-all
on Debian-based distributions like Ubuntu, orsudo dnf install git
on Fedora and closely-related RPM-Package-Manager-based distributions like CentOS. For further information see Installing Git. - Clone the source code by running
git clone git@github.com:building-envelope-data/database.git
and navigate into the new directorydatabase
by runningcd ./database
. - Initialize, fetch, and checkout possibly-nested submodules by running
git submodule update --init --recursive
. An alternative would have been passing--recurse-submodules
togit clone
above. - Prepare your environment by running
cp ./.env.sample ./.env
,cp ./frontend/.env.local.sample ./frontend/.env.local
, and adding the line127.0.0.1 local.solarbuildingenvelopes.com
to your/etc/hosts
file. - Install Docker Desktop, and GNU Make.
- List all GNU Make targets by running
make help
. - Generate and trust a self-signed certificate authority and SSL certificates
by running
make ssl
. If you are locally working on the metabase and the database and if you need them to communicate over HTTPS, then instead of runningmake ssl
, make theCERTIFICATE_AUTHORITY_*
variable values in the.env
file match the ones from the metabase (these variables match in the.env.sample
files), copy the certificate authority files from the directories./ssl
,./backend/ssl
, and./frontend/ssl
of the metabase project into the respective directories in the database project (if the repository reside alongside each other by runningmkdir ./ssl ./backend/ssl ./frontend/ssl && cp ../metabase/ssl/ca.* ./ssl && cp ../metabase/backend/ssl/ca.* ./backend/ssl && cp ../metabase/frontend/ssl/ca.* ./frontend/ssl
), and run the commandmake generate-ssl-certificate
. - Generate JSON Web Token (JWT) encryption and signing certificates by running
make jwt-certificates
. - Generate and export a GnuPG key with the passphrase
${GNUPG_PRIVATEKEY_PASSPHRASE}
to the file./backend/src/gpg-keys/${GNUPG_PRIVATEKEY_FILE_NAME}
by runningmake NAME=${name} COMMENT=${comment} EMAIL=${email} gpg
with your information filled in. - Start all services and follow their logs by running
make up logs
. - To see the web frontend navigate to
https://local.solarbuildingenvelopes.com:5051
in your web browser, to see the GraphQL API navigate tohttps://local.solarbuildingenvelopes.com:5051/graphql/
, and to see sent emails navigate tohttps://local.solarbuildingenvelopes.com:5051/email/
. Note that the port is5051
by default. If you set the variableHTTPS_PORT
within the./.env
to some other value though, you need to use that value instead within the URL.
In another shell
- Drop into
bash
with the working directory/app
, which is mounted to the host's./backend
directory, inside a fresh Docker container based on./backend/Dockerfile
by runningmake shellb
. If necessary, the Docker image is (re)built automatically, which takes a while the first time. Note that the Docker image and containers try to use the same user and group IDs as the ones on the host machine. This has the upside that files created within containers in mounted directories are owned by the host user. It has the downside that the Docker image may fail to build because the IDs may already be taken by other users and groups in the base image. This happens for example if you areroot
on the host machine with the user and group IDs 0. If there is an ID collision, then you can either change the user and group ID on the host machine (for example by logging in as another user) or you can replace all occurrences ofshell id --group
andshell id --user
inMakefile
andMakefile.production
by fixed non-colliding IDs like 1000. If you know a better way, please let use know on GitHub. - List all backend GNU Make targets by running
make help
. - For example, update packages and tools by running
make update
. - Drop out of the container by running
exit
or pressingCtrl-D
.
The same works for frontend containers by running make shellf
.
On the very first usage:
- Install Visual Studio Code and open it.
Navigate to the Extensions pane (
Ctrl+Shift+X
). Add the extension Remote Development. - Navigate to the
Remote Explorer
pane. Hover over the running
database-backend-*
container (if it is not running, then runmake up
in a shell inside the project directory) and click on the "Attach in Current Window" icon. In the Explorer pane, open the directory/app
, which is mounted to the host's./backend
directory. Navigate to the Extensions pane. Add the extensions C# Dev Kit, IntelliCode for C# Dev Kit, GraphQL: Language Feature Support, and GitLens — Git supercharged. - Navigate to the
Remote Explorer
pane. Hover over the running
database-frontend-*
container and click on the "Attach in New Window" icon. In the Explorer pane, open the directory/app
, which is mounted to the host's./frontend
directory. Navigate to the Extensions pane. Add the extensions GraphQL: Language Feature Support, and GitLens — Git supercharged.
Note that the Docker containers are configured in ./docker-compose.yml
in
such a way that Visual Studio Code extensions installed within containers are
retained in Docker volumes and thus remain installed across make down
and
make up
cycles.
On subsequent usages: Open Visual Studio Code, navigate to the "Remote Explorer" pane, and attach to the container(s) you want to work in.
The following Visual Studio Code docs may be of interest for productivity and debugging
To debug the
ASP.NET Core web application,
attach Visual Studio Code to the database-backend-*
container,
press Ctrl+Shift+P
, select "Debug: Attach to a .NET 5+ or .NET Core process",
and choose the process /app/src/bin/Debug/net9.0/Database run
titled
Database
or alternatively navigate to the "Run and Debug" pane
(Ctrl+Shift+D
), select the launch profile ".NET Core Attach", press the
"Start Debugging" icon (F5
), and select the same process as above. Then, for
example, open some source files to set breakpoints, navigate through the
website https://local.buildingenvelopedata.org:4041, which will stop at
breakpoints, and inspect the information provided by the debugger at the
breakpoints. For details on debugging C# in Visual Studio Code, see
Debugging.
Note that the debugger detaches after the
polling file watcher
restarts the process, which happens for example after editing a source file
because dotnet watch
is configured in ./docker-compose.yml
with
DOTNET_USE_POLLING_FILE_WATCHER
set to true
. As of this writing, there is
an
open feature request to reattach the debugger automatically.
There also are multiple extensions like
.NET Watch Attach
and
.NET Stalker Debugger
that attempt to solve that. Those extensions don't work in our case though, as
they try to restart dotnet watch
themselves, instead of waiting for the
polling file watcher of dotnet watch
to restart
/app/src/bin/Debug/net9.0/Database run
and attach to that process.
After migrating the PostgreSQL database or changing the database
schema
manually or upgrading Npgsql, the service backend
may throw exceptions
regarding the object-relational mapping (Npgsql or EF Core). In that case it
may be necessary to restart the service backend
, for example, by running
make down up
and it may even be necessary recreate the database from scratch
by running make down remove-data up
. Note that the latter will remove all
data from PostgreSQL, recreate the database and its schema, and seed it
freshly.
After changing the domain model in ./backend/src/data
, you probably need to
migrate the database by dropping into make shellb
, adding a migration make NAME=${MIGRATION_NAME} add-migration
, generating a migration script make FROM=${PREVIOUS_MIGRATION} TO=${NEW_MIGRATION} generate-migration-script
, and
executing it make SQL=${SCRIPT_PATH} sql
.
When your hard-disk starts to grow full, it may be the case that Docker does
not clean-up anonymous volumes properly. You can do so manually by running
docker system prune
potentially with the arguments --volumes
and/or
--all
. Note that this may result in loss of data. It may also be the case
that the log files grew huge. You can delete them by running
rm ./backend/src/logs/*
.
When the frontend
Docker image does not build in production because of an
unused import in an automatically generated file, for example, one in the
directory ./frontend/__generated__
, then temporarily ignore TypeScript
build errors by adding the following lines to ./frontend/next.config.js
, for
example with vi
or nano
in a shell on the deployment machine:
typescript: {
ignoreBuildErrors: true,
},
The same can happen in development when running make build
(or yarn run build
) in the shell entered by make shellf
. In that case, remove the
offending import manually in the file and try again, for example using tail
like so tail -n +5 ./__generated__/queries/... > x.tmp && mv x.tmp ...
. Do
not disable build errors in development because when you do so, build errors in
non-generated files may leak into the code base.
For information on using Docker in production see Configure and troubleshoot the Docker daemon and the pages following it.
- Use the sibling project machine and its instructions for the first stage of the set-up.
- Enter a shell on the production machine using
ssh
. - Change into the directory
/app
by runningcd /app
. - Clone the repository twice by running
for environment in staging production ; do git clone git@github.com:building-envelope-data/database.git ./${environment} done
- For each of the two environments staging and production referred to by
${environment}
below:- Change into the clone
${environment}
by runningcd /app/${environment}
. - Prepare the environment by running
cp ./.env.${environment}.sample ./.env
,cp ./frontend/.env.local.sample ./frontend/.env.local
, and by adjusting variable values in the copies to your needs, in particular, by setting passwords to newly generated ones, where random passwords may be generated by runningopenssl rand -base64 32
. Here is some information on what the variables meanNAME
is the name Docker project name, in particular, it is the prefix of the Docker container names listed bydocker ps --all
;HOST
is the domain name with sub-domain of the deployment, in particular, it is used to make resource locators absolute;HTTP_PORT
is the HTTP port on which the reverse proxy NGINX listens for requests;METABASE_HOST
is the domain name with sub-domain of the metabase, in particular, to use it as OpenId Connect provider and to ask it for information about logged-in users needed for authorization;DATABASE_ID
is the UUID that was assigned to this product-data database upon registering it at the metabase;VERIFICATION_CODE
is the verification code that was generated for this product-data database upon registering it at the metabase;OPEN_ID_CONNECT_CLIENT_SECRET
is the OpenId Connect client secret of this product-data database as a client of the metabase acting as identity provider (the client secret is given when registering an OpenId Connect client at the metabase);JSON_WEB_TOKEN_ENCRYPTION_CERTIFICATE_PASSWORD
andJSON_WEB_TOKEN_SIGNING_CERTIFICATE_PASSWORD
are passwords used to encrypt and sign JSON web tokens (JWT) used by OpenId Connect;SMTP_HOST
andSMTP_PORT
are host and port of the message transfer agent to be used to send emails through the Simple Mail Transfer Protocol (SMTP);RELAY_SMTP_HOST
,RELAY_SMTP_PORT
, andRELAY_ALLOWED_EMAILS
are host and port of the message transfer agent and a list of allowed email addresses to send emails to even in the staging environment.
- Prepare PostgreSQL by generating new password files by running
make --file=Makefile.production postgres_passwords
and creating the database by runningmake --file=Makefile.production createdb
. - Generate JSON Web Token (JWT) encryption and signing certificates by running
make --file=Makefile.production jwt-certificates
.
- Change into the clone
- Draft a new release with a new version according to
Semantic Versioning by running the GitHub action
Draft a new release
which, creates a new branch named
release/v*.*.*
, creates a corresponding pull request, updates the Changelog, and bumps the version inpackage.json
, where*.*.*
is the version. Note that this is not the same as "Draft a new release" on Releases. - Fetch the release branch by running
git fetch
and check it out by runninggit checkout release/v*.*.*
, where*.*.*
is the version. - Prepare the release by running
make prepare-release
in your shell, review, add, commit, and push the changes. In particular, migration and rollback SQL files are created in./backend/src/Migrations/
which need to be reviewed --- see Migrations Overview and following pages for details. - Publish the new release
by merging the release branch into
main
whereby a new pull request frommain
intodevelop
is created that you need to merge to finish off.
- Enter a shell on the production machine using
ssh
. - Back up the production database by running
make --directory /app/production --file=Makefile.production BACKUP_DIRECTORY=/app/production/backup backup
. - Change to the staging environment by running
cd /app/staging
. - Restore the staging database from the production backup by running
make --file=Makefile.production BACKUP_DIRECTORY=/app/production/backup restore
. - Adapt the environment file
./.env
if necessary by comparing it with the./.env.staging.sample
file of the release to be deployed. - Deploy the new release in the staging environment by running
make --file=Makefile.production TARGET=${TAG} deploy
, where${TAG}
is the release tag to be deployed, for example,v1.0.0
. - If it fails after the database backup was made, rollback to the previous
state by running
make --file=Makefile.production rollback
, figure out what went wrong, apply the necessary fixes to the codebase, create a new release, and try to deploy that release instead. - If it succeeds, deploy the new reverse proxy that handles sub-domains by
running
cd /app/machine && make deploy
and test whether everything works as expected and if that is the case, continue. Note that in the staging environment sent emails can be viewed in the web browser underhttps://staging.solarbuildingenvelopes.com/email/
and emails to addresses in the variableRELAY_ALLOWED_EMAILS
in the.env
file are delivered to the respective inboxes (the variable's value is a comma separated list of email addresses). - Change to the production environment by running
cd /app/production
. - Adapt the environment file
./.env
if necessary by comparing it with the./.env.staging.sample
file of the release to be deployed. - Deploy the new release in the production environment by running
make --file=Makefile.production TARGET=${TAG} deploy
, where${TAG}
is the release tag to be deployed, for example,v1.0.0
. - If it fails after the database backup was made, rollback to the previous
state by running
make --file=Makefile.production rollback
, figure out what went wrong, apply the necessary fixes to the codebase, create a new release, and try to deploy that release instead.
The file Makefile.production
contains GNU Make targets to manage Docker
containers like up
and down
, to follow Docker container logs with logs
,
to drop into shells inside running Docker containers like shellb
for the
backend service and shellf
for the frontend service and psql
for the
database service, and to list information about Docker like list
and
list-services
.
And the file contains GNU Make targets to deploy a new release or rollback it
back as mentioned above. These targets depend on several smaller targets like
begin-maintenance
and end-maintenance
to begin or end displaying
maintenance information to end users that try to interact with the website, and
backup
to backup all data before deploying a new version, migrate
to
migrate the database, and run-tests
to run tests.
If for some reason the website displays the maintenance page without maintenance happening at the moment, then drop into a shell on the production machine, check all logs for information on what happened, fix issues if necessary, and end maintenance. It could for example happen that a cron job set-up by machine begins maintenance, fails to do its actual job, and does not end maintenance afterwards. Whether failing to do its job is a problem for the inner workings of the website needs to be decided by some developer. If it for example backing up the database fails because the machine is out of memory at the time of doing the backup, the website itself should still working.
If the database container restarts indefinitely and its logs say
PANIC: could not locate a valid checkpoint record
for example preceded by LOG: invalid resource manager ID in primary checkpoint record
or LOG: invalid primary checkpoint record
, then the database is
corrupt. For example, the write-ahead log (WAL) may be corrupt because the
database was not shut down cleanly. One solution is to restore the database
from a backup by running
make --file=Makefile.production BACKUP_DIRECTORY=/app/data/backups/20XX-XX-XX_XX_XX_XX/ restore
where the X
s need to be replaced by proper values. Another solution is to
reset the transaction log by entering the database container with
docker compose --file=docker-compose.production.yml --project-name database_production run database bash
and dry-running
gosu postgres pg_resetwal --dry-run /var/lib/postgresql/data
and, depending on the output, also running
gosu postgres pg_resetwal /var/lib/postgresql/data
Note that both solutions may cause data to be lost.
If one field in the SQL database needs to be updated and there is no GraphQL
mutation available, then you may update it in PostgreSQL directly as
illustrated in the following example. Test it in the staging
environment
under /app/staging before doing it in production
under /app/production.
- Drop into a shell on the server as user
cloud
by runningssh -CvX -A cloud@IpAdressOfCloudServer
. - Navigate to the production environment by running
cd /app/production
. - Make a database backup by running
DATE=$(date +"%Y-%m-%d_%H_%M_%S")
andmake --file=Makefile.production BACKUP_DIRECTORY=/app/data/backups/${DATE} backup
- Navigate to the staging environment by running
cd /app/staging
. - Load the backup into the staging database by running
make --file=Makefile.production BACKUP_DIRECTORY=/app/data/backups/${DATE} restore
. - Drop into
psql
by runningmake --file=Makefile.production psql
. - List all tables in the schema
database
by running\dt database.*
. - List all optical data records by running
select * from database.optical_data;
and remember for example one identifier of a record that you want to update. - Update a single field by running
update database.optical_data set "Description" = '...' where "Id" = 'f07499ab-f119-471f-8aad-d3c016676bce';
. - Delete a faulty record by running
delete from database.optical_data where "Id" = 'f07499ab-f119-471f-8aad-d3c016676bce';
.
The product identifier service should provide the following endpoints:
- Obtain a new product identifier possibly associating internal meta information with it, like a custom string or a JSON blob
- Update the meta information of one of your product identifiers
- Get meta information of one of your product identifiers
- Get the owner of a product identifier (needed, for example, by the IGSDB to check that the user adding product data with a product identifier owns the identifier)
- List all your product identifiers
- Request the transferal of the ownership of one or all of your product identifiers to another (once the receiving user agrees, the transferal is made)
- Respond to a transferal request
How to obtain a unique product identifier and add product data to some database:
- Create an account at a central authentication service, that is, a domain specific and lightweight service like Auth0 managed by us (the details of how users prove to be a certain manufacturer are still open)
- Authenticate yourself at the authentication service receiving a JSON web token (this could be a simple username/password authentication scheme)
- Obtain a new product identifier from the respective service passing your JSON web token as means of authentication
- Add product data to some database like IGSDB passing the product identifier and your JSON web token
JSON web tokens are used for authentication across different requests, services, and domains.
Product identifiers are randomly chosen and verified to be unique 32, 48, or 64 bit numbers, which can be communicated for easy human use as proquints there are implementations in various languages. We could alternatively use version 4 universally-unique identifiers; I consider this to be overkill as it comes with a performance penalty and our identifiers do not need to be universally unique. Either way, those identifiers do not replace primary keys.
Randomness of identifiers ensures that
- the product identifier does not encode any information regarding the product, like its manufacturer, which would, for example, be problematic when one manufacturer is bought by another
- a user cannot run out of product identifiers, because there is no fixed range of possible identifiers per user
- it's unlikely that flipping one bit or replacing one letter in the proquint representation by another results in a valid identifier owned by the same user
We may add some error detection and correction capabilities by, for example, generating all but the last 4 bits randomly and using the last 4 bits as some sort of checksum.