DataOps-platform/add_origin_source_field.sql

-- ===============================================
-- 修改business_cards表，新增origin_source字段
-- 执行日期: 2024年（请在执行前填写实际日期）
-- 修改说明: 为名片表新增原始资料记录字段，采用JSON格式保存原始资料信息
-- ===============================================

-- 1. 新增origin_source字段 (原始资料记录字段)
ALTER TABLE business_cards 
ADD COLUMN origin_source JSON;

-- 2. 为新增字段添加注释
COMMENT ON COLUMN business_cards.origin_source IS '原始资料记录字段 - 采用JSON格式保存原始资料信息，包括数据来源、MinIO路径等';

-- 3. 验证字段是否成功添加
SELECT 
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns 
WHERE table_name = 'business_cards' 
    AND column_name = 'origin_source'
ORDER BY column_name;

-- 4. 查看字段注释
SELECT 
    a.attname AS column_name,
    format_type(a.atttypid, a.atttypmod) AS data_type,
    COALESCE(pg_catalog.col_description(a.attrelid, a.attnum), '无注释') AS description
FROM 
    pg_catalog.pg_attribute a
JOIN 
    pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN 
    pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE 
    c.relname = 'business_cards' 
    AND a.attname = 'origin_source'
    AND a.attnum > 0 
    AND NOT a.attisdropped
ORDER BY a.attname;

-- ===============================================
-- 执行说明：
-- 1. 请在生产环境执行前先在测试环境验证
-- 2. 建议在业务低峰期执行此脚本
-- 3. 执行前请备份相关数据
-- 4. 新增字段允许NULL值，不会影响现有数据
-- 5. origin_source字段用于存储原始资料的JSON数据
-- ===============================================

-- 可选：示例数据格式说明
-- origin_source字段的JSON格式示例：
-- {
--   "type": "webpage_talent",
--   "minio_path": "webpage_talent/webpage_talent_20240101_12345.md",
--   "source_date": "2024-01-01 12:00:00",
--   "talent_data": {...},
--   "web_md_content": "部分网页内容..."
-- }

-- 执行完成后检查表结构
\d business_cards;