Store TikTok Engagement Snapshots in SQLite with Python

Public engagement counters change over time. If every collection overwrites the previous row, the database can show the current value but cannot answer how the value changed between observations.

An append-only snapshot table solves that problem. This tutorial stores TikTok post metrics under a composite key of post_id and collected_at, then compares consecutive observations without confusing missing data with zero.

A snapshot is an observation

A post response can contain public play, like, comment, share, collect, and repost counts. Each number reflects what the platform exposed when collection ran. It is cumulative-looking public data, not a count of unique people and not campaign attribution.

For collection, a typical workflow resolves a username through scraper.tiktok.user.detail, then requests posts with scraper.tiktok.user.work. The User Work actor documentation describes the post request fields.

The database layer begins after a successful response. Give every run a UTC timestamp and store one row for every observed post.

Design the SQLite table

Identifiers remain TEXT, while counters use INTEGER. Nullable metric columns allow the database to represent a field that was absent from a response.

CREATE TABLE IF NOT EXISTS post_metric_snapshots (
    post_id TEXT NOT NULL,
    collected_at TEXT NOT NULL,
    account_unique_id TEXT NOT NULL,
    post_url TEXT,
    created_at TEXT,
    play_count INTEGER,
    like_count INTEGER,
    comment_count INTEGER,
    share_count INTEGER,
    collect_count INTEGER,
    repost_count INTEGER,
    collection_status TEXT NOT NULL,
    PRIMARY KEY (post_id, collected_at)
);

The composite primary key prevents the same post and observation time from being inserted twice. It still allows the same post to appear at every later collection time.

SQLite's CREATE TABLE documentation explains how primary keys and column constraints are enforced.

Insert observed posts with Python

Assume posts-response.json contains a raw, successfully collected User Work response. The script creates the table and appends normalized rows.

import json
import sqlite3
from datetime import datetime, timezone

DB_PATH = "tiktok-metrics.db"
SOURCE_PATH = "posts-response.json"


def as_id(value):
    return "" if value is None else str(value)


with open(SOURCE_PATH, encoding="utf-8") as source:
    response = json.load(source)

collected_at = datetime.now(timezone.utc).isoformat()
account = as_id(response.get("unique_id") or response.get("account_unique_id"))
items = response.get("items") or []

connection = sqlite3.connect(DB_PATH)
connection.execute("""
CREATE TABLE IF NOT EXISTS post_metric_snapshots (
    post_id TEXT NOT NULL,
    collected_at TEXT NOT NULL,
    account_unique_id TEXT NOT NULL,
    post_url TEXT,
    created_at TEXT,
    play_count INTEGER,
    like_count INTEGER,
    comment_count INTEGER,
    share_count INTEGER,
    collect_count INTEGER,
    repost_count INTEGER,
    collection_status TEXT NOT NULL,
    PRIMARY KEY (post_id, collected_at)
)
""")

for item in items:
    post_id = as_id(item.get("id") or item.get("post_id"))
    if not post_id:
        continue
    connection.execute(
        """
        INSERT INTO post_metric_snapshots VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """,
        (
            post_id,
            collected_at,
            account,
            item.get("url") or item.get("post_url"),
            item.get("create_time") or item.get("date"),
            item.get("play_count"),
            item.get("like_count"),
            item.get("comment_count"),
            item.get("share_count"),
            item.get("collect_count"),
            item.get("repost_count"),
            "success",
        ),
    )

connection.commit()
connection.close()
print(f"stored {len(items)} observed items at {collected_at}")

Keep the raw JSON outside or alongside the database. It is useful when the response schema changes or a later analysis needs a field that the first normalized table omitted.

Record failed collection separately

If the HTTP request fails, there are no observed post metrics. Do not insert zero-count snapshot rows. Instead, keep run-level status in a second table.

CREATE TABLE IF NOT EXISTS collection_runs (
    run_id TEXT PRIMARY KEY,
    account_unique_id TEXT NOT NULL,
    requested_at TEXT NOT NULL,
    completed_at TEXT,
    request_cursor TEXT,
    requested_count INTEGER,
    returned_count INTEGER,
    status TEXT NOT NULL,
    error_code TEXT
);

This design distinguishes three cases:

• A successful run returned the post and its counter did not change.
• A successful run did not include the post in the bounded sample.
• The collection run failed and produced no observation.

Only the first case supports a confirmed zero delta. The other two are coverage gaps.

Compare consecutive snapshots

SQLite window functions can pair each observation with the previous one for the same post.

WITH ordered AS (
    SELECT
        post_id,
        collected_at,
        play_count,
        like_count,
        LAG(collected_at) OVER (
            PARTITION BY post_id ORDER BY collected_at
        ) AS previous_collected_at,
        LAG(play_count) OVER (
            PARTITION BY post_id ORDER BY collected_at
        ) AS previous_play_count,
        LAG(like_count) OVER (
            PARTITION BY post_id ORDER BY collected_at
        ) AS previous_like_count
    FROM post_metric_snapshots
)
SELECT
    post_id,
    previous_collected_at,
    collected_at,
    CASE
        WHEN play_count IS NULL OR previous_play_count IS NULL THEN NULL
        ELSE play_count - previous_play_count
    END AS play_delta,
    CASE
        WHEN like_count IS NULL OR previous_like_count IS NULL THEN NULL
        ELSE like_count - previous_like_count
    END AS like_delta
FROM ordered
WHERE previous_collected_at IS NOT NULL
ORDER BY post_id, collected_at;

The CASE expressions matter. SQL arithmetic with null already returns null, but making the rule explicit helps reviewers understand that a missing metric cannot produce a numeric delta.

Negative deltas should remain visible. A platform correction, changed availability, or source behavior may explain them, but the storage layer should not silently clamp them to zero.

Compare equal observation windows

A delta is only comparable when its time window is comparable. One post observed over 24 hours and another observed over seven days should not appear in the same unlabeled growth ranking.

Store both timestamps and calculate the interval in the reporting layer. For scheduled monitoring, choose a cadence the project can maintain, then identify late or skipped runs. A regular schedule does not guarantee perfectly equal intervals, so report the actual timestamps.

Keep sample scope beside the metrics

Post collection is bounded by the request and the public content available at that time. Store:

• requested cursor and count
• returned item count
• account identifier
• collection status
• raw response location or checksum
• parser version when transformations may change

This metadata prevents a missing row from turning into a false performance statement. It also makes later backfills and parser revisions easier to review.

Scrapeless exposes the TikTok actors through Scraping API. The application controls scheduling, storage, and comparison logic.

What the deltas do not prove

The difference between two public counters is an observed change. It does not prove:

• unique reach
• viewer identity
• conversion or revenue
• which exposure caused an action
• that a post alone caused follower growth

Join first-party campaign or commerce data in a clearly labeled layer when those outcomes matter.

Conclusion

An append-only SQLite table turns isolated TikTok metrics into a reviewable history. Store one row per post and collection time, keep failed runs separate, preserve nulls, and calculate deltas only across valid observations. The result is small enough for local analysis and precise enough to reveal its own coverage gaps.

FAQ

Why use a composite primary key?

post_id identifies the content, while collected_at identifies the observation. Together they prevent duplicate rows without overwriting history.

Should a failed request create zero-valued rows?

No. Record the failed run in collection metadata and leave post metrics unobserved.

What does a negative delta mean?

It means the later public counter was lower than the earlier observation. Retain it for investigation rather than inventing a correction.

Is SQLite suitable for production monitoring?

It works well for compact local workflows. Larger concurrent systems may use another database, but the same identity, timestamp, null, and coverage rules still apply.

Disclaimer: This article is for technical education and does not provide legal advice. Follow applicable laws, platform terms, and organizational policies when collecting or using public web data.