Soli - API & DB Schema

Yoonjae Song (@jaytherampage)

[4] API & 데이터베이스 스키마 설계

Project: Soli — OMR Choir Practicing App
Author: Yoonjae Song (@jaytherampage)
Version: 1.0.0

1. RESTful API Endpoints

1.1 POST /api/v1/auth/anonymous

Generate an anonymous user session with 3 free sheet uploads.

Request Payload

{}

Response Payload

{
  "user_id": "uuid-generated-user-id",
  "anonymous_token": "jwt-token-for-session",
  "free_sheets_remaining": 3,
  "created_at": "2026-07-08T12:00:00Z"
}

Status Codes

CodeDescription
201Anonymous user created successfully
429Rate limit exceeded

1.2 POST /api/v1/auth/link

Link an anonymous account with Kakao or Phone credentials.

Request Payload

{
  "anonymous_token": "existing-jwt-token",
  "provider": "kakao",
  "provider_token": "kakao-oauth-access-token"
}

{
  "anonymous_token": "existing-jwt-token",
  "provider": "phone",
  "phone_number": "+821012345678",
  "verification_code": "123456"
}

Response Payload

{
  "user_id": "uuid-user-id",
  "access_token": "new-jwt-token",
  "refresh_token": "refresh-token-string",
  "has_active_subscription": false,
  "tier": "free"
}

Status Codes

CodeDescription
200Account linked successfully
400Invalid provider or missing fields
401Anonymous token invalid or expired
409Provider account already linked to another user

1.3 POST /api/v1/sheets/upload

Multipart upload for sheet music images.

Request Payload (multipart/form-data)

FieldTypeDescription
fileFileSheet music image (PNG, JPEG, PDF)
titlestringOptional display title for the sheet

Response Payload

{
  "sheet_id": "uuid-sheet-id",
  "status": "PENDING",
  "uploaded_at": "2026-07-08T12:00:00Z",
  "estimated_wait_seconds": 30
}

Status Codes

CodeDescription
201Upload accepted, parsing queued
400Invalid file format or size exceeds 20MB
402Free tier limit reached (requires subscription)
413File too large

1.4 GET /api/v1/sheets/{sheet_id}/status

Poll the OMR parsing status of a submitted sheet.

Response Payload

{
  "sheet_id": "uuid-sheet-id",
  "status": "PARSING",
  "progress_pct": 45,
  "created_at": "2026-07-08T12:00:00Z",
  "updated_at": "2026-07-08T12:00:35Z"
}

Possible Status Values

StatusDescription
PENDINGQueued for processing
PARSINGOMR model is analyzing the image
SYSTEM_PROMPT_CORRECTIONLLM is post-processing and correcting the output
COMPLETEParsing finished successfully
FAILEDParsing encountered an error

Status Codes

CodeDescription
200Status retrieved
404Sheet not found
403Sheet does not belong to user

1.5 GET /api/v1/sheets/{sheet_id}/parsed

Returns the structured OMR music JSON data for a completed sheet.

Response Payload

{
  "sheet_id": "uuid-sheet-id",
  "status": "COMPLETE",
  "title": "Amazing Grace",
  "metadata": {
    "tempo": 80,
    "key_signature": "G major",
    "time_signature": "4/4"
  },
  "measures": [
    {
      "measure_number": 1,
      "parts": {
        "soprano": [
          {
            "pitch": "G4",
            "duration_type": "quarter",
            "duration_seconds": 0.75,
            "is_rest": false,
            "image_coordinates": { "x": 120, "y": 45, "w": 30, "h": 20 }
          }
        ],
        "alto": [],
        "tenor": [],
        "bass": []
      }
    }
  ]
}

Status Codes

CodeDescription
200Parsed data returned
202Parsing not yet complete (retry later)
404Sheet not found
422Parsing failed, see error details

1.6 POST /api/v1/presets

Save individual volume and mute mixer states per sheet.

Request Payload

{
  "sheet_id": "uuid-sheet-id",
  "mixer_state": {
    "soprano": { "volume": 0.8, "muted": false },
    "alto": { "volume": 0.6, "muted": false },
    "tenor": { "volume": 0.7, "muted": false },
    "bass": { "volume": 1.0, "muted": true }
  },
  "preset_name": "My Practice Mix"
}

Response Payload

{
  "preset_id": "uuid-preset-id",
  "sheet_id": "uuid-sheet-id",
  "preset_name": "My Practice Mix",
  "created_at": "2026-07-08T12:05:00Z"
}

Status Codes

CodeDescription
201Preset saved
400Invalid mixer state format
404Sheet not found

1.7 GET /api/v1/presets/{sheet_id}

Retrieve the user's saved mixer presets for a specific sheet.

Response Payload

{
  "sheet_id": "uuid-sheet-id",
  "presets": [
    {
      "preset_id": "uuid-preset-id",
      "preset_name": "My Practice Mix",
      "mixer_state": {
        "soprano": { "volume": 0.8, "muted": false },
        "alto": { "volume": 0.6, "muted": false },
        "tenor": { "volume": 0.7, "muted": false },
        "bass": { "volume": 1.0, "muted": true }
      },
      "created_at": "2026-07-08T12:05:00Z"
    }
  ]
}

Status Codes

CodeDescription
200Presets retrieved
404Sheet not found or no presets saved

1.8 POST /api/v1/billing/revenuecat-webhook

RevenueCat webhook receiver for handling subscription lifecycle events.

Request Payload — See Section 4: RevenueCat Webhook Payload Schema

Response Payload

{
  "status": "ok",
  "processed_event": "INITIAL_PURCHASE"
}

Status Codes

CodeDescription
200Webhook received and processed
400Invalid or malformed payload
401Invalid webhook signature
500Internal processing error

2. PostgreSQL Relational DB Schema

2.1 users Table

CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    anonymous_token TEXT UNIQUE,
    kakao_id        TEXT UNIQUE,
    phone_number    TEXT UNIQUE,
    tier            TEXT NOT NULL DEFAULT 'free'
                        CHECK (tier IN ('free', 'premium')),
    subscription_platform TEXT CHECK (subscription_platform IN ('apple', 'google')),
    revenuecat_original_app_user_id TEXT,
    free_sheets_remaining INTEGER NOT NULL DEFAULT 3,
    total_sheets_uploaded  INTEGER NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_users_anonymous_token ON users (anonymous_token);
CREATE INDEX idx_users_kakao_id ON users (kakao_id);
CREATE INDEX idx_users_phone_number ON users (phone_number);
CREATE INDEX idx_users_tier ON users (tier);

2.2 sheets Table

CREATE TABLE sheets (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title           TEXT,
    file_path       TEXT NOT NULL,
    file_size_bytes INTEGER NOT NULL,
    file_mime_type  TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'PENDING'
                        CHECK (status IN (
                            'PENDING',
                            'PARSING',
                            'SYSTEM_PROMPT_CORRECTION',
                            'COMPLETE',
                            'FAILED'
                        )),
    omr_result      JSONB,
    error_message   TEXT,
    parsed_at       TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_sheets_user_id ON sheets (user_id);
CREATE INDEX idx_sheets_status ON sheets (status);
CREATE INDEX idx_sheets_user_status ON sheets (user_id, status);
CREATE INDEX idx_sheets_created_at ON sheets (created_at DESC);

2.3 part_presets Table

CREATE TABLE part_presets (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    sheet_id        UUID NOT NULL REFERENCES sheets(id) ON DELETE CASCADE,
    preset_name     TEXT NOT NULL DEFAULT 'Default',
    mixer_state     JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT unique_user_sheet_preset UNIQUE (user_id, sheet_id, preset_name)
);

CREATE INDEX idx_part_presets_user_id ON part_presets (user_id);
CREATE INDEX idx_part_presets_sheet_id ON part_presets (sheet_id);
CREATE INDEX idx_part_presets_user_sheet ON part_presets (user_id, sheet_id);

2.4 Entity Relationship Summary

users 1 --- * sheets
users 1 --- * part_presets
sheets 1 --- * part_presets

3. OMR Output Structural JSON Schema

Below is a complete mock example of the structured JSON returned by the OMR pipeline.

{
  "sheet_id": "550e8400-e29b-41d4-a716-446655440000",
  "title": "Amazing Grace",
  "composer": "John Newton",
  "arranger": "Soli AI",
  "status": "COMPLETE",
  "metadata": {
    "tempo": 80,
    "key_signature": "G major",
    "time_signature": "4/4",
    "total_measures": 16
  },
  "measures": [
    {
      "measure_number": 1,
      "time_signature_override": null,
      "parts": {
        "soprano": [
          {
            "pitch": "G4",
            "duration_type": "quarter",
            "duration_seconds": 0.75,
            "is_rest": false,
            "image_coordinates": {
              "x": 120.5,
              "y": 45.2,
              "w": 28.0,
              "h": 18.5
            }
          },
          {
            "pitch": "A4",
            "duration_type": "quarter",
            "duration_seconds": 0.75,
            "is_rest": false,
            "image_coordinates": {
              "x": 165.0,
              "y": 42.8,
              "w": 26.0,
              "h": 18.5
            }
          },
          {
            "pitch": "B4",
            "duration_type": "half",
            "duration_seconds": 1.5,
            "is_rest": false,
            "image_coordinates": {
              "x": 210.0,
              "y": 40.0,
              "w": 35.0,
              "h": 18.5
            }
          }
        ],
        "alto": [
          {
            "pitch": "D4",
            "duration_type": "quarter",
            "duration_seconds": 0.75,
            "is_rest": false,
            "image_coordinates": {
              "x": 120.5,
              "y": 72.0,
              "w": 28.0,
              "h": 18.5
            }
          },
          {
            "pitch": "E4",
            "duration_type": "quarter",
            "duration_seconds": 0.75,
            "is_rest": false,
            "image_coordinates": {
              "x": 165.0,
              "y": 69.5,
              "w": 26.0,
              "h": 18.5
            }
          },
          {
            "pitch": "F#4",
            "duration_type": "half",
            "duration_seconds": 1.5,
            "is_rest": false,
            "image_coordinates": {
              "x": 210.0,
              "y": 67.0,
              "w": 35.0,
              "h": 18.5
            }
          }
        ],
        "tenor": [
          {
            "pitch": "B3",
            "duration_type": "quarter",
            "duration_seconds": 0.75,
            "is_rest": false,
            "image_coordinates": {
              "x": 120.5,
              "y": 98.5,
              "w": 28.0,
              "h": 18.5
            }
          },
          {
            "pitch": "C#4",
            "duration_type": "eighth",
            "duration_seconds": 0.375,
            "is_rest": false,
            "image_coordinates": {
              "x": 165.0,
              "y": 96.0,
              "w": 22.0,
              "h": 18.5
            }
          },
          {
            "pitch": "D4",
            "duration_type": "eighth",
            "duration_seconds": 0.375,
            "is_rest": false,
            "image_coordinates": {
              "x": 192.0,
              "y": 96.0,
              "w": 22.0,
              "h": 18.5
            }
          }
        ],
        "bass": [
          {
            "pitch": "G2",
            "duration_type": "half",
            "duration_seconds": 1.5,
            "is_rest": false,
            "image_coordinates": {
              "x": 120.5,
              "y": 128.0,
              "w": 35.0,
              "h": 20.0
            }
          },
          {
            "pitch": "G2",
            "duration_type": "half",
            "duration_seconds": 1.5,
            "is_rest": false,
            "image_coordinates": {
              "x": 210.0,
              "y": 128.0,
              "w": 35.0,
              "h": 20.0
            }
          }
        ]
      }
    },
    {
      "measure_number": 2,
      "time_signature_override": null,
      "parts": {
        "soprano": [
          {
            "pitch": null,
            "duration_type": "quarter",
            "duration_seconds": 0.75,
            "is_rest": true,
            "image_coordinates": {
              "x": 280.0,
              "y": 45.0,
              "w": 20.0,
              "h": 10.0
            }
          },
          {
            "pitch": "C5",
            "duration_type": "dotted_quarter",
            "duration_seconds": 1.125,
            "is_rest": false,
            "image_coordinates": {
              "x": 320.0,
              "y": 38.0,
              "w": 30.0,
              "h": 18.5
            }
          }
        ],
        "alto": [
          {
            "pitch": "G4",
            "duration_type": "whole",
            "duration_seconds": 3.0,
            "is_rest": false,
            "image_coordinates": {
              "x": 280.0,
              "y": 70.0,
              "w": 85.0,
              "h": 18.5
            }
          }
        ],
        "tenor": [],
        "bass": [
          {
            "pitch": "C3",
            "duration_type": "whole",
            "duration_seconds": 3.0,
            "is_rest": false,
            "image_coordinates": {
              "x": 280.0,
              "y": 128.0,
              "w": 85.0,
              "h": 20.0
            }
          }
        ]
      }
    }
  ]
}

Notes on the OMR JSON

FieldTypeDescription
pitchstringnull
duration_typestringNote duration: whole, half, quarter, eighth, sixteenth, dotted_*
duration_secondsnumberPrecise duration in seconds computed from tempo and time signature
is_restbooleanWhether this entry is a rest (no pitch)
image_coordinatesobjectBounding box of the note/rest in the original sheet image (pixels)
image_coordinates.xnumberLeft edge of bounding box
image_coordinates.ynumberTop edge of bounding box
image_coordinates.wnumberWidth of bounding box
image_coordinates.hnumberHeight of bounding box

4. RevenueCat Webhook Payload Schema

RevenueCat sends webhook events to POST /api/v1/billing/revenuecat-webhook for subscription lifecycle management.

4.1 Common Payload Structure

{
  "event": {
    "type": "INITIAL_PURCHASE",
    "event_timestamp_ms": 1750400000000,
    "product_id": "com.soli.premium.monthly",
    "entitlement_id": "premium",
    "entitlement_ids": ["premium"],
    "app_user_id": "internal-user-uuid",
    "original_app_user_id": "revenuecat-original-id",
    "country_code": "KR",
    "currency": "KRW",
    "price": 5900,
    "price_in_purchased_currency": 5900,
    "period_type": "trial",
    "store": "play_store",
    "is_family_share": false,
    "subscriber_attributes": {
      "$displayName": "Soli Premium",
      "$email": null
    },
    "new_product_id": null,
    "expiration_at_ms": 1753084800000,
    "auto_resume_at_ms": null,
    "cancel_reason": null,
    "transfer_id": null,
    "environment": "PRODUCTION"
  },
  "api_version": "1.0"
}

4.2 Event Types

Event TypeDescriptionAction on Server
INITIAL_PURCHASEFirst subscription purchase (including free trial start)Set user tier to premium, update revenuecat_original_app_user_id
RENEWALSuccessful subscription renewalExtend expiration_at_ms tracking, log renewal event
CANCELLATIONUser cancelled subscriptionLog cancellation, continue service until expiration
BILLING_ISSUEFailed billing attemptNotify user, retry billing; downgrade after grace period

4.3 Server-Side Processing Logic (Pseudocode)

# Pseudocode for RevenueCat webhook processing
def process_revenuecat_webhook(event: dict) -> None:
    event_type = event["event"]["type"]
    app_user_id = event["event"]["app_user_id"]

    match event_type:
        case "INITIAL_PURCHASE":
            # Update user tier to premium
            db.execute("""
                UPDATE users
                SET tier = 'premium',
                    revenuecat_original_app_user_id = :original_id
                WHERE id = :user_id
            """, {
                "user_id": app_user_id,
                "original_id": event["event"]["original_app_user_id"],
            })

            # Log the purchase event
            db.execute("""
                INSERT INTO billing_events
                    (user_id, event_type, product_id, price, currency, raw_payload)
                VALUES (:user_id, :event_type, :product_id, :price, :currency, :raw)
            """, {
                "user_id": app_user_id,
                "event_type": event_type,
                "product_id": event["event"]["product_id"],
                "price": event["event"]["price"],
                "currency": event["event"]["currency"],
                "raw": json.dumps(event),
            })

        case "RENEWAL":
            # Extend subscription period
            db.execute("""
                UPDATE users SET updated_at = now()
                WHERE id = :user_id AND tier = 'premium'
            """, {"user_id": app_user_id})

            # Log renewal
            db.execute("""
                INSERT INTO billing_events
                    (user_id, event_type, product_id, price, currency, raw_payload)
                VALUES (:user_id, :event_type, :product_id, :price, :currency, :raw)
            """, {
                "user_id": app_user_id,
                "event_type": event_type,
                "product_id": event["event"]["product_id"],
                "price": event["event"]["price"],
                "currency": event["event"]["currency"],
                "raw": json.dumps(event),
            })

        case "CANCELLATION":
            # Log cancellation; service continues until expiration
            db.execute("""
                INSERT INTO billing_events
                    (user_id, event_type, cancel_reason, raw_payload)
                VALUES (:user_id, :event_type, :cancel_reason, :raw)
            """, {
                "user_id": app_user_id,
                "event_type": event_type,
                "cancel_reason": event["event"].get("cancel_reason"),
                "raw": json.dumps(event),
            })

        case "BILLING_ISSUE":
            # Log billing issue; schedule retry
            db.execute("""
                INSERT INTO billing_events
                    (user_id, event_type, product_id, raw_payload)
                VALUES (:user_id, :event_type, :product_id, :raw)
            """, {
                "user_id": app_user_id,
                "event_type": event_type,
                "product_id": event["event"]["product_id"],
                "raw": json.dumps(event),
            })

            # Trigger notification to user about failed billing
            notify_billing_issue(app_user_id)

4.4 billing_events Table

CREATE TABLE billing_events (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    event_type      TEXT NOT NULL,
    product_id      TEXT,
    price           INTEGER,
    currency        TEXT,
    cancel_reason   TEXT,
    raw_payload     JSONB NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_billing_events_user_id ON billing_events (user_id);
CREATE INDEX idx_billing_events_event_type ON billing_events (event_type);
CREATE INDEX idx_billing_events_created_at ON billing_events (created_at DESC);

5. Supplemental Tables

5.1 billing_events (see Section 4.4 above)

5.2 migration_history

CREATE TABLE migration_history (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    version         INTEGER NOT NULL UNIQUE,
    description     TEXT NOT NULL,
    applied_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    checksum        TEXT NOT NULL
);

End of API & Database Schema Specification v1.0
Prepared by Yoonjae Song (@jaytherampage)