A post’s daily metrics may change after the first time they are fetched. Suppose an integration pulls daily views for a post on Monday and stores 420 views for Sunday. On Tuesday, the same Sunday bucket comes back as 435. Appending that second response creates two rows for the same measurement; a report that sums them will show 855.
A minimal sync can use (account_id, post_id, metric_date, metric_name) as the unique key. For each poll, fetch the last seven days of daily post insights and upsert each value: insert a new combination, or replace its stored value if it already exists. Record fetched_at separately so the report date remains Sunday even though the revision arrived Tuesday. Log changed values with the previous value, new value, post ID, and fetch time. A failed fetch should leave the existing value intact and mark that date range for retry rather than writing a zero.
That seven-day window is a tradeoff, not a universal setting. A longer refresh catches later revisions but consumes more API calls, especially across many accounts and posts. Also, keep daily buckets distinct from lifetime totals: a lifetime count fetched on two dates is two snapshots, not two amounts to add together.
What refresh window and change-logging approach have worked for members syncing post-level insights?