> For the complete documentation index, see [llms.txt](https://learn.capstorm.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://learn.capstorm.com/frequently-asked-questions/how-do-copystorm-archives-work/archive-data-storage-structure.md).

# Snapshot Data Storage Structure

CopyStorm stores all snapshot 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 a snapshot.                                                                                                                                                               |
| CopyForceArchivePolicy        | This the root table for each individual snapshot.                                                                                                                                                                                                |
| CopyForceArchivePolicyStat    | Statistics about the data stored in each snapshot.                                                                                                                                                                                               |
| CopyForceArchiveRetentionRule | Rules which describe data retention rules for groups of tables within a snapshot. This table allows a single snapshot to support different retention policies for various tables.                                                                |
| CopyForceArchiveFieldSet      | Description of the fields defined in Salesforce at the point a record was snapshot.                                                                                                                                                              |
| CopyForceArchiveRecord        | Snapshot records. This is the primary storage table for each snapshot.                                                                                                                                                                           |
| 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 a snapshot record in the *CopyForceArchiveRecord* table. |

The following diagram illustrates the relationships between the various tables.

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

![](https://4057376488-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsbSqfKw3vSfr8qwXao4j%2Fuploads%2Fgit-blob-7baafb77c14879e9985134e550f4e89e1c9d7411%2Fee76e03c-ArchiveDatabaseERD.jpg?alt=media)

### 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 snapshot. These classes are built into the CopyStorm Snapshot subsystem and include:

* Deleted 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 Snapshot — if a record matches the rules for a CopyStormArchivePolicy then it will be written to 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 snapshot 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 snapshot 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 snapshot by the policy just after it is updated in CopyStorm.                                                              |
| RetentionDays           | Integer   | Default number of days a record will be retained in the snapshot 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 Snapshot. Statistics about the tables contained in a CopyStorm Snapshot are maintained automatically while the record count statistics require a scheduled job.

| Column               | Data Type | Description                                                                                     |
| -------------------- | --------- | ----------------------------------------------------------------------------------------------- |
| PolicyId             | Integer   | Snapshot policy that owns the statistic                                                         |
| TableNameToken       | Integer   | Table for which the statistic applies.                                                          |
| NumRecords           | Integer   | Number of table records in the snapshot (updated on demand).                                    |
| FirstArchiveDate     | Timestamp | Timestamp of the earliest record in the snapshot (updated on demand).                           |
| LastArchiveDate      | Timestamp | Timestamp of the oldest record in the snapshot (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 snapshot 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 snapshot:

* If the current table field definitions have never been snapshot then create a record in the *CopyForceArchiveFieldSet* table\*.\*
* Associate a *CopyForceArchiveFieldSet* with each snapshot record. This supports recovering both the schema and data for a snapshot 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 snapshot 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 snapshot record.                                                                                                                                                            |
| SalesforceId   | String    | Salesforce Id of the snapshot 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 snapshot policy which created the record.                                                                                                                                  |
| FieldSetId     | Integer   | Internal Id of the field set definitions in place when the record was snapshot.                                                                                                               |
| Fingerprint    | String    | Unique string used to rapidly avoid redundant snapshot records.                                                                                                                               |
| FieldData      | XML       | Simple XML document describing data in the record.                                                                                                                                            |
| SystemModStamp | Timestamp | The Salesforce *SystemModStamp* when the record was snapshot. 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 snapshot. 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 snapshot. 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 snapshot (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 Snapshot Explorer* tool rather than handcraft XML. However, if you are a master of XML and database then this section illustrates the format of a snapshot 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 snapshot 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 snapshot (UTC).                                                                 |
