SQL

CREATE TABLE item_submissions  (
  id              TEXT PRIMARY KEY,
  item_id         TEXT NOT NULL REFERENCES assignment_items(id) ON DELETE CASCADE,
  assignment_id   TEXT NOT NULL,
  child_id        TEXT NOT NULL,
  content         TEXT DEFAULT '',
  media_urls      TEXT DEFAULT '[]',
  -- JSON数组: 提交图片/语音URL列表
    ocr_text        TEXT DEFAULT '',
  ai_comment      TEXT DEFAULT '',
  score           INTEGER,
  total_score     INTEGER DEFAULT 100,
  ai_pipeline     TEXT CHECK(ai_pipeline IN
                      ('ocr_correction','copy_check','recite_check','dictation_check','manual')),
  status          TEXT DEFAULT 'submitted'
                      CHECK(status IN ('submitted','corrected')),
  corrected_at    DATETIME,
  created_at      DATETIME DEFAULT (datetime('now')),
  updated_at      DATETIME DEFAULT (datetime('now'))
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id TEXT ✓ ✓ Rename | Drop
item_id TEXT Rename | Drop
assignment_id TEXT Rename | Drop
child_id TEXT Rename | Drop
content TEXT ✓ Rename | Drop
media_urls TEXT ✓ Rename | Drop
ocr_text TEXT ✓ Rename | Drop
ai_comment TEXT ✓ Rename | Drop
score INTEGER ✓ Rename | Drop
total_score INTEGER ✓ Rename | Drop
ai_pipeline TEXT ✓ Rename | Drop
status TEXT ✓ Rename | Drop
corrected_at DATETIME ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop
updated_at DATETIME ✓ Rename | Drop

Foreign Keys

Column Destination
item_id assignment_items.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_sub_child child_id SQL 
CREATE INDEX idx_sub_child
ON item_submissions(child_id)
Drop
idx_sub_item item_id SQL 
CREATE INDEX idx_sub_item
ON item_submissions(item_id)
Drop
idx_sub_status status SQL 
CREATE INDEX idx_sub_status
ON item_submissions(status)
Drop
sqlite_autoindex_item_submissions_1 id ✓ SQL
-- no sql found --
 Drop