Understanding Alfresco Meta Database Structure

Alfresco is a well-designed open source Enterprise Content Management (ECM) system. The initial release of Alfresco was in 2005. Comparing IBM FileNet (1980s), Oracle WebCenter Content (1980s, Stellent UCM), OpenText (1990s) and Documentum (1990s, Acquired by OpenText in 2016), Alfresco is quite new, which allows Alfresco build on completely new technology stacks. There’s no doubt that Alfresco is one of the best designed ECM system from architecture, performance and Web UI perspectives.

Alfresco is designed to run on Alfresco Application Server, as a series web applications, including alfresco.war, share.war, solr.war. Alfresco.war provides the platform services to mobile access (through REST APIs). Share.war offers Web UI. Apache Solr 4 provides the indexing on content which enables search services. Alfresco.war supports embedded Activiti Workflow Engine, directory server (LDAP) and SMTP server (email) services.

All of these services are based on content repository which consists of Alfresco meta database and file system. Content metadata is stored in relational database, such as PostgreSQL, MySQL or Oracle, etc. The actual content is stored in file system.

Although it’s not recommended to change Alfresco meta database, it’s quite helpful to understand its structure. This analysis is based on Alfresco Community Edition 5.2 with meta database on PostgreSQL 9.4.12. The login username and password are: alfresco/admin. There are 71 entities in the data model. I generated three Alfresco meta database diagrams for your reference and downloading:

The data model consists of following modules (with main tables):

  • Core Content Module
    • alf_node
    • alf_node_properties
    • alf_node_assoc
    • alf_node_aspects
    • alf_child_assoc
    • alf_store
    • alf_transaction
    • alf_subscription
    • alf_locale
  • Access Control Module
    • alf_access_control_entry
    • alf_authority
    • alf_authority_alias
    • alf_ace_context
    • alf_permission
    • alf_acl_member
    • alf_access_control_list
  • Auditing and Content Data Module
    • alf_audit_model
    • alf_audit_app
    • alf_audit_entry
    • alf_content_data
    • alf_content_url_encryption
    • alf_content_url
    • alf_encoding
    • alf_mimetype

  • Activiti / Workflow Module
    • act_ru_execution
    • act_ru_task
    • act_ru_variable
    • act_ru_event_subscr
    • act_ru_identitylink
    • act_ru_job
    • act_re_model
    • act_re_procdef
    • act_re_deployment
  • Other Orphan Tables
    • act_hi_taskinst
    • act_hi_detail
    • act_hi_procinst
    • act_hi_varinst
    • act_hi_actinst
    • act_hi_attachment
    • alf_applied_patch
    • act_id_membership
    • act_id_group
    • act_id_user
    • alf_tenant
    • alf_auth_status
    • alf_activity_feed
    • alf_activity_post

Lionsgatesoft.com consultant has 10+ years working experience on ECM, WCM and EAI. Should you have any questions regarding Alfresco and content integration, please feel free to contact us.