How to Integrate Retool with Ghost

In Ghost Admin, navigate to Settings (gear icon in the left sidebar). Click Integrations under the Advanced section. Click Add custom integration. Give it a name (e.g., 'Retool Integration') and click Create. Ghost generates an integration entry with two values: Admin API Key and Content API Key. Click the Admin API Key to copy it. The Admin API key format is: ADMIN_KEY_ID:ADMIN_KEY_SECRET, separated by a colon. Split this value — everything before the colon is the key ID, everything after is the secret. For example, if the key is '64abc:def123456789', the ID is '64abc' and the secret is 'def123456789'. Ghost Admin API authentication uses JWT tokens. You must generate a JWT signed with the key secret for each API session. The JWT payload must include: { sub: 'user_email_or_id', iat: current_timestamp_seconds, exp: timestamp_5_minutes_from_now, aud: '/v3/admin/' }. Sign with HS256 using the hex-decoded key secret. For simplicity in Retool, you can generate a long-lived JWT token externally (valid for hours or days) and store it in Retool Configuration Variables. Alternatively, use a Retool JavaScript query to generate a fresh JWT token before each API request using the jose or jsonwebtoken library patterns. Store in Retool Configuration Variables: GHOST_ADMIN_KEY_ID, GHOST_ADMIN_KEY_SECRET (marked as secret), and GHOST_SITE_URL (e.g., https://yourblog.ghost.io).

1// JavaScript query to generate Ghost Admin API JWT
2// Run this before making API calls, refresh as needed
3const keyId = retoolContext.configVars.GHOST_ADMIN_KEY_ID;
4const keySecret = retoolContext.configVars.GHOST_ADMIN_KEY_SECRET;
5
6// Convert hex secret to bytes
7const secretBytes = new Uint8Array(keySecret.match(/.{1,2}/g).map(b => parseInt(b, 16)));
8
9// Build JWT manually (simplified — use a proper JWT library in production)
10const now = Math.floor(Date.now() / 1000);
11const header = btoa(JSON.stringify({ alg: 'HS256', kid: keyId, typ: 'JWT' })).replace(/=/g,'').replace(/\+/g,'-').replace(/\//g,'_');
12const payload = btoa(JSON.stringify({ iat: now, exp: now + 300, aud: '/v3/admin/' })).replace(/=/g,'').replace(/\+/g,'-').replace(/\//g,'_');
13
14// Note: Full HMAC signing requires crypto API — use a pre-generated token for initial setup
15return `${header}.${payload}.SIGNATURE_PLACEHOLDER`;

In Retool, navigate to the Resources tab and click Add Resource. Select REST API from the connector list. Name: Ghost Admin API Base URL: {{ retoolContext.configVars.GHOST_SITE_URL }}/ghost/api/v3/admin For Ghost Pro sites, the URL is typically https://yoursite.ghost.io/ghost/api/v3/admin. For self-hosted Ghost, use your domain: https://yourdomain.com/ghost/api/v3/admin. Authentication: Select Bearer Token. In the Token field, enter {{ retoolContext.configVars.GHOST_ADMIN_TOKEN }}. This references your pre-generated JWT token. Add a default header: Accept-Version: v5.0 (optional, but specifies the Ghost API version you're targeting. Ghost Admin API is backwards compatible but specifying the version prevents unexpected behavior from API updates). Click Create Resource. Test the connection with a GET query to /posts/?limit=5&fields=id,title,status. A successful response returns a JSON object with a posts array. If you get 401, the JWT token is expired or incorrectly formed — regenerate it. Note: Ghost also has a Content API (read-only, public data) at /ghost/api/content/ authenticated with just a content key as a URL parameter: ?key=YOUR_CONTENT_KEY. For dashboards that only need to read published content without accessing member data, the Content API is simpler to set up.

Create a getPosts query: GET /posts/ with the following URL parameters: - limit: 25 (Ghost uses 'limit' not 'per_page') - page: {{ Math.floor(table_posts.pagination.offset / 25) + 1 }} - filter: {{ buildFilter.data }} (Ghost uses a structured filter syntax) - fields: id,title,slug,status,published_at,updated_at,featured,excerpt,tags,authors,primary_author - include: tags,authors (embeds tag and author objects in the response) - order: published_at desc Ghost's filter parameter supports a powerful query syntax. For example: - status:published — only published posts - author:john-doe — posts by a specific author slug - tag:newsletter — posts with a specific tag - published_at:>2024-01-01 — posts published after a date - status:[draft,scheduled] — multiple status values Combine with + for AND: status:published+featured:true for published featured posts. Bind filter options to form components: a Status Select with options [All, Draft, Published, Scheduled], an Author Select (populated from GET /authors/), and a Tag Select (populated from GET /tags/). Build the filter string dynamically in a JavaScript query based on selected filter values.

1// Transformer for Ghost posts
2const posts = data?.posts || [];
3return posts.map(post => ({
4  id: post.id,
5  title: post.title || '(No Title)',
6  slug: post.slug,
7  status: post.status,
8  author: post.primary_author?.name || post.authors?.[0]?.name || 'Unknown',
9  tags: (post.tags || []).map(t => t.name).join(', '),
10  published_at: post.published_at ? new Date(post.published_at).toLocaleDateString() : 'Unpublished',
11  updated_at: post.updated_at ? new Date(post.updated_at).toLocaleDateString() : '',
12  featured: post.featured ? 'Yes' : 'No',
13  excerpt: (post.excerpt || '').substring(0, 150),
14  edit_url: `${retoolContext.configVars.GHOST_SITE_URL}/ghost/#/editor/post/${post.id}`
15}));

Create a getMembers query: GET /members/ with parameters: - limit: 25 - page: {{ Math.floor(table_members.pagination.offset / 25) + 1 }} - filter: {{ select_tierFilter.value === 'paid' ? 'status:paid' : select_tierFilter.value === 'free' ? 'status:free' : '' }} - include: tiers,newsletters - order: created_at desc - search: {{ input_memberSearch.value || '' }} Ghost members include: id, name, email, status (free, paid, comped), created_at, last_seen_at (last login), tiers (subscription plan objects with name and billing_period), newsletters (subscribed newsletter lists), email_count, email_opened_count, and email_open_rate. The email_open_rate field is Ghost's computed engagement metric — the ratio of opened emails to total sent, expressed as a percentage. Use this to identify disengaged members. Create a getMemberDetail query: GET /members/{{ table_members.selectedRow.data.id }} to fetch a single member's full profile when clicking a table row. For membership management actions, create updateMember: PUT /members/{{ selected_member_id }} with body containing fields to update. Common updates: changing tiers, updating newsletter subscriptions, or adding a note. Create deleteMember for removing members (use cautiously — deletion is permanent).

1// Transformer for Ghost members
2const members = data?.members || [];
3const today = new Date();
4
5return members.map(member => {
6  const lastSeen = member.last_seen_at ? new Date(member.last_seen_at) : null;
7  const daysSinceLogin = lastSeen ? Math.floor((today - lastSeen) / 86400000) : null;
8  const isPaid = member.status === 'paid';
9  const isAtRisk = isPaid && (daysSinceLogin > 30 || member.email_open_rate < 10);
10
11  return {
12    id: member.id,
13    name: member.name || 'Anonymous',
14    email: member.email,
15    status: member.status,
16    tier: member.tiers?.[0]?.name || 'Free',
17    billing_period: member.tiers?.[0]?.billing_period || '',
18    created_at: new Date(member.created_at).toLocaleDateString(),
19    last_seen: lastSeen ? lastSeen.toLocaleDateString() : 'Never',
20    days_since_login: daysSinceLogin,
21    open_rate: `${Math.round(member.email_open_rate || 0)}%`,
22    at_risk: isAtRisk,
23    newsletter_count: (member.newsletters || []).length
24  };
25});

Ghost tracks email performance per newsletter send. Create a getNewsletters query: GET /newsletters/ to fetch all newsletter channels. Each newsletter has: id, name, subscriber_count, open_rate, and feedback_enabled. For post email analytics, fetch individual post email stats: GET /posts/{{ select_post.value }}?include=email. The email object on a post contains: status (sent, failed), recipients (total sent count), opened_count, open_rate, clicked_count, click_rate, and feedback (thumbs up/down counts if feedback is enabled). For post creation from Retool, create a createPost query: POST /posts/ with body: { "posts": [{ "title": "{{ input_title.value }}", "lexical": null, "status": "draft", "tags": [{ "name": "{{ input_tag.value }}" }], "authors": [{ "id": "{{ select_author.value }}" }], "excerpt": "{{ input_excerpt.value }}" }] } Note: Ghost posts require either mobiledoc or lexical content format. Creating with null lexical creates an empty post that editors can then open in Ghost's editor via the edit_url. For bulk operations like adding a tag to multiple posts, create a JavaScript query that loops over selected Table rows and patches each post using PUT /posts/{id}. Add a version timestamp in each update payload: "updated_at": post.updated_at (Ghost uses optimistic locking and requires the current updated_at to prevent overwriting concurrent edits). For publishers managing large Ghost sites combining membership analytics, newsletter performance, and cross-team content workflows, RapidDev's team can help architect the complete Retool management solution.

1// createPost request body
2{
3  "posts": [{
4    "title": "{{ input_postTitle.value }}",
5    "status": "draft",
6    "excerpt": "{{ input_excerpt.value }}",
7    "featured": {{ toggle_featured.value || false }},
8    "tags": {{ JSON.stringify(
9      (input_tags.value || '').split(',').map(t => ({ name: t.trim() })).filter(t => t.name)
10    ) }},
11    "authors": [{ "id": "{{ select_author.value }}" }],
12    "custom_excerpt": "{{ input_excerpt.value }}"
13  }]
14}