Schema
    RPS Digital P3 Intake ArchiveUpdated May 10, 2026SQL

    RPS Digital P3 Intake Archive database schema

    Imported SQL source from ../../shared/db/schema.sql.

    Source file

    ../../shared/db/schema.sql

    SQL82 lines
    1. CREATE TABLE IF NOT EXISTS shops (
    2. id TEXT PRIMARY KEY,
    3. shopify_domain TEXT NOT NULL UNIQUE,
    4. access_token TEXT NOT NULL,
    5. created_at TEXT NOT NULL
    6. );
    8. CREATE TABLE IF NOT EXISTS products (
    9. id TEXT PRIMARY KEY,
    10. shop_id TEXT NOT NULL,
    11. shopify_product_id TEXT,
    12. current_release_id TEXT,
    13. prompt_id TEXT,
    14. created_at TEXT NOT NULL,
    15. FOREIGN KEY (shop_id) REFERENCES shops(id)
    16. );
    18. CREATE TABLE IF NOT EXISTS releases (
    19. id TEXT PRIMARY KEY,
    20. product_id TEXT NOT NULL,
    21. version TEXT NOT NULL,
    22. status TEXT NOT NULL,
    23. created_at TEXT NOT NULL,
    24. FOREIGN KEY (product_id) REFERENCES products(id)
    25. );
    27. CREATE TABLE IF NOT EXISTS assets (
    28. id TEXT PRIMARY KEY,
    29. release_id TEXT NOT NULL,
    30. key TEXT NOT NULL,
    31. checksum TEXT NOT NULL,
    32. mime TEXT NOT NULL,
    33. width INTEGER,
    34. height INTEGER,
    35. size INTEGER NOT NULL,
    36. variant_label TEXT NOT NULL,
    37. created_at TEXT NOT NULL,
    38. FOREIGN KEY (release_id) REFERENCES releases(id)
    39. );
    41. CREATE TABLE IF NOT EXISTS prompts (
    42. id TEXT PRIMARY KEY,
    43. shop_id TEXT NOT NULL,
    44. raw_prompt TEXT NOT NULL,
    45. provider TEXT NOT NULL,
    46. model TEXT NOT NULL,
    47. output_summary TEXT,
    48. created_at TEXT NOT NULL,
    49. FOREIGN KEY (shop_id) REFERENCES shops(id)
    50. );
    52. CREATE TABLE IF NOT EXISTS jobs (
    53. id TEXT PRIMARY KEY,
    54. shop_id TEXT NOT NULL,
    55. type TEXT NOT NULL,
    56. status TEXT NOT NULL,
    57. input_payload TEXT NOT NULL,
    58. output_payload TEXT,
    59. error TEXT,
    60. group_id TEXT,
    61. item_index INTEGER,
    62. created_at TEXT NOT NULL,
    63. updated_at TEXT NOT NULL,
    64. FOREIGN KEY (shop_id) REFERENCES shops(id)
    65. );
    67. CREATE TABLE IF NOT EXISTS entitlements (
    68. id TEXT PRIMARY KEY,
    69. shop_id TEXT NOT NULL,
    70. order_id TEXT NOT NULL,
    71. customer_id TEXT,
    72. release_id TEXT NOT NULL,
    73. token TEXT NOT NULL,
    74. expires_at TEXT NOT NULL,
    75. created_at TEXT NOT NULL,
    76. FOREIGN KEY (shop_id) REFERENCES shops(id)
    77. );
    79. CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs(status);
    80. CREATE INDEX IF NOT EXISTS idx_jobs_group ON jobs(group_id);
    81. CREATE INDEX IF NOT EXISTS idx_assets_release ON assets(release_id);
    82. CREATE INDEX IF NOT EXISTS idx_entitlements_token ON entitlements(token);