SfSkills Domains Docs GitHub

Home / admin / system-field-behavior-and-audit

system-field-behavior-and-audit

Use when practitioners need to understand system-managed fields (CreatedDate, LastModifiedDate, SystemModstamp, CreatedById, LastModifiedById, IsDeleted) — their update behavior, indexing, and queryability. Triggers: 'difference between SystemModstamp and LastModifiedDate', 'how to query deleted records', 'set CreatedDate during data migration', 'why does LastModifiedDate not match SystemModstamp', 'Create Audit Fields permission'. NOT for a field's value-change history — use data/field-history-tracking. NOT for Shield Field Audit Trail — use security/field-audit-trail.

Open the skill folder on GitHub

System Field Behavior and Audit

Use this skill when a practitioner needs to understand how Salesforce automatically manages system fields on every record — when each field updates, which fields are indexed, how to query deleted records, and how to override audit fields during data migration.

Before Starting

Gather this context before working on anything in this domain:

Core Concepts

SystemModstamp vs. LastModifiedDate

Both fields record when a record was last changed, but they track different scopes of change:

SystemModstamp is indexed by the platform. LastModifiedDate is not indexed. For any SOQL query that filters by modification date (delta sync, incremental ETL, change data capture fallback), always use SystemModstamp for correctness and performance.

Create Audit Fields

The "Create Audit Fields" org permission (also called "Set Audit Fields upon Record Creation") allows API clients to set the four audit ownership fields on insert:

This is used exclusively during data migration to preserve historical timestamps and ownership from a source system. The permission must be enabled by Salesforce Support (Setup > Contact Support, or via case). It applies org-wide once enabled and works only on insert operations — you cannot retroactively change these fields on existing records via update.

IsDeleted and Soft Deletes

When a record is moved to the Recycle Bin, Salesforce sets IsDeleted = true on the record rather than physically removing it. The record remains queryable through two mechanisms:

Records in the Recycle Bin are retained for 15 days (configurable up to 15 days). After permanent deletion, records are no longer queryable by any means. The ALL ROWS keyword also returns archived records on objects that support archiving.

Common Patterns

Delta Sync with SystemModstamp

When to use: An integration or ETL process needs to pull only records modified since the last sync.

How it works:

SELECT Id, Name, AccountNumber, SystemModstamp
FROM Account
WHERE SystemModstamp > 2025-01-15T00:00:00Z
ORDER BY SystemModstamp ASC

Store the highest SystemModstamp value returned as the watermark for the next sync. Use ORDER BY SystemModstamp ASC so if the sync is interrupted, you can resume from the last processed record.

Why not LastModifiedDate: A workflow field update that fires after record save will update SystemModstamp but not LastModifiedDate. Using LastModifiedDate misses those changes, causing data drift between systems.

Data Migration with Audit Field Preservation

When to use: Migrating records from a legacy system or another Salesforce org where you need to preserve the original creation date and creator.

How it works:

  1. Confirm Create Audit Fields permission is enabled (Setup > User Interface > Enable "Set Audit Fields upon Record Creation", or contact Support).
  2. In your Data Loader or Apex script, include CreatedDate, CreatedById, LastModifiedDate, and LastModifiedById columns in the insert payload.
  3. The user performing the insert must have the "Set Audit Fields upon Record Creation" user permission assigned via permission set.
  4. Insert the records. The platform accepts the provided timestamps instead of auto-generating them.

Why not post-insert update: You cannot update audit fields after insert — the platform rejects DML updates to these fields even with the permission enabled.

Soft-Delete Recovery Query

When to use: A user accidentally deleted records and you need to identify what was lost before the Recycle Bin expires.

How it works:

SELECT Id, Name, CreatedDate, LastModifiedDate, LastModifiedById
FROM Contact
WHERE IsDeleted = true
  AND LastModifiedDate = LAST_N_DAYS:7
ALL ROWS

Use this to generate a list of recently deleted records for review. Admins can then undelete from the Recycle Bin via Setup or the Database.undelete() Apex method.

Decision Guidance

SituationRecommended FieldReason
Incremental ETL / delta sync queriesSystemModstampIndexed; captures all changes including automated processes
Display "last edited" to end usersLastModifiedDateReflects intentional human/API edits only
Audit trail of who last changed a recordLastModifiedByIdPaired with LastModifiedDate for human-edit attribution
Preserve historical dates during migrationCreatedDate (with Create Audit Fields)Only works on insert; requires org permission from Support
Find recently deleted recordsIsDeleted + ALL ROWS15-day Recycle Bin window; permanent deletes are unrecoverable
Replication / data synchronizationSystemModstamp + IsDeleted (ALL ROWS)Covers modifications, automated changes, and soft deletes

Step-by-step instructions for an AI agent or practitioner working on system field questions:

  1. Identify the use case — Determine whether the practitioner needs delta sync, data migration, soft-delete recovery, or general field understanding.
  2. Check field semantics — Confirm which system field applies. Use SystemModstamp for any query where completeness matters; use LastModifiedDate only for human-attribution display.
  3. Verify org permissions — If data migration with audit field override is needed, confirm Create Audit Fields is enabled at the org level and the user has the corresponding user permission.
  4. Write or review the SOQL — Ensure delta sync queries filter on SystemModstamp (not LastModifiedDate). Ensure soft-delete queries include ALL ROWS. Verify ORDER BY for resumable processing.
  5. Test in a sandbox — Trigger a workflow field update on a record, then compare SystemModstamp vs. LastModifiedDate to confirm they diverge as expected. Test Create Audit Fields with a sample insert.
  6. Document the watermark strategy — For delta sync, document where the high-watermark is stored, how failures are handled, and the expected sync frequency.

Review Checklist

Run through these before marking work in this area complete:

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

  1. SystemModstamp updates silently on formula recalculations — If a formula field references a parent record's field and that parent changes, the child's SystemModstamp may update without any direct change to the child record. This inflates delta sync volumes.
  2. Create Audit Fields works on insert only — Attempting to update CreatedDate or CreatedById on an existing record via DML silently fails or throws an error depending on context. There is no supported way to change these fields after record creation.
  3. ALL ROWS includes archived records — On objects with archiving enabled, ALL ROWS returns both soft-deleted and archived records. If you only want Recycle Bin items, add IsDeleted = true as an explicit filter.
  4. LastModifiedDate is not indexed — Filtering SOQL queries by LastModifiedDate > will result in a full table scan on large objects. SystemModstamp is the indexed alternative.
  5. Bulk API queryAll behaves differently — The Bulk API uses the queryAll operation to include deleted/archived records. The syntax differs from REST API's /queryAll/ endpoint — consult the Bulk API documentation for the correct job configuration.

Output Artifacts

ArtifactDescription
Field selection recommendationWhich system field to use for the practitioner's specific use case with rationale
Delta sync SOQL templateReady-to-use query with SystemModstamp filter and ORDER BY for resumable sync
Create Audit Fields enablement checklistSteps to enable the permission, assign to users, and validate with test insert
Soft-delete recovery queryALL ROWS query template for identifying and recovering deleted records

Official Sources Used

Package files