Database schema

Disclaimer

Understanding the basics of our schema model

See also: Database schema for Confluence backups, Database schema for Jira backups

Table prefix

• Prefix in Confluence: AO_32F7CE

• Prefix in Jira: AO_42D05A

• Prefix for PSEA in Confluence: AO_B895AB

All our tables are stored with the prefix “AO_32F7CE” corresponding to the plugin key in md5.

Columns are uppercase (and may require quotes)

Most databases assume that table names are lowercase, even when you type SELECT SPACEKEY, KEY, BASELINE FROM AO_32F7CE_DBREQUIREMENT.

So you have to put double-quotes ("), or backticks in weird databases (`) : SELECT "SPACEKEY", "KEY", "BASELINE" FROM "AO_32F7CE_DBREQUIREMENT".

Our unique keys are SPACEKEY - KEY - BASELINE (3 required items)

Despite a technical ID available in all tables, which is a Long, we almost always use the 3 values above to reference requirements. Don’t omit to include all 3 parameters, even if the baseline is often null!

Example: Table AO_32F7CE_DBREQUIREMENT

• ID: Technical primary key

• SPACEKEY*: The space key

• KEY*: The key of the requirement

• BASELINE*: The baseline (for ARCHIVED requirements only), or null (for ACTIVE only).

• STATUS: ACTIVE, ARCHIVED, MOVED, DELETED.

*Primary key

In Jira, the primary key has 4 identifiers, by necessity of being a remote app: APPLINKID, SPACEKEY, KEY, BASELINE.

Confluence database schema

• AO_32F7CE_DBREQUIREMENT

  • Primary key: SPACEKEY, KEY, BASELINE,

  • STATUS: One of { ACTIVE, ARCHIVED, MOVED, DELETED },

  • STORAGETYPE, HTMLEXCERPT: The storage type indicates the indexing engine. 1 or null means LEGACY_JS, 2 means it’s the 2023 engine, 3 means XHTML (not rendered yet), 4 means it’s pure text. The HTMLEXCERPT is the description of the requirement.

  • NEWKEY, NEWSPACEKEY: If the status is MOVED, then this contains the target.

  • UPPERKEY: Predefined field for case-insensitive searches.

• AO_32F7CE_DBPROPERTY:

  • TYPE can be INLINE (belongs to the requirement, since it’s the page) or EXTERNAL (losely associated IDs).

• AO_32F7CE_DBDEPENDENCY

  • Primary key: RELATIONSHIP, PARENT_ID, CHILD_ID

• AO_32F7CE_DBLINK

  • PARENT_ID: ID of the DBREQUIREMENT,

  • If ORIGIN=true, this is the page where the requirement was created,

  • If ORIGIN=false, this is a page that cites the requirement, or a Jira issue that cites it, since it depends on the type.

  • The primary key for this table is … almost all columns, since metadata varies per type of link.

• AO_32F7CE_DBTRACEABILITYMATRIX

  • The list of saved traceability matrices,

  • Primary key: SPACEKEY, ORIGINALID

• AO_32F7CE_DBQUEUE:

  • The queue (messages to Jira, indexation jobs or background jobs),

• AO_32F7CE_DBBACKUPITEM and AO_32F7CE_DBBACKUPMAPPING:

  • Those are used as temporary storage tables when you export requirements to another instance of Confluence.

• AO_32F7CE_DBAPPLINK:

  • Descriptor of Jira apps registered in Confluence, and opposite.

Jira database schema

In Jira, there are 4 identifiers for requirements, since we add the APPLINKID.

All our tables are stored with the prefix “AO_42D05A” in Jira.

• AO_42D05A_DBREMOTEREQUIREMENT:

  • The list of requirements.

  • Identified by APPLINKID, SPACEKEY, KEY, BASELINE,

  • FLAGDELETED: Boolean, the requirement is not shown if true.

• AO_42D05A_DBISSUELINK

  • ISSUEID: The primary identifier of issues

  • ISSUEKEY: Text only, FYI and not necessarily up to date (e.g. if the issue was moved),

  • RELATIONSHIP: A free verb to characterize the link to Confluence,

  • REQUIREMENT_ID: The ID of the table AO_42D05A_DBREMOTEREQUIREMENT.

• AO_42D05A_DBAUDITTRAIL:

  • A sequential list of changes on issue links,

  • They are serialized and stored in the JSON column,

  • This table is regularly purged depending on the options chosen in the administration.

• AO_42D05A_DBQUEUE: Queue messages

• AO_42D05A_DBBACKUPITEM: Import/export feature

• AO_42D05A_DBBACKUPMAPPING: Import/export feature

• AO_42D05A_DBAPPLINK: Configuration details of this applink

Generic metadata

A lot of tables have the following fields:

• SCHEMAVERSION: An number corresponding to our ModelVersion, always ascending. Whenever we upgrade records to a new format, we update this field, so that we don’t work on the same entities twice. Not all records will have the latest SCHEMAVERSION, because upgrade tasks can ignore subsets of records depending on various criteria.

• ID: Will be of type Long (64 bits) for all our modern entities, which start with “DB” (“DBREQUIREMENT”). Our old entities didn’t start with “DB” and only had shorter Integer (32 bits) identifiers, which was too short.

• CREATEDUSER, LASTUPDATEDUSER: The userKey of the last person who created/updated the entity.

• CREATEDDATE, LASTUPDATEDDATE: The date of creation and edition.