Foreign's X Drops¶

A full-stack application for coordinating mutual engagement "drops" on X (Twitter).
Backend: .NET 8 (ASP.NET Core) · Frontend: Angular 17 · Auth: X OAuth 2.0 PKCE
Storage: Google Sheets · Secrets: KeePass via KPScript · HTTP: Flurl · Resilience: Polly · Scheduling: Quartz.NET

Architecture Overview¶

┌─────────────────────────────────────────────────────────┐
│                    Angular Frontend                      │
│  Dashboard · Auth Callback · Admin User Panel           │
└──────────────────────┬──────────────────────────────────┘
                       │ HTTP / REST
┌──────────────────────▼──────────────────────────────────┐
│                  ASP.NET Core API                        │
│  Controllers: Auth, Lists, Users                        │
│  Services: XApiService, GoogleSheetsService,            │
│            KeePassService, DropOrchestrationService     │
│  Scheduler: Quartz.NET (CreateDailyList, EngagementRun) │
└──────┬──────────────────┬────────────────────┬──────────┘
       │                  │                    │
  X API v2          Google Sheets          KeePass DB
  (Flurl + Polly)   (Service Account)     (KPScript.exe)

Google Sheets Layout¶

Sheet 1: `Users`¶

Col A	Col B	Col C	Col D
Twitter User ID	Role	Status	Bearer Token Timestamp (epoch ms)

Role values: admin, primary_staff, secondary_staff, general_member, messenger
Status values: approved, pending, suspended, banned
Column D stores the millisecond epoch timestamp of the last time a bearer token was received for this user. The bearer token itself is stored only in KeePass (not in the sheet).

Sheet 2: `Lists`¶

Col A	Col B	Col C	Col D	Col E	Cols F–Y	Col Z+
List Name	List ID	Created At (ISO)	Go-Live At (ISO)	Duration (min)	(blank)	Member User IDs

Member user IDs begin at column 26 (Z) and grow rightward.
Default: created at 10 PM CST, go-live at 11 PM CST, duration 60 min.

Sheet 3+: `<listId>` (one per drop)¶

Col A	Col B	Col C	Col D	Col E
TweetId	AuthorId	Username	Text	CreatedAt

KeePass Security Design¶

Each user's OAuth bearer token is stored in a KeePass database: - Entry Title / Username: Twitter User ID (e.g., 2244994945) - Entry Password: OAuth 2.0 bearer token

The Google Sheet stores only a timestamp (column D) indicating when the token was last issued — not the token itself. KPScript.exe is used to read/write entries programmatically from the .NET backend.

KeePass Group: TwitterDropTokens (configurable)

Drop Lifecycle (Daily)¶

10:00 PM CST  ── CreateDailyListJob fires
                   • POST /2/lists → creates X list
                   • Writes row to Lists sheet
                   • Messenger DMs all approved members

10:00–11:00 PM ── Join window open
                   • Members click "Join Today's Drop" in UI
                   • POST /2/lists/{id}/members per user
                   • User ID appended to sheet row col 26+

11:00 PM–12:00 AM ── Members post on X inside the list

12:00 AM CST  ── EngagementRunJob fires
                   • GET /2/lists/{id}/tweets → all posts
                   • Saves tweets to sheet named by list ID
                   • For each approved member:
                       - LIKE all tweets not by them
                       - REPOST all tweets not by them
                       - REPOST tweets BY them (collected last)
                   • Messenger DMs "drop complete + next drop time"

Prerequisites¶

1. X (Twitter) Developer Account¶

Go to https://developer.x.com/en/portal/dashboard
Create an App with OAuth 2.0 enabled
Set Redirect URI to http://localhost:4200/auth/callback
Enable scopes: tweet.read tweet.write users.read list.read list.write like.write dm.write offline.access
Copy Client ID, Client Secret, and generate an App Bearer Token

2. Google Service Account¶

Go to https://console.cloud.google.com/
Create a project → Enable Google Sheets API
Create a Service Account → Download JSON credentials → Save as Backend/google-credentials.json
Create your Google Spreadsheet
Share the spreadsheet with the service account email (Editor permission)
Copy the Spreadsheet ID from the URL

3. KeePass Setup¶

Download KeePass 2.x Portable ZIP from https://keepass.info/download.html
Download KPScript plugin from https://keepass.info/plugins.html#kpscript
Create a new .kdbx database and add a group named TwitterDropTokens
Copy all KeePass portable files and KPScript.exe into a keepass/ folder at the repo root — this folder is bundled into the Docker image and invoked via Mono on Cloud Run
Upload your .kdbx to a GCS bucket (see Cloud Run section) — do not commit it

4. Messenger Account¶

Designate one X account as the messenger role
Set its row in the Users sheet with role messenger and status approved
That account must authenticate through the app to store its bearer token in KeePass

Setup & Configuration¶

Backend¶

Copy and fill in appsettings.json in Backend/:

{
  "AppSettings": {
    "XApi": {
      "ClientId": "YOUR_X_CLIENT_ID",
      "ClientSecret": "YOUR_X_CLIENT_SECRET",
      "RedirectUri": "http://localhost:4200/auth/callback",
      "AppBearerToken": "YOUR_APP_BEARER_TOKEN",
      "BaseUrl": "https://api.x.com/2"
    },
    "GoogleSheets": {
      "SpreadsheetId": "YOUR_SPREADSHEET_ID",
      "UsersSheetName": "Users",
      "ListsSheetName": "Lists",
      "ServiceAccountCredentialPath": "google-credentials.json"
    },
    "KeePass": {
      "DatabasePath": "C:\\Path\\To\\TwitterDrop.kdbx",
      "MasterPassword": "YOUR_MASTER_PASSWORD",
      "KPScriptPath": "C:\\Program Files\\KeePass Password Safe 2\\KPScript.exe",
      "GroupName": "TwitterDropTokens"
    },
    "Gcs": {
      "BucketName": "",
      "DatabaseObjectName": "TwitterDrop.kdbx"
    },
    "Schedule": {
      "TimeZoneId": "America/Chicago",
      "ListCreationHour": 22,
      "ListCreationMinute": 0,
      "JoinWindowMinutes": 60,
      "GoLiveDurationMinutes": 60
    }
  }
}

Local development: Leave Gcs.BucketName empty — the .kdbx is read from the local DatabasePath with no GCS sync. Cloud Run: Set KeePass.DatabasePath to /tmp/keepass/TwitterDrop.kdbx, KPScriptPath to /app/keepass/KPScript.exe, and Gcs.BucketName to your bucket name. The service downloads the database before each call and uploads it after each write. TimeZoneId: "America/Chicago" works on both Linux and Windows. "Central Standard Time" also works locally via the TimeZoneHelper fallback.

Place google-credentials.json in the Backend/ directory for local development. On Cloud Run credentials are provided via GOOGLE_APPLICATION_CREDENTIALS (see Cloud Run section below).

Run the backend locally:

cd Backend
dotnet restore
dotnet run
# API available at http://localhost:8080
# Swagger UI at http://localhost:8080/swagger

Frontend¶

cd Frontend
npm install
ng serve
# App available at http://localhost:4200

Google Sheets Setup¶

Create your spreadsheet with these sheets:

Users sheet — Add a header row (optional), then rows like:

2244994945    general_member    approved    0

Lists sheet — Add a header row:

List Name | List ID | Created At | Go-Live At | Duration | (blank cols 6-25) | Member IDs...

API Reference¶

Method	Endpoint	Description
GET	`/api/auth/login`	Get X OAuth2 authorization URL
POST	`/api/auth/callback`	Exchange code for token, update KeePass + Sheets
GET	`/api/auth/me?userId={id}`	Get current user session
GET	`/api/lists/today`	Get today's drop list
POST	`/api/lists/join`	Join today's list
GET	`/api/lists`	Get all historical lists
GET	`/api/users`	Get all users (admin)
PATCH	`/api/users/{id}/status`	Update user status (admin)

X API Endpoints Used¶

Operation	X API Endpoint
OAuth token exchange	`POST https://api.x.com/2/oauth2/token`
Get current user	`GET /2/users/me`
Create list	`POST /2/lists`
Add list member	`POST /2/lists/{id}/members`
Get list tweets	`GET /2/lists/{id}/tweets`
Get list members	`GET /2/lists/{id}/members`
Like a tweet	`POST /2/users/{id}/likes`
Retweet	`POST /2/users/{id}/retweets`
Send DM	`POST /2/dm_conversations/with/{id}/messages`

Polly Resilience Policy¶

All X API calls are wrapped in a Polly retry policy: - Retries: 3 - Triggers: HTTP 429 (rate limit), 500, 502, 503 - Backoff: Exponential (2s, 4s, 8s)

Security Notes¶

Bearer tokens are never stored in Google Sheets — only a timestamp indicating when the token was last received
All tokens live exclusively in KeePass (.kdbx), encrypted at rest with the master password
The .kdbx is persisted in a private GCS bucket; the master password is injected as a Cloud Run env var
CORS origins are configured via the CORS_ORIGINS environment variable — set this to your production frontend URL
Never commit google-credentials.json, appsettings.json with secrets, or the .kdbx file

Deploying to Google Cloud Run (Free Tier)¶

The free tier includes 2 million requests/month, 360,000 vCPU-seconds, and 180,000 GiB-seconds of memory — more than enough for a nightly drop app.

How KeePass works on Cloud Run¶

KeePass 2.x is a .NET Framework application. Cloud Run containers run Linux. The solution:

Mono is installed in the Docker image — it provides a Linux .NET Framework runtime.
KeePass Portable + KPScript.exe are bundled directly in the image (keepass/ folder).
KeePassService detects Linux at runtime and runs mono KPScript.exe ... instead of calling KPScript.exe directly.
Database persistence: since Cloud Run containers are ephemeral, the .kdbx is stored in a GCS bucket. Before every KPScript call the file is downloaded to /tmp; after every write it is uploaded back. This uses the GCS free tier (5 GB free) — the database is a few KB.

Prerequisites¶

Google Cloud CLI (gcloud) installed and authenticated
Docker installed locally
A GCP project with billing enabled (required even for free tier)
KeePass 2.x Portable ZIP and KPScript.exe downloaded locally

1 — Prepare the `keepass/` folder¶

keepass/                  ← place this at the repo root
├── KeePass.exe
├── KPScript.exe
├── KeePass.XmlSerializers.dll
└── ... (all other DLLs from the KeePass Portable ZIP)

Download: - KeePass Portable: https://keepass.info/download.html - KPScript: https://keepass.info/plugins.html#kpscript

2 — Enable required GCP APIs¶

gcloud services enable \
  run.googleapis.com \
  storage.googleapis.com \
  containerregistry.googleapis.com \
  sheets.googleapis.com

3 — Create a GCS bucket and upload the .kdbx¶

PROJECT="your-gcp-project-id"
BUCKET="$PROJECT-keepass-db"

# Create a private bucket (no public access)
gcloud storage buckets create "gs://$BUCKET" \
  --project "$PROJECT" \
  --location us-central1 \
  --uniform-bucket-level-access

# Upload your .kdbx (the one with the TwitterDropTokens group already created)
gcloud storage cp path/to/TwitterDrop.kdbx "gs://$BUCKET/TwitterDrop.kdbx"

4 — Grant the service account access to the bucket¶

SA_EMAIL="your-service-account@your-project.iam.gserviceaccount.com"

gcloud storage buckets add-iam-policy-binding "gs://$BUCKET" \
  --member="serviceAccount:$SA_EMAIL" \
  --role="roles/storage.objectAdmin"

5 — Store the Google credentials JSON as a Cloud Run secret¶

# Create a Secret Manager secret for the credentials JSON
gcloud secrets create google-credentials \
  --data-file=Backend/google-credentials.json

gcloud secrets add-iam-policy-binding google-credentials \
  --member="serviceAccount:$SA_EMAIL" \
  --role="roles/secretmanager.secretAccessor"

This uses Secret Manager for one static secret (the service account JSON file), which stays within the free tier (6 active versions free). No per-user secrets are created — all user tokens live in the KeePass database in GCS.

6 — Build and push the Docker image¶

IMAGE="gcr.io/$PROJECT/foreignx-drops"

docker build -t $IMAGE -f Dockerfile .
docker push $IMAGE

Or use Cloud Build (no local Docker required):

gcloud builds submit --tag $IMAGE

7 — Deploy to Cloud Run¶

gcloud run deploy foreignx-drops \
  --image $IMAGE \
  --region us-central1 \
  --platform managed \
  --allow-unauthenticated \
  --min-instances 1 \
  --max-instances 1 \
  --memory 512Mi \
  --cpu 1 \
  --set-env-vars "ASPNETCORE_ENVIRONMENT=Production" \
  --set-env-vars "AppSettings__XApi__ClientId=YOUR_X_CLIENT_ID" \
  --set-env-vars "AppSettings__XApi__ClientSecret=YOUR_X_CLIENT_SECRET" \
  --set-env-vars "AppSettings__XApi__AppBearerToken=YOUR_APP_BEARER_TOKEN" \
  --set-env-vars "AppSettings__XApi__RedirectUri=https://YOUR_FRONTEND_URL/auth/callback" \
  --set-env-vars "AppSettings__GoogleSheets__SpreadsheetId=YOUR_SPREADSHEET_ID" \
  --set-env-vars "AppSettings__KeePass__DatabasePath=/tmp/keepass/TwitterDrop.kdbx" \
  --set-env-vars "AppSettings__KeePass__MasterPassword=YOUR_MASTER_PASSWORD" \
  --set-env-vars "AppSettings__KeePass__KPScriptPath=/app/keepass/KPScript.exe" \
  --set-env-vars "AppSettings__KeePass__GroupName=TwitterDropTokens" \
  --set-env-vars "AppSettings__Gcs__BucketName=$BUCKET" \
  --set-env-vars "AppSettings__Gcs__DatabaseObjectName=TwitterDrop.kdbx" \
  --set-env-vars "AppSettings__Schedule__TimeZoneId=America/Chicago" \
  --set-env-vars "CORS_ORIGINS=https://YOUR_FRONTEND_URL" \
  --set-env-vars "GOOGLE_APPLICATION_CREDENTIALS=/secrets/google-credentials/latest" \
  --set-secrets "/secrets/google-credentials/latest=google-credentials:latest"

--min-instances 1 is critical. The Quartz scheduler runs inside the process. If Cloud Run scales to zero between the 10 PM list-creation job and the midnight engagement job, the scheduler is lost. One minimum instance keeps it alive and still falls within the free tier (1 × 512 Mi running continuously = ~360,000 vCPU-seconds/month, right at the free limit; memory stays well under 180,000 GiB-seconds).

8 — Update the X OAuth Redirect URI¶

In the X Developer Portal, add your Cloud Run service URL as an allowed redirect URI:

https://<your-service>-<hash>-uc.a.run.app/auth/callback

9 — Verify the deployment¶

# Get the service URL
gcloud run services describe foreignx-drops --region us-central1 --format="value(status.url)"

# Health check
curl https://<service-url>/healthz

# Swagger UI
open https://<service-url>/swagger

Environment variable reference¶

Variable	Description
`AppSettings__XApi__ClientId`	X OAuth 2.0 Client ID
`AppSettings__XApi__ClientSecret`	X OAuth 2.0 Client Secret
`AppSettings__XApi__AppBearerToken`	X App-level bearer token
`AppSettings__XApi__RedirectUri`	Full OAuth callback URL (your frontend)
`AppSettings__GoogleSheets__SpreadsheetId`	Google Sheet ID from the URL
`AppSettings__KeePass__DatabasePath`	Local path inside container: `/tmp/keepass/TwitterDrop.kdbx`
`AppSettings__KeePass__MasterPassword`	KeePass master password
`AppSettings__KeePass__KPScriptPath`	`/app/keepass/KPScript.exe` (bundled in image)
`AppSettings__KeePass__GroupName`	KeePass group name (default: `TwitterDropTokens`)
`AppSettings__Gcs__BucketName`	GCS bucket storing the `.kdbx`
`AppSettings__Gcs__DatabaseObjectName`	Object name in bucket (default: `TwitterDrop.kdbx`)
`AppSettings__Schedule__TimeZoneId`	IANA timezone (`America/Chicago`)
`CORS_ORIGINS`	Comma-separated allowed frontend origin(s)
`GOOGLE_APPLICATION_CREDENTIALS`	Path to mounted service account JSON

NuGet packages required (Backend.csproj additions)¶

<PackageReference Include="Google.Cloud.Storage.V1" Version="4.*" />

Remove the Serilog file sink package if present — Cloud Run captures stdout directly.

Project Structure¶

ForeignsXDrops/
├── Backend/
│   ├── Backend.csproj
│   ├── Program.cs
│   ├── appsettings.json
│   ├── google-credentials.json        ← local dev only, never committed
│   ├── Configuration/
│   │   └── AppSettings.cs
│   ├── Models/
│   │   └── Models.cs
│   ├── Services/
│   │   ├── KeePassService.cs          ← KPScript via Mono on Linux, GCS sync for .kdbx
│   │   ├── GoogleSheetsService.cs
│   │   ├── XApiService.cs
│   │   └── DropOrchestrationService.cs
│   ├── Controllers/
│   │   ├── AuthController.cs
│   │   ├── ListsController.cs
│   │   └── UsersController.cs
│   └── Jobs/
│       └── ScheduledJobs.cs           ← includes TimeZoneHelper
├── Dockerfile
├── .dockerignore
└── Frontend/
    ├── angular.json
    ├── package.json
    ├── tsconfig.json
    └── src/
        ├── index.html
        ├── main.ts
        └── app/
            ├── app.component.ts
            ├── app.config.ts
            ├── app.routes.ts
            ├── models/models.ts
            ├── services/
            │   ├── auth.service.ts
            │   ├── drops.service.ts
            │   └── users.service.ts
            └── components/
                ├── dashboard.component.ts
                ├── auth-callback.component.ts
                └── admin-users.component.ts

Foreign's X Drops¶

Architecture Overview¶

Google Sheets Layout¶

Sheet 1: Users¶

Sheet 2: Lists¶

Sheet 3+: <listId> (one per drop)¶

KeePass Security Design¶

Drop Lifecycle (Daily)¶

Prerequisites¶

1. X (Twitter) Developer Account¶

2. Google Service Account¶

3. KeePass Setup¶

4. Messenger Account¶

Setup & Configuration¶

Backend¶

Frontend¶

Google Sheets Setup¶

API Reference¶

X API Endpoints Used¶

Polly Resilience Policy¶

Security Notes¶

Deploying to Google Cloud Run (Free Tier)¶

How KeePass works on Cloud Run¶

Prerequisites¶

1 — Prepare the keepass/ folder¶

2 — Enable required GCP APIs¶

3 — Create a GCS bucket and upload the .kdbx¶

4 — Grant the service account access to the bucket¶

5 — Store the Google credentials JSON as a Cloud Run secret¶

6 — Build and push the Docker image¶

7 — Deploy to Cloud Run¶

8 — Update the X OAuth Redirect URI¶

9 — Verify the deployment¶

Environment variable reference¶

NuGet packages required (Backend.csproj additions)¶

Project Structure¶

Sheet 1: `Users`¶

Sheet 2: `Lists`¶

Sheet 3+: `<listId>` (one per drop)¶

1 — Prepare the `keepass/` folder¶