Can LLMs Build a Multi-Tenant Short-Video SaaS? An Experiment and Retrospective

    This article documents a hands-on experiment testing whether large language models can build the core modules of a multi-tenant SaaS platform with minimal human intervention. The pipeline takes user-uploaded product images as input and outputs a short video with subtitles, voiceover, and product information. It covers compliance prerequisites, the architecture and technology stack (Next.js, FastAPI, PostgreSQL with RLS, Redis, Celery, and S3-compatible storage), the data model, row-level security policies, and API contracts—along with practical lessons on tenant isolation, idempotency, and cost measurement.

    1. Experiment Scope and Compliance Prerequisites

    The question this experiment addresses: can a large language model build the main modules of a multi-tenant SaaS with limited human intervention? The platform’s input is product images uploaded by users; the output is a short video with subtitles, voiceover, and product information. Video generation depends on third-party model APIs, whose quality and pricing are determined by the vendor. Therefore, this article validates only the technical pipeline and makes no judgment about commercial returns.

    Before development begins, the following must be confirmed:

    • Content licensing: Users must have authorization for the images they upload. The user agreement should require an explicit commitment from users, and the platform must retain authorization records.
    • Likeness and privacy: Assets containing faces require the consent of the person depicted, with a clearly stated storage period. Original files and generated videos must be automatically deleted once the period expires.
    • Advertising compliance: Marketing copy must avoid absolute claims and unverified product benefits. The specific restricted-word list should be written into moderation rules after legal review.
    • Content moderation: Generated results must pass automated review before publication, with a proportion of results sampled for manual review. Failed assets enter a pending queue; users can appeal, and appeal records are retained.
    • Data retention and cross-border transfer: Source assets and generated videos are stored in S3-compatible storage. If the vendor is located overseas, the contract must specify the scope of permitted data transfer, confidentiality obligations, and deletion deadlines.
    • Cost estimation: The per-video cost depends on vendor pricing, resolution, and duration. Unit prices must be measured through real API calls, logging the vendor, resolution, duration, call count, and failed retry count. Do not substitute rough estimates.

    2. Architecture and Technology Stack

    Layer Choice Suggested Version Key Configuration
    Frontend Next.js App Router 14+ Server components for list pages; client components for upload and polling
    Backend FastAPI 0.110+ Middleware uniformly validates JWTs and extracts the tenant_id
    Database PostgreSQL 15+ (or Supabase) 15 Enable RLS; the application account must not have BYPASSRLS
    Queue Redis 7 + Celery 5 (or arq) 7 / 5 Configure REDIS_URL via environment variables; set tasks to acks_late
    Object storage S3-compatible storage N/A Buckets are private; access via presigned URLs; only paths are stored in the database

    Version numbers are suggested minimums; defer to your team’s actual environment. All secrets must be stored in environment variables or a secrets management service—never in code or logs.

    PostgreSQL was chosen because RLS enforces tenant isolation at the database layer. Databases without RLS, such as MySQL, require manually adding tenant conditions to every query at the application layer—a single omission can lead to unauthorized cross-tenant access. If MySQL is unavoidable, wrap all queries behind a unified application-layer abstraction, and use automated tests to verify that every endpoint returns 404 for cross-tenant requests.

    How does the database connection identify the tenant?

    At the start of each request, the backend sets a session variable within a transaction:

    1. SELECT set_config('app.tenant_id', $1, true);

    Here $1 is the tenant ID parsed from the JWT, passed in via parameter binding—never via string concatenation.

    3. Data Model and Multi-Tenant Isolation

    3.1 Table Structure

    • users: id (uuid, primary key), phone_hash (varchar(64), unique—stores an HMAC of the phone number, never plaintext), created_at (timestamptz).
    • tenants: id (uuid, primary key), name (varchar(100)), type (individual or business), created_at (timestamptz).
    • memberships: Maps users to tenants. Composite primary key of (user_id, tenant_id); role (owner, admin, or member); created_at.
    • wallets: Credit balance. tenant_id (uuid, primary key), balance (bigint, default 0), updated_at. Constraint: CHECK (balance >= 0).
    • credit_ledger: Credit transactions. id (bigserial, primary key), tenant_id (uuid, not null), delta (bigint—positive for credits, negative for debits), balance_after (bigint—snapshot of the balance after the change), biz_no (varchar(64), business order number), idempotency_key (varchar(128), unique), reason (varchar(32), e.g., consume, refund, recharge), created_at (timestamptz).
    • media_assets: Source assets and generated videos. id (uuid), tenant_id (uuid), kind (source or output), storage_path (varchar(512)), created_at.
    • video_jobs: Generation tasks. id (uuid), tenant_id (uuid), asset_id (uuid), status (varchar(32)), attempts (int, default 0), last_error (text), created_at, updated_at. Index: (tenant_id, status, created_at).

    3.2 Row-Level Security Policies

    1. ALTER TABLE wallets ENABLE ROW LEVEL SECURITY;
    2. ALTER TABLE credit_ledger ENABLE ROW LEVEL SECURITY;
    3. ALTER TABLE video_jobs ENABLE ROW LEVEL SECURITY;
    4. ALTER TABLE media_assets ENABLE ROW LEVEL SECURITY;
    5. CREATE POLICY tenant_isolation ON wallets
    6. USING (tenant_id = current_setting('app.tenant_id')::uuid)
    7. WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

    Create equivalent policies for the remaining tables. Migration scripts must include the policy definitions, and CI should run cross-tenant query tests using a non-superuser account.

    3.3 API Contract

    Creating a generation task:

    • Request: POST /api/v1/video-jobs
    • Header: Authorization: Bearer <JWT>
    • Body: {"asset_id": "uuid", "duration_sec": 15, "resolution": "720p"}
    • Success response, 202: {"job_id": "uuid", "status": "queued"}