The content management tables handle the storage, organization, and classification of website content.
The content table is the heart of the CMS. Every content record represents a single addressable piece of content in the website.
| Column | Datatype | Info |
|---|---|---|
| content_id | key# | Content ID |
| type | key:content_type | Content type |
| title | string | Title (how it appears in headings) |
| name | string | Name (how it appears in URLs) |
| label | string | Label (how it appears in menus and indexes) |
| url | url | Special URL - this will be used to link to the content. Note that specifying this does not actually create the URL. If it is a local URL, you must ensure that your webserver knows how to respond to this URL, such as by setting up an Apache redirect. This is also useful for creating pseudo-content objects that link off-site. |
| parent | key:content | Parent content ID - this determines this content's position in the site map. |
| template | key=content | Template content ID - this is used to format the content for display as a complete web document. |
| master | key=content | Original content ID - points to the source content that this content is representing, in the case of translations and aliases. |
| language | list:language | Language of this content, if not the default |
| sortkey | string | Sort Key - an alphanumeric string that is used to sort this content under the parent. Note that it sorts alphabetically, so if you use numeric keys, "10" will come before "2". If you wish to use numeric keys, used a fixed number of digits and add leading zeroes, eg. 0001, 0002. |
| access | enum:access | Access - the access level (from 0 to 8) that is needed to view this content. |
| hide | set:hide | Hide From - the content will be hidden from the selected content discovery mechanisms. Menu - the content will not be displayed in menus. Sitemap - the content will not be included in the site map. Indexes - the content will not be include in other content listings. Search - the content will be excluded from the local search index. Robots - the content will request that it be ignored by robots. |
| content_role | list:content_role | etermines who can manage this content: editors, designers, administrators, or users. Note that this is normally inferred from the content_type, but you can use this setting to override the default content_role. |
| publish | list:publish | Publish Rule - determines how this content gets published. Static - the content is written out to fast static files. Dynamic - the content is regenerated every time it is viewed. Hourly, Daily, Weekly, Monthly - the content is static, but republishes automatically on this interval. None - the content is not published for public viewing. |
| status | enum:content_status | Workflow state - determines where the content is in the CMS workflow. |
| mtime | timestamp | When this content was last modified. |
| ctime | timestamp | When this content object was first created. |
| ptime | timestamp | When this content was published. |
The content_date table is used to track content date services, including scheduling and calendaring.
| Column | Datatype | Info |
|---|---|---|
| content_date_id | key# | Date ID |
| content_id | key:content | Content that this date is associated with |
| type | list:date_type | Date type, one of task, calendar, todo, log, other |
| description | string | Description |
| start | datetime:picker | The date (and optionally time) |
| end | datetime:picker | For date ranges, the end date (and optionally time) |
| status | list:date_status | Active for good dates, inactive otherwise. For tasks, hourly, daily, weekly, and completed are also valid values. |
The content_flag table tracks flags, which are boolean metadata that can be associated with any piece of content.
| Column | Datatype | Info |
|---|---|---|
| content_flag_id | key# | Flag ID |
| content_id | key:content | Content that this flag is associated with. |
| flag | string | The flag name. The presence of a flag name means that flag is turned on. |
The content_like table tracks user interest in content across the website.
| Column | Datatype | Info |
|---|---|---|
| content_like_id | key# | Like ID |
| content_id | key:content | Content that this like is associated with |
| uid | key:user | User who registered this like. |
| account | key:account | Account that registered this like - allows non-users to also record their likes. |
| value | int | Strength of the like (default is 1). Negative values are dislikes. |
| ctime | timestamp | Time the like was recorded. |
Every piece of content has a content_type which determines its core behaviours.
| Column | Datatype | Info |
|---|---|---|
| content_type_id | key# | Content Type ID |
| name | string | Name of content type |
| class | string | Viewer - the module that is used to display content of this type. |
| plugin | string | Controller - the module that is used to manage content of this type. |
| role | list:content_role | Role - what type of user is permitted to manage content of this type. |
| publish_as | list:cnodetype_publish | How this content gets published - as a file (eg. "/foo.html"), or as a directory (eg. "/foo"). |
| publish | list:publish | Publish Rule - when this content gets published. Static (always published), dynamic (not published, always generated at the time it is viewed), never (not displayed to website visitors), or hourly/daily/weekly/monthly (automatically republished on the declared interval). |
| navtype | list:navtype | Navigation Type - how this content type is treated for navigation purposes. Page - primary navigation, shown in menus. Item - secondary navigation, shown in content listings. None - not used for navigation. |
| displaytype | list:disptype | Display Type - how this content is formatted into a web document. Raw - the content is sent directly to the browser. Formatted - the content is merged into a format before being used as the body of a page. Template - the content represents a complete web page template, into which other content will be inserted. None - the content is not displayed to visitors. |
The actual visible bits of content that get delivered to the end-user (such as text and images) are stored in the cview table.
| Column | Datatype | Info |
|---|---|---|
| cview_id | key# | View ID |
| revision_id | key:revision | Revision - which revision this view is associated with. |
| type | list:view_type | The size of the view, one of large, normal, small, thumbnail, or icon. |
| mime_type | list:mime | The MIME-type of this view. |
| format | list:view_format | The format of the data in the data field below. One of text (the data field contains text data), file (the data field contains an encoded file), url (the data field contains a URL), or path (the data field contains the path to a file on the server). |
| data | mediumtext | The content itself, in the format noted above. |
| attributes | string | Additional key=value pairs that further describe the content. |
Content is organized hierarchically, with every piece of content nesting under a corresponding parent content. The content_rel table defines the rules for where each content type can be placed.
| Column | Datatype | Info |
|---|---|---|
| content_rel_id | key# | Content Relation ID |
| type | key:content_type | The content type that this relational rule applies to. |
| under | key:content_type | A content type that the above content is permitted to nest under. |
Metadata are name-value pairs that can be associated with any piece of content.
| Column | Datatype | Info |
|---|---|---|
| metadata_id | key# | Metadata ID |
| name | string | Name |
| value | text | Value |
| id | key:content | The content ID that this name=value pair relates to. |
| mtime | timestamp | Last Modified timestamp |
| ctime | timestamp | Creation timestamp |
Every time content is altered, a new revision is added to the content. Older revisions are kept on file and can be recovered later, if needed.
| Column | Datatype | Info |
|---|---|---|
| revision_id | key# | Revision ID |
| content_id | key:content | The content that this revision belongs to. |
| uid | key=user | Who added this revision. |
| status | enum:revision_status | 0=approved, 5=draft. Draft revisions will never be published. |
| mime_type | list:mime | The MIME-type of this revision. (Note that alternative views can override this MIME-type.) |
| note | text | A comment/explanation for the revision system. |
| ctime | timestamp | Creation Time |
| ptime | timestamp | Publication Time |
The CMS maintains a simple search index. Every searchable URL has one entry in the searchurl table, and every searchable term/word at that URL has an entry in the searchterm table.
| Column | Datatype | Info |
|---|---|---|
| searchterm_id | key# | Search Term ID |
| term | string | The word or term being indexed. |
| searchurl_id | key:searchurl | The searchurl record this term is found at. |
| weight | int | A relative weight to apply to this term. |
| Column | Datatype | Info |
|---|---|---|
| searchurl_id | key# | Search URL ID |
| url | string | A URL that is included in the search index. |
| section_id | key:content | The section or website that this URL belongs to. |
| content_id | key:content | The content that is represented at this URL. |
| title | string | Used to describe this URL in search results. |
| description | text | Used to describe this URL in search results. |
| access | int | The access level you need to view this URL in search results. |
| language | string | The language of content at this URL. |
| type | string | A category of URL, to classify search results by content type, allowing for searches to restrict themselves to certain parts of the site such as blogs or forums. |
| mtime | timestamp | The last time this URL was indexed |
You can install plug-ins on your website that perform special functions beyond what your CMS is capable of. In some cases, the plug-in will need a home in your site map so that there is a place where these functions can be performed. That home is called a service page, and plug-ins with a service page are called services.
A typical example of a service is a shopping cart - although you can purchase things all over your site, you need a page to go back to your cart and perform cart operations like removing items, or checking out. That page is the shopping cart service page, which you will typically link to using a shopping cart icon near the top of the page.
| Column | Datatype | Info |
|---|---|---|
| service_id | key# | Service ID |
| name | string | The module that is serviced at this page. |
| section_id | key:section | The section or website that this service is for. |
| page_id | key:content | The page that runs this service. |
Users are the people who can log into your website. They can have many different access levels, roles, and permissions, but they broadly fall into two categories:
Content keys control access permissions to different parts of your website. They are typically used to grant administrator access to sections, but they can be assigned with more specificity than that, if needed.
| Column | Datatype | Info |
|---|---|---|
| content_key_id | key# | Key ID |
| content_id | key:content | The content that this key controls access to, typically a section. Permissions will extend to this content and all content underneath it. |
| uid | key:user | The user that this key is assigned to. |
| gid | key:usergroup | Keys can optionally be assigned to groups instead of individual users. |
| type | list:key_type | Grant - the key grants access to this content. Revoke - the key removes access to this content. |
| role | set:role | The role determines what sort of privileges are granted. Editor - keyholders can update editorial content. Designer - keyholders can update graphic design content. Administrator - keyholders can add, remove, and publish content. Reviewer - keyholders can approve or reject content. Owner - keyholder is a non-admin but is permitted to modify this content. |
| ctime | timestamp | When the key was issued. |
Users are the login identities that people use to connect to website and get access to various permissions.
| Column | Datatype | Info |
|---|---|---|
| uid | key# | User ID |
| login | string | Login name, as entered into login forms. |
| password | password | Secret password used to prove your identity. Passwords are encoded according to the password_storage_method, so may not be directly readable. |
| name | string | User's full name |
| User's preferred E-mail address | ||
| access | int | User's access level (typically 1 - 8) |
| section_id | key:content | Users home site, a section ID. The login credentials are only valid in that section. |
| subscribe | list:yn | Consent to receive bulk email communications from the website |
| login_count | int | Number of Logins |
| last_login | timestamp | Last Login |
In some cases it makes sense to add users to groups, and then manage permissions/keys for the group. Examples include committees and boards of directors.
| Column | Datatype | Info |
|---|---|---|
| gid | key# | GID |
| section_id | key=content | Section that this group belongs to |
| name | string | Group Name |
| description | text | Description |
Membership in a group is managed by groupkey records, which join the user and usergroup.
| Column | Datatype | Info |
|---|---|---|
| groupkey_id | key# | Group Key ID |
| gid | key:usergroup | Group this user belongs to |
| uid | key:user | User in this group |
| info | string | User's role in this group, for example committee chair, or president. |
| status | enum:group_status | Active, archived, or canceled. |
General system tables are used to manage low-level system functions.
The attribute table is a simple tool for extending the database schema using metadata-like key=value pairs that are associated with any table+record in the database. They are typically referenced using the preference utility, ie. $value=&preference($name,$tablename,$id). Note that $tablename and $id default to the current section if not specified, which means the &preference() function is a handy way to override configuration settings on a section-by-section basis.
| Column | Datatype | Info |
|---|---|---|
| attribute_id | key# | ID |
| name | string | Attribute name |
| value | text | Attribute value |
| tablename | string | The table this attribute is associated with |
| id | int | The record ID this attribute is associated with |
The crontask table defines any scheduled jobs you would like to run in your plug-in modules.
| Column | Datatype | Info |
|---|---|---|
| crontask_id | key# | Task ID |
| description | string | Description of this task |
| module | string | The plug-in module that will be asked to perform this task |
| command | string | 1st parameter that will be passed to the module, typically a command |
| type | string | 2nd parameter that will be passed to the module, typically an object type, table, or category |
| id | string | 3rd parameter that will be passed to the module, typically an object ID |
| method | list:cron_method | preset - the task should be run once hourly, daily, weekly - the task should be run on a regular interval |
| exectime | datetime:picker | Execution time - for preset tasks, this is when the task should be run. For interval tasks, this indicates when it was last run. |
| uid | key:user | Assume this user's identity when running the task, to obtain the necessary permissions. |
| status | list:cron_status | Active - task is currently enabled. Inactive - task has been turned off and should be ignored. Completed - preset task has now been completed. |
Any report can save itself so that it can be restored and viewed by admins. Note that saved reports are not "live" reports - they take a snapshot of the data at the time of the report. However, they are very quick to recover and view, since they do not require re-doing all the queries and computations. Note that reports are saved by the ExSite::ReportBuilder::save() method.
report
| Column | Datatype | Info |
|---|---|---|
| report_id | key# | ID |
| section_id | key:content | Website that this report is for |
| title | string | Title of the report |
| description | string | Additional information about the report |
| type | string | A simple classification or category for the report. Typically this is the name of the plug-in module that generated the report; this allows modules to recognize their own reports and distinguish them from others. |
| data | data | Raw (binary) report data |
| source | url | The URL at which this report was generated |
| parameters | text | The report parameters - ideally these can be posted to the URL to regenerate the report |
| schedule | list:report_schedule | How often to re-run this report, eg. daily, weekly, monthly. |
| ctime | timestamp | When this report was generated |
| expiry | timestamp | When this report expires |
Any text messages that are output by the system code may need to be translated into other languages. Example include headings, status messages, confirmations, errors and warnings, and so on. The system messages facility is a dictionary of all such messages that the system can display, mapped to their equivalents in the other languages supported by the system.
If you run a multilingual system, any text string that is displayed using the %msg facility while in another language mode will be added to the system message dictionary. The System Messages plug-in allows you to translate those messages to your other supported languages.
The sysmsg table is a listing of all the English-language messages that are available for translation.
| Column | Datatype | Info |
|---|---|---|
| sysmsg_id | key# | System Message ID |
| ctime | timestamp | Updated |
| message | text | English text string |
The sysmsg_alt table carries translations of all the above messages.
| Column | Datatype | Info |
|---|---|---|
| sysmsg_alt_id | key# | Alternate Message ID |
| sysmsg_id | key:sysmsg | The English message that is translated here |
| ctime | timestamp | When this translation was last updated |
| message_alt | text | The translated message |
| language | list:language | The language of this translation |
The token table is a simple tool for tracking persistent key=value pairs. The token is the key, and the code can use this to look up the value, using $token{$key}. This makes it easy to pass complex data around securely using simple tokens. It can be used for URL shortening, authorizations, captchas, encoding sensitive data in URLs or forms, or any other situation requiring secure communication of information.
The token table is optional; without this table, the persistent data store will be used for token management. In that case, tokens will be cleared if the store is ever reset, so long-term token persistence may be in doubt. If you have a token table, then tokens can persist indefinitely.
| Column | Datatype | Info |
|---|---|---|
| name | key$ | Key |
| value | data | Value |
| uid | key:user | UID - a user that can be associated with this token. This has no preset meaning, so it is up to your code to decide what to do with this information. |
| singleuse | list:yn | If 'y', this is a single-use token, and will be automatically removed after it gets looked up. |
| ctime | timestamp | Creation timestamp |
| expiry | timestamp | The token cannot be used after this date/time. |
The system trash bin collects all deleted database records, allowing for them to be recovered if necessary. A single trash record encodes a single deletion, which may in turn consist of numerous distinct database records that were deleted at once. The trash data contains the deleted records in a format that allows them to be un-deleted if data recovery is needed. Since all records have unique primary key IDs, the un-deleted records should simply re-appear in their original locations.
Note that the deleted data is encoded in a mediumtext field, which usually allows up to 16MB of data to be encoded. This limit, and/or the limit on MySQL query sizes may truncate data in large deletions, preventing data recovery.
Trash records are generated by the trash methods (trash_key, trash_r) in ExSite::DB, and are recovered by the undelete method in the same.
| Column | Datatype | Info |
|---|---|---|
| trash_id | key# | Trash ID |
| ctime | timestamp | Date of deletion |
| description | string | Description |
| uid | key=user | Owner |
| data | mediumtext | Deleted data, encoded in a way to permit recovery |
The e-commerce tables track customers, accounts, purchases, and payments.
An account represents a customer. If the customer logs in, we can re-use their account on each purchase, and build up a purchase history. If they do not log in, we treat them as a new customer on each visit.
| Column | Datatype | Info |
|---|---|---|
| account_id | key# | Account ID |
| section_id | key=content | Website that this account is for |
| uid | key=user | User login identity associated with this account |
| name | string | Customer Name |
| code | string | Optional account code, if you are also tracking this account through another system |
| taxcode | string | Tax-Exemption # - this purchaser may be eligible for certain tax exemptions if this is defined. |
Accounting codes are used to classify purchases for purposes of reporting and calculating surcharges.
| Column | Datatype | Info |
|---|---|---|
| acctcode_id | key# | ID |
| code | string | A short text code for this category of purchase |
| description | text | A longer text description describing this category of purchase |
Gift cards are credits that can be used by buyers to get discounts on their purchases. The gift card can be pre-assigned to their account, in which case the credit will be earned automatically, or it can be typed in by the purchaser at checkout to claim the discount manually.
| Column | Datatype | Info |
|---|---|---|
| giftcard_id | key# | Gift Card ID |
| section_id | key:content | Website where this gift card is valid |
| account_id | key:account | Account that this giftcard is assigned to |
| acctcode_id | key:acctcode | Gift card can only be used on these accounting codes |
| glcode | string | Gift card can only be used on these GL codes |
| objtype | string | Gift card can only be used when purchasing items of this type |
| objid | int | Gift card can only be used to purchase this specific object ID |
| code | string | The gift card code that can be entered at checkout |
| description | string | This will be used to describe the discount on the purchase receipt |
| value | money | The total value of the gift card |
| expiry | date | The gift card must be used before this date |
| notes | text | The history of the gift card usage is logged here |
The accounting system automatically generates its own general ledger (GL) accounting codes. If you have your own GL accounting codes, you can map the internal GL codes to your external accounting codes using this table.
| Column | Datatype | Info |
|---|---|---|
| glcode_id | key# | ID |
| icode | string | Internal GL Code |
| xcode | string | External GL Code |
| description | string | Description of these purchases |
Payments are actual funds paid to you or by you.
| Column | Datatype | Info |
|---|---|---|
| payment_id | key# | Payment ID |
| account_id | key:account | Account that this payment is for |
| receivable_id | key:receivable | The specific purchase that this payment is for (optional) |
| date | timestamp | Date of the payment |
| amount | decimal | Amount of the payment - positive for payments from the customer, negative for payments to the customer (such as refunds) |
| method | list:payment_method | Payment method, eg. e-commerce, check/cheque, credit card, EFT, other |
| txid | string | Transaction ID - for e-commerce payments, the transaction ID from the payment processor |
| ident | string | Identifier - additional information to help distinguish this payment, such as cheque number, credit card type, last 4 digits |
| status | list:payment_status | Payment status - all good payments have a status of paid. Bad payments can have a status of canceled. Payments that could not be validated as either good or bad may get a status of suspicious, and will require manual reconciliation. If these are more than a day old, manual reconciliation may be necessary. Payments that are pending are waiting for their payment date to be processed. Payments that are archived have been retired and are no longer included in account statements or financial reports. |
| note | text | Optional extra information about the payment |
| uid | key=user | The user who entered this payment |
The price table is used to manage the price points of any sellable items described by the content table. This includes membership fees, registrations, product sales, order forms, courses, job postings, and others.
| Column | Datatype | Info |
|---|---|---|
| price_id | key# | Price ID |
| content_id | key:content | The purchased item |
| status | list:price_status | Active - the price is valid. Inactive - the price should be ignored. Priority - this price should be preferred over other valid prices. |
| description | string | Optional extra descriptive information about this price. Note that purchases are already adequately described, so this field should only be used to describe the price itself, in cases where it differs from the standard price. For example, "late fee", or "member rate". |
| cost | money | The actual price |
| acctcode_id | key=acctcode | Optional special accounting classification for purchases at this price point. Note that purchases have their own accounting classifications, so you should only use this to override those default accounting codes. |
| access | enum:access | The access level a buyer must have to get this price; use this to set special member pricing. |
| start | datetime | The price is only valid after this date/time. |
| end | datetime | The price is only valid before this date/time. |
| ctime | timestamp | When this price was set up. |
Receivables describe whole purchases. Depending on context they can be called invoices, receipts, refunds, or shopping carts.
| Column | Datatype | Info |
|---|---|---|
| receivable_id | key# | ID |
| account_id | key:account | Account that made this purchase |
| entered_by | key=user | Who entered this purchase |
| type | list:receivable_type | Receivable - a purchase by the customer. Payable - a sale or refund to the customer. |
| date | timestamp | Purchase date |
| description | text | Optional extra descriptive information about the purchase |
| status | list:receivable_status | Active - this is a completed sale, and should be included in financial reports. Inactive - this is an incomplete sale (either a shopping cart, or an abandoned sale). Archived - this is an old sale that is no longer included in financial reports. Processing - this sale has been sent for e-commerce payment, but no response has been received. Canceled - this sale was canceled, and can be ignored. Advisory - this is a copy or reminder of a previous sale or amount owing, for purposes of collecting overdue amounts, and should not be construed as a new sale. Comp - payment of this invoice was waived, but the customer still received the items on it. |
| invoice | string | If this invoice corresponds to a 3rd-party invoice, you can record the other invoice number here. |
Every line item on an invoice/receipt is described by a receivable_item record.
| Column | Datatype | Info |
|---|---|---|
| receivable_item_id | key# | Item # |
| receivable_id | key:receivable | The receivable that this item belongs to |
| status | list:receivable_item_status | Active - this item is a normal part of the purchase. Readonly - this item is active, but cannot be altered using the normal shopping cart tools. Hidden - this item not displayed to the purchaser. Deleted - the purchaser removed this item from their cart, and it should be ignored for accounting purposes. |
| item | string | The purchased item name |
| description | text | Additional details about the purchased item |
| quantity | decimal | Quantity - if blank, a quantity of 1 is assumed, but is not editable. If set, the quantity can be altered in the shopping cart (unless the item is set to readonly). |
| size | decimal | Unit size of the item - this is used to calculate sized-based surcharges. |
| cost | money | Unit cost of the item - the item subtotal will be this times the quantity |
| acctcode_id | key=acctcode | How this item is classified for accounting purposes |
| acctcode2 | string | Sales Subcode - additional sales classification |
| acctcode3 | string | Sales Subcode 2 - additional sales classification |
| surcharge_id | key=surcharge | If this item is a surcharge, this points to the surcharge rule used to calculate it. |
| parent | key:receivable_item | If this is a bundled purchase, this points to the main item of the bundle. Removing the main item from your cart will usually remove the whole bundle. |
| uid | key=user | Who added this item to the receivable |
| note | text | Additional notes about the purchased item |
| objtype | string | Points to a record table that represents this purchased item |
| objid | int | Points to a record ID that represents this purchased item |
| country | string | Country for tax purposes, if different from the usual tax rules |
| provstate | string | Province/State for tax purposes, if different from the usual tax rules |
| fulfilled_by | key=user | Which user fulfilled this order |
| fulfilled_on | datetime | When the order was fulfilled |
| fulfilled_qty | decimal | The quantity of fulfilled so far |
Surcharges are extra costs/line-items that get added to an invoice during checkout. They include taxes, shipping costs, discounts, and other adjustments. The surcharge table is a collection of rules for how to compute surcharges. They are applied individually to each line item on the invoice.
| Column | Datatype | Info |
|---|---|---|
| surcharge_id | key# | ID |
| name | string | How the surcharge will appear on the receipt |
| apply_to | key:acctcode | Which accounting code this surcharge applies to |
| apply_to_gl | string | Which GL code this surcharge applies to |
| apply_to_objtype | string | Which object type (table) this surcharge applies to |
| apply_to_objid | int | Which object ID (ecord) this surcharge applies to |
| treat_as | key:acctcode | How the surcharge itself should be classified in financial reports |
| percent | decimal | The surcharge cost is this % of the item subtotal |
| peritem | money | The surcharge cost is this × the item quantity |
| perproduct | money | The surcharge cost is this, charged only once per line item |
| persize | money | The surcharge cost is this × the item quantity × the item size |
| perorder | money | The surcharge cost is this, charged only once per invoice |
| provstate | string | The surcharge only applies in this province/state |
| country | string | The surcharge only applies in this country |
| section_id | key:content | The surcharge only applies on this website |
| code | string | The surcharge only applies if the customer entered this coupon code |
| apply_if | key:acctcode | The surcharge only applies if the customer ALSO bought a different item with this accounting code |
| apply_if_gl | string | The surcharge only applies if the customer ALSO bought a different item with this GL code |
| apply_if_objtype | string | The surcharge only applies if the customer ALSO bought a different item of this type |
| apply_if_objid | int | The surcharge only applies if the customer ALSO bought a different item with this ID |
| min_quantity | int | The surcharge only applies if they bought this quantity or more |
| min_value | money | The surcharge only applies if the item subtotal is this or more |
| min_size | decimal | The surcharge only applies if the item size is this or more |
| valid_from | datetime | The surcharge only applies after this date/time |
| valid_to | datetime | The surcharge only applies before this date/time |
| status | list:surcharge_status | Active surcharges are in effect; inactive surcharges are ignored. |
Note that calendars, events, courses, and registration fees are handled by the CMS, and are represented by content records of the appropriate content type.
Registrations themselves are treated as tickets, which are purchased via a registration fee.
A ticket is a space that is available in an event for a participant. For limited-attendance events, there may be a fixed number of pre-generated tickets available, whereas for open events, new tickets can be generated in any number, as needed.
| Column | Datatype | Info |
|---|---|---|
| ticket_id | key# | Ticket ID |
| content_id | key:content | The fee that this ticket was purchased from |
| info | string | Special information about this ticket, such as seat # |
| status | list:ticket_status | active - ticket is available; inactive - ticket has not been released for sale yet |
| result | list:ticket_result | The outcome for this attendee: present, not present, pass, fail |
A ticket claim represents an attempt to obtain a ticket. The claim may succeed if the ticket is acquired and paid for, in which case the claim status will be confirmed or reserved. Or the claim may fail if the payment doesn't go through, or the purchaser waits too long to confirm their purchase, in which case the claim status will be canceled. The legitimate ticket holder is the last ticket claim that is confirmed or reserved.
| Column | Datatype | Info |
|---|---|---|
| ticket_claim_id | key# | Ticket Claim ID |
| ticket_id | key:ticket | Ticket being claimed |
| account_id | key:account | Purchaser of this ticket |
| guest_account_id | key:account | Guest attendee this ticket was purchased for, if not the purchaser |
| status | list:ticket_claim_status | confirmed - ticket sale is complete; reserved - ticket has been sold, but not paid for; held - ticket is in someone's shopping cart, but they have not checked out; waitlist - event is sold out, but purchaser would like to be included if more tickets are released; canceled - ticket has been released from its previous claim, and can now be re-sold |
| parent | key:ticket_claim | If this ticket is part of a bundle, this points to the main ticket of the bundle |
| first_name | string | First Name of ticket holder |
| last_name | string | Last Name of ticket holder |
| dupekey | int | Used to distinguish between multiple ticket holders with the same name |
| response_id | key=response | Registration form response |
| ctime | timestamp | Date/time this ticket was purchased |
The result table tracks e-learning outcomes in online courses.
| Column | Datatype | Info |
|---|---|---|
| result_id | key# | ID |
| content_id | key:content | The lesson or course this result was for |
| response_id | key:response | Exam/Quiz Response |
| uid | key=user | The user ID of the student |
| status | list:result | The learning outcome - pass, fail, complete, or incomplete |
| score | int | A numeric score, such as a % grade |
| grade | list:letter_grade | A letter grade |
| credits | int | CE credits earned |
| note | text | Notes |
| ctime | timestamp | The date and time the result was recorded |
The address book tracks all your contacts. Contacts are like address cards; they are a collection of contact information that is associated with a person, place, or organization. Contacts that are associated with accounts are presumed to be customer information, which is confidential (for example, billing addresses). Contacts that are associated with content is presumed to be contact information of relevance to website visitors, and may be displayed (for example, event venues).
A contact record represents one "bundle" of contact information.
| Column | Datatype | Info |
|---|---|---|
| contact_id | key# | Contact ID |
| content_id | key:content | Website content this contact is for |
| account_id | key:account | Customer account this contact is for |
| type | list:contact_type | The type of contact this record describes, eg. mailing, billing, office, home, etc. |
| name | string | Contact name or other routing information. Note that the contact name can be inferred from the contact or account name, so this field is optional. Use this to override the inferred name, or to provide additional context. For example, if the account name is a company, this column might further specify "Accounting Department" if this is a billing contact. |
| privacy | enum:access | Access level needed to view this contact record. |
| subscribe | list:yn | Whether or not the addressee wishes to receive bulk communications at this contact |
| category | string | Keywords or tags that can be used to classify this contact |
| notes | text | Additional information about this contact |
| ctime | timestamp | When this contact was added to your address book |
A contact_info record represents one key=value pair of contact information that goes on this address card.
| Column | Datatype | Info |
|---|---|---|
| contact_info_id | key# | Contact Info ID |
| contact_id | key:contact | The contact record this contact information is for |
| name | list:contact_fields | The type of contact information in this record; one of: address, city, provstate, country, pcode, phone, fax, cell, email, website |
| value | text | The actual contact info corresponding to the selected name |
| privacy | enum:access | The access level needed to view this info. Optional, since the overall contact record also specifies an access level. This can be used to make certain pieces of info (such as home phone numbers) more private than the contact record as a whole. |
A contact list efines a set of contacts, which can be used for bulk communications like newsletters, notifications, and so on.
| Column | Datatype | Info |
|---|---|---|
| contact_list_id | key# | Contact list ID |
| section_id | key:content | Website this contact list belongs to |
| name | string | Descriptive name of this contact List |
| type | list:contact_list_type | Determines the rules for how to collect contacts for this list. membership - contact list contains members of this organization customer - contact list contains people who have purchased from you registrant - contact list contains people who have attended your events location - contact list contains locations from your website other - custom rules |
| dupe | list:contact_list_dupe | all - include all contact records for each person; best one - only include one contact record per person, based on completeness |
| unsubscribe | list:contact_list_unsubscribe | exclude contact records of people who have opted out of receiving bulk communications, or include those contact records |
| parameters | text | A set of form-encoded key=value parameters that are used to filter/select eligible contacts. The parameters depend on the contact list type. |
| include | key:contact_list | Another contact list that should be included as part of this one. This allows you to build large contact lists by daisy-chaining smaller ones. |
Note that forms, quizzes, and questions are handled by the CMS, and are represented by content records of the appropriate content type. Responses that are submitted from those forms are managed by the response and answer tables.
An answer is the information provided for a single question in a form.
| Column | Datatype | Info |
|---|---|---|
| answer_id | key# | ID |
| response_id | key:response | The overall form response that this answer is part of |
| question_id | key:content | The question being answered |
| value | text | The answer that was provided |
A response is a complete set of answers for an entire form, as submitted by one person.
| Column | Datatype | Info |
|---|---|---|
| response_id | key# | ID |
| form_id | key:content | The form being responded to |
| parent | key:response | For multipart forms, the response to the previous form section |
| uid | key=user | The user who submitted this response |
| language | list:language | The language the respondent submitted in |
| note | text | Extra information about this response, such as where it was submitted from |
| status | enum:response_status | The response status, in cases where a response work flow is used. Can be any of completed, archived, pass, accepted, submitted, draft, returned, fail, rejected. |
| ctime | timestamp | Date/time the response was received |
| Datatype | Format/Allowed Values |
|---|---|
| text | Long text data, including line breaks |
| string | Short text data, no line breaks |
| var | A single name consisting of letters, numbers, periods, and/or dashes; no spaces. |
| date | YYYY-MM-DD |
| time | HH:MM |
| datetime | YYYY-MM-DD HH:MM |
| timestamp | YYYY-MM-DD HH:MM:SS |
| email address, eg. yourname@domain.com | |
| url | website URL, eg. https://www.domain.com |
| key | a database record ID, typically an integer/serial number |
| int | a numeric integer value |
| float | a numeric floating point value |
| decimal | a numeric value with a fixed number of decimal places |
| money | a numeric value with 2 decimal places |
| password | a secret string of text that should not be displayed |
| file | a small file up to 48K in size |
| bigfile | a large file |
| data | unformatted/binary data |
| filepath | complete path to a file |
List datatypes permit one of a set of values. Set datatypes permit any number of a set of values. Enum datatypes permit one of a set of values, but store the value as a number rather than as a string.
| Datatype | Allowed Values (separated by '|') |
|---|---|
| list:yn | y|n |
| list:toggle | on|off |
| list:language | (site-defined) |
| list:gender | male|female|other/non-binary |
| list:report_schedule | n/a|daily|weekly|monthly |
| list:publish | static|dynamic|hourly|daily|weekly|never |
| list:navtype | page|item|none |
| list:disptype | raw|formatted|template|none |
| list:view_type | normal|large|small|thumbnail|icon |
| list:view_format | text|file|url|path|alias |
| list:role | editor|designer|reviewer|administrator |
| list:cnodetype_publish | file|directory|n/a |
| list:content_role | editorial|design|user |
| list:key_type | grant|revoke |
| list:content_type_publish | file|directory|never |
| list:date_type | task|todo|calendar|log|other |
| list:date_status | active|hourly|daily|weekly|completed|inactive |
| list:price_status | active|priority|inactive |
| list:mime | text/plain|text/html|text/css|image/jpeg|image/png|etc... |
| list: email_recipient_type | notification|bulk email |
| set:hide | menu|sitemap|indexes|search|robots |
| set:role | administrator|editor|designer|reviewer|owner |
| enum:access | 0:public|1:users|2:members|3:level 3|4:level 4|5:any executive-level|6:level 6|7:managers|8:sysadmins|9:root |
| enum:content_status | 0:published|1:archived|2:queued|3:approved|4:submitted|5:draft|6:expired|7:canceled|8:rejected |
| enum:revision_status | 0:active|5:draft |
| enum:group_status | 0:active|1:archived|7:canceled |