FilesExpand file tree

main

/

init-database.sql

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

194 lines (159 loc) · 7.53 KB

main

/

init-database.sql

Copy path

Top

File metadata and controls

• Code
• Blame

194 lines (159 loc) · 7.53 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

-- =============================================

-- PayNote 数据库初始化脚本

-- =============================================

-- 创建数据库（需要以超级用户身份执行）

-- 如果已连接到 postgres 数据库，执行以下命令：

CREATE DATABASE paynote;

-- 然后连接到 paynote 数据库执行以下内容

-- \c paynote

-- =============================================

-- 创建枚举类型

-- =============================================

CREATE TYPE "TransactionType" AS ENUM ('INCOME', 'EXPENSE');

CREATE TYPE "BudgetPeriod" AS ENUM ('MONTHLY', 'QUARTERLY', 'YEARLY');

-- =============================================

-- 创建表结构

-- =============================================

-- 用户表

CREATE TABLE "User" (

"id" TEXT NOT NULL,

"name" TEXT,

"email" TEXT NOT NULL,

"emailVerified" TIMESTAMP(3),

"image" TEXT,

"password" TEXT,

"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

"updatedAt" TIMESTAMP(3) NOT NULL,

CONSTRAINT "User_pkey" PRIMARY KEY ("id")

);

-- OAuth 账户表

CREATE TABLE "Account" (

"id" TEXT NOT NULL,

"userId" TEXT NOT NULL,

"type" TEXT NOT NULL,

"provider" TEXT NOT NULL,

"providerAccountId" TEXT NOT NULL,

"refresh_token" TEXT,

"access_token" TEXT,

"expires_at" INTEGER,

"token_type" TEXT,

"scope" TEXT,

"id_token" TEXT,

"session_state" TEXT,

CONSTRAINT "Account_pkey" PRIMARY KEY ("id")

);

-- 会话表

CREATE TABLE "Session" (

"id" TEXT NOT NULL,

"sessionToken" TEXT NOT NULL,

"userId" TEXT NOT NULL,

"expires" TIMESTAMP(3) NOT NULL,

CONSTRAINT "Session_pkey" PRIMARY KEY ("id")

);

-- 验证令牌表

CREATE TABLE "VerificationToken" (

"identifier" TEXT NOT NULL,

"token" TEXT NOT NULL,

"expires" TIMESTAMP(3) NOT NULL

);

-- 分类表

CREATE TABLE "Category" (

"id" TEXT NOT NULL,

"userId" TEXT,

"name" TEXT NOT NULL,

"type" "TransactionType" NOT NULL,

"icon" TEXT,

"color" TEXT,

"isSystem" BOOLEAN NOT NULL DEFAULT false,

"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT "Category_pkey" PRIMARY KEY ("id")

);

-- 交易记录表

CREATE TABLE "Transaction" (

"id" TEXT NOT NULL,

"userId" TEXT NOT NULL,

"type" "TransactionType" NOT NULL,

"amount" DECIMAL(10,2) NOT NULL,

"categoryId" TEXT,

"description" TEXT,

"date" TIMESTAMP(3) NOT NULL,

"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

"updatedAt" TIMESTAMP(3) NOT NULL,

CONSTRAINT "Transaction_pkey" PRIMARY KEY ("id")

);

-- 预算表

CREATE TABLE "Budget" (

"id" TEXT NOT NULL,

"userId" TEXT NOT NULL,

"categoryId" TEXT,

"amount" DECIMAL(10,2) NOT NULL,

"period" "BudgetPeriod" NOT NULL,

"startDate" TIMESTAMP(3) NOT NULL,

"endDate" TIMESTAMP(3) NOT NULL,

"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

"updatedAt" TIMESTAMP(3) NOT NULL,

CONSTRAINT "Budget_pkey" PRIMARY KEY ("id")

);

-- =============================================

-- 创建唯一索引

-- =============================================

CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

CREATE UNIQUE INDEX "Account_provider_providerAccountId_key" ON "Account"("provider", "providerAccountId");

CREATE UNIQUE INDEX "Session_sessionToken_key" ON "Session"("sessionToken");

CREATE UNIQUE INDEX "VerificationToken_token_key" ON "VerificationToken"("token");

CREATE UNIQUE INDEX "VerificationToken_identifier_token_key" ON "VerificationToken"("identifier", "token");

CREATE UNIQUE INDEX "Category_userId_name_type_key" ON "Category"("userId", "name", "type");

-- =============================================

-- 创建普通索引

-- =============================================

CREATE INDEX "Category_userId_type_idx" ON "Category"("userId", "type");

CREATE INDEX "Transaction_userId_date_idx" ON "Transaction"("userId", "date");

CREATE INDEX "Transaction_userId_type_idx" ON "Transaction"("userId", "type");

CREATE INDEX "Transaction_userId_categoryId_idx" ON "Transaction"("userId", "categoryId");

CREATE INDEX "Budget_userId_startDate_endDate_idx" ON "Budget"("userId", "startDate", "endDate");

CREATE INDEX "Budget_userId_categoryId_idx" ON "Budget"("userId", "categoryId");

-- =============================================

-- 创建外键约束

-- =============================================

ALTER TABLE "Account" ADD CONSTRAINT "Account_userId_fkey"

FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Session" ADD CONSTRAINT "Session_userId_fkey"

FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Category" ADD CONSTRAINT "Category_userId_fkey"

FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Transaction" ADD CONSTRAINT "Transaction_userId_fkey"

FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Transaction" ADD CONSTRAINT "Transaction_categoryId_fkey"

FOREIGN KEY ("categoryId") REFERENCES "Category"("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "Budget" ADD CONSTRAINT "Budget_userId_fkey"

FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Budget" ADD CONSTRAINT "Budget_categoryId_fkey"

FOREIGN KEY ("categoryId") REFERENCES "Category"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- =============================================

-- 插入系统预设分类（种子数据）

-- =============================================

-- 支出分类

INSERT INTO "Category" ("id", "userId", "name", "type", "icon", "color", "isSystem", "createdAt") VALUES

('cat_expense_food', NULL, '餐饮', 'EXPENSE', '🍽️', '#ef4444', true, CURRENT_TIMESTAMP),

('cat_expense_transport', NULL, '交通', 'EXPENSE', '🚗', '#f97316', true, CURRENT_TIMESTAMP),

('cat_expense_shopping', NULL, '购物', 'EXPENSE', '🛒', '#eab308', true, CURRENT_TIMESTAMP),

('cat_expense_entertainment', NULL, '娱乐', 'EXPENSE', '🎮', '#22c55e', true, CURRENT_TIMESTAMP),

('cat_expense_housing', NULL, '居住', 'EXPENSE', '🏠', '#14b8a6', true, CURRENT_TIMESTAMP),

('cat_expense_communication', NULL, '通讯', 'EXPENSE', '📱', '#06b6d4', true, CURRENT_TIMESTAMP),

('cat_expense_medical', NULL, '医疗', 'EXPENSE', '🏥', '#3b82f6', true, CURRENT_TIMESTAMP),

('cat_expense_education', NULL, '教育', 'EXPENSE', '📚', '#8b5cf6', true, CURRENT_TIMESTAMP),

('cat_expense_clothing', NULL, '服饰', 'EXPENSE', '👔', '#d946ef', true, CURRENT_TIMESTAMP),

('cat_expense_other', NULL, '其他支出', 'EXPENSE', '📦', '#6b7280', true, CURRENT_TIMESTAMP);

-- 收入分类

INSERT INTO "Category" ("id", "userId", "name", "type", "icon", "color", "isSystem", "createdAt") VALUES

('cat_income_salary', NULL, '工资', 'INCOME', '💰', '#22c55e', true, CURRENT_TIMESTAMP),

('cat_income_bonus', NULL, '奖金', 'INCOME', '🎁', '#14b8a6', true, CURRENT_TIMESTAMP),

('cat_income_investment', NULL, '投资', 'INCOME', '📈', '#3b82f6', true, CURRENT_TIMESTAMP),

('cat_income_parttime', NULL, '兼职', 'INCOME', '💼', '#8b5cf6', true, CURRENT_TIMESTAMP),

('cat_income_redpacket', NULL, '红包', 'INCOME', '🧧', '#ef4444', true, CURRENT_TIMESTAMP),

('cat_income_other', NULL, '其他收入', 'INCOME', '💵', '#6b7280', true, CURRENT_TIMESTAMP);

-- =============================================

-- 完成

-- =============================================

-- 验证表创建成功

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';