Sell and invoice Ghost newsletter sponsor slots in one board

Build me a sponsorship desk for my Ghost newsletter, an app I open to sell and track the advertising slots inside my issues. Right now this lives in a spreadsheet that is always out of date, and the app should replace that ritual entirely.

The main view is a board of my upcoming issues. Read them from Ghost with Browse Posts (Admin API) filtered to scheduled posts, using the NQL filter status:scheduled, ordered by publish date ascending, and resolve which newsletter each issue belongs to with Browse Newsletters (Admin API). Each issue is a card showing the issue title, the send date, the newsletter it goes to, the subscriber count behind that newsletter, and one row per sponsor slot. Every slot shows its state as sold, held, or open, and when it is sold or held it also shows the sponsor name, the agreed price, and the current invoice status.

Get the subscriber count for each newsletter with Browse Members (Admin API), filtering by newsletter and by member status, so my rate card is backed by real audience size rather than a number I half remember. Do not page through every member to count them: request a single page and read the total out of the pagination metadata Ghost returns. Cache those counts so the board is not refetching them on every render.

Sponsor records, slot definitions, and my rate card live in the app's own storage, because Ghost has no concept of sponsorship inventory. A sponsor has a name, a billing contact email, an optional Stripe customer id once one exists, and notes. A slot belongs to an issue and has a placement type such as top banner, mid issue, or classified, a state of open, held, or sold, an optional sponsor, an agreed price, and an optional Stripe invoice id. The rate card holds a list price per placement type per newsletter and displays next to the live subscriber count, so I can see the price I ask against the audience it buys. Let me define a default set of slots per newsletter that gets applied to each newly scheduled issue, and let me add or remove slots on an individual issue.

When I mark a slot sold, walk me through billing the sponsor. First find or create them in Stripe: Search Customers by the sponsor's billing email, and if there is no match, Create Customer. Then raise the invoice: Create Invoice Item for the agreed price against that customer, Create Invoice with the collection method set to send invoice plus a due date from my payment terms, then Finalize Invoice, then Send Invoice so it actually reaches the sponsor. Keep these as deliberate steps with the amount shown back to me before anything is sent. Remember that Stripe amounts are in the smallest currency unit, so a 500 dollar slot is 50000. Store the resulting invoice id on the slot.

Keep every slot's payment state current by reading List Invoices from Stripe, filtered by customer, and matching on the stored invoice id, so I can see at a glance who has actually paid. Show draft, sent, paid, past due, and void as distinct states rather than a single paid flag, because an invoice still sitting as a draft has never reached the sponsor and is not the same thing as one that is genuinely unpaid. Treat an invoice that is still open after its due date as past due.

Pin to the top of the board any issue sending within the next 14 days that still has an open slot, since that is unsold inventory against a hard deadline. Separately, flag any sponsor whose invoice is past its due date. Give me a focused view of just those two things, unsold inventory closing soon and money that is late, so I can work them without scrolling the whole board.

Give each sponsor a Build the recap button that kicks off a background agent to assemble a post campaign recap. The agent finds the issues that actually ran for that sponsor using Browse Posts (Admin API) filtered to published posts and Read Post (Admin API) for the detail of each one, pulls the audience size those issues went to with Browse Members (Admin API), and reads the payment status with List Invoices. It writes all of that up as a sponsor facing summary and stores it back in the app on the sponsor record with a timestamp, so I can open it, read it, and send it on. Show the recap status on the sponsor page while it runs, and link the finished write up when it lands.

One important limit to respect: Ghost exposes no email open rate, click rate, or send statistics through this connection, only subscriber counts. Do not promise or display open rates, click rates, or delivery stats anywhere in the app or in the recap, and express reach purely as subscriber numbers. Because subscriber counts move over time, snapshot the count for a newsletter when an issue goes out and store it on that issue, so a recap reports the audience the sponsor actually reached rather than today's number.