certificatetemplates

Each row represents a single certificate template. A certificate template is the configuration used to generate certificates when a learner completes a course or learning path. A template defines the title, expiration rules, recertification behavior, visual asset, and any conditional targeting for a specific user audience. Issued certificates in the certificates table reference the template they were generated from.

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, statusgroup, and the import/export columns imported, importid, backupexportid, tableimportid), see Common Columns. The columns below are specific to certificate templates.

title: the certificate title shown to learners on the certificate itself and in places where the certificate is referenced (transcripts, downloads, LinkedIn sharing). Configured by an admin when the certificate is set up on the course or learning path.

resourcetype and resourceid: identify the course or learning path this template belongs to. A given resource can have at most one certificate template, uniquely identified by (resourcetype, resourceid). resourcetype is either course or learningPath. resourceid is the id of that course (joins to courses.id) or learning path (joins to learningpaths.id).

expirationdate and expirationdays: control whether and when certificates issued from this template expire. Only one of the two is set on a given template. expirationdate is a fixed absolute date that all issued certificates expire on, regardless of when they were earned. expirationdays is a relative duration in days from the moment the certificate is issued (for example, 365 means the certificate expires one year after the learner earns it). If both are null, certificates from this template do not expire.

customcss: custom CSS applied to certificates rendered from this template. Allows admins to tweak layout, fonts, and colors beyond the positioning of individual labels.

recertification action

recertificationaction is the primary recertification setting, controlling what happens when an issued certificate expires and the learner needs to recertify. Only relevant when expiration is configured. See Attaching a Certificate in a Course for the learner-facing experience.

One of:

• null: no recertification action configured.
• resetProgress: the learner's course progress is reset so they can retake the course.
• recertifyInSession: the learner is offered to recertify within their current session by re-completing key activities. Course-level templates only, not available for learning paths.
• actionUrl: the learner is directed to an external URL (for example, a link to a separate recertification course or a third-party recertification system).

recertification settings

recertificationactiontext and recertificationactionurl: used only when recertificationaction = 'actionUrl'. recertificationactiontext is the button or link label shown to the learner; recertificationactionurl is the destination URL.

recertificationafterdays: used only when recertificationaction = 'recertifyInSession'. The number of days before expiration during which the learner is allowed to recertify in-session. null otherwise.

recertificationautoreset: boolean. When true and a recertification action is configured, the platform automatically applies the action when the certificate expires (rather than waiting for the learner to trigger it). false otherwise.

recertificationinstructions: free-text instructions shown to the learner when their certificate is approaching expiration or has expired. Authored by an admin.

assets

The visual asset is the image (or PDF) rendered as the certificate background. Labels in certificatetemplatelabels are positioned on top of this asset. A template's asset can come from one of two places:

• Inline: asset is the URL of the asset uploaded directly to the template, and assetlibraryid is null.
• Asset library: assetlibraryid references an entry in the company asset library (so the same asset can be reused across multiple templates), and asset may be null.

To get the effective asset for any template, prefer assetlibraryid (look it up in the asset library) when populated, otherwise fall back to asset.

supplementalassets: additional asset URLs associated with the template, for example secondary pages of a multi-page certificate. Stored as a JSON-serialized array of URL strings.

usercustomfieldslug and usercustomfieldvalue

optional targeting that limits the template to a specific learner audience. A course or learning path can have multiple certificate templates, each targeted at a different audience by a user custom field value (for example, a region-specific certificate design). When both are set, the template only applies to learners whose value for the user custom field identified by usercustomfieldslug equals usercustomfieldvalue. When both are null, the template is the default and applies to all learners earning the certificate from this resource.

Sample query

select
  t.id,
  t.title,
  t.resourcetype,
  t.resourceid,
  t.expirationdate,
  t.expirationdays,
  t.recertificationaction
from certificatetemplates as t
limit 100

Joining certificates to templates

Issued certificates link to the template they were generated from via certificates.certificatetemplateid = certificatetemplates.id:

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
limit 100

Joining templates to courses and learning paths

The template's resource is identified by resourcetype + resourceid:

select
  ct.id as template_id,
  ct.title as certificate_title,
  ct.resourcetype,
  coalesce(co.title, lp.name) as resource_title
from certificatetemplates as ct
  left join courses as co
    on ct.resourcetype = 'course'
    and co.id = ct.resourceid
  left join learningpaths as lp
    on ct.resourcetype = 'learningPath'
    and lp.id = ct.resourceid
limit 100

Joining templates to their labels

To see the labels positioned on each template (name, date, identifier, etc.), join to certificatetemplatelabels:

select
  ct.id as template_id,
  ct.title,
  ctl.x,
  ctl.y,
  ctl.fontsize,
  ctl.fontcolor,
  ctl.textalign,
  ctl.certificatefieldid
from certificatetemplates as ct
  left join certificatetemplatelabels as ctl on ctl.certificatetemplateid = ct.id
limit 100