旅行足迹App技术架构全解析

旅行足迹移动应用技术架构文档

1. Architecture design

2. Technology Description

• 前端: React Native@0.72 + TypeScript + React Navigation@6 + React Native Maps + Lottie React Native

• 后端: Node.js + Express.js + TypeScript

• 数据库: Supabase (PostgreSQL)

• 云存储: Supabase Storage

• 地图服务: 百度地图React Native SDK

• AI服务: OpenAI API / 百度文心一言API

• 音乐服务: 网易云音乐API / QQ音乐API

• 动画: Lottie + React Native Reanimated

• 状态管理: Redux Toolkit + RTK Query

• 本地存储: AsyncStorage + MMKV

3. Route definitions

4. API definitions

4.1 Core API

用户认证相关

POST /api/auth/login

Request:

Response:

足迹管理相关

GET /api/footprints
POST /api/footprints
PUT /api/footprints/:id
DELETE /api/footprints/:id

多媒体文件相关

POST /api/media/upload
GET /api/media/:id
DELETE /api/media/:id

AI对话相关

POST /api/chat/message
GET /api/chat/history
POST /api/chat/analyze-emotion

游戏相关

POST /api/game/lottery
GET /api/game/destinations

5. Server architecture diagram

6. Data model

6.1 Data model definition

6.2 Data Definition Language

用户表 (users)

-- 创建用户表
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    nickname VARCHAR(100) NOT NULL,
    avatar_url TEXT,
    phone VARCHAR(20),
    user_type VARCHAR(20) DEFAULT 'normal' CHECK (user_type IN ('normal', 'premium', 'admin')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at DESC);

-- 设置权限
GRANT SELECT ON users TO anon;
GRANT ALL PRIVILEGES ON users TO authenticated;

足迹表 (footprints)

-- 创建足迹表
CREATE TABLE footprints (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    address TEXT,
    visit_time TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建索引
CREATE INDEX idx_footprints_user_id ON footprints(user_id);
CREATE INDEX idx_footprints_location ON footprints(latitude, longitude);
CREATE INDEX idx_footprints_visit_time ON footprints(visit_time DESC);

-- 设置权限
GRANT SELECT ON footprints TO anon;
GRANT ALL PRIVILEGES ON footprints TO authenticated;

多媒体文件表 (media_files)

-- 创建多媒体文件表
CREATE TABLE media_files (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    footprint_id UUID,
    file_name VARCHAR(255) NOT NULL,
    file_url TEXT NOT NULL,
    file_type VARCHAR(50) NOT NULL CHECK (file_type IN ('image', 'video', 'audio')),
    file_size INTEGER,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建索引
CREATE INDEX idx_media_files_user_id ON media_files(user_id);
CREATE INDEX idx_media_files_footprint_id ON media_files(footprint_id);
CREATE INDEX idx_media_files_type ON media_files(file_type);

-- 设置权限
GRANT SELECT ON media_files TO anon;
GRANT ALL PRIVILEGES ON media_files TO authenticated;

聊天消息表 (chat_messages)

-- 创建聊天消息表
CREATE TABLE chat_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    message TEXT NOT NULL,
    message_type VARCHAR(20) DEFAULT 'user' CHECK (message_type IN ('user', 'ai')),
    emotion_analysis JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建索引
CREATE INDEX idx_chat_messages_user_id ON chat_messages(user_id);
CREATE INDEX idx_chat_messages_created_at ON chat_messages(created_at DESC);

-- 设置权限
GRANT SELECT ON chat_messages TO anon;
GRANT ALL PRIVILEGES ON chat_messages TO authenticated;

播放列表表 (playlists)

-- 创建播放列表表
CREATE TABLE playlists (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    cover_url TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建播放列表歌曲表
CREATE TABLE playlist_songs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    playlist_id UUID NOT NULL,
    song_id VARCHAR(100) NOT NULL,
    song_name VARCHAR(200) NOT NULL,
    artist VARCHAR(200),
    album VARCHAR(200),
    cover_url TEXT,
    duration INTEGER,
    sort_order INTEGER DEFAULT 0,
    added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建索引
CREATE INDEX idx_playlists_user_id ON playlists(user_id);
CREATE INDEX idx_playlist_songs_playlist_id ON playlist_songs(playlist_id);
CREATE INDEX idx_playlist_songs_sort_order ON playlist_songs(playlist_id, sort_order);

-- 设置权限
GRANT SELECT ON playlists TO anon;
GRANT ALL PRIVILEGES ON playlists TO authenticated;
GRANT SELECT ON playlist_songs TO anon;
GRANT ALL PRIVILEGES ON playlist_songs TO authenticated;

初始化数据

-- 插入测试用户
INSERT INTO users (email, password_hash, nickname, user_type) VALUES
('test@example.com', '$2b$10$example_hash', '测试用户', 'normal'),
('admin@example.com', '$2b$10$example_hash', '管理员', 'admin');

-- 插入示例足迹
INSERT INTO footprints (user_id, title, description, latitude, longitude, address, visit_time) VALUES
((SELECT id FROM users WHERE email = 'test@example.com'), '天安门广场', '北京的心脏地带', 39.9042, 116.4074, '北京市东城区天安门广场', NOW() - INTERVAL '7 days');