# UGC Engineer tracker: guide for coding agents Install: `npm install -g ugct`, then run `ugct agent-guide` for the same guide with your live schema and login state. Site: https://ugcengineer.app/tracker/ # ugct agent guide (v0.1.0) UGC Engineer fetches every tracked creator account (instagram, tiktok, x, youtube) once a day and keeps a daily snapshot of each post. `ugct` manages creators and campaigns and runs read-only SQL (DuckDB dialect) over your own workspace. State: NOT logged in. Ask the user to run `ugct signup --email ` (new workspace) or `ugct login` (existing key on stdin). Schema below: bundled copy. ## Rules - Pass `--format json` whenever you parse output. Errors arrive on stderr as {"error":{"code","message"}}. - Never print, echo or log the API key. `ugct whoami` shows only its prefix; check login state with it. - Prefer a template (`ugct templates run --param k=v`); write free SQL with `ugct sql` only when no template fits, and start from `ugct templates show `. - Before adding accounts check `ugct whoami` (accounts_used vs account_limit) and `ugct creators list` to avoid duplicates; accounts are `:` or a profile URL. - Removing a creator or archiving a campaign is not undoable by the CLI; confirm with the user first. - Exactly one SELECT (WITH ... SELECT allowed); no `;`, no DDL/DML, PRAGMA, SET, ATTACH, COPY, DESCRIBE, SUMMARIZE. - Only the tables below, unqualified (no schema or catalog prefix); never name a CTE like one of them. - Functions come from an allowlist: aggregates, window functions (lag, lead, row_number, rank), date_trunc, coalesce, round, CASE, CAST and arithmetic are safe; nothing that reads files, settings, environment or other databases. Table functions: only unnest, generate_series, range. - Results are capped (max_rows, default 10000); `truncated: true` means more rows exist. - Snapshots are daily: `posts` and `campaign_posts` hold each post's latest snapshot, `post_daily`/`account_daily` the history. Anchor windows on max(snapshot_date), not the clock. Join posts on (platform, post_id). - Metrics mean different things per platform: a view is a play on TikTok and Instagram, an impression on X and a view on YouTube, and each platform reports different interactions. Rates, averages, shares and rankings are per platform only, never one blended figure. Counts may be totalled across a creator's or campaign's platforms as a combined figure shown after the per-platform ones, saying which platforms it adds (a platform that does not report the metric is left out, not zero), that X views are impressions and that followers may overlap. Templates do this: per-platform rows, ranks within a platform, then a row with platform 'combined' (no rates) whose views_from / interactions_from / followers_from list the platforms it adds. - A null metric is not reported by the platform, never zero (Instagram: no views on images and carousels, no shares or saves; YouTube: views only). sum() skips nulls and is null when no post reports the metric: show that as not reported, not 0. Averages and rates use only the posts that report the metric: avg(views) skips nulls, and an engagement rate divides interactions by the views of the same posts (views IS NOT NULL). - Growth is per post: lag(...) OVER (PARTITION BY platform, post_id ORDER BY snapshot_date), kept only when the previous snapshot is the day before. A post's first snapshot is not growth, and never difference daily sums: posts enter and leave the fetched window. - `stale` = true: the account's latest fetch no longer lists the post (older than the lookback), its metrics are frozen at snapshot_date; say so, or filter NOT stale when current numbers matter. published_at null: the listing gave no date and the post is in no date window; report such undated posts (templates count them) instead of leaving them out silently. - Values: platform is instagram | tiktok | x | youtube; handle is lower case without @; media_type is video | image | carousel | text; campaigns.status is active | archived; ends_on null means open-ended. Exit codes: 0 ok; 1 API error (the gateway answered with an error); 2 usage error (bad command, option or parameter); 3 not logged in, or the API key was rejected (401); 4 local or network failure (unreachable API, bad response, config file, signup timeout). Error codes: `not_logged_in` no key: run `ugct signup` or `ugct login`; `unauthorized` key revoked or unknown: log in again; `payment_required` workspace not active: `ugct billing checkout --plan `; `sql_rejected` the SQL broke a rule above: fix it, do not retry unchanged; `query_timeout` narrow the query (date window, fewer joins); `rate_limited` too many concurrent queries (2 per workspace): run them one at a time; `limit_exceeded` account limit reached: see `ugct whoami`, upgrade or remove accounts; `validation_error` bad input (handle, URL, dates); `not_found` unknown id in this workspace; `conflict` duplicate or conflicting state, an archived creator, or billing_disabled; `internal` unexpected server failure: retry once, then report; `upstream_unavailable` service busy or down: retry later. ## Tables - `creators`(creator_id UUID, creator_name VARCHAR, platform VARCHAR, handle VARCHAR, added_at TIMESTAMP) — Tracked accounts of active creators: one row per creator and account. - `campaigns`(campaign_id UUID, name VARCHAR, starts_on DATE, ends_on DATE, caption_filter VARCHAR, status VARCHAR) — Campaigns of the workspace. - `campaign_creators`(campaign_id UUID, creator_id UUID) — Active members of the workspace's campaigns. - `posts`(platform VARCHAR, handle VARCHAR, creator_id UUID, creator_name VARCHAR, post_id VARCHAR, url VARCHAR, published_at TIMESTAMP, caption VARCHAR, media_type VARCHAR, duration_s DOUBLE, views BIGINT, likes BIGINT, comments BIGINT, shares BIGINT, saves BIGINT, followers BIGINT, snapshot_date DATE, observed_at TIMESTAMP, stale BOOLEAN) — The latest snapshot of each post of a tracked account; rates, averages and ranks per platform, cross-platform sums only beside the per-platform figures, labelled combined. - `post_daily`(platform VARCHAR, handle VARCHAR, creator_id UUID, creator_name VARCHAR, post_id VARCHAR, snapshot_date DATE, views BIGINT, likes BIGINT, comments BIGINT, shares BIGINT, saves BIGINT, followers BIGINT, observed_at TIMESTAMP) — One row per post per snapshot day. Growth = a post's difference between consecutive days; a first snapshot is not growth; never difference daily sums. - `campaign_posts`(campaign_id UUID, platform VARCHAR, handle VARCHAR, creator_id UUID, creator_name VARCHAR, post_id VARCHAR, url VARCHAR, published_at TIMESTAMP, caption VARCHAR, media_type VARCHAR, duration_s DOUBLE, views BIGINT, likes BIGINT, comments BIGINT, shares BIGINT, saves BIGINT, followers BIGINT, snapshot_date DATE, observed_at TIMESTAMP, stale BOOLEAN) — Posts of a campaign's active members published within [starts_on, ends_on] whose caption matches caption_filter; latest snapshot. - `account_daily`(platform VARCHAR, handle VARCHAR, creator_id UUID, creator_name VARCHAR, snapshot_date DATE, followers BIGINT, posts_observed BIGINT) — Per account per snapshot day: followers (no post-metric sums; take growth from post_daily). ## Templates (`ugct templates run --param k=v`; no default = required) - `campaign-creator-breakdown` [campaign_id:uuid] — Per member creator of a campaign and platform: accounts, posts, views, engagement, share of the platform's campaign views, last post date and stale posts; a member's platforms without posts are included, and a member without accounts gives one row with a null platform. Then per member with posts one row with platform 'combined' adding the counts across platforms. The combined row has no rate or share (those are per platform only); its views mix TikTok and Instagram plays, X impressions and YouTube views, and add only the platforms that report views: views_from names them. - `campaign-daily-growth` [campaign_id:uuid days:int=30] — Per snapshot day and platform for one campaign: accounts and posts observed, and the views and interactions gained since the day before; then per day one row with platform 'combined' adding them across platforms (its views mix TikTok and Instagram plays, X impressions and YouTube views, and add only the platforms with a views gain that day: views_from names them). A gain adds the growth of the posts observed on both days and the counts of posts first observed that day and published since the day before; a post that enters tracking with its history (a newly added account) adds nothing. A gain is null when the platform has no snapshot the day before or no post reports the metric. The latest day can still be filling while the day's fetches run: compare accounts_observed with the day before. - `campaign-overview` [campaign_id:uuid] — A campaign's dates and members, then one row per platform: posts, totals at each post's latest snapshot, engagement rate, average views, views gained in the latest fetch, stale and undated posts; then one row with platform 'combined' that adds the counts across platforms. The combined row has no rate or average (those are per platform only); its views mix TikTok and Instagram plays, X impressions and YouTube views, and each total adds only the platforms that report the metric: views_from and interactions_from name them. A total is null when no post of the platform reports the metric; the average and the engagement rate count only posts that report the metrics. views_gained_last_fetch adds, over the posts still listed, each post's growth since the day before and the views of posts first observed and published since the day before. stale_posts count with numbers frozen at an older snapshot; undated_posts match the caption filter but have no publication date, so they are not counted. A campaign without posts gives one row with a null platform. - `campaign-top-posts` [campaign_id:uuid top:int=10] — The best posts of a campaign on each platform by views at their latest snapshot, ranked within the platform (views are not comparable across platforms), with engagement rate and the stale flag. - `campaigns-summary` [status:text=active] — One row per campaign with the given status and platform: dates, members, posts, views and engagement at each post's latest snapshot, stale posts; then per campaign one row with platform 'combined' adding the counts across platforms. A campaign without posts gives one row with a null platform. The combined row has no engagement rate (rates are per platform only); its views mix TikTok and Instagram plays, X impressions and YouTube views, and add only the platforms that report views: views_from names them. - `creator-leaderboard` [days:int=30] — Per platform, creators ranked by views of the posts they published in the last N days (relative to the latest snapshot); ranks are within a platform because views are not comparable across platforms, and a creator's platform without posts is included. Then per creator with several platforms one unranked row with platform 'combined' adding the counts. Averages and engagement rate count only posts that report the metrics and exist per platform only. The combined views mix TikTok and Instagram plays, X impressions and YouTube views and add only the platforms that report views (views_from). stale_posts count with views frozen at an older snapshot (windows longer than the lookback); undated_posts (listed but without a publication date) are in no window and not counted. - `follower-growth` [days:int=30] — Per tracked account: followers at the first and the latest snapshot day within the last N days, the gain and the growth percentage; an account without a follower count in the window (no snapshot yet, or no post listed) gives a row of nulls. Then per creator with several accounts one row with platform 'combined' adding the accounts that have both counts (followers_from names them). The combined row has no growth percentage, and its sum counts a person who follows several of the creator's accounts once per account. - `new-posts` [days:int=3] — Posts published in the last N days (relative to the latest snapshot), newest first, with their latest metrics; views are per platform and never comparable across platforms. Posts without a publication date never appear here: platform-mix and creator-leaderboard count them as undated_posts. - `platform-mix` [days:int=30] — Per platform over posts published in the last N days: accounts, creators, posts and share of posts, views, average views and engagement rate. Views and rates mean different things on each platform (plays on TikTok and Instagram, impressions on X, views on YouTube; different interactions), so the rows sit side by side and are never summed or compared as one scale. The average and the rate count only posts that report the metrics; undated_posts (listed but without a publication date) are in no window and not counted. A last row with platform 'combined' adds the counts across platforms, without average or rate; its views mix plays, impressions and YouTube views and add only the platforms in views_from. - `post-velocity` [top:int=10] — Per platform, the posts gaining views fastest: views gained per day between each post's two latest snapshots, for posts still listed by their account's latest fetch; ranked within the platform because views are not comparable across platforms. A post's first snapshot is not a gain. - `posting-consistency` [days:int=28] — Per creator over the last N days: posts on all platforms, active posting days, average and longest gap between posts, and days since the last post. undated_posts (listed but without a publication date) are not in these counts, so a creator whose posts are all undated reads as inactive unless it is non-zero. - `underperforming-posts` [days:int=30 threshold_pct:int=50 min_age_days:int=2] — Posts of the last N days, at least min_age_days old, whose views are below threshold_pct percent of their account's average over the same window (one account, so one platform's views). Only accounts with 3+ such posts that report views; posts without views and stale posts (views frozen at an older snapshot) are skipped. ## Commands (global: --format json|table|csv, --api-url ) - `ugct signup --email [--name ] [--plan ] [--no-wait] [--resume] [--poll-interval ] [--timeout ] [--force]` — Create a workspace, print the checkout URL, wait until it is active and store the API key - `ugct login (reads the API key from stdin, or prompts without echo on a terminal)` — Verify an API key and store it in the config file - `ugct logout` — Remove the stored API key from the config file - `ugct whoami` — Show the workspace, plan, usage and the key prefix in use - `ugct keys list` — List API keys (prefixes only) - `ugct keys create [--name ]` — Create an API key; the full key is shown once - `ugct keys revoke ` — Revoke an API key - `ugct creators list [--include-archived]` — List creators and their accounts (archived ones only with --include-archived) - `ugct creators add --name [--notes ] [--account ...]` — Add a creator with zero or more accounts - `ugct creators show ` — Show one creator - `ugct creators update [--name ] [--notes | --clear-notes]` — Rename a creator or change the notes (accounts: use add-account / remove-account) - `ugct creators remove ` — Archive a creator (its accounts stop counting toward the limit) - `ugct creators add-account ` — Track one more account for a creator - `ugct creators remove-account ` — Stop tracking one account of a creator - `ugct campaigns list [--include-archived]` — List campaigns (archived ones only with --include-archived) - `ugct campaigns create --name --starts-on [--ends-on ] [--caption-filter ] [--creator ...]` — Create a campaign - `ugct campaigns show ` — Show one campaign - `ugct campaigns update [--name ] [--starts-on ] [--ends-on | --clear-ends-on] [--caption-filter | --clear-caption-filter] [--status active|archived]` — Change a campaign's name, dates, caption filter or status (--status active un-archives) - `ugct campaigns archive ` — Archive a campaign - `ugct campaigns add-creators [ ...]` — Add creators to a campaign - `ugct campaigns remove-creator ` — Remove a creator from a campaign - `ugct status` — Per tracked account: last snapshot date and post count - `ugct schema` — The SQL tables and columns you can query - `ugct sql | sql --file | sql - [--max-rows ]` — Run one read-only SELECT against your workspace - `ugct templates list` — List the bundled SQL templates and their params - `ugct templates show ` — Print a template's SQL - `ugct templates run [--param = ...] [--max-rows ] [--dry-run]` — Fill a template's params and run it (--dry-run prints the SQL instead) - `ugct billing checkout --plan ` — Get a Stripe Checkout URL to start or change a subscription - `ugct billing portal` — Get a Stripe customer portal URL - `ugct agent-guide` — Compact guide for coding agents: rules, schema, templates, commands