Archive Data Storage Structure

CopyStorm stores all archived data in the following database tables.

Table Name Description
CopyForceArchiveToken Mapping from string names to integer tokens. The table is used to minimize the size of other tables.
CopyForceArchiveFilter List of filter types that are used to determine if a record belongs in an archive.
CopyForceArchivePolicy This the root table for each individual archive.
CopyForceArchivePolicyStat Statistics about the data stored in each archive.
CopyForceArchiveRetentionRule Rules which describe data retention rules for groups of tables within an archive. This table allows a single archive to support different retention policies for various tables.
CopyForceArchiveFieldSet Description of the fields defined in Salesforce at the point a record was archived.
CopyForceArchiveRecord Archived records. This is the primary storage table for each archive.
CopyForceArchiveRecordRelat Relationships between record based on Salesforce references fields. The purpose of this table is to facilitate fast record relationship traversal without scanning the complete text of an archived record in the CopyForceArchiveRecord table.

The following diagram illustrates the relationships between the various tables.

Note: All physical database tables are prefixed with “CopyForce”.

CopyForceArchiveToken

Column Data Type Description
Id Integer Unique Id assigned to the Name.
Name String Name of a table, field, etc. in Salesforce.

CopyForceArchiveFilter

A CopyForceArchiveFilter describes the implementation of Java class which can determine if records should be archived. These classes are built into the CopyStorm/Archive subsystem and include:

  • Delete Records — determine if a record has been deleted in Salesforce.
  • Column Expression — evaluation of a user supplied expression to determine if a record matches.
  • All Changes — indicate if a record matches if it has any changes.
  • SQL Predicate — use a bit of user supplied SQL to determine if a record matches.
Column Data Type Description
Id Integer Unique internal id for the filter.
Name String Name of the filter as presented to a user.
Description String Description of the filter.
ClassName String Name of a Java class that implements the filter. These classes are typically developed by Capstorm rather than customers.
CreatedDate Timestamp Timestamp of when the record was created.
ModifiedDate Timestamp Timestamp of the most recent modification to the record.

CopyForceArchivePolicy

A CopyForceArchivePolicy record holds the definition for a  CopyStorm/Archive — if a record matches the rules for a CopyStormArchivePolicy then it will be written to the the policy’s associated database storage.

Column Data Type Description
Id Integer Unique internal id for the filter.
Name String Name of the filter as presented to a user.
Description String Description of the policy.
FilterId Integer Id of the CopyForceArchiveFilter that will be used to determine if a record matches the policy.
Active Boolean True if the policy should be automatically evaluated during a CopyStorm backup.
IncludeFormulaFields Boolean True if formula field values should be stored in an archived record.
IncludeNullFields Boolean True if field with an empty (null) value should be explicitly stored in an archive record.
IncludedTables Boolean Comma separated list of table names and/or regular expressions for which this policy is applicable.
ExcludedTables String Comma separated list of tables and/or regular expressions that should be ignored by the policy. If the IncludedTables value is not blank then the ExcludedTables value is ignored.
ExcludedReferenceFields String Comma separated list of Salesforce reference field names that should be excluded from the CopyForceArchiveRecordRelat table. The primary reason to exclude a reference field is to save space.
Argument String Argument that will be passed to the corresponding ArchiveFilterId logic. For example, the Column Expression filter has a argument the represents the expression.
ArchiveAfterInsert Boolean If True and the policy is active then evaluate a record for potential archival by the policy immediately after it is inserted into CopyStorm.
ArchiveBeforeUpdate Boolean If True and the policy is active then evaluate a record for potential archival by the policy just before it is updated in CopyStorm.
ArchiveAfterUpdate Boolean If True and the policy is active then evaluate a record for potential archival by the policy just after it is updated in CopyStorm.
RetentionDays Integer Default number of days a record will be retained in the archive before it is deleted. If blank then records will be kept forever.
RetentionVersions Integer Maximum number of versions to keep for a record even when the default retention days has not been met.
CreatedDate Timestamp Timestamp of when the record was created.
ModifiedDate Timestamp Timestamp of the most recent modification to the record.

CopyForceArchivePolicyStat

The CopyForceArchivePolicyStat contains table level statistics about a  CopyStorm/Archive.  Statistics about the tables contained in a CopyStorm/Archive are maintained automatically while the record count statistics require a scheduled job.

Column Data Type Description
PolicyId Integer Archive policy that owns the statistic
TableNameToken Integer Table for which the statistic applies.
NumRecords Integer Number of table records in the archive (updated on demand).
FirstArchiveDate Timestamp Timestamp of the earliest record in the archive (updated on demand).
LastArchiveDate Timestamp Timestamp of the oldest record in the archive (updated on demand).
LastRetentionRunDate Timestamp Most recent date on which the policy’s record retention date rules were run.
NumRetentionDeleted Integer Number of records deleted from the archive during the most recent record retention policy run.

CopyForceArchiveRetentionRule

The CopyForceArchiveRetentionRule  table contains rules that override the default data retention rules for an archive for specific tables. For example:

  • The default retention rule for an archive is set to 30 days.
  • For the Account,Contact, and Case table use a override retention rule to keep them in the archive for 180 days.

The algorithm which determines the policy for a specific table always selects the matching rule with the longest number of days.

Column Data Type Description
Id Integer Unique id for the rule.
PolicyId Integer The archive policy that owns the rule.
RetentionDays Integer Number to retain records before they are deleted from the archive.
IncludedTables String Comma separated list of table names and/or regular expressions for which the retention rule applies.

CopyForceArchiveFieldSet

The CopyForceArchiveFieldSet  table contains a unique configuration of the fields for a Saleforce table. When a record is archived:

  • If the current table field definitions have never been archived then create a record in the CopyForceArchiveFieldSet table.
  • Associate a CopyForceArchiveFieldSet with each archived record. This supports recovering both the schema and data for an archived record.
Column Data Type Description
Id Integer Unique id for the field set.
TableName String API Name of the Salesforce table used to define the field set.
TableLabel String Label of the Salesforce table used to define the field set.
Fingerprint String Unique fingerprint for the field set. Used for rapid searching and duplicate detection.
Fields XML Simple XML document describing the fields in a table.
CreatedDate Timestamp Date on which the field set was created.
ModifiedDate Timestamp Date on which the field set was last updated.

The fields in this type of record are represented as XML in the form below. Naturally, a complete record will contains many more fields and this is only a subset.

<ArchiveFields table="Account" version="24MAY18">
   <Field name="AccountNumber" label="Account Number" type="string" length="40" ndigits="0" scale="0" nameField="false" calculated="false" encrypted="false" custom="false"/>
   <Field name="AccountSource" label="Account Source" type="picklist" length="40" ndigits="0" scale="0" nameField="false" calculated="false" encrypted="false" custom="false"/>
   <Field name="Active__c" label="Active" type="boolean" length="0" ndigits="0" scale="0" nameField="false" calculated="false" encrypted="false" custom="true"/>
 
   <Field name="CLS_Account_Reconcile__c" label="Account Reconcile" type="string" length="1300" ndigits="0" scale="0" nameField="false" calculated="true" formula="IF( RecordType.DeveloperName = 'Procurement_Supplier', 'ESP', &#xA;IF( RecordType.DeveloperName = 'CLS_Customer_Account', 'CLS', &#xA;IF( RecordType.DeveloperName = 'Global_Account', 'GLOBAL', &#xA;IF( RecordType.DeveloperName = 'Supplier', 'SRM', &#xA;'' &#xA;))))" encrypted="false" custom="true"/>
</ArchiveFields>

CopyForceArchiveRecord

The CopyForceArchiveRecord  table contains all archived records. Since it can grow quite large sites may consideration partitioning the table based on the policyId column.

Column Data Type Description
Id Integer Unique id for the archived record.
SalesforceId String Salesforce Id of the archived record.
Name String Value of the Salesforce Name field for the record. If a record does not contain a Name field then the table name is used.
TableNameToken Integer Internal Id used to lookup the name of the Salesforce table.
PolicyId Integer Internal Id of the archive policy which created the record.
FieldSetId Integer Internal Id of the field set definitions in place when the record was archived.
Fingerprint String Unique string used to rapidly avoid redundant archive records.
FieldData XML Simple XML document describing data in the record.
SystemModStamp Timestamp The Salesforce SystemModStamp when the record was archived. In the rare case where a record does not have a SystemModStamp column a different column with the same semantics is selected.
CreatedDate Timestamp The Salesforce CreatedDate when the record was archived. In the rare case where a record does not have a CreatedDate column a different column with the same semantics is selected.
ModifiedDate Timestamp The Salesforce ModifiedDate when the record was archived. In the rare case where a record does not have a ModifiedDate column a different column with the same semantics is selected.
ArchivedDate Timestamp The timestamp when the record was written to the archive (UTC).

CopyForceArchiveRecord  field data can be read directly using the XML extensions for your database though most people will use SQL generated from the CopyStorm/ArchiveExplorer tool rather than handcraft XML. However, if you are a master of XML and database then this section illustrates the format of an archive record. Note:

  • In the example code formula column values were excluded.
  • In the example fields with empty values were excluded.
<FieldData>
   <Active__c>false</Active__c>
   <BillingCity>Orlando</BillingCity>
   <BillingCountry>Florida</BillingCountry>
   <BillingPostalCode>L4B 1Y3</BillingPostalCode>
   <BillingState>Orlando</BillingState>
   <BillingStreet>Walt Disney Street</BillingStreet>
   <CreatedById>0051a000000EVk1AAG</CreatedById>
   <CreatedDate>2015-04-23T15:06:27.000Z</CreatedDate>
   <Description>Walt Disney World is a leading entertainment destination.</Description>
   <Id>0011a000003B7M2AAK</Id>
   <Industry>Media</Industry>
   <IsCustomerPortal>false</IsCustomerPortal>
   <IsDeleted>false</IsDeleted>
   <LastActivityDate>2015-04-22</LastActivityDate>
   <LastModifiedById>0051a000000EVk1AAG</LastModifiedById>
   <LastModifiedDate>2015-04-23T15:06:27.000Z</LastModifiedDate>
   <Name>Walt Disney</Name>
   <NumberOfEmployees>14668</NumberOfEmployees>
   <OwnerId>0051a000000EVk1AAG</OwnerId>
   <Phone>(905) 232-8888</Phone>
   <SystemModstamp>2016-07-29T10:57:56.000Z</SystemModstamp>
   <Type>Prospect</Type>
</FieldData>

CopyForceArchiveRecordRelat

The purpose of the  CopyForceArchiveRecordRelat table is to support rapid traversal of relationships between archived records by avoiding scanning the XML to find relationships.

Column Data Type Description
ArchiveRecordId Integer Id of the CopyForceArchiveRecord entry that contains the relationship.
ParentTableToken String Salesforce tokenized name of the table that owns the relationship. Example: Contact
ParentFieldToken String Salesforce tokenized name of the table column that owns the relationship. Example: AccountId (for relationship Contact.ParentId)
ParentId String Salesforce Id of the record that owns the relationships.
ReferenceTableToken Integer Salesforce tokenized name of the table pointed to by the relationship. Example: Account.
ReferenceId Integer Salesforce Id of the record the relationship is pointing to.
ArchivedDate Timestamp The timestamp when the record was written to the archive (UTC).