- 7 minutes to read

Audit Reports and Retention

This spoke covers compliance audit reports, data retention policies, SQL query examples, and GDPR deletion workflows for Mapify's change tracking system. For the full overview, see Multi-User Collaboration.

Audit Report Formats for Compliance

Mapify includes four pre-built compliance report types for SOX, GDPR, and HIPAA.

1. Change Activity Report (All Entities)

Purpose: Comprehensive report of all changes for a given period.

Fields: Entity Type, Entity Name, Changed By, Changed Date, Field Name, Old Value, New Value, Change Reason

C# Excel generation (EPPlus):

public async Task<byte[]> GenerateChangeActivityReportAsync(
    DateTime startDate,
    DateTime endDate,
    string entityType = null)
{
    using (var package = new ExcelPackage())
    {
        var worksheet = package.Workbook.Worksheets.Add("Change Activity");

        // Header row
        string[] headers = {
            "Entity Type", "Entity Name", "Field Name",
            "Old Value", "New Value", "Changed By", "Changed Date", "Change Reason"
        };
        for (int col = 0; col < headers.Length; col++)
            worksheet.Cells[1, col + 1].Value = headers[col];

        // Style header: bold, blue background, white text
        using (var range = worksheet.Cells[1, 1, 1, headers.Length])
        {
            range.Style.Font.Bold = true;
            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
            range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0, 112, 192));
            range.Style.Font.Color.SetColor(Color.White);
        }

        // Fetch and populate data
        var query = _context.EntityChangeLogs
            .Where(c => c.ChangedDate >= startDate && c.ChangedDate <= endDate);
        if (!string.IsNullOrEmpty(entityType))
            query = query.Where(c => c.EntityType == entityType);

        var changes = await query.OrderByDescending(c => c.ChangedDate).ToListAsync();

        int row = 2;
        foreach (var change in changes)
        {
            worksheet.Cells[row, 1].Value = change.EntityType;
            worksheet.Cells[row, 2].Value = change.EntityName;
            worksheet.Cells[row, 3].Value = change.FieldName ?? "(Entity Created)";
            worksheet.Cells[row, 4].Value = change.OldValue ?? "";
            worksheet.Cells[row, 5].Value = change.NewValue ?? "";
            worksheet.Cells[row, 6].Value = change.ChangedBy;
            worksheet.Cells[row, 7].Value = change.ChangedDate;
            worksheet.Cells[row, 7].Style.Numberformat.Format = "yyyy-mm-dd hh:mm:ss";
            worksheet.Cells[row, 8].Value = change.ChangeReason ?? "";
            row++;
        }

        worksheet.Cells.AutoFitColumns();
        return package.GetAsByteArray();
    }
}

2. User Activity Report (By Person)

Purpose: Track all changes by specific users — useful for access reviews and offboarding.

Fields: User Name, User Email, Total Changes, Entity Types Modified, Most Frequently Modified Entities

Sample output:

User Activity Report – January 2026

| User Name     | Email              | Total Changes | Integrations | Systems | Most Edited Entity          |
|:---|:---|:---|:---|:---|:---|
| Alice Johnson | alice@contoso.com  | 142           | 98           | 32      | SAP to Salesforce (23×)    |
| Bob Taylor    | bob@contoso.com    | 87            | 45           | 28      | Dynamics CRM API (18×)     |
| Charlie Davis | charlie@contoso.com| 63            | 40           | 15      | Legacy EDI Bridge (15×)    |

3. Compliance Audit Report (Filtered)

Purpose: Audit changes to entities tagged for specific compliance scope (GDPR, SOX, HIPAA, PCI-DSS).

SQL query example:

-- All changes to GDPR-regulated integrations in Q1 2026
SELECT
    ecl.EntityType,
    ecl.EntityName,
    ecl.FieldName,
    ecl.OldValue,
    ecl.NewValue,
    ecl.ChangedBy,
    ecl.ChangedDate,
    ecl.ChangeReason,
    cm.MetadataValue AS ComplianceTag
FROM EntityChangeLog ecl
INNER JOIN Entities e ON ecl.EntityId = e.EntityId
INNER JOIN CustomMetadata cm ON e.EntityId = cm.EntityId
WHERE cm.MetadataKey = 'ComplianceTag'
  AND cm.MetadataValue LIKE '%GDPR%'
  AND ecl.ChangedDate >= '2026-01-01'
  AND ecl.ChangedDate < '2026-04-01'
ORDER BY ecl.ChangedDate DESC;

4. High-Risk Change Report

Purpose: Extra scrutiny for critical production system changes, off-hours deployments, and undocumented changes.

Report Criteria:

  • Changes to production environments
  • Changes to entities tagged "Critical"
  • Changes outside business hours (off-hours)
  • Changes without a ChangeReason value

Sample SQL:

-- High-risk changes: production, off-hours, or no change reason
SELECT
    ecl.EntityName,
    cm.MetadataValue   AS Environment,
    ecl.ChangedBy,
    ecl.ChangedDate,
    ecl.FieldName,
    ecl.ChangeReason,
    CASE
        WHEN ecl.ChangeReason IS NULL                    THEN 'HIGH'
        WHEN DATEPART(HOUR, ecl.ChangedDate) NOT BETWEEN 8 AND 18 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS RiskLevel
FROM EntityChangeLog ecl
INNER JOIN Entities e ON ecl.EntityId = e.EntityId
INNER JOIN CustomMetadata cm ON e.EntityId = cm.EntityId AND cm.MetadataKey = 'Environment'
WHERE cm.MetadataValue = 'Production'
  AND ecl.ChangedDate >= DATEADD(DAY, -30, GETUTCDATE())
ORDER BY RiskLevel, ecl.ChangedDate DESC;

Audit Query Examples

Useful SQL queries for governance and investigations:

-- 1. Full change history for a specific entity
SELECT ChangeType, FieldName, OldValue, NewValue, ChangedBy, ChangedDate, ChangeReason
FROM EntityChangeLog
WHERE EntityId = '3fa85f64-5717-4562-b3fc-2c963f66afa6'
ORDER BY ChangedDate DESC;

-- 2. All changes by a specific user in last 30 days
SELECT EntityType, EntityName, ChangeType, FieldName, ChangedDate, ChangeReason
FROM EntityChangeLog
WHERE ChangedBy = 'alice.johnson@contoso.com'
  AND ChangedDate >= DATEADD(DAY, -30, GETUTCDATE())
ORDER BY ChangedDate DESC;

-- 3. High-churn entities — changed more than 10 times in 30 days
SELECT EntityName, EntityType, COUNT(*) AS ChangeCount
FROM EntityChangeLog
WHERE ChangedDate >= DATEADD(DAY, -30, GETUTCDATE())
GROUP BY EntityId, EntityName, EntityType
HAVING COUNT(*) > 10
ORDER BY ChangeCount DESC;

-- 4. Changes without documentation (no ChangeReason)
SELECT EntityName, ChangedBy, ChangedDate, FieldName
FROM EntityChangeLog
WHERE ChangeReason IS NULL
  AND ChangedDate >= DATEADD(DAY, -30, GETUTCDATE())
ORDER BY ChangedDate DESC;

-- 5. Off-hours changes (outside 8 AM – 6 PM)
SELECT EntityName, ChangedBy, ChangedDate, FieldName, ChangeReason
FROM EntityChangeLog
WHERE (DATEPART(HOUR, ChangedDate) < 8 OR DATEPART(HOUR, ChangedDate) > 18)
  AND ChangedDate >= DATEADD(DAY, -30, GETUTCDATE())
ORDER BY ChangedDate DESC;

-- 6. Most frequently changed fields across all entities
SELECT FieldName, COUNT(*) AS ChangeCount
FROM EntityChangeLog
WHERE ChangedDate >= DATEADD(DAY, -90, GETUTCDATE())
  AND ChangeType = 'UPDATE'
GROUP BY FieldName
ORDER BY ChangeCount DESC;

-- 7. Rollback detection (same field changed back to original value)
WITH ChangeHistory AS (
    SELECT *, LEAD(NewValue) OVER (PARTITION BY EntityId, FieldName ORDER BY ChangedDate) AS NextValue
    FROM EntityChangeLog
    WHERE EntityId = '3fa85f64-5717-4562-b3fc-2c963f66afa6'
)
SELECT EntityName, FieldName, OldValue, NewValue, ChangedBy, ChangedDate
FROM ChangeHistory
WHERE NewValue = LEAD(OldValue) OVER (PARTITION BY EntityId, FieldName ORDER BY ChangedDate);

Retention Policy and Data Management

Environment Default Retention Configurable Range Compliance Notes
Production 365 days (1 year) 90 days – Indefinite SOX requires 7 years for financial systems; GDPR allows deletion after purpose fulfilled
Test/QA 90 days 30 days – 180 days No regulatory requirement; focus on performance data
Development 30 days 7 days – 90 days No compliance requirements; short retention minimizes storage

Configuration (appsettings.json):

{
    "ChangeTracking": {
        "RetentionDays": {
            "Production": 365,
            "Test": 90,
            "Development": 30
        },
        "ComplianceOverrides": {
            "SOX": 2555,
            "HIPAA": 2190,
            "GDPR": 365
        },
        "AutoDelete": {
            "Enabled": true,
            "Schedule": "0 2 * * 0",
            "ArchiveBeforeDelete": true,
            "ArchivePath": "\\\\fileserver\\audit-archives"
        }
    }
}

Automated cleanup background service:

public class AuditLogCleanupService : BackgroundService
{
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            var retentionDate = DateTime.UtcNow.AddDays(-_config.RetentionDays.Production);

            // Archive before deleting
            if (_config.AutoDelete.ArchiveBeforeDelete)
            {
                var expiredLogs = await _context.EntityChangeLogs
                    .Where(e => e.ChangedDate < retentionDate)
                    .ToListAsync();

                await ExportToCsvArchive(expiredLogs, _config.AutoDelete.ArchivePath);
            }

            // Delete expired records
            var deleted = await _context.EntityChangeLogs
                .Where(e => e.ChangedDate < retentionDate)
                .ExecuteDeleteAsync();

            _logger.LogInformation(
                "Audit log cleanup: deleted {Count} records older than {RetentionDate}",
                deleted, retentionDate);

            // Run weekly (schedule: Sunday 2 AM)
            await Task.Delay(TimeSpan.FromDays(7), stoppingToken);
        }
    }
}

GDPR Compliance and Right to Deletion

Key Principle: Change logs containing personal data must respect GDPR "right to be forgotten" (Article 17), but audit retention requirements (SOX, HIPAA) may override in certain cases. Consult legal counsel for your regulatory context.

GDPR Deletion Workflow

  1. User submits deletion request via formal GDPR request form
  2. Legal review determines if audit retention overrides apply (financial systems = 7 years SOX)
  3. If deletion approved:
    • Anonymize ChangedBy field → replace with "User_DELETED_20260119"
    • Preserve change metadata (what/when) for audit continuity
    • Delete or redact PII in OldValue/NewValue fields
  4. Generate deletion certificate for the data subject

SQL Anonymization Script 

DECLARE @UserEmail NVARCHAR(255) = 'alice.johnson@contoso.com';
DECLARE @AnonymizedId NVARCHAR(255) =
    CONCAT('User_DELETED_', FORMAT(GETUTCDATE(), 'yyyyMMdd'));

BEGIN TRANSACTION;

UPDATE EntityChangeLog
SET ChangedBy = @AnonymizedId
WHERE ChangedBy = @UserEmail;

-- Redact PII in change values
UPDATE EntityChangeLog
SET OldValue = '[REDACTED]'
WHERE OldValue LIKE '%' + @UserEmail + '%';

UPDATE EntityChangeLog
SET NewValue = '[REDACTED]'
WHERE NewValue LIKE '%' + @UserEmail + '%';

COMMIT TRANSACTION;

-- Log the GDPR deletion for compliance audit
INSERT INTO GDPRDeletionLog (UserEmail, DeletionDate, RecordsAffected)
VALUES (@UserEmail, GETUTCDATE(), @@ROWCOUNT);

For full GDPR, SOX, HIPAA, and PCI-DSS compliance guidelines, see the Compliance and Data Governance Guide.