Skip to main content

July 17

Hi all, hope you’re well! Lots of Deserted Chateau updates to talk about, also a brief update to ArtPoster.

Database stored procedure organisation

Up until now I was naming stored procedures with basic function names, but with more and more of them, it was important to assign them a proper naming convention (also so that stored procedures for separate areas of the site don’t get confused for each other). That meant trawling through a few database-related code files to make sure wrong procedure names weren’t being called and so on, I’ve written a simple script to clear and re-insert all stored procedures in the database when needed to simplify the testing process.

Stripe refactoring and integration testing

Properly testing Stripe integration means testing the webhooks that Stripe sends updates on; I realised I kept putting this off as there’s no efficient way to open up the test server to Stripe’s IP addresses on Amazon Lightsail’s normal features, so to solve that problem I’ve restricted access to the test environment using Linux’s iptables service instead.

The database structure for Deserted Chateau, in terms of Stripe payments, has also been simplified: in the past I was storing invoice information there as well as subscription info, but that’s unnecessary as the subscription can be configured to cancel on repeated failed payments and so on. As such there are now only two tables storing actual subscription data (and no sensitive data, just what we need to know if a user is subscribed and when).

The process of buying a subscription is implemented and working, as is managing a subscription. All that’s really left is to test the webhook works correctly and check that subscription cancellation/deletion events coming from the webhook endpoint are correctly handled, which should be fairly easy now.

Moving from CloudFront to BunnyCDN

The biggest financial obstacle to Deserted Chateau working has been CloudFront - Amazon’s CDN service is incredibly expensive (around $0.07 per GB of bandwidth, once savings plans are accounted for). It may be powerful, but there’s actually very little of the power features needed for this use case. In addition, all of the code to optimise images and the like I’ve implemented outside of CloudFront entirely, so switching CDN is thankfully fairly easy.

I’ve therefore switched the site to BunnyCDN, which is massively cheaper - $0.01 per GB, over 80% cheaper. Performance seems to be fine; obviously it doesn’t have as many features as CloudFront, but thankfully we don’t need them, nor can I think of any foreseeable use case where we would. Setting up the origin for the CDN (Amazon S3 buckets) wasn’t too hard, and BunnyCDN can send authorisation signatures to S3 so the buckets don’t have to be publicly accessible which is good. To be absolutely certain, I made sure to ask them about their data policy, and they’ve made it clear they don’t send data to third parties or use it for e.g. AI purposes. Wasn’t likely anyway, as a CDN being cache makes such use impractical, but it was important to check.

It likely means the planned subscription fee can be reduced from $5 to $3, but either way it should make the site far more viable in the long term.

Moderation and reports progress

I spent some time deciding exactly how to structure reports in the backend, after previously implementing report groups as a distinct entity:

  • Reports, using SQL GROUP BY to aggregate reports
  • Report groups, grouping reports into a “report group”, stored in a separate database table

I decided to keep the existing report group structure, as while it’s a bit more complex, it will make it easier to manage grouped reports in terms of e.g. needing less updates to resolve a report, easier separation of past reports and current ones, etc. I’m in the process of making the webpages to actually manage the reports; most are done functionally but need prettifying.

AWS Reserved Instances

Both to try it out, and also for cost-efficiency purposes, I bought a 1 year reserved instance for Deserted Chateau’s test database and cache servers (around $200 for one year). Pretty easy to do, and it’s a ~35% saving versus paying month to month. Handy to know how the process works for when the live site is made, though sadly Lightsail web servers can’t be reserved in that way.

Back to top