directrate

Automation

Feeds

A matrix on a schedule, served as CSV, JSON lines or JSON at a stable URL your BI tool pulls — Google Sheets, Looker Studio, Power BI, Postgres, BigQuery, Snowflake — with a monthly budget it cannot cross.

A feed is a matrix that runs itself: a set of hotels over a rolling window of check-in nights (“the next 30 nights”), refreshed on a cadence, with the latest window served as a file at a URL that never changes. Nothing is stored server-side: like /export, the file is re-derived from the rate store on every fetch. Set it up once, point Power BI, a Sheet or a cron job at the URL, and the data lands in the tool your team already opens.

Create one

curl -s https://api.directrate.dev/v1/feeds \
  -H "x-api-key: $DIRECTRATE_API_KEY" \
  -H "content-type: application/json" \
  -d '{
    "name": "Chicago comp set",
    "chain": "marriott",
    "hotel_codes": [
      "CHIRL",
      "CHIMK",
      "CHIDT"
    ],
    "window": {
      "start_in_days": 0,
      "nights": 30
    },
    "los": [
      1
    ],
    "kind": "cash",
    "refresh_sec": 86400,
    "at_hour_utc": 6,
    "budget_credits_per_month": 600,
    "webhook_url": "https://example.com/hooks/directrate"
  }'
import os
import requests

r = requests.post(
    "https://api.directrate.dev/v1/feeds",
    headers={"x-api-key": os.environ["DIRECTRATE_API_KEY"]},
    json={
      "name": "Chicago comp set",
      "chain": "marriott",
      "hotel_codes": ["CHIRL", "CHIMK", "CHIDT"],
      "window": {
        "start_in_days": 0,
        "nights": 30,
      },
      "los": [1],
      "kind": "cash",
      "refresh_sec": 86400,
      "at_hour_utc": 6,
      "budget_credits_per_month": 600,
      "webhook_url": "https://example.com/hooks/directrate",
    },
)
r.raise_for_status()
print(r.headers.get("x-credits-charged"), r.json())
const res = await fetch("https://api.directrate.dev/v1/feeds", {
  method: "POST",
  headers: {
    "x-api-key": process.env.DIRECTRATE_API_KEY,
    "content-type": "application/json",
  },
  body: JSON.stringify({
    "name": "Chicago comp set",
    "chain": "marriott",
    "hotel_codes": [
      "CHIRL",
      "CHIMK",
      "CHIDT"
    ],
    "window": {
      "start_in_days": 0,
      "nights": 30
    },
    "los": [
      1
    ],
    "kind": "cash",
    "refresh_sec": 86400,
    "at_hour_utc": 6,
    "budget_credits_per_month": 600,
    "webhook_url": "https://example.com/hooks/directrate"
  }),
});
if (!res.ok) throw new Error(`HTTP ${res.status}`);
console.log(res.headers.get("x-credits-charged"), await res.json());

The body is the /matrix request with three changes: window instead of nights (start_in_days from today in UTC, nights to cover), refresh_sec (3600, 10800, 21600 or 86400; at_hour_utc pins a daily feed to an hour, to the quarter-hour), and the money fields below. The answer carries the feed, its three URLs, and — once — the file token and, if you gave a webhook_url, the signing secret. Creation costs nothing and does not refresh: the first refresh runs at the next 15-minute tick (or at once with POST /feeds/{id}/refresh), so you see projected_credits_per_month before anything is spent.

GET https://api.directrate.dev/feeds/{id}/latest.csv?token=drf_…
GET https://api.directrate.dev/feeds/{id}/latest.jsonl
GET https://api.directrate.dev/feeds/{id}/latest.json      ← one JSON array

The token goes in ?token= for tools that cannot send headers (Google Sheets), or as Authorization: Bearer drf_… everywhere else — prefer the header; a URL ends up in logs and histories. A wrong or missing token is a 404, never a challenge. The file is free: no credits, no quota, as often as you like (a tight loop is rate-limited per feed). POST /feeds/{id}/rotate issues a new token and kills the old one at once; the dashboard cannot show a lost token (only its hash is kept), so rotate and paste again.

What a refresh costs

A refresh is a /matrix call: cells the store already holds fresh within half the interval come back at the cache-hit rate (0.25×); the rest are shopped live at the chain’s per-cell price and run as a job tagged with the feed. credits_per_refresh_max on the feed is the worst case (every cell live) and projected_credits_per_month multiplies it by the refreshes in a 30-day month.

The honest example. 50 hotels × 30 nights, daily, on a chain with a 1-credit shop weight is 1,500 credits a refresh in the typical case — a feed’s own cells are a full interval old when it runs, so they are live unless another customer shopped them meanwhile; the 375-credit “warm” figure is a floor, not a plan. On a chain with a native calendar the same feed is 0.1 a cell: 150 credits a day on Marriott, Hilton or IHG at los 1. Fewer nights, a longer cadence, or a calendar chain are the three dials.

The budget, and the pauses

Three rules keep a feed from spending what you did not mean to spend.

  1. A monthly budget (budget_credits_per_month): a refresh whose worst case would cross it is skipped, with one feed.budget_reached event; the schedule continues and the feed resumes by itself when the month turns, or at once when you raise the budget. The default and the ceiling are half the plan’s monthly allowance, and the active feeds on an account may not budget more than the allowance together. Lower it to any number.
  2. Idle pause: no file fetch and no delivered webhook for three intervals (at least three days) pauses the feed (paused_reason: idle, one feed.paused event). The file keeps serving its last window, so a dashboard that comes back after a holiday still loads — and shows why it is stale. Resume from the dashboard or POST /feeds/{id}/resume.
  3. Failure pause: three refreshes in a row whose job failed outright (the chain down or blocking) pause the feed with last_error. Failed cells were never billed; pausing stops the attempts.

The plan sets the floors: feeds per account 1 / 3 / 10 / 25 (Free / Starter / Growth / Scale), the fastest cadence daily / 6 h / hourly / hourly, cells per feed 500 / 5,000 / 20,000 / 20,000. GET /v1/usage reports scheduled_credits_per_month, the sum of your active feeds’ budgets, so a script or an agent sees the committed spend.

The file

The columns are schema 1 and frozen — a loader keyed on them keeps working; an addition would be schema 2, opt-in. Every response says X-Directrate-Schema: 1, X-Feed-Refreshed-At (moves only when a refresh completes) and X-Feed-Status: idle | refreshing (a fetch during a refresh mixes old and new rows; observed_at and cache_age_sec are per row). certs is a string (cert:top_up;…), not JSON. Empty fields are nulls.

chain, hotel_code, night, los, kind, adults, children, rooms, currency, status, min_los, lowest_bar, lowest_bar_after_tax, lowest_member, lowest_points, lowest_points_cash, cash_copay, cpp, cpp_points_cash, certs, from_default_occupancy, observed_at, cache_age_sec

Destinations

Each recipe is the shortest honest one. <URL> is the feed’s urls.csv; <TOKEN> its token.

Google Sheets

=IMPORTDATA("<URL>?token=<TOKEN>")

Refreshes about hourly on its own; no headers, so the token rides in the URL — anyone who can view the Sheet can read it. About 2,100 rows fit (Sheets’ 2 MB / 50,000-cell limit), roughly 70 hotels × 30 nights at one length of stay. Format the hotel_code column as plain text so codes keep leading zeros, and night as a date.

Looker Studio

Through the Sheet above, with the Google Sheets connector (15-minute minimum freshness). There is no hosted-CSV connector; “File Upload” is manual.

Power BI

let
  Source = Csv.Document(Web.Contents("https://api.directrate.dev", [RelativePath="feeds/<ID>/latest.csv", Headers=[Authorization="Bearer <TOKEN>"]]), [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
  Table = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
  Table

Anonymous authentication, privacy level Public; the base URL is a literal and the path is RelativePath, so scheduled refresh in the Service works without a gateway. Set night to Date and observed_at to Date/Time/Timezone. Pro refreshes up to 8× a day.

Postgres

CREATE TABLE rates_stage (chain text, hotel_code text, night date, los int, kind text, adults int, children text, rooms int, currency text,
  status text, min_los int, lowest_bar numeric, lowest_bar_after_tax numeric, lowest_member numeric, lowest_points int, lowest_points_cash numeric,
  cash_copay numeric, cpp numeric, cpp_points_cash numeric, certs text, from_default_occupancy int, observed_at timestamptz, cache_age_sec int);
curl -fsS -H "Authorization: Bearer $TOKEN" "$URL" | psql "$DSN" -c "\copy rates_stage FROM STDIN (FORMAT csv, HEADER MATCH)"

Under cron, then INSERT … ON CONFLICT (chain, hotel_code, night, los, kind) DO UPDATE into your table. Client-side \copy, not server-side COPY FROM PROGRAM: that needs superuser and is not available on managed Postgres.

BigQuery

curl -fsS -H "Authorization: Bearer $TOKEN" "$URL" -o latest.csv
bq load --source_format=CSV --skip_leading_rows=1 --replace ds.rates latest.csv schema.json

With an explicit schema.json (not --autodetect, which turns numeric hotel codes into integers). Zero infrastructure alternative: the Sheet above as a Drive external table, with Sheets’ limits.

Snowflake

curl -fsS -H "Authorization: Bearer $TOKEN" "$URL" -o latest.csv
snow sql -q "PUT file://latest.csv @%rates; COPY INTO rates FROM @%rates FILE_FORMAT=(TYPE=CSV SKIP_HEADER=1 FIELD_OPTIONALLY_ENCLOSED_BY='\"' EMPTY_FIELD_AS_NULL=TRUE); REMOVE @%rates"

A CLI is needed: a worksheet cannot PUT. The table stage avoids creating a named stage.

The webhook

Optional. After every refresh the feed POSTs feed.refreshed to webhook_url — the window, the job id (null when every cell was fresh), cells, credits spent and the token-less URLs — signed with X-Directrate-Signature exactly as watch events are, retried for about 24 hours, and replayable from GET /v1/events (where source: "feed" tells the two streams apart). feed.budget_reached and feed.paused arrive the same way. A delivered webhook counts as a read for the idle rule, so a pipeline that only consumes the hook never pauses.

Managing feeds

GET /v1/feeds, GET /v1/feeds/{id}, PATCH /v1/feeds/{id} (name, webhook, cadence, hour, budget — not the hotels: a different list is a different feed), POST …/pause, …/resume, …/refresh (now, billed as a matrix), …/rotate, DELETE. The dashboard does all of it, shows the budget bar and the last read, and hands you the Sheets, Power BI and curl recipes with the token filled in, once, at creation.

Two notes. Windows resolve on UTC days. Two feeds on the same hotels of a calendar chain make two jobs — put the hotels in one feed.