supabase-schema.sql

-- Supabase 数据库表创建脚本
-- 在 Supabase 控制台的 SQL Editor 中执行

-- 创建语音记录表
CREATE TABLE IF NOT EXISTS voices (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    audio_name TEXT NOT NULL,
    audio_size INTEGER NOT NULL,
    audio_url TEXT,
    recognition_result TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);

-- 启用行级安全（RLS）
ALTER TABLE voices ENABLE ROW LEVEL SECURITY;

-- 创建策略：允许所有人读取和插入（生产环境建议限制）
CREATE POLICY "Allow public read access" ON voices
    FOR SELECT USING (true);

CREATE POLICY "Allow authenticated inserts" ON voices
    FOR INSERT WITH CHECK (true);

CREATE POLICY "Allow owners to update" ON voices
    FOR UPDATE USING (auth.uid() = id);

-- 创建存储桶用于音频文件
INSERT INTO storage.buckets (id, name, public)
VALUES ('audio-recordings', 'audio-recordings', true)
ON CONFLICT (id) DO NOTHING;

-- 设置存储策略
CREATE POLICY "Allow public access to audio files" ON storage.objects
    FOR SELECT USING ( bucket_id = 'audio-recordings' );

CREATE POLICY "Allow authenticated uploads" ON storage.objects
    FOR INSERT WITH CHECK ( bucket_id = 'audio-recordings' AND auth.role() = 'authenticated' );

-- 启用实时订阅（可选）
ALTER PUBLICATION supabase_realtime ADD TABLE voices;

-- 创建索引优化查询性能
CREATE INDEX idx_voices_created_at ON voices(created_at DESC);

-- 查看创建结果
SELECT * FROM voices;