Data Architecture

Overview

Starchart needs to store different pieces of information related to users, their subdomains, and TLS/SSL certificates.

Technologies

Data is stored in MySQL (production) or SQLite (development/testing). We use both the Prisma ORM to work directly with data, and PowerDNS, which uses MySQL and SQLite via backends.

Data Requirements

The exact data and schemas used in Starchart are still be designed. However, we provide an initial set of requirements for the data.

The system needs to manage and store a number of different types of data, including:

Users
Subdomains
Certificates

Users

Users are authenticated using Seneca's Azure Active Directory via SAML2. Once authenticated, the following information is known about a user (all specifics have been changed or revoked below):

{
  "issuer": "https://sts.windows.net/...idp-uuid.../",
  "inResponseTo": "_851...",
  "sessionIndex": "_dfa...",
  "nameID": "username@seneca-domain.ca",
  "nameIDFormat": "urn:oasis:names:tc:SAML:1.1:nameid-format:emailAddress",
  "http://schemas.microsoft.com/identity/claims/tenantid": "...app-uuid...",
  "http://schemas.microsoft.com/identity/claims/objectidentifier": "...uuid...",
  "http://schemas.microsoft.com/identity/claims/displayname": "Full Name",
  "http://schemas.microsoft.com/identity/claims/identityprovider": "https://sts.windows.net/...app-uuid.../",
  "http://schemas.microsoft.com/claims/authnmethodsreferences": "http://schemas.microsoft.com/ws/2008/06/identity/authenticationmethod/password",
  "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname": "Firstname",
  "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/surname": "Lastname",
  "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress": "username@seneca-domain.ca",
  "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name": "username@seneca-domain.ca",
  "sAMAccountName": "username"
}

When a user logs into the system for the first time, a new User record is created, and the following record is created using this information:

Column Name	Initial Value	Note
`username`	"username"	Unique, Primary Key, the username portion of http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress
`email`	"username@address.com"	http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress
`name`	"Full Name"	http://schemas.microsoft.com/identity/claims/displayname
`createdAt`	Current Date and Time
`expiresAt`	1 Year from Today	Unless renewed, expire in 1 year (i.e., delete all subdomains, revoke certificate, delete User)
`disabled`	false	Accounts are enabled by default, but can be disabled (subdomains disabled, account locked)

Subdomains

A User can have many Subdomains. The User's Subdomains are stored in two places. First, in a Subdomain table:

Column Name	Value	Note
`username`	"username"	Foreign Key to User table
`subdomain`	"ccp555.username.example.com."	Canonical name of subdomain in PowerDNS (note trailing `.`)
`description`	"CCP555 AWS Load Balancer"	Free-form information about how this domain is being used
`createdAt`	Current Date and Time

The second place that subdomains are stored is in the PowerDNS authoritative DNS server using the HTTP API. PowerDNS also stores its information in the database, but is managed separately (i.e., not by us). PowerDNS stores subdomains as RRSet objects within a Zone (e.g., example.com).

Each RRSet object includes the following information:

Name	Value	Note
`name`	"ccp555.username.example.com."	Canonical name of subdomain, matches Subdomain table
`type`	"CNAME"	DNS Record type, one of "A", "AAAA", or "CNAME"
`ttl`	3600	DNS TTL in seconds
`record.content`	"some.external.domain"	Value of the DNS record (e.g., ip address, domain name)
`record.disabled`	false	Whether or not this subdomain is disabled (maps to `disabled` in `User` table

PowerDNS allows an RRSet to include one or more comments, but we use our own tables (User, Subdomain) to keep track of this information.

Certificates

Starchart allows a user to create a single, wildcard certificate for *.username.example.com. This certificate is then usable for any current or future subdomain that the user might create.

The user initiates the creation of the certificate (i.e., it doesn't happen automatically). Upon successful creation, the following are created:

Certificate Signing Request (CSR)
Certificate
Private Key

Each of these is a string in PEM form, for example:

We need to decide whether to store any of this information in the database on behalf of our users. There is no single approach here, and each option has both usability and security implications:

We could store nothing, and simply present the certificates to the user upon creation. The user would be responsible for safely storing these, and not losing them. This has the benefit of not exposing the private key to anyone other than the user. It also means that we can't do automatic renewals. Let's Encrypt certificates expire in 90 days. The user would have to manually request a new certificate.
We could store the certificate and private key in the database upon creation. This would decouple the initial "order" from the final "creation," which can take some time (though this is likely not a big deal). Storing this data means that anyone who has access to the database (e.g., administrators) would also have access. Storing the data would also enable automatic renewals, which requires the certificate and private key to be stored somewhere after a successful renewal completes.

If we go with option 1., we probably don't need any data storage about certificates, since everything would be done manually, and certificate storage would be up to the user.

If we go with option 2., we need to parse the resulting certificate (e.g., using X506Certificate) and store at least the following:

Column Name	Value	Note
`id`	1	Auto-Increment, Primary Key
`username`	"username"	Foreign Key to User table
`subject`	"CN=*.username.example.com"	The certificate's subject
`certificate`	"-----BEGIN CERTIFICATE-----..."	PEM formatted certificate
`privkey`	"-----BEGIN CERTIFICATE-----..."	PEM formatted private key
`validFrom`	Date and Time from which this certificate is valid	When the certificate was successfully created
`validTo`	Date and Time until which this certificate is valid	Let's Encrypt certificates expire in 90 days by default
`renewed`	false	Whether or not this certificate has been renewed, and whether or not to try renewing (again)

NOTE: if we do renewals, a User can have multiple certificates, some of which may be valid at the same time (e.g., if we renew within 30 days of expiry, both certificates would be valid for 30 days). We would probably only want to show user's valid certificate records in the UI.