Signatures missing "date_created" and "date_modified" timestamps

[mlehotskylf]

Problem Description

I've identified 1,293 valid, approved signatures in the DynamoDB cla-prod-signatures table that are missing both date_created and date_modified timestamps. About 4k signatures are missing date_created alone.
These records appear to be legitimate signatures (most are signed and approved) but lack timestamp metadata.

Impact

• ~1.4% of signatures lack both timestamps and all of them were synced by Fivetran to Snowflake between 2024-03-08 and 2025-11-11 (so some of them are very recent!)
• ~5% are lacking date_created alone

Record Characteristics:

• ✅ most of these records have signature_signed = true and signature_approved = true
• ✅ most of these signatures seem to be ECLA
• ❌ 1,293 records have both date_created and date_modified as NULL
• ❌ 4.3k records have date_created as NULL

Fivetran Sync Timeline:

• Earliest sync: 2024-03-08
• Latest sync: 2025-11-11
• Recent additions: ~150 signatures without timestamp synced in the last month

Sample Affected Signatures:

  SELECT
      s.data:signature_id::varchar AS signature_id,
      s.data:signature_project_id::varchar AS cla_group_id,
      s.data:signature_type::varchar AS signature_type,
      s.data:signature_reference_type::varchar AS signature_reference_type,
      s.data:signature_reference_id::varchar AS signature_reference_id,
      s.data:signature_signed::boolean AS signature_signed,
      s.data:signature_approved::boolean AS signature_approved,
      s.data:user_email::varchar AS user_email,
      s.data:user_name::varchar AS user_name,
      s.data:user_lf_username::varchar AS user_lf_username,
      s.data:user_github_username::varchar AS user_github_username,
      s.data:signatory_name::varchar AS signatory_name,
      -- These are the problematic fields - both are NULL
      s.data:date_created AS date_created,
      s.data:date_modified AS date_modified,
      -- Fivetran metadata (can be used as reference)
      s._fivetran_synced AS fivetran_first_synced,
      s._fivetran_deleted
  FROM
      FIVETRAN_INGEST.DYNAMODB_PRODUCT_US_EAST_1.CLA_PROD_SIGNATURES s
  WHERE
      NOT s._fivetran_deleted
      AND (s.data:date_created IS NULL OR s.data:date_modified IS NULL)
  ORDER BY s._fivetran_synced DESC;

Expected Behavior

All signature records in DynamoDB should have date_created and date_modified populated.

Action Required

1. Investigation: Determine why these signatures were created without timestamps
2. Prevention: Ensure application code sets these fields on all new/updated signatures
3. Validation: Add DynamoDB validation/constraints to prevent future signatures without timestamps
4. Backfill: Populate missing date_created and date_modified for affected records. This may be little tricky as we don't have the timestamp at all. It may be possible for ICLAs and CCLAs because we have a Docusign PDF, but I'm not sure how we can do this for ECLAs.

Additional Context
I discovered this during timestamp parsing improvements in Snowflake. The issue appears to be in the source EasyCLA application/database, not a Fivetran sync issue, because I checked those records in DynamoDB and they don't have timestamp.

[lukaszgryglicki]

PR up: #4864.

[lukaszgryglicki]

Prod PR up: #4865.
cc @mlehotskylf @ahmedomosanya

[lukaszgryglicki]

This shouldn't be closed yet. -GitHub did that automagically, reopened.