Confluence Docs 3.0 : The Confluence data model This page last changed on May 25, 2009 by ganand. On this page: General Database Diagram Authentication Atlassian-user OpenSymphony Content Clustering System information Spaces Appearance Miscellaneous
| The Hibernate mapping files are the authoritative reference. These are the *.hbm.xml files which have been bundled into the main Confluence .jar file in recent releases.
This document is little more than the Confluence schema with added comments, but the priority was placed on making the information available. ## General Database Diagram ## Authentication ### Atlassian-user This is the "new" authentication system, which is more flexible and extensible than `OpenSymphony`.
                 Table "groups"
      Column   |          Type          | Modifiers
    -----------+------------------------+-----------
     id        | bigint                 | not null
     groupname | character varying(255) | not null
    Indexes:
        "groups_pkey" PRIMARY KEY, btree (id)
                    Table "users"
      Column  |            Type             | Modifiers
    ----------+-----------------------------+-----------
     id       | bigint                      | not null
     name     | character varying(255)      | not null
     password | character varying(255)      |
     email    | character varying(255)      |
     created  | timestamp without time zone |
     fullname | character varying(255)      |
    Indexes:
        "users_pkey" PRIMARY KEY, btree (id)
        "users_name_key" UNIQUE, btree (name)
`local_members`: establishes many-to-many association between users and groups.
     Table "local_members"
     Column  |  Type  | Modifiers
    ---------+--------+-----------
     userid  | bigint | not null
     groupid | bigint | not null
    Indexes:
        "local_members_pkey" PRIMARY KEY, btree (groupid, userid)
    Foreign-key constraints:
        "fk6b8fb445117d5fda" FOREIGN KEY (groupid) REFERENCES groups(id)
        "fk6b8fb445ce2b3226" FOREIGN KEY (userid) REFERENCES users(id)
`external_entities`: Maps users from LDAP (or any other external authentication system) to IDs in Confluence DB
          Table "external_entities"
     Column |          Type          | Modifiers
    --------+------------------------+-----------
     id     | bigint                 | not null
     name   | character varying(255) |
     type   | character varying(255) | not null
    Indexes:
        "external_entities_pkey" PRIMARY KEY, btree (id)
`external_members`: associates LDAP (or other external) users with local groups.
     Table "external_members"
       Column    |  Type  | Modifiers
    -------------+--------+-----------
     extentityid | bigint | not null
     groupid     | bigint | not null
    Indexes:
        "external_members_pkey" PRIMARY KEY, btree (groupid, extentityid)
    Foreign-key constraints:
        "fkd8c8d8a5117d5fda" FOREIGN KEY (groupid) REFERENCES groups(id)
        "fkd8c8d8a5f25e5d5f" FOREIGN KEY (extentityid) REFERENCES external_entities(id)
### OpenSymphony The "old" authentication system, which was the default prior to 2.7.
                Table "os_group"
      Column   |          Type          | Modifiers
    -----------+------------------------+-----------
     id        | bigint                 | not null
     groupname | character varying(255) | not null
    Indexes:
        "os_group_pkey" PRIMARY KEY, btree (id)
        "os_group_groupname_key" UNIQUE, btree (groupname)
                Table "os_user"
      Column  |          Type          | Modifiers
    ----------+------------------------+-----------
     id       | bigint                 | not null
     username | character varying(255) | not null
     passwd   | character varying(255) |
    Indexes:
        "os_user_pkey" PRIMARY KEY, btree (id)
        "os_user_username_key" UNIQUE, btree (username)
     Table "os_user_group"
      Column  |  Type  | Modifiers
    ----------+--------+-----------
     group_id | bigint | not null
     user_id  | bigint | not null
    Indexes:
        "os_user_group_pkey" PRIMARY KEY, btree (user_id, group_id)
    Foreign-key constraints:
        "fk932472461e2e76db" FOREIGN KEY (group_id) REFERENCES os_group(id)
        "fk93247246f73aee0f" FOREIGN KEY (user_id) REFERENCES os_user(id)
## Content The actual information that users are storing and sharing. `attachmentdata`: stores the binary data for attached files. Only used when Confluence is configured to store attachments in the database; otherwise, attachments are stored in the local filesystem.
         Table "attachmentdata"
          Column      |  Type   | Modifiers
    ------------------+---------+-----------
     attachmentdataid | bigint  | not null
     attversion       | integer | not null
     data             | bytea   |
     attachmentid     | bigint  |
    Indexes:
        "attachmentdata_pkey" PRIMARY KEY, btree (attachmentdataid)
        "attch_data_idx" btree (attachmentid)
    Foreign-key constraints:
        "fk9dc3e34d34a4917e" FOREIGN KEY (attachmentid) REFERENCES attachments(attachmentid)
`attachments`: metadata for attachments.
                      Table "attachments"
           Column       |            Type             | Modifiers
    --------------------+-----------------------------+-----------
     attachmentid       | bigint                      | not null
     title              | character varying(255)      | not null
     contenttype        | character varying(255)      | not null
     pageid             | bigint                      | not null
     creator            | character varying(255)      |
     creationdate       | timestamp without time zone |
     lastmodifier       | character varying(255)      |
     lastmoddate        | timestamp without time zone |
     filesize           | bigint                      |
     attachment_comment | character varying(255)      |
     attversion         | integer                     |
     prevver            | bigint                      |
    Indexes:
        "attachments_pkey" PRIMARY KEY, btree (attachmentid)
        "att_pageid_idx" btree (pageid)
        "att_prevver_idx" btree (prevver)
    Foreign-key constraints:
        "fk54475f9017d4a070" FOREIGN KEY (prevver) REFERENCES attachments(attachmentid)
        "fk54475f908c38fbea" FOREIGN KEY (pageid) REFERENCES content(contentid)
`bodycontent`: stores the actual content of Confluence pages. No versioning information or other metadata is stored here, though; that's all in the `content` table.
         Table "bodycontent"
        Column     |  Type  | Modifiers
    ---------------+--------+-----------
     bodycontentid | bigint | not null
     body          | text   |
     contentid     | bigint |
    Indexes:
        "bodycontent_pkey" PRIMARY KEY, btree (bodycontentid)
        "body_content_idx" btree (contentid)
    Foreign-key constraints:
        "fka898d4778dd41734" FOREIGN KEY (contentid) REFERENCES content(contentid)
`content`: a persistence table for the ContentEntityObject class of objects. The subclass is indicated by the `contenttype` column.
                       Table "content"
          Column      |            Type             | Modifiers
    ------------------+-----------------------------+-----------
     contentid        | bigint                      | not null
     contenttype      | character varying(255)      | not null
     title            | character varying(255)      |
     version          | integer                     |
     creator          | character varying(255)      |
     creationdate     | timestamp without time zone |
     lastmodifier     | character varying(255)      |
     lastmoddate      | timestamp without time zone |
     versioncomment   | text                        |
     prevver          | bigint                      |
     content_status   | character varying(255)      |
     spaceid          | bigint                      |
     parentid         | bigint                      |
     messageid        | character varying(255)      |
     draftpageid      | character varying(255)      |
     draftspacekey    | character varying(255)      |
     drafttype        | character varying(255)      |
     draftpageversion | integer                     |
     pageid           | bigint                      |
     parentcommentid  | bigint                      |
     username         | character varying(255)      |
    Indexes:
        "content_pkey" PRIMARY KEY, btree (contentid)
        "c_draftpageid_idx" btree (draftpageid)
        "c_draftspacekey_idx" btree (draftspacekey)
        "c_drafttype_idx" btree (drafttype)
        "c_messageid_idx" btree (messageid)
        "c_parentcommid_idx" btree (parentcommentid)
        "c_parentid_idx" btree (parentid)
        "c_prevver_idx" btree (prevver)
        "c_spaceid_idx" btree (spaceid)
        "c_title_idx" btree (title)
        "c_username_idx" btree (username)
    Foreign-key constraints:
        "fk6382c05917d4a070" FOREIGN KEY (prevver) REFERENCES content(contentid)
        "fk6382c05974b18345" FOREIGN KEY (parentid) REFERENCES content(contentid)
        "fk6382c0598c38fbea" FOREIGN KEY (pageid) REFERENCES content(contentid)
        "fk6382c059b2dc6081" FOREIGN KEY (spaceid) REFERENCES spaces(spaceid)
        "fk6382c059b97e9230" FOREIGN KEY (parentcommentid) REFERENCES content(contentid)
`content_label`: Arbitrary text labels for content.
                  Table "content_label"
        Column    |            Type             | Modifiers
    --------------+-----------------------------+-----------
     id           | bigint                      | not null
     labelid      | bigint                      | not null
     contentid    | bigint                      | not null
     spacekey     | character varying(255)      |
     owner        | character varying(255)      |
     creationdate | timestamp without time zone |
     lastmoddate  | timestamp without time zone |
    Indexes:
        "content_label_pkey" PRIMARY KEY, btree (id)
        "cl_contentid_idx" btree (contentid)
        "cl_labelid_idx" btree (labelid)
        "cl_lastmoddate_idx" btree (lastmoddate)
        "cl_spacekey_idx" btree (spacekey)
    Foreign-key constraints:
        "fkf0e7436e27072aef" FOREIGN KEY (labelid) REFERENCES label(labelid)
        "fkf0e7436e8dd41734" FOREIGN KEY (contentid) REFERENCES content(contentid)
`label`: the other half of the `content_label` system.
                      Table "label"
        Column    |            Type             | Modifiers
    --------------+-----------------------------+-----------
     labelid      | bigint                      | not null
     name         | character varying(255)      |
     owner        | character varying(255)      |
     namespace    | character varying(255)      |
     creationdate | timestamp without time zone |
     lastmoddate  | timestamp without time zone |
    Indexes:
        "label_pkey" PRIMARY KEY, btree (labelid)
        "l_name_idx" btree (name)
        "l_namespace_idx" btree (namespace)
        "l_owner_idx" btree ("owner")
`content_perm`: content-level permissions objects.
                  Table "content_perm"
        Column    |            Type             | Modifiers
    --------------+-----------------------------+-----------
     id           | bigint                      | not null
     cp_type      | character varying(10)       | not null
     username     | character varying(255)      |
     groupname    | character varying(255)      |
     cps_id       | bigint                      |
     creator      | character varying(255)      |
     creationdate | timestamp without time zone |
     lastmodifier | character varying(255)      |
     lastmoddate  | timestamp without time zone |
    Indexes:
        "content_perm_pkey" PRIMARY KEY, btree (id)
        "cp_gn_idx" btree (groupname)
        "cp_os_idx" btree (cps_id)
        "cp_un_idx" btree (username)
    Foreign-key constraints:
        "fkbd74b31676e33274" FOREIGN KEY (cps_id) REFERENCES content_perm_set(id)
`content_perm_set`: one-to-many mapping for content items and their permissions, with added metadata.
                 Table "content_perm_set"
         Column     |            Type             | Modifiers
    ----------------+-----------------------------+-----------
     id             | bigint                      | not null
     cont_perm_type | character varying(10)       | not null
     content_id     | bigint                      |
     creationdate   | timestamp without time zone |
     lastmoddate    | timestamp without time zone |
    Indexes:
        "content_perm_set_pkey" PRIMARY KEY, btree (id)
        "cps_content_idx" btree (content_id)
    Foreign-key constraints:
        "fkbf45a7992caf22c1" FOREIGN KEY (content_id) REFERENCES content(contentid)
## Clustering `clustersafety`: normally, this table only contains one row. The value of the `safetynumber` is what Confluence uses to find out whether another instance is sharing its database without being part of the cluster.
         Table "clustersafety"
         Column      |  Type   | Modifiers
    -----------------+---------+-----------
     clustersafetyid | bigint  | not null
     safetynumber    | integer |
    Indexes:
        "clustersafety_pkey" PRIMARY KEY, btree (clustersafetyid)
### System information `confversion` used by the upgrade system to determine what to expect from the database, so as to negotiate upgrades.
                   Table "confversion"
        Column     |            Type             | Modifiers
    ---------------+-----------------------------+-----------
     confversionid | bigint                      | not null
     buildnumber   | integer                     | not null
     installdate   | timestamp without time zone |
     versiontag    | character varying(255)      |
     creationdate  | timestamp without time zone |
     lastmoddate   | timestamp without time zone |
    Indexes:
        "confversion_pkey" PRIMARY KEY, btree (confversionid)
        "confversion_buildnumber_key" UNIQUE, btree (buildnumber)
`plugindata`: records which plugins have been installed, and when. `data` is a blob of the actual plugin .jar file. This is principally cluster-related.
                   Table "plugindata"
        Column    |            Type             | Modifiers
    --------------+-----------------------------+-----------
     plugindataid | bigint                      | not null
     pluginkey    | character varying(255)      | not null
     filename     | character varying(255)      | not null
     lastmoddate  | timestamp without time zone |
     data         | bytea                       |
    Indexes:
        "plugindata_pkey" PRIMARY KEY, btree (plugindataid)
        "plugindata_filename_key" UNIQUE, btree (filename)
        "plugindata_pluginkey_key" UNIQUE, btree (pluginkey)
### Spaces `spacegroups`: this table is only used by the hosted environment.
                    Table "spacegroups"
         Column     |            Type             | Modifiers
    ----------------+-----------------------------+-----------
     spacegroupid   | bigint                      | not null
     spacegroupname | character varying(255)      |
     spacegroupkey  | character varying(255)      | not null
     licensekey     | text                        |
     creator        | character varying(255)      |
     creationdate   | timestamp without time zone |
     lastmodifier   | character varying(255)      |
     lastmoddate    | timestamp without time zone |
    Indexes:
        "spacegroups_pkey" PRIMARY KEY, btree (spacegroupid)
        "spacegroups_spacegroupkey_key" UNIQUE, btree (spacegroupkey)
                 Table "spacepermissions"
        Column     |            Type             | Modifiers
    ---------------+-----------------------------+-----------
     permid        | bigint                      | not null
     spaceid       | bigint                      |
     permtype      | character varying(255)      | not null
     permgroupname | character varying(255)      |
     permusername  | character varying(255)      |
     creator       | character varying(255)      |
     creationdate  | timestamp without time zone |
     lastmodifier  | character varying(255)      |
     lastmoddate   | timestamp without time zone |
    Indexes:
        "spacepermissions_pkey" PRIMARY KEY, btree (permid)
        "sp_permtype_idx" btree (permtype)
        "sp_pgname_idx" btree (permgroupname)
        "sp_puname_idx" btree (permusername)
        "sp_spaceid_idx" btree (spaceid)
    Foreign-key constraints:
        "fkd33f23beb2dc6081" FOREIGN KEY (spaceid) REFERENCES spaces(spaceid)
`spaces`: information about the spaces themselves: key, human-friendly name and numeric ID.
                     Table "spaces"
        Column    |            Type             | Modifiers
    --------------+-----------------------------+-----------
     spaceid      | bigint                      | not null
     spacename    | character varying(255)      |
     spacekey     | character varying(255)      | not null
     spacedescid  | bigint                      |
     homepage     | bigint                      |
     creator      | character varying(255)      |
     creationdate | timestamp without time zone |
     lastmodifier | character varying(255)      |
     lastmoddate  | timestamp without time zone |
     spacetype    | character varying(255)      |
     spacegroupid | bigint                      |
    Indexes:
        "spaces_pkey" PRIMARY KEY, btree (spaceid)
        "spaces_spacekey_key" UNIQUE, btree (spacekey)
        "s_homepage_idx" btree (homepage)
        "s_spacedescid_idx" btree (spacedescid)
        "s_spacegroupid_idx" btree (spacegroupid)
    Foreign-key constraints:
        "fk9228242d11b7bfee" FOREIGN KEY (homepage) REFERENCES content(contentid)
        "fk9228242d16994414" FOREIGN KEY (spacegroupid) REFERENCES spacegroups(spacegroupid)
        "fk9228242d2c72d3d2" FOREIGN KEY (spacedescid) REFERENCES content(contentid)
## Appearance `decorator`: storage of custom display templates, for customising layouts.
                    Table "decorator"
        Column     |            Type             | Modifiers
    ---------------+-----------------------------+-----------
     decoratorid   | bigint                      | not null
     spacekey      | character varying(255)      |
     decoratorname | character varying(255)      |
     body          | text                        |
     lastmoddate   | timestamp without time zone |
    Indexes:
        "decorator_pkey" PRIMARY KEY, btree (decoratorid)
        "dec_key_idx" btree (spacekey)
        "dec_name_idx" btree (decoratorname)
## Miscellaneous `os_propertyentry`: for arbitrary association of entities and properties.
                Table "os_propertyentry"
       Column    |            Type             | Modifiers
    -------------+-----------------------------+-----------
     entity_name | character varying(125)      | not null
     entity_id   | bigint                      | not null
     entity_key  | character varying(200)      | not null
     key_type    | integer                     |
     boolean_val | boolean                     |
     double_val  | double precision            |
     string_val  | character varying(255)      |
     text_val    | text                        |
     long_val    | bigint                      |
     int_val     | integer                     |
     date_val    | timestamp without time zone |
    Indexes:
        "os_propertyentry_pkey" PRIMARY KEY, btree (entity_name, entity_id, entity_key)
`bandana`: a catch-all persistence layer. It contains things like user settings and space- and global-level configuration data, and is used as storage by plugins such as the Dynamic Task List plugin. Essentially, for storing arbitrary data that doesn't fit anywhere else.
                   Table "bandana"
         Column     |          Type          | Modifiers
    ----------------+------------------------+-----------
     bandanaid      | bigint                 | not null
     bandanacontext | character varying(255) |
     bandanakey     | character varying(100) |
     bandanavalue   | text                   |
    Indexes:
        "bandana_pkey" PRIMARY KEY, btree (bandanaid)
        "band_context_idx" btree (bandanacontext)
        "band_key_idx" btree (bandanakey)
`extrnlnks`: storage of referral links.
                    Table "extrnlnks"
        Column    |            Type             | Modifiers
    --------------+-----------------------------+-----------
     linkid       | bigint                      | not null
     contenttype  | character varying(255)      | not null
     viewcount    | integer                     | not null
     url          | character varying(255)      | not null
     contentid    | bigint                      | not null
     creator      | character varying(255)      |
     creationdate | timestamp without time zone |
     lastmodifier | character varying(255)      |
     lastmoddate  | timestamp without time zone |
    Indexes:
        "extrnlnks_pkey" PRIMARY KEY, btree (linkid)
        "el_contentid_idx" btree (contentid)
    Foreign-key constraints:
        "fk97c10fe78dd41734" FOREIGN KEY (contentid) REFERENCES content(contentid)
`hibernate_unique_key`: used by the high/low ID generator - the subsystem which generates our primary keys. Mess with this at the cost of being able to create objects.
    Table "hibernate_unique_key"
     Column  |  Type   | Modifiers
    ---------+---------+-----------
     next_hi | integer |
`indexqueueentries`: arbitrates full-content indexing across the system. This table generally contains the last 12 hours or so of updates, to allow re-syncing of cluster nodes after restarts.
                Table "indexqueueentries"
        Column    |            Type             | Modifiers
    --------------+-----------------------------+-----------
     entryid      | bigint                      | not null
     creationdate | timestamp without time zone |
     type         | integer                     |
     handle       | character varying(255)      |
    Indexes:
        "indexqueueentries_pkey" PRIMARY KEY, btree (entryid)
`keystore`: used by the trusted apps framework to store the server's private key, and other servers' public keys.
                Table "keystore"
      Column   |          Type          | Modifiers
    -----------+------------------------+-----------
     keyid     | bigint                 | not null
     alias     | character varying(255) | not null
     type      | character varying(32)  | not null
     algorithm | character varying(32)  | not null
     keyspec   | text                   | not null
    Indexes:
        "keystore_pkey" PRIMARY KEY, btree (keyid)
`links`: tracks links within the server (i.e. across and within spaces).
                      Table "links"
        Column     |            Type             | Modifiers
    ---------------+-----------------------------+-----------
     linkid        | bigint                      | not null
     destpagetitle | character varying(255)      |
     destspacekey  | character varying(255)      | not null
     contentid     | bigint                      | not null
     creator       | character varying(255)      |
     creationdate  | timestamp without time zone |
     lastmodifier  | character varying(255)      |
     lastmoddate   | timestamp without time zone |
    Indexes:
        "links_pkey" PRIMARY KEY, btree (linkid)
        "l_contentid_idx" btree (contentid)
        "l_destspacekey_idx" btree (destspacekey)
    Foreign-key constraints:
        "fk45157998dd41734" FOREIGN KEY (contentid) REFERENCES content(contentid)
`notifications`: storage of page- and space-level watches.
                   Table "notifications"
         Column     |            Type             | Modifiers
    ----------------+-----------------------------+-----------
     notificationid | bigint                      | not null
     pageid         | bigint                      |
     spaceid        | bigint                      |
     username       | character varying(255)      | not null
     creator        | character varying(255)      |
     creationdate   | timestamp without time zone |
     lastmodifier   | character varying(255)      |
     lastmoddate    | timestamp without time zone |
    Indexes:
        "notifications_pkey" PRIMARY KEY, btree (notificationid)
        "n_pageid_idx" btree (pageid)
        "n_spaceid_idx" btree (spaceid)
    Foreign-key constraints:
        "fk594acc88c38fbea" FOREIGN KEY (pageid) REFERENCES content(contentid)
        "fk594acc8b2dc6081" FOREIGN KEY (spaceid) REFERENCES spaces(spaceid)
`pagetemplates`: acts as the back-end of the templates feature.
                  Table "pagetemplates"
        Column    |            Type             | Modifiers
    --------------+-----------------------------+-----------
     templateid   | bigint                      | not null
     templatename | character varying(255)      | not null
     templatedesc | character varying(255)      |
     labels       | character varying(255)      |
     content      | text                        |
     spaceid      | bigint                      |
     prevver      | bigint                      |
     version      | integer                     | not null
     creator      | character varying(255)      |
     creationdate | timestamp without time zone |
     lastmodifier | character varying(255)      |
     lastmoddate  | timestamp without time zone |
    Indexes:
        "pagetemplates_pkey" PRIMARY KEY, btree (templateid)
        "pt_prevver_idx" btree (prevver)
        "pt_spaceid_idx" btree (spaceid)
    Foreign-key constraints:
        "fkbc7ce96a17d4a070" FOREIGN KEY (prevver) REFERENCES pagetemplates(templateid)
        "fkbc7ce96ab2dc6081" FOREIGN KEY (spaceid) REFERENCES spaces(spaceid)
                 Table "trackbacklinks"
        Column    |            Type             | Modifiers
    --------------+-----------------------------+-----------
     linkid       | bigint                      | not null
     contenttype  | character varying(255)      | not null
     viewcount    | integer                     | not null
     url          | character varying(255)      | not null
     title        | character varying(255)      |
     blogname     | character varying(255)      |
     excerpt      | character varying(255)      |
     contentid    | bigint                      | not null
     creator      | character varying(255)      |
     creationdate | timestamp without time zone |
     lastmodifier | character varying(255)      |
     lastmoddate  | timestamp without time zone |
    Indexes:
        "trackbacklinks_pkey" PRIMARY KEY, btree (linkid)
        "tbl_contentid_idx" btree (contentid)
    Foreign-key constraints:
        "fkf6977a478dd41734" FOREIGN KEY (contentid) REFERENCES content(contentid)
`confancestors`: used to speed up permissions checks, by allowing quick lookup of all a page's ancestors.
          Table "confancestors"
          Column      |  Type   | Modifiers
    ------------------+---------+-----------
     descendentid     | bigint  | not null
     ancestorid       | bigint  | not null
     ancestorposition | integer | not null
    Indexes:
        "confancestors_pkey" PRIMARY KEY, btree (descendentid, ancestorposition)
    Foreign-key constraints:
        "fk9494e23c37e35a2e" FOREIGN KEY (ancestorid) REFERENCES content(contentid)
        "fk9494e23cc45e94dc" FOREIGN KEY (descendentid) REFERENCES content(contentid)
Attachments: confluence-2.8.1-postgresql.sql (application/octet-stream) Confluence_DB_Diagram.jpg (image/jpeg)

Document generated by Confluence on Nov 05, 2009 23:34