Skip to main content

certificates

Each row represents an issued credential for a user on a course, learning path, or external resource, including issue and expiration dates, the template that generated it, and recertification state.

Full schema​

View all columns and table relationships

Status group​

Yes, this table includes a statusgroup column. See Status Group for details.

Column details​

For columns that appear across many tables (id, companyid, createdat, updatedat, deleted, statusgroup, etc.), see Common Columns. The columns below are specific to certificates.

certificatetemplateid: the id of the certificate template this certificate was generated from. Joins to certificatetemplates.id. null for externally-uploaded certificates and for any legacy certificates that pre-date the template-tracking feature. Use a left join so those rows are not dropped.

issuedat: timestamp when the certificate was issued to the learner (when they met the completion criteria, or when an admin granted it manually).

expirationdate: when the certificate expires. Derived at issue time from the template's expiration configuration (either a fixed date or issuedat + expirationdays). null when the template has no expiration configured (the certificate does not expire).

originalexpirationdate: the certificate's expiration date as it was first issued. If the learner recertifies and the expiration is extended, expirationdate advances but originalexpirationdate stays at the value from the original issuance, so the recertification history is preserved.

recertificationdate: timestamp when the certificate was last recertified. null if the certificate has never been recertified.

didexpire: boolean. true once the certificate has expired (and expirationdate is in the past). false for certificates that are still valid or have no expiration.

progresswasreset: boolean. true if the learner's progress on the underlying course was reset as part of recertification (recertificationaction = 'resetProgress'). Used to track whether the learner is going through the course again to recertify.

identifier: a unique credential identifier shown on the certificate (for example, a Credential ID for LinkedIn sharing). Generated from the certificate-field configuration when an admin sets up a Unique Identifier field on the template.

pdfasset: URL of the rendered PDF version of the certificate, when generated. null for certificates that have not had a PDF rendered.

url: URL of the public-facing certificate page (where the certificate can be viewed and shared, e.g. via LinkedIn).

source: indicates how the certificate was granted (for example, completion of a course, manual admin grant, external upload, etc.).

externalresourcetitle: for externalCertificate rows only, the title of the external credential as supplied at upload time. null for course and learning-path certificates (use the template's title instead).

resourcetype and resourceid​

Identifies which content item the certificate was earned on.

  • resourcetype is one of course, learningPath, or externalCertificate (a manually uploaded credential not tied to TI-hosted content).
  • resourceid is the id of that resource.

For convenience, the table also surfaces courseid and learningpathid as separate columns (only one is populated per row, depending on resourcetype). externalCertificate rows have neither set.

Sample query​

select
  t.userid,
  t.resourcetype,
  t.resourceid,
  t.certificatetemplateid,
  t.issuedat,
  t.expirationdate,
  t.didexpire,
  t.identifier
from certificates as t
where t.deleted = false
limit 100

Joining certificates to their template​

To get the certificate title and recertification configuration that was in effect when the certificate was issued, join to certificatetemplates:

select
  c.id,
  c.userid,
  c.issuedat,
  c.expirationdate,
  c.didexpire,
  ct.title,
  ct.recertificationaction,
  ct.expirationdays
from certificates as c
  left join certificatetemplates as ct on ct.id = c.certificatetemplateid
where c.deleted = false
limit 100

Use a left join because externally-uploaded certificates and legacy certificates may have certificatetemplateid as null.

Joining certificates to the underlying course or learning path​

select
  c.id,
  c.userid,
  c.issuedat,
  c.resourcetype,
  coalesce(co.title, lp.name) as resource_title
from certificates as c
  left join courses as co on co.id = c.courseid
  left join learningpaths as lp on lp.id = c.learningpathid
where c.deleted = false
limit 100