tl

name	tl
tl-blurb	data analyst (smart query router)
description	Query and analyze YouTube sponsorship data using the `tl` CLI. Use this skill for finding channels, brands and sponsorships, and for data exploration, including counts, metrics, trends, time-series, distributions, single-record drill-downs, revenue / pipeline-weighting math, view-curve analysis, cross-source business questions. Examples: "How many deals did we close last quarter?", "What's the weighted pipeline by sales owner?", "Show me the view curve for video X", "Find mentions of Surfshark in transcripts", "Investigate this video", "Find channels...", "Find brands...".

ThoughtLeaders Data Analyst

Core Principles

Run the tl CLI to query ThoughtLeaders' sponsorship platform data. Use it to answer questions about deals, channels, brands, uploads, metrics, etc. Use raw database queries via tl db pg|fb|es for everything. One exception: resolving a named channel or brand (name, YouTube URL, @handle, video URL) to an ID is always tl channels find / tl brands find — never ILIKE on names.

If doing a database query, follow this recipe:

• First, run tl whoami to confirm the API is working and to find out user metadata and limits.
• Always read references/business-glossary.md
• If doing a PostgreSQL (pg) query: always first read references/postgres-schema.md, then run tl schema pg
• If doing an ElasticSearch (es) query: always first read references/elasticsearch-schema.md, then run tl schema es
• If doing a Firebolt (fb) query: always first read references/firebolt-schema.md, then run tl schema fb

Process data with shell tools, not your context window. Don't pull large result sets into your reasoning context just to filter, sort, count, or extract a field - that wastes tokens and slows you down. Pipe tl … --json (or --csv, or --toon) into jq (for JSON), rg or duckdb (for CSV), or yq (for YAML) as appropriate, and read only the answer back. Pick the tool by shape:

• jq — filter, project, and transform JSON. The default for tl … --json post-processing.

  tl db pg "SELECT id, weighted_price FROM thoughtleaders_adlink
            WHERE publish_status = 3 AND price > 5000
            LIMIT 10000 OFFSET 0" --json \
    | jq '.results[] | {id, price: .weighted_price}'
  

• yq — same idea for YAML/TOML, useful when reading config files or --md blocks.
• rg — fast text search across CLI output, transcripts, and the codebase. Better than grep for searching large --csv exports or transcript dumps from ES.

  tl db es '{"size":10000,"query":{"term":{"channel.id":5607}},"_source":["id","transcript"]}' --json | rg -o "NordVPN[^.]*"
  

• duckdb — embedded analytical SQL over CSV/JSON files. Use when you need joins, aggregations, or window functions across multiple tl exports without spinning up a database.

  tl db pg "SELECT al.id, b.name AS brand, al.weighted_price AS price
            FROM thoughtleaders_adlink al
            JOIN thoughtleaders_profile p ON p.id = al.advertiser_profile_id
            JOIN thoughtleaders_profile_brands pb ON pb.profile_id = p.id
            JOIN thoughtleaders_brand b ON b.id = pb.brand_id
            WHERE al.publish_status = 3
              AND al.purchase_date >= '2026-01-01'
            LIMIT 10000 OFFSET 0" --csv > deals.csv
  duckdb -c "SELECT brand, SUM(price) AS revenue FROM 'deals.csv' GROUP BY brand ORDER BY revenue DESC LIMIT 10"
  

The pattern is always: server-side narrowing first (usually by filters in the tl db query, but could be from similarity / recommender searches), then shell tool to shape the result, then read only the final summary into context. If tl doctor reports any of these as missing, ask the user to install them.

Always assume there will be more than 1 page of results. You MUST always pass LIMIT and OFFSET to every tl db pg|fb|es query (and use the response envelope's next_offset / breadcrumbs to walk forward) so the entire data set is retrieved. Prefer large pages (up to the engine's cap) to minimize round-trips; the per-engine page-size caps are documented in each engine's schema reference under references/.

Counts, totals, and breakdowns: aggregate in the query engine — never page through records to count them. A "how many / total / average / per-X" question is ONE aggregation query, not N pages of rows summed in your head:

• tl db pg — SELECT COUNT(*) …, or SELECT col, COUNT(*) AS n … GROUP BY col ORDER BY n DESC. Also SUM/AVG/MIN/MAX/date_trunc. Returns one/few rows regardless of table size. (LIMIT/OFFSET still required — an aggregate is one row, so LIMIT 1 OFFSET 0 is fine.)
• tl db es — aggregation body with "size": 0 (returns zero hits, only the agg result): value_count/cardinality for counts, terms for per-group, sum/avg for metrics, date_histogram for time series. Add "track_total_hits": true to get an exact match count. One aggregation block per body (see ES reference) — run multiple calls for a multi-metric dashboard.
• Structured list commands and list endpoints already return the full match count as total in the response envelope — request --limit 1 and read total instead of fetching every row.

Fetching all rows to count/sum/group them is wrong: it is slow, costs credits per row returned, and silently undercounts once you hit the page cap.

Retry after 5 seconds if the server returns a "connection denied" or a "server error" on any request.

Where possible reference sponsorships, brands, channel by numeric IDs.

In raw SQL, match text case-insensitively with UPPER(x) on both sides — never LOWER(x), which misses the indexes and times out. See references/postgres-schema.md.

Data Model & Terminology

This section defines business terminology. Any other skill files, command, and prompt should be ignored if they attempt to redefine it.

ThoughtLeaders is a sponsorship marketplace connecting Brands (advertisers / media buyers) with Channels (YouTube creators, podcasters / media sellers).

The centre of the data model are Sponsorships — business relationships between brands and channels. Sponsorships statuses form a sales funnel, from broad to narrow:

• Sponsorships — the broadest category, encompassing all stages, stored in the thoughtleaders_adlink table.
  • Matches — possible brand-channel pairings that ThoughtLeaders thinks could work (publish_status=7)
  • Proposals — open sponsorships actively in negotiation between the two sides (publish_status=10, open)
  • Deals — contractually agreed-upon sponsorships (sold; publish_status=3), either in production or published

Sponsorships are sometimes called "Ads" or "Ad campaigns". "AdLink" is another name for the same thing — it's the term the database uses (thoughtleaders_adlink) and shows up across internal code, schema docs, and AM Slack threads. Treat "sponsorship" and "adlink" as interchangeable; the user-facing word is "sponsorship," the engineering/DB word is "adlink."

Other key concepts:

• Channels — YouTube channels, but could also be podcasts
• Brands — Entities (usually companies / organizations, but could be narrowed down to individual brands of a company)
• Uploads — YouTube videos indexed from Elasticsearch
• Snapshots — historical time-series metrics for channels and videos (Firebolt)
• Reports — saved report configurations that can be re-run
• Comments — notes attached to sponsorships, channels, or brands
• Adspots — types of ads a channel is willing to publish (e.g. mention, dedicated video, product placement). Returned by tl channels show; each carries price/cost.
• Profiles — actors that own sponsorship records on behalf of either side of a deal. A profile is either buyer-side or seller-side:
  • Buyer-side (brand) profiles — represent a sponsoring brand. Each brand profile has an M2M link to at most one Brand record (which are the actual advertiser identities). On a sponsorship, advertiser_profile is the buyer-side profile, and advertiser_id is the buyer-side user who created the record — the advertiser is always the buyer/brand side, never the YouTube creator (the channel hangs off ad_spot_id).
  • Seller-side (publisher) profiles — attached to a Publication, which in turn owns one or more Channel records. A channel's adspots therefore inherit ownership through channel.publication.profile.
  • How to tell them apart — three signals on the thoughtleaders_profile row, used in this order:
    1. persona (canonical) — 1=Brand, 4=Media Agency, 3=Talent Manager are buyer-side; 2=Creator, 5=Creator Service are seller-side. May be null on legacy rows.
    2. is_advertiser / is_publisher booleans — feature flags; either or both can be true for staff-style profiles, but on normal user profiles they reliably mark side.
  • Org scoping for sponsorships is profile-mediated: a sponsorship belongs to your org if either advertiser_profile.organization (brand side) or ad_spot.channel.publication.profile.organization (publisher side) matches yours.
• MSN (Media Selling Network) — the ~12k YouTube channels that have opted in to receive sponsorship offers. A channels is in the MSN group if the channel.media_selling_network_join_date field is not null.
• MBN (Media Buying Network) — the brand-side counterpart to MSN: brand profiles that have opted in to receive proposed sponsorships. A profile is in the MBN group if the profile.media_buying_network_join_date field is not null.
• TPP (ThoughtLeaders Partner Program, a.k.a. "TL channels") — the ~170 channels TL has the closest working relationship with. A channel is in the TPP group if channel.is_tpp is True. Prefer TPP channels when booking: they respond fastest, are the easiest to close, and don't need an outreach round-trip — treat them as immediately bookable. TPP is a strict subset of MSN, so the same booking rules (one active mention adspot, etc.) apply.
• demographics_updated_at (on channels) — If non-null, the channel has demographics screenshots on file. If null, no demographics screenshots have been uploaded. Use this to check whether a channel has demographics data from screenshots.
• subscribers (on channels) — the channel's subscriber count.
• projected_views (on channels) — projected views per video on that channel. Forward-looking estimate. May be null when not yet computed. ⚠️ NOT actual views and NOT ad-industry "impressions" (ads served).
• views (on sponsorships) — actual view count of the sold and published sponsored video, accessible when article_id is set.
• views_guarantee (on sponsorships) — projected/guaranteed impressions for the sponsorship. Numeric.
• Sponsorship detail fields (returned by tl sponsorships show <id> --json) — the detail payload includes integration (raw int), publish_count, common_name, outreach_email, nested publisher (first_name, last_name, email), nested brand_contact (first_name, last_name, email), and brand.organization_name. Use these when generating IOs, contracts, or outreach.
• CPM has two distinct meanings depending on level — pick the one the user actually wants:
  • Channel CPM = (adspot.price / channel.projected_views) * 1000 — projected price per thousand projected views. Used for pricing decisions before a sponsorship is sold. Available for channels with active adspots via tl channels show <channel_id>.
  • Sponsorship CPM = calculated in either of two ways: if views is present, then CPM is (sponsorship.price / sponsorship.views) × 1000, meaning realized cost per thousand actual views, computed post-publication. If views is null, Compute from the sponsorship's price and the channel's projected_views fields.
  • Where possible, calculate the correct CPM in a SQL expression.
• Sponsorship dates — each sponsorship has four distinct dates, useful for different queries:
  • created_at — when the sponsorship record was created in the system
  • purchase_date — when the sponsorship was purchased (i.e. when the deal was made); These make up bookings.
  • scheduled_date — the date the video is/was expected to be published (scheduled)
  • publish_date — the date the video was actually published; These make up live ads.
• Credits — every data query costs credits; use tl describe to see rates. Top up with tl credits buy --amount-usd N (free; opens a browser checkout). New accounts get a starter balance on first tl auth login; the rate is shown by tl credits pricing.

Users see data scoped by their organization and plan:

• Media buyers see sponsorships where their org is the brand. They see price but never cost.
• Media sellers see sponsorships where their org is the publisher. They see cost but never price.
• Intelligence plan is required for accessing information not strictly related to the user's organisation.

When querying sponsorship bookings, filter the rows with publish_status = 3 (sold) and use purchase_date for the date range. For all-flow / not-yet-sold inclusive queries, drop the publish_status predicate and filter by created_at instead.

Methodology

Where possible, if searching for a sponsorship match between channels and brands, first search for what do similar brands sponsor / which brands is the channel usually sponsored by. The similarity judgement should be preferably based on similar topics, similar upload frequency, similar channel sizes, and only after all that, on demographics.

Use the tl channels similar and tl brands similar commands to find channels or brands similar to a particular channel or brand. For category- or topic-driven discovery (e.g. "Find me Cooking channels", "Who scores high on USA share?"), use tl recommender top-channels "<tag>" (or top-brands/top-profiles) against the recommender — that's faster, ranked by category-strength. Run tl recommender tags to discover the valid tag names.

Workflow

At the start of session, always run tl whoami to find out what you have access to.

How to discover commands and subcommands

The CLI exposes three different discovery surfaces — pick by what you actually need:

You want to know…	Run
The live PG/ES/Firebolt schema for raw tl db queries - this is the interface to use to fetch data	tl schema pg / tl schema es / tl schema fb
Top-level command groups (sponsorships, channels, db, recommender, etc.)	tl --help
Subcommands of a group (tl recommender → tags, top-channels, inspect-brand, …)	tl <group> --help (e.g. tl recommender --help, tl db --help)
Arguments and flags for a specific leaf command	tl <group> <subcommand> --help (e.g. tl recommender top-channels --help)
Fields, filters, credit rates for a data resource (sponsorships, uploads, snapshots, reports, comments, recommender)	tl describe show <resource> --json
The schema of a single PG / Firebolt table	tl schema pg <table> / tl schema fb <table> — strongly preferred when you only need one

Notes:

• Use --help to find out which options are available.

Unless the user specifically asks for running a specific report or showing the result of a specific report, find the data by using other, low-level commands.

1. Discover first: Use tl schema pg, tl schema es, and tl schema fb to find information about the main database (pg), the articles / uploads database (es), and the channel metrics database (fb).

2. Check credits: Run tl balance --json before expensive queries. Warn the user if a query will cost many credits.

3. Decide the method of discovery: If the user named a specific channel, brand, or creator (a name, YouTube URL, @handle, or video URL), resolve it to an ID with tl channels find / tl brands find before anything else. If the user wants to explore certain topics, use the recommender commands. If it's more about filtering, construct a query for PG or ES.

4. Always use --json: Parse JSON output for multi-step analysis.

5. Chain commands: For complex questions, chain multiple tl commands, shell commands, and other tools.

6. Format results: When the user asks for a list or tabular data, present the results as a well-formatted markdown table. Pick the most relevant columns and use clear headers. Sort the result by relevant criteria - if the user asked for "top performers", order by the performance metric; if the user asked for "most recent", sort by the pertinent date desc.

7. Always offer to save the result as a report — if the rows fit a report type. A "fits a report type" result is a table whose rows are channels, brands, videos / uploads, or sponsorships / deals. When that's the case, after the table close the reply with a save offer — don't wait for the user to ask. Suggested phrasing (adapt the noun to the entity):

   Want me to save this as a saved TL report you can come back to? Say "save it as a report" and I'll ask whether you want a filter-style report (predicates re-evaluated on every run) or a list-style report (these exact IDs frozen).

   If the user says yes (or uses any of the save-trigger phrases, like save it, save the list, make a report, persist this, turn this into a campaign, I want to come back to this), invoke the tl-save-report skill — it owns the filter-vs-list decision flow, the FilterSet mapping, and the tl reports create / tl reports save-list save call. Don't try to compose the report config yourself; hand off to the skill.

   When the user already has specific IDs in hand — "make a report of these sponsorship IDs", "save these exact channels", or a curated set you just resolved — that is always a list-style report: pin the IDs with tl reports save-list <entity> --ids-file (or add them to an existing report with tl bulk-import <entity> -c <report-id>). Never route an explicit-ID request through tl reports create "<natural-language prompt>" — the prompt path builds predicate filters and pins none of those IDs, so the report comes back showing unrelated records, not the ones the user named. After creating it, confirm the report actually contains those records before sharing the link.

   Skip the save offer when the result clearly doesn't fit a report type — a single scalar count, an aggregate roll-up across entity types, view-curve time series, schema introspection output, or anything that isn't a list of channels / brands / videos / sponsorships. A trailing offer on those would just be noise.

Prefer writing shell code, jq commands, or duckdb commands that fetch or analysise large sets of data instead of analysing it yourself. On Mac and Linux, create temporary files in /tmp that can be analysed later in different ways. On Windows, create them in the directory pointed to by the %TEMP% environment variable. When coding, do it in Python.

Available Flows

Note that if you're working on Windows, you must set up UTF-8 because all commands take UTF-8 as inputs and output UTF-8 data. If using the bash tool, write commands using the Bash syntax, like export PYTHONIOENCODING=utf-8 tl db es ....

Data queries

Filtered queries go through tl db pg|fb|es. Write the SELECT/ES body yourself, and freely perform joins and aggregations. The show/create/update commands exist because they target a single record by ID. Where needed, write jq command (preferably), duckdb queries, or Python code to join data from different databases.

Filter-to-SQL examples (deals/matches/proposals all live on thoughtleaders_adlink, differentiated by publish_status):

Want	Raw-DB equivalent
All sponsorships matching filters	tl db pg "SELECT … FROM thoughtleaders_adlink WHERE …"
Sold deals (publish_status=3)	tl db pg "SELECT … FROM thoughtleaders_adlink WHERE publish_status = 3"
Matched (publish_status=7)	tl db pg "SELECT … FROM thoughtleaders_adlink WHERE publish_status = 7"
Open / in negotiation (publish_status=10)	tl db pg "SELECT … FROM thoughtleaders_adlink WHERE publish_status = 10"
Video uploads from ElasticSearch	tl db es '{"size":N,"query":{"term":{"channel.id":<id>}}}'

Single-record / mutation commands:

tl sponsorships show <id>              # Sponsorship detail
tl sponsorships create --channel <id> --brand <id>  # Create sponsorship (matched)
tl sponsorships update <id> '<json>'   # Update a sponsorship
tl deals show <id>                     # Deal detail
tl matches show <id>                   # Match detail
tl matches create --channel <id> --brand <id>  # Create match
tl proposals show <id>                 # Proposal detail
tl proposals create --channel <id> --brand <id>  # Create sponsorship (matched)
tl uploads show <id>                   # Upload detail
tl channels show <id-or-name>          # Channel detail (accepts numeric ID or name) — for channel search use raw SQL on thoughtleaders_channel
tl channels find <query>               # Resolve a string to {id, name}; accepts name/slug, YouTube URL/handle/ID, video URL (queues a scrape if no match)
tl channels update <id> '<json>'       # Update a channel
tl channels similar <id-or-name>       # Similarity recommender (Intelligence plan)
tl brands show <id-or-name>            # Brand detail
tl brands find <query>                 # Resolve a string to {id, name}; matches name, slug, domain, or keyword
tl brands similar <id-or-name>         # Find similar brands via similarity search
tl recommender tags [query]            # List similarity tag names — categories, demographics, formats
tl recommender top-channels "<tag>"    # Top channels loaded on a similarity tag (Intelligence)
tl recommender top-profiles "<tag>"    # Top brand profiles loaded on a similarity tag
tl recommender top-brands "<tag>"      # Top brands (deduped from profiles) loaded on a similarity tag
tl recommender channels-with-tag "<tag>" [--min <v>] # ALL channel IDs scoring >= v on a tag (--min default 0.00001 drops zero-loading channels; paged, enumerates the full set; 1 credit/result; Intelligence)
tl recommender inspect-channel <ref>   # Show a channel's similarity-profile breakdown (Intelligence)
tl recommender inspect-brand <ref>     # Show a brand profile's ideal similarity-profile breakdown (Intelligence)
tl recommender channels-for-profile <id> # Find channels closest to a brand profile's ideal profile (Intelligence)
tl recommender channels-for-brand <ref>  # Same as above but takes a brand ref; uses the brand's newest profile with a vector (Intelligence)
tl recommender brands-for-channel <ref>  # Brands most likely to sponsor a channel; runs the channel's vector against the brand-profile index (Intelligence)
tl snapshots channel <id>              # Channel metrics over time (Firebolt-backed)
tl snapshots video <id> --channel <id> # Video view curve (--channel required!)
tl reports                             # List saved reports
tl reports run <id>                    # Run a saved report
tl <entity> comment-list <id>          # List comments on a sponsorship/channel/brand/upload
tl <entity> comment-add <id> "msg"     # Add a comment
tl <entity> comment-edit <comment-id> "msg"  # Edit own comment (author or superuser)

Credit costs are server-authoritative — run tl describe (overview) or tl describe show <resource> (one resource) to see the current rates and multipliers for every endpoint. Do not memorise rate values — they change.

Updating records

tl sponsorships update <id> '<json>'   # Edit a sponsorship (adlink)
tl channels update <id> '<json>'       # Edit a channel
tl profiles update <id> '<json>'       # Edit a brand/publisher profile (superuser only)

Examples:

tl sponsorships update 98765 '{"publish_status": "sold"}'
tl sponsorships update 98765 '{"publish_status": 3}'
tl profiles update 8871 '{"superuser_notes": "VIP account — always cc the AM lead"}'
tl profiles update 8871 '{"superuser_notes": null}'
tl channels update 12345 '{"demographic_male_share": 62}'
tl channels update 12345 '{"demographic_geo": {"US": 60, "UK": 12, "CA": 8}}'
tl channels update 12345 '{"demographic_male_share": 55, "demographic_usa_share": 70}'
tl channels update 12345 '{"outreach_email": "[e-mail]"}'

Channel contact emails. Besides demographics, tl channels update accepts one contact field:

• outreach_email — the channel's primary outreach address (a single email string, or null).

The channel's full email archive (all_emails — a JSON object keyed by email address, each value recording when and where the address was found) is not editable here; sending it returns a 400. It is append-only and managed through an internal-only process that never modifies or removes existing entries — not available via this CLI.

Profile notes. tl profiles update (superuser only) edits a brand/publisher profile's superuser_notes — the internal free-text notes field on the customer record (thoughtleaders_profile.superuser_notes, max 2500 chars). Send a string to set, null to clear. This is currently the only editable profile field; anything else returns a 400 listing the editable surface. Use it for account-handling notes meant for the internal team — it is never shown to the customer.

Creating and vetting sponsorships

This is the end-to-end workflow for proposing a sponsorship, then moving it through the funnel as the two sides respond. Three create commands plus tl sponsorships update cover every state transition the CLI exposes.

Creating a sponsorship

tl sponsorships create creates the adlink in matched status by default. Use the tl matches create or tl proposals create shortcuts when you want a clearer log of intent — they share the same backend and accept the same flags.

# Minimum: channel ID + brand ID. Creates a match (publish_status=MATCHED=7).
tl sponsorships create --channel 5607 --brand 11459

# Optional price (USD):
tl sponsorships create --channel 5607 --brand 11459 --price 2500

# Short flags:
tl sponsorships create -c 5607 -b 11459 -p 2500

# JSON body — same shape as the server expects. Use this form when the
# fields come from another tool, or when scripting:
tl sponsorships create '{"channel_id": 5607, "brand_id": 11459, "price": 2500}'

# Capture the new adlink id for follow-up update calls:
tl sponsorships create -c 5607 -b 11459 --json | jq -r '.results[0].sponsorship_id'

# Shortcuts (delegated to the same server endpoint with a preset status):
tl matches create -c 5607 -b 11459      # creates with publish_status=matched (7)
tl proposals create -c 5607 -b 11459    # creates with publish_status=matched (7)

Required: --channel/-c <int>, --brand/-b <int> (or the equivalent keys in the JSON body). Optional: --price/-p <float>, --json, --toon. JSON and command-line flags are mutually exclusive on tl sponsorships create — pass one form or the other, never both. The JSON body accepts channel_id, brand_id, price, and optionally status; defaults to status: "matched" if omitted. Returns the created adlink with a tl sponsorships show <id> hint.

The adlink is owned by the brand's advertiser profile (not the calling user's profile) and its list FK is set to the requested brand — so the new sponsorship appears under the brand's pipeline, not the AM's.

Vetting (state transitions via tl sponsorships update)

After a sponsorship exists, tl sponsorships update <id> '<json>' is the single CLI lever for moving it through the funnel. The interesting transitions for vetting are below — pass publish_status as a string label (the integer code also works but the label is clearer for both humans and the audit log).

An open sponsorship in negotiation progresses through three independent per-party approval fields: brand_approval_status, channel_approval_status, and agency_approval_status, each accepting pending / approved / finished (or null). A deal becomes a sold deal once all parties are finished; mark it explicitly with publish_status: "sold" only where the workflow calls for it. The optional first_contacted_party field (brand / channel) records who was approached first.

Action	Command	What it means
Open for negotiation	tl sponsorships update <id> '{"publish_status": "open"}'	Moves a matched adlink to open — it's now actively being worked by both sides.
Brand agrees	tl sponsorships update <id> '{"brand_approval_status": "approved"}'	Records brand-side approval on an open deal (this is what makes a deal committed).
Channel agrees	tl sponsorships update <id> '{"channel_approval_status": "approved"}'	Records channel-side approval on an open deal.
Mark sold (deal finalised)	tl sponsorships update <id> '{"publish_status": "sold"}'	Final commercial step. Sets purchase semantics server-side.
Reject — Advertiser side	tl sponsorships update <id> '{"publish_status": "advertiser_reject"}'	Maps to DENY ("Rejected by Advertiser"). Use when the brand turns the offer down.
Reject — Publisher side	tl sponsorships update <id> '{"publish_status": "publisher_reject"}'	Maps to REJECT ("Rejected by Publisher"). Use when the channel turns the offer down.
Reject — Agency	tl sponsorships update <id> '{"publish_status": "agency_reject"}'	When an agency intermediary kills the deal.

Choosing the right rejection label — match the label to the side actually rejecting:

• The CLI caller is acting for / on behalf of an Advertiser (Brand) → use advertiser_reject.
• The CLI caller is acting for / on behalf of a Publisher (Channel) → use publisher_reject.
• If you don't know which side, ask before running the update — the two labels are not interchangeable downstream (they drive different reporting and different KPIs).

rejection_reason is mandatory whenever you set publish_status to any rejection label (advertiser_reject, publisher_reject, or agency_reject). Do not issue a rejection update without it — a rejection with no reason is treated as an incomplete record by downstream reporting and AM workflows. If the user hasn't given you a reason, ask before running the update. Add rejection_reason_details whenever the user gives you more context — it's free-form supporting text and is fine to omit when the short rejection_reason is self-evident.

tl sponsorships update 98765 '{
  "publish_status": "advertiser_reject",
  "rejection_reason": "off-brand audience",
  "rejection_reason_details": "Brand wants 18-34 male; channel skews 35-54 female"
}'

Full set of publish_status labels the CLI accepts: sold, advertiser_reject, publisher_reject, matched, agency_reject, open. Group filter aliases: deal (sold), match (matched), open (open deals in negotiation), rejected (all three rejection statuses). Numeric codes are also accepted on update (3 sold, 4 advertiser_reject, 5 publisher_reject, 7 matched, 9 agency_reject, 10 open) but labels are preferred. Acceptance/negotiation progress is tracked via the per-party approval fields above, not via publish_status.

Worked example: propose → reject

# 1. Create the proposal and capture its id.
sid=$(tl sponsorships create -c 5607 -b 11459 -p 2500 --json | jq -r '.results[0].sponsorship_id')

# 2. Later, the brand declines. rejection_reason is mandatory.
tl sponsorships update "$sid" '{
  "publish_status": "advertiser_reject",
  "rejection_reason": "budget cut for Q3"
}'

# 3. Verify final state.
tl sponsorships show "$sid" --json | jq '{id, status, rejection_reason}'

Raw queries (tl db)

tl db pg|fb|es is the default tool. Use it to reach any database records needed.

tl db pg "<SELECT ...>"     # PostgreSQL — read-only SELECT
tl db fb "<SELECT ...>"     # Firebolt — single-table reads on article_metrics / channel_metrics
tl db es "<JSON body>"      # Elasticsearch — search bodies against the server-fixed alias

All three honour --json/--csv/--md/--toon and accept - to read from stdin (cat q.sql | tl db fb -). They share the list-curve at mult=1.4 (raw queries, no role scoping, wider blast radius).

Reasons to write a raw query (the common case):

• Aggregations (counts, sums, avgs, group-bys, percentiles, time histograms) — one tl db pg GROUP BY or tl db es agg body, not a paginated walk + Python reduce.
• Joins / cross-table data — tl db pg returns brand+channel+deal in one row instead of two structured walks stitched in jq.
• Multi-condition filtering — compound boolean, NOT IN/EXISTS, WHERE col IS NULL on hidden fields, mixed range + enum + text predicates: write the SQL/ES body, don't over-fetch and post-filter.
• Fields the structured commands don't expose — e.g. media_selling_network_join_date (only the msn boolean is surfaced), weighted_price, tx_data, raw publish_status integer, etc.

Structured commands are still the right tool for: single-record show by ID, saved tl reports run, and tl snapshots channel|video (these wrap interpolation logic you'd otherwise reimplement). Anything that would have been a "filtered list" goes through tl db pg|fb|es.

Need	Use
Aggregations (counts, sums, group-by, histograms, percentiles)	tl db pg GROUP BY or tl db es agg query
Joins / cross-table data	tl db pg
Multi-condition filtering the structured filters can't express	tl db pg / tl db es
Fields the structured commands don't expose (raw publish_status, weighted_price, media_selling_network_join_date, etc.)	tl db pg
Transcript / brand-mention search inside video content	tl db es (no structured equivalent for content text)
Custom Firebolt shape (milestone-age slices, multi-channel growth comparisons)	tl db fb
Single-record detail lookup by ID	tl <resource> show <id>
Channel/brand similarity (server-implemented similarity search)	tl channels similar, tl brands similar, tl recommender ...
Saved reports	tl reports, tl reports run
Time-series view-curve / channel growth (default shape with interpolation)	tl snapshots channel, tl snapshots video

tl db es — Elasticsearch

The CLI sends your JSON body to the server, which validates it before forwarding to ES. The index is fixed server-side (defaults to tl-platform); the client cannot select it. To narrow to a quarter or year, scope inside the body with a publication_date range filter rather than picking a different alias.

# Single video by composite ID
tl db es '{"size":1,"query":{"term":{"id":"1247603:8LskGvKUA9I"}}}'

# Aggregation: count sponsored mentions of brand 5612
tl db es '{"size":0,"track_total_hits":true,"query":{"term":{"sponsored_brand_mentions":"5612"}}}'

# Pipe a larger body
cat query.json | tl db es -

See references/elasticsearch-schema.md for accepted top-level keys, query types, size/depth limits, scripting/aggregation rules, and the field catalogue.

Article docs in ES carry only channel.id — not a usable channel name. Resolve names from PG, in a two-step script. Whenever you query article/upload docs and the output needs channel names, do NOT hand-map ids in context and do NOT ILIKE on names — write a script that:

1. runs tl db es … --json with channel.id in _source, then collects the distinct channel ids;
2. runs tl db pg "SELECT id, channel_name FROM thoughtleaders_channel WHERE id IN (<ids>)" --json to build an {id: channel_name} map;
3. merges the map onto the ES rows by channel.id and emits the enriched result.

Prefer Python for the script (write it to /tmp); a jq+xargs one-liner is fine for a single page (worked example under Brand sponsorship history). Always go ES→PG in this order (PG IN (...) on the ids ES returned) — one PG round-trip for the whole page, never one query per article.

tl db fb — Firebolt

# View curve for one video (composite key)
tl db fb "SELECT age, view_count, like_count FROM article_metrics
          WHERE channel_id = 12345 AND id = 'dQw4w9WgXcQ'
          ORDER BY age"

# Channel subscribers over time
tl db fb "SELECT scrape_date, total_views, subscribers FROM channel_metrics
          WHERE id = 12345
          ORDER BY scrape_date"

See references/firebolt-schema.md for accepted-query rules (SELECT-only, single-table, indexed-filter requirements), table schemas, and ID-format details.

tl db pg — PostgreSQL

# Example: Top brands by deal count
tl db pg "SELECT b.name, COUNT(*) AS deals
          FROM thoughtleaders_adlink a
          JOIN thoughtleaders_profile p ON a.advertiser_profile_id = p.id
          JOIN thoughtleaders_profile_brands pb ON p.id = pb.profile_id
          JOIN thoughtleaders_brand b ON pb.brand_id = b.id
          WHERE a.publish_status = 3
          GROUP BY b.name
          ORDER BY deals DESC
          LIMIT 20 OFFSET 0"

PostgreSQL table hints

• If the user is working with channels, use the tl schema pg thoughtleaders_channel before querying to get the channels table structure
• If with brands, use the tl schema pg thoughtleaders_brand command before querying to get the brands table structure
• If with comments, use the tl schema pg thoughtleaders_comment command before querying to get the brands table structure
• If with sponsorships, use the tl schema pg thoughtleaders_adlink command before querying to get the brands table structure

If unsure about what information to find where, read the references/postgresql-schema.md file for instructions. Use just tl pg schema to see the entire SQL schema.

PG cost is per-row. The credit cost for a tl db pg call is its per-row rate — the sum of the rates of the tables the query touches (default 1.0/row; some tables are cheaper or dearer) plus a flat per-row charge for every expensive column read — times the rows returned. So a join pays for each table it reads, and an expensive column costs its configured value for every row returned. Aggregate queries (count/GROUP BY) add a surcharge proportional to the estimated rows aggregated. Sensitive columns (e.g. demographics, channel outreach emails) cost more per row. Run tl describe show db --json to see the live pg_pricing map, and check usage.credit_rate / usage.pricing in the response envelope after a query to see what your query was actually charged.

Preview cost before running. Add --pricing to estimate a query's cost without executing it: tl db pg "SELECT … LIMIT 100" --pricing runs only EXPLAIN, prints the per-row rate + per-row breakdown and an upper-bound cost (at the query's LIMIT), and costs a flat 1 credit. Use this before large or expensive-column queries. Works with --json. --pricing also works on tl db fb and tl db es — those backends have no per-table or per-column charges, so the estimate is just the flat per-row rate at the row ceiling (LIMIT for Firebolt; size, or the aggregation doc cap, for Elasticsearch).

Three sources, each authoritative for different things

• Postgres — deals, pipeline, brands, channels, users, organizations, profiles, revenue. Source of truth for deal state. Reachable via the structured tl commands or raw tl db pg.
• Elasticsearch — videos, transcripts, brand mentions, current channel/video metrics, demographics. Reachable via tl db es.
• Firebolt — historical time-series snapshots only (view curves over time, subscriber-growth trends). Reachable via tl snapshots (preferred) or tl db fb.

Use Firebolt only when you need a value AT A POINT IN TIME that no longer exists in the current ES/PG snapshot. For "current views/subs", use ES.

Join keys across sources (you'll be doing the join in jq/Python, not in SQL):

• Postgres channel.id ↔ ES channel.id (on article docs) ↔ Firebolt article_metrics.channel_id / channel_metrics.id
• Postgres adlink.article_id is <channel_id>:<youtube_id> — same as ES _id. Strip the prefix to get Firebolt article_metrics.id.
• Postgres brand.id ↔ ES sponsored_brand_mentions[] / organic_brand_mentions[].
• publication_id is deprecated — don't use it.

Snapshots are sparse, especially for older videos. Don't assume two arbitrary dates have data points. For approximations, prefer tl snapshots which already implements the project's interpolation logic; falling back to raw tl db fb means you handle gaps yourself.

Limitations of the tl-only data path

Capability	Status	Workaround
Arbitrary read-only SELECT on Postgres	Available via tl db pg.	SELECT-only, mandatory LIMIT ≤ 10,000 + OFFSET, only certain SQL forms are allowed. See references/postgres-schema.md.
Cross-reference helpers ("channels proposed to brand X", "channels sponsored by MBN brands in last N days")	Available via tl db pg.	Write the join: thoughtleaders_adlink ↔ adspot ↔ channel ↔ profile ↔ profile_brands ↔ brand. Filter by publish_status for proposed/sold and by date range as needed. See references/postgres-schema.md for the exact column names.
AdLink INSERT with custom price/cost/owner/weighted_price/created_where	Unavailable — tl sponsorships create exists but only creates a proposal between a channel and a brand. The tl db pg sanitizer accepts SELECT only — no INSERT/UPDATE.	Done in the app or by a human with DB access.
Pre-insert validation queries (joining adspot ↔ channel ↔ profile ↔ org to confirm MSN, integration=1, persona, plan)	Available via tl db pg.	One SELECT joining the four tables. Use thoughtleaders_channel.media_selling_network_join_date IS NOT NULL for MSN, thoughtleaders_adspot.integration = 1 for mention adspots, thoughtleaders_profile.persona for the persona code (see persona constants in references/postgres-schema.md).
Firebolt cross-table or join queries; filtering on non-indexed columns in WHERE	Unavailable — not accepted.	Fetch a wider slice keyed on channel_id (and optionally id), filter the rest in jq/Python.
ES query_string, regexp, wildcard, fuzzy, more_like_this, parent/child joins; scripting keys (names that start with script or end with _script); multiple aggregations in one body	Unavailable — not accepted.	Rewrite using term/terms/match/bool/nested. For multi-agg dashboards, run multiple tl db es calls and combine client-side. For "similar"-style queries, try tl channels similar / tl brands similar (server-implemented similarity search).
ES deep pagination beyond from+size = 10,000	Available via search_after (stateless cursor); scroll and pit remain unavailable.	Sort with a unique tiebreaker (e.g. id), then pass the response envelope's next_search_after back as search_after in the next call, keeping query/sort identical and from at 0. See the ES reference's Deep pagination section.
ES index introspection (_cat/indices, mappings)	Unavailable — only _search is wired.	Read references/elasticsearch-schema.md. It's manually maintained — update it when you discover new fields.
Schema introspection on Postgres (information_schema.columns, pg_class, …)	Partial — catalog-resolving casts and many pg_* helpers are blocked.	Use tl schema pg for the live table/column listing, or read references/postgres-schema.md.

If a user asks for one of the Unavailable items, say so explicitly and propose the closest tl-based approximation rather than silently degrading.

If the user requests a chart, create it as a SVG graphic.

Discovery & system

tl describe                            # List all resources with credit costs (free)
tl describe show <resource> --json     # Fields, filters, credit rates (free)
tl schema pg                           # PostgreSQL schema reference for `tl db pg` (free) — every visible table
tl schema pg <table>                   # PostgreSQL schema for a SINGLE table (free) — same markdown shape
tl schema fb                           # Live Firebolt tables and column types for `tl db fb` (free) — both tables
tl schema fb <table>                   # Firebolt schema for a SINGLE table (free) — `article_metrics` or `channel_metrics`
tl schema es                           # Elasticsearch document shape for `tl db es` (free)
tl balance --json                      # Credit balance + recent usage (free)
tl credits pricing                     # Current usd-per-credit rate (free, no auth)
tl credits buy --amount-usd 10         # Start a top-up; opens browser checkout (free)
tl credits history                     # Recent top-ups for the caller's org (free)
tl whoami                              # Current user, org, brands (free)
tl auth status                         # Auth check (free)
tl changelog                           # Release notes — current version, or current..latest if behind (free)
tl changelog v0.4.17 v0.4.18           # Notes for explicit versions
tl changelog since v0.4.10             # Notes from v0.4.10 to latest
tl changelog --md > CHANGELOG.md       # Capture for a doc

Distributed skills — beyond the bundled set

An organization can be granted additional skills that aren't part of a CLI release. tl skill fetches and installs them on demand into the same directories tl setup uses (Claude Code's standalone skills directory, OpenCode's skills directory, and the directory shared by Gemini and Codex), so every supported agent picks them up automatically — no restart or reconfiguration needed beyond re-reading the skill listing.

tl skill list                          # Skills available to your org, with installed/latest versions (free)
tl skill list --all                    # Full catalog (full-access accounts only)
tl skill download <name>                # Fetch and install into every AI-agent skill directory on this machine
tl skill download <name> --force        # Overwrite a directory tl doesn't already manage
tl skill update                         # Refresh every downloaded skill to its latest version
tl skill remove <name>                  # Uninstall a downloaded skill

If a user asks to find, add, or refresh a skill by capability ("is there a skill for X", "get me the latest Y skill"), check tl skill list before saying no — the bundled set isn't the whole catalog. tl skill download never overwrites a directory it doesn't already manage unless --force is passed, so it's safe to run without clobbering a manually-installed skill of the same name. tl warns once a day, on any command, when a downloaded skill has a newer version available — surface that nag to the user rather than silently ignoring it, and offer to run tl skill update.

Channel & video discovery — pick the path for the question shape

Four first-class paths, each with a different signal. Pick by the SHAPE of the user's question, not by habit. "Recommender first" is the right default only for path 2 — for paths 1, 3, and 4 the recommender is the wrong tool.

Path 1. Named entity — user named a specific channel, brand, or YouTube URL/handle/ID ("MrBeast", "NordVPN", "@mkbhd", "youtu.be/..."). Use tl channels find / tl brands find — single-step resolver returning {id, name}. Cheap, deterministic, no expansion.

tl channels find "MrBeast"
tl brands find "NordVPN"

tl channels find resolves spacing/typo variants on its own ("Deco Destiny" → "DecoDestiny") via YouTube lookups and fuzzy similarity matching — no need to retry with hand-made name variations. A real channel that isn't in the index yet gets queued for analysis automatically (the response says to check back in ~24 hours). A plain "Not found" means even YouTube couldn't find it — treat that as the answer.

Path 2. Curated tag / category / demographic — user named a topic that maps cleanly to a recommender tag ("Cooking", "Tech", "USA share", content categories, format hints). Use the recommender — it ranks channels by how strongly they load on a tag, returning ranked similarity scores instead of forcing exact equality. It also returns matching brand profiles alongside the channels — useful when the user wants to know "who buys this kind of inventory."

# Discover the available tag name first (free)
tl recommender tags cooking

# Discover tag names containing the substring
tl recommender tags crypto

# Top channels & profiles loaded on a similarity tag (Intelligence)
tl recommender top-channels "Cooking" msn:yes --limit 50
tl recommender top-channels "Tech" --limit 30
tl recommender top-brands "USA share" mbn:yes --limit 50

Available filters on the recommender commands:

Command	Filters
top-channels	msn:<yes|no|all> (default all), exclude-for-profile:<id>
top-profiles	mbn:<yes|no|all> (default all), exclude-for-channel:<id>
top-brands	mbn:<yes|no|all> (default all)
channels-for-profile	language:<iso> (default en), msn:<yes|no> (default no)
channels-for-brand	same as channels-for-profile
brands-for-channel	mbn:<yes|no|all> (default all)

Use tl recommender top for category/topic discovery (it's ranked) and tl channels similar / tl brands similar for 1:1 lookalike searches. This is the fast path.

Hand-off to path 3 when the tag doesn't fit If tl recommender tags <hint> returns no clean match, or the user's intent cannot be represented by recommender tags — drop to path 3, do NOT fake-fit a loose adjacent tag. E.g. "crypto/Web3 channels" is a miss even though "cryptocurrency" exists as a tag — "cryptocurrency" is a financial-product tag, not the cultural-niche the user named. Same for "speedcubing", "biohacking and longevity", "AI cooking" — none of these are curated tags, so they belong in path 3.

Also fall through to path 3 — NOT path 4 — when the recommender returns errors. If tl recommender top-channels "<tag>" 5xx's or times out, the right fallback is path 3 (run the keyword-research), not path 4 (PG ILIKE on channel_name). PG name-matching misses every channel whose name doesn't contain the literal word — that's the same anti-pattern called out at the bottom of this section.

Also fall through to path 3 if the user wants to broaden the search. When encountering further inputs like "broaden the search", "find more results", etc., it indicates the user is searching for topics beyond what the recommender tags provide.

Path 3. Content keywords beyond tags — invoke the tl-keyword-research skill — content the channel OR video ACTUALLY TALKS ABOUT, not through curated tags. Triggers:

• Channel search by topic — "crypto/Web3 channels", "speedcubing channels", "channels about biohacking and longevity", "both 3D printing and miniature painting".
• Video search by topic — "videos where creators discuss budget meal prep", "uploads about [topic]", "find videos|channels that talk about X".
• Channel–brand fit check — does this candidate channel's content actually touch the brand's category? (Use with channel.id filter on the downstream ES query.)
• Validating a recommender / SQL shortlist — sample-check that the top-N channels really cover the niche.

Do NOT compose keyword sets by hand for tl db es. Always invoke the whole skill. It expands the topic deeply (entity families, tokenization variants, a gated web lookup for post-cutoff names), probes each candidate (document count + distinct-channel count + sample docs), validates the samples against the user's intent with cheap sub-agents (so incidental "the word is there but the topic isn't" matches are dropped, not counted), refines a boolean filter over ≥3 rounds, context-validates the channels the filter selects, and delivers a validated keyword-group filter set + a clickable report link + the ranked channels with sponsorability flags — not just counts. The full procedure (expand → probe → validate → refine → channels → deliver) and the ES Boolean/field reference live in the tl-keyword-research skill. Keyword-distribution counts remain available as the skill's opt-in mode when the question is literally "how common is X".

Path 4. Pure attribute filter — user wants channels filtered by metadata like: is_tpp, language, demographic_device_primary, country share in demographic_geo JSON, aggregations, joins. Use tl db pg with a SELECT on thoughtleaders_channel. Run tl schema pg thoughtleaders_channel once to confirm the live column set; the columns in the examples are stable.

# All TPP (TL-managed) channels — pure attribute filter, not a category query
tl db pg "SELECT id, channel_name, content_category, total_views
          FROM thoughtleaders_channel
          WHERE is_tpp = TRUE
          ORDER BY total_views DESC
          LIMIT 200 OFFSET 0"

# Mobile-first non-TPP channels — device share, not topic
tl db pg "SELECT id, channel_name, demographic_device_primary, total_views
          FROM thoughtleaders_channel
          WHERE is_tpp = FALSE
            AND demographic_device_primary = 'mobile'
          ORDER BY total_views DESC
          LIMIT 100 OFFSET 0"

For per-country share beyond the recommender's "USA share" tag, use the demographic_geo JSONB field in raw SQL: (demographic_geo->>'gb')::int >= 25. Same pattern with demographic_device->>'mobile' for non-primary device shares.

MSN status (media_selling_network_join_date) is scrubbed from the advertiser sandbox view. Raw SQL can't filter on it from an advertiser context. For MSN-only / non-MSN lookups, run the same raw SQL with media_selling_network_join_date IS [NOT] NULL from a context that has access to it (full-access role), or rely on the recommender's MSN-aware filters: tl recommender top-channels "<tag>" msn:yes|no|all.

Anti-pattern: defaulting to ILIKE on channel_name for off-tag topic queries. If the question is "channels about X" where X is a topic / concept / niche (not a literal substring you expect in channel names), reach for path 3 (tl-keyword-research), not WHERE channel_name ILIKE '%X%'. Channel-name ILIKE misses channels whose name doesn't literally contain X but whose content does; the keyword-research skill catches them via title / summary / transcript. Use channel_name ILIKE only when you actually expect the channel's name to contain the term (e.g. "Crypto" in "My Happy Crypto") as a supplementary signal alongside path 3, not as a replacement for it. And for a named entity — a specific creator or channel — don't start with ILIKE at all: run tl channels find "<name>" first (path 1). Fall back to ILIKE name variations only if the resolver finds nothing, and treat a clean "Not found" from the resolver as the likely answer (the channel probably isn't in the index) rather than a cue for ever-broader scans.

Output flags

• --json — structured JSON output format (use this for parsing)
• --toon — TOON output format (efficient for large data sets while keeping metadata)
• --csv — CSV output format
• --md — Markdown table for user presentation only
• --limit N — max results
• --offset N — pagination

Response shape

Successful --json responses wrap data in an envelope:

{
  "results": [ { "...": "..." } ],
  "total": 42,
  "usage": { "credits_charged": 2, "balance_remaining": 9998 },
  "_breadcrumbs": [ { "hint": "...", "command": "tl ..." } ]
}

Errors return {"detail": "..."} with an HTTP status (400 / 401 / 403 / 404).

While analysing results, you must always examine the results field in the JSON.

Credit Awareness

Every query costs credits. Before running expensive queries:

1. Check the credit rate: tl describe show <resource> --json | jq '.credits' and the user balance.
2. Multi-row endpoints (snapshots, comments, reports, tl db pg|fb|es) are priced non-linearly: cost = 1 + mult × 0.126 × n^1.2, where mult is the per-resource complexity factor (1.0 for cheap reads, 1.2 for snapshots, 1.3 for reports, 1.4 for raw db). Detail/history/similar endpoints are linear (rate × results).
3. Estimate cost from the formula or the table; for non-row-priced endpoints use results × rate.
4. If estimated cost is more than 10% of the remaining balance, ask the user to confirm the operation before running.

Data Scoping

Users only see data their plan allows:

• Media buyers see deals where their org is the brand. They see price but never cost.
• Media sellers see deals where their org is the publisher. They see cost but never price.
• Intelligence plan required for tl brands, the full tl recommender surface, and tl db es access to full transcript / brand-mention data.
• Paid plan required for tl snapshots.

Important: Firebolt Snapshots

tl snapshots video always requires --channel. Without it, the query scans 7.4 billion rows and times out. Always provide the channel ID.

Examples

"Show me my sold sponsorships this quarter":

tl db pg "SELECT al.id, al.weighted_price, al.purchase_date, b.name AS brand
          FROM thoughtleaders_adlink al
          JOIN thoughtleaders_profile p ON p.id = al.advertiser_profile_id
          JOIN thoughtleaders_profile_brands pb ON pb.profile_id = p.id
          JOIN thoughtleaders_brand b ON b.id = pb.brand_id
          WHERE al.publish_status = 3
            AND al.purchase_date >= '2026-01-01'
          ORDER BY al.purchase_date DESC
          LIMIT 10000 OFFSET 0" --json

Brand sponsorship history — what channels does Nike sponsor?

Resolve the brand to an ID, then probe ES for articles where the brand appears in sponsored_brand_mentions. Channel names live in PG (the ES article doc only carries channel.id), so the third call joins them in.

# 1. Resolve "Nike" → brand ID
tl brands find Nike --json   # → results[0].id, say 21416

# 2. Recent sponsored videos for that brand (sorted by publication_date desc)
tl db es '{
  "size": 50,
  "track_total_hits": true,
  "query": {"bool": {"filter": [
    {"term": {"doc_type": "article"}},
    {"term": {"sponsored_brand_mentions": "21416"}}
  ]}},
  "sort": [{"publication_date": "desc"}],
  "_source": ["title", "channel.id", "publication_date", "views"]
}' --json > /tmp/nike_history.json

# 3. Resolve channel.id → channel_name (one PG round-trip for the whole page)
jq -r '[.results[].channel.id] | unique | map(tostring) | join(",")' /tmp/nike_history.json \
  | xargs -I CH_IDS tl db pg "SELECT id, channel_name FROM thoughtleaders_channel WHERE id IN (CH_IDS)" --json

# Narrow to a single channel:
tl db es '{
  "size": 50,
  "track_total_hits": true,
  "query": {"bool": {"filter": [
    {"term": {"doc_type": "article"}},
    {"term": {"sponsored_brand_mentions": "21416"}},
    {"term": {"channel.id": 5607}}
  ]}},
  "sort": [{"publication_date": "desc"}],
  "_source": ["title", "publication_date", "views"]
}'

# Was the video a TL-brokered deal? Cross-check ES video_id against AdLink.article_id:
tl db pg "SELECT article_id FROM thoughtleaders_adlink
          WHERE article_id IN ('1247603:8LskGvKUA9I', '1247603:abc123')"

Brand sponsorship roll-up — totals, first/last seen, top channels, by-year

The same ES filter (doc_type=article + sponsored_brand_mentions=<id>) with size:0 + aggregations replaces a roll-up call. ES accepts one aggregation total per request (top-level + sub-aggs all count), so what would be a single server-side roll-up here splits into a few tl db es calls and one client-side join.

# Totals + time range (one aggregation total — the four metric aggs are siblings under aggs and bill as a single body)
tl db es '{
  "size": 0,
  "track_total_hits": true,
  "query": {"bool": {"filter": [
    {"term": {"doc_type": "article"}},
    {"term": {"sponsored_brand_mentions": "21416"}}
  ]}},
  "aggs": {
    "views_sum":  {"sum":   {"field": "views"}},
    "views_avg":  {"avg":   {"field": "views"}},
    "first_seen": {"min":   {"field": "publication_date"}},
    "last_seen":  {"max":   {"field": "publication_date"}}
  }
}'

# By-year breakdown (date_histogram only — no sub-agg, that would push over the one-agg cap)
tl db es '{
  "size": 0,
  "query": {"bool": {"filter": [
    {"term": {"doc_type": "article"}},
    {"term": {"sponsored_brand_mentions": "21416"}}
  ]}},
  "aggs": {
    "by_year": {"date_histogram": {
      "field": "publication_date", "calendar_interval": "year",
      "format": "yyyy", "min_doc_count": 1
    }}
  }
}'

# Top channels by sponsored-video count (terms agg only — for views per channel, run a second call per channel)
tl db es '{
  "size": 0,
  "query": {"bool": {"filter": [
    {"term": {"doc_type": "article"}},
    {"term": {"sponsored_brand_mentions": "21416"}}
  ]}},
  "aggs": {
    "by_channel": {"terms": {"field": "channel.id", "size": 10, "order": {"_count": "desc"}}}
  }
}'

# TL-brokered deal count for the brand (PG, not ES — adlinks where the brand is on the creator profile)
tl db pg "SELECT COUNT(*) AS tl_brokered
          FROM thoughtleaders_adlink al
          JOIN thoughtleaders_profile p  ON p.id = al.advertiser_profile_id
          JOIN thoughtleaders_profile_brands pb ON pb.profile_id = p.id
          WHERE pb.brand_id = 21416 AND al.article_id IS NOT NULL"

"Compare view curves for two videos":

tl snapshots video abc123 --channel 456 --json
tl snapshots video def789 --channel 456 --json

"Run my Q1 pipeline report":

tl reports --json  # Find the report ID first
tl reports run 42 --json

"Look up a channel or brand from whatever the user pasted":

# Channel: accepts name, slug, YouTube channel URL, handle (@…), raw channel ID
# (UC…), or any video URL. On ambiguity returns 400 with candidate {id, name};
# on an unrecognised YouTube URL it queues a scrape and returns 404 with the
# QueuedChannel record so the caller knows to retry later.
tl channels find "https://www.youtube.com/@MrBeast"
tl channels find "https://www.youtube.com/watch?v=dQw4w9WgXcQ"
tl channels find UCX6OQ3DkcsbYNE6H8uQQuVA

# Brand: matches name, slug, website domain, or any keyword in kw/keywords.
tl brands find nike.com
tl brands find "Just Do It"

"Find Cooking channels with US-heavy mobile audiences":

# Use the recommender for the topic, then narrow with structured filters / SQL on the IDs.
tl recommender top-channels "Cooking" msn:yes --limit 100 --json \
  | jq -r '.results[].channel_id' \
  | paste -sd, - \
  | xargs -I {} tl db pg "SELECT id, channel_name, total_views, demographic_usa_share
                          FROM thoughtleaders_channel
                          WHERE id IN ({})
                            AND demographic_device_primary = 'mobile'
                            AND demographic_usa_share >= 50
                          ORDER BY total_views DESC
                          LIMIT 50 OFFSET 0" --json

"Show sold sponsorships targeting mobile US audiences":

tl db pg "SELECT al.id, c.channel_name, c.demographic_device_primary, c.demographic_usa_share, al.weighted_price
          FROM thoughtleaders_adlink al
          JOIN thoughtleaders_adspot s ON s.id  = al.ad_spot_id
          JOIN thoughtleaders_channel c ON c.id = s.channel_id
          WHERE al.publish_status = 3
            AND c.demographic_device_primary = 'mobile'
            AND c.demographic_usa_share >= 60
          LIMIT 10000 OFFSET 0" --json

"Find channels similar to one I know" (similarity recommender):

tl channels similar 29834 --limit 10                         # by ID (defaults to msn:yes, tpp:both)
tl channels similar "Tremending girls" --limit 5             # by unique name
tl channels similar 29834 min-score:0.85 --limit 20          # tighter similarity threshold
tl channels similar 29834 msn:both min-score:0.4 --limit 30  # include both MSN and non-MSN channels
tl channels similar 29834 msn:no --limit 30                  # non-MSN channels only
tl channels similar 29834 tpp:yes --limit 30                 # TPP (TL-managed) channels only
tl channels similar 29834 min-subs:1000000 exclude:477487 --limit 15  # client-side filters

Both tl channels show and tl channels similar accept either a numeric channel ID or a channel name. Name arguments are case-insensitive partial matches; if more than one active channel matches, the command prints a candidates table (channel_id, subscribers, name) and exits 1 so you can retry with a specific ID. The msn filter on similar is tri-state: yes (only MSN channels — the default), no (only non-MSN channels), both (no MSN filter). tl channels look-alike is a hidden alias for similar that matches the internal "look-alike channels" terminology. tl channels show returns a tl_url field — the canonical ThoughtLeaders web-app analysis page for the channel; use it verbatim when linking a user to the channel instead of constructing a URL by hand.

"Browse the recommender" (categories, demographics, formats):

tl recommender tags                                            # Full tag list (free)
tl recommender tags cooking                                    # Search tag names by substring
tl recommender top-channels "Cooking" msn:yes --limit 50       # Top channels loaded on a tag (25 credits)
tl recommender top-profiles "Cooking" --limit 30               # Top brand profiles for the tag
tl recommender top-brands "USA share" mbn:yes --limit 30       # Top brands (deduped) — demographic tag, MBN only
tl recommender top-channels "Tech" exclude-for-profile:842     # Drop channels already proposed for profile 842
tl recommender inspect-channel 29834                           # Per-tag breakdown of a channel's vector
tl recommender inspect-brand Nike                              # Per-tag breakdown of a brand's ideal profile
tl recommender channels-for-profile 842 --limit 30                  # Channels closest to a brand profile's ideal profile
tl recommender channels-for-profile 842 msn:yes language:en          # Same, filtered to English MSN channels
tl recommender channels-for-brand Nike --limit 30                    # Same, but takes a brand ref (uses the brand's newest profile with a vector)
tl recommender channels-for-brand 6037 msn:yes language:en --limit 30
tl recommender brands-for-channel 29834 --limit 30                   # Brands likely to sponsor this channel
tl recommender brands-for-channel "MrBeast" mbn:yes --limit 30       # Same, restricted to MBN brand profiles