forked from uengine-oss/process-gpt
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathinit.sql
More file actions
2747 lines (2496 loc) · 92.9 KB
/
init.sql
File metadata and controls
2747 lines (2496 loc) · 92.9 KB
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
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
-- ===============================================
-- INIT.SQL FILE WRITING GUIDE
-- ===============================================
--
-- 이 파일은 데이터베이스 초기화를 위한 SQL 스크립트입니다.
-- 새로운 데이터베이스 환경에서 처음 실행되는 파일입니다.
-- 다른 개발자들이 안전하게 수정할 수 있도록 다음 가이드를 따라주세요.
--
-- 파일 역할:
-- - init.sql: 새로운 테이블 생성 (CREATE TABLE)
-- - migration.sql: 기존 테이블 구조 변경 (ALTER TABLE)
--
-- 1. 확장 기능 (Extensions):
-- - 필요한 확장 기능을 최상단에 추가
-- - CREATE EXTENSION IF NOT EXISTS 사용
-- - vector, pgcrypto 등 필수 확장 기능 포함
--
-- 2. 함수 정의:
-- - 테이블 생성 전에 필요한 함수들을 먼저 정의
-- - 테넌트별 정보가 필요한 경우 public.tenant_id() 함수 사용
-- - CREATE OR REPLACE FUNCTION 사용
--
-- 3. 테이블 생성 규칙:
-- - CREATE TABLE IF NOT EXISTS 사용
-- - 모든 테이블에 적절한 제약조건 설정
-- - Primary Key, Foreign Key 명시적 정의
-- - 테넌트별 데이터는 tenant_id 컬럼 추가
--
-- 4. 인덱스 생성:
-- - 테이블 생성 후 관련 인덱스 추가
-- - CREATE INDEX IF NOT EXISTS 사용
-- - 유니크 인덱스는 테넌트별로 설정
--
-- 5. 뷰 생성:
-- - 복잡한 조인이나 자주 사용되는 쿼리는 뷰로 생성
-- - CREATE OR REPLACE VIEW 사용
--
-- 6. 함수 및 트리거:
-- - 비즈니스 로직 함수 정의
-- - 트리거 함수와 트리거 생성
-- - 보안 관련 함수 포함
--
-- 7. Row Level Security (RLS):
-- - 모든 테이블에 RLS 활성화
-- - 적절한 정책(Policy) 정의
-- - 인증된 사용자와 관리자 권한 구분
--
-- 8. 실시간 구독 설정:
-- - supabase_realtime publication에 테이블 추가
-- - 실시간 업데이트가 필요한 테이블만 포함
--
-- 9. ENUM 타입:
-- - 필요에 따라 상태값 등은 ENUM 타입으로 정의
-- - 기존 데이터 마이그레이션 로직 포함
--
-- ===============================================
-- Enable required extensions
create extension if not exists vector;
create extension if not exists pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_cron;
-- ==========================================
-- ENUM 타입 정의
-- ==========================================
-- 프로세스 인스턴스 상태 enum
CREATE TYPE process_status AS ENUM ('NEW', 'RUNNING', 'COMPLETED');
-- 할일 항목 상태 enum
CREATE TYPE todo_status AS ENUM ('NEW', 'TODO', 'IN_PROGRESS', 'SUBMITTED', 'PENDING', 'DONE', 'CANCELLED');
-- 에이전트 모드 enum
CREATE TYPE agent_mode AS ENUM ('DRAFT', 'COMPLETE');
-- 오케스트레이션 방식 enum
CREATE TYPE agent_orch AS ENUM ('crewai-action', 'openai-deep-research', 'crewai-deep-research', 'langchain-react', 'browser-automation-agent', 'a2a', 'visionparse');
-- 드래프트 상태 enum
CREATE TYPE draft_status AS ENUM ('STARTED', 'CANCELLED', 'COMPLETED', 'FB_REQUESTED', 'HUMAN_ASKED', 'FAILED');
-- 이벤트 타입 enum
CREATE TYPE event_type_enum AS ENUM (
'task_started',
'task_completed',
'tool_usage_started',
'tool_usage_finished',
'crew_completed',
'human_asked',
'human_response',
'error'
);
-- 이벤트 상태 enum
CREATE TYPE event_status AS ENUM ('ASKED', 'APPROVED', 'REJECTED');
-- Create tenant_id function
create or replace function public.tenant_id()
returns text
language sql stable
as $$
select
nullif(
((current_setting('request.jwt.claims')::jsonb ->> 'app_metadata')::jsonb ->> 'tenant_id'),
''
)::text
$$;
-- Create initial tables
create table if not exists public.tenants (
id text not null,
owner uuid null default auth.uid (),
is_deleted boolean not null default false,
deleted_at timestamp with time zone null,
mcp jsonb null,
constraint tenants_pkey primary key (id)
) tablespace pg_default;
INSERT INTO public.tenants (id, owner) VALUES ('process-gpt', null);
create table if not exists public.user_devices (
user_email text not null,
device_token text null,
access_page text null,
last_access_at timestamp with time zone null default now(),
constraint user_devices_pkey primary key (user_email)
) tablespace pg_default;
create table if not exists public.users (
id uuid not null,
username text null,
profile text null default '/images/defaultUser.png'::text,
email text null,
is_admin boolean not null default false,
role text null,
tenant_id text null,
device_token text null,
goal text null,
persona text null,
endpoint text null,
description text null,
tools text null,
skills text null,
is_agent boolean not null default false,
model text null,
constraint users_pkey primary key (id, tenant_id),
constraint users_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
create table if not exists public.configuration (
key text not null,
value jsonb null,
tenant_id text null default public.tenant_id(),
uuid uuid not null default gen_random_uuid (),
constraint configuration_pkey primary key (uuid),
constraint configuration_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
create table if not exists public.proc_map_history (
value jsonb not null,
created_at timestamp with time zone not null default now(),
tenant_id text null default public.tenant_id(),
uuid uuid not null default gen_random_uuid (),
constraint proc_map_history_pkey primary key (uuid),
constraint proc_map_history_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
create table if not exists public.proc_def (
id text not null,
name text null,
definition jsonb null,
bpmn text null,
uuid uuid not null default gen_random_uuid (),
tenant_id text null default public.tenant_id(),
isdeleted boolean not null default false,
owner text null,
type text null,
constraint proc_def_pkey primary key (uuid),
constraint proc_def_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
create table if not exists public.proc_def_arcv (
arcv_id text not null,
proc_def_id text not null,
version text not null,
snapshot text null,
"timeStamp" timestamp without time zone null default current_timestamp,
diff text null,
message text null,
uuid uuid not null default gen_random_uuid (),
tenant_id text null default public.tenant_id(),
constraint proc_def_arcv_pkey primary key (uuid),
constraint proc_def_arcv_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
create table if not exists public.proc_def_version (
arcv_id text not null,
proc_def_id text not null,
version text not null,
version_tag text null,
snapshot text null,
definition jsonb null,
"timeStamp" timestamp without time zone null default current_timestamp,
diff text null,
message text null,
uuid uuid not null default gen_random_uuid (),
tenant_id text null default public.tenant_id(),
constraint proc_def_version_pkey primary key (uuid),
constraint proc_def_version_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
create table if not exists public.form_def (
uuid uuid not null default gen_random_uuid (),
html text not null,
proc_def_id text not null,
activity_id text not null,
tenant_id text null default public.tenant_id(),
id text null default ''::text,
fields_json jsonb null,
constraint form_def_pkey primary key (uuid),
constraint form_def_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
create table if not exists public.notifications (
id uuid not null,
title text null,
type text null,
description text null,
is_checked boolean null default false,
time_stamp timestamp with time zone null default now(),
user_id text null,
url text null,
consumer text null,
from_user_id text null,
tenant_id text null default public.tenant_id(),
constraint notifications_pkey primary key (id),
constraint notifications_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
create table if not exists public.lock (
id text not null,
user_id text null,
tenant_id text null default public.tenant_id(),
uuid uuid not null default gen_random_uuid (),
constraint lock_pkey primary key (uuid),
constraint lock_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade,
constraint lock_tenant_id_id_unique unique (tenant_id, id)
) tablespace pg_default;
create table if not exists public.bpm_proc_inst (
proc_def_id text null,
proc_inst_id text not null,
proc_inst_name text null,
root_proc_inst_id text null,
parent_proc_inst_id text null,
execution_scope text null,
current_activity_ids text[] null,
participants text[] null,
role_bindings jsonb null,
variables_data jsonb null,
status process_status null,
tenant_id text null default public.tenant_id(),
proc_def_version text null,
version_tag text null,
version text null,
project_id uuid null,
start_date timestamp without time zone null,
end_date timestamp without time zone null,
due_date timestamp without time zone null,
updated_at timestamp with time zone default now(),
is_deleted boolean not null default false,
deleted_at timestamp with time zone null,
is_clean_up boolean not null default false,
constraint bpm_proc_inst_pkey primary key (proc_inst_id),
constraint bpm_proc_inst_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
create table if not exists public.todolist (
id uuid not null,
user_id text null,
username text null,
proc_inst_id text null,
root_proc_inst_id text null,
execution_scope text null,
proc_def_id text null,
version_tag text null,
version text null,
activity_id text null,
activity_name text null,
start_date timestamp without time zone null,
end_date timestamp without time zone null,
status todo_status null,
description text null,
tool text null,
due_date timestamp without time zone null,
tenant_id text null default public.tenant_id(),
reference_ids text[] null,
adhoc boolean null default false,
assignees jsonb null,
duration integer null,
output jsonb null,
retry integer null default 0,
consumer text null,
log text null,
project_id uuid null,
draft jsonb null,
agent_mode agent_mode null,
agent_orch agent_orch null,
feedback jsonb null,
draft_status draft_status null,
updated_at timestamp with time zone default now(),
temp_feedback text null,
output_url text null,
rework_count integer null default 0,
query text null,
constraint todolist_pkey primary key (id),
constraint todolist_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
create table if not exists public.chat_rooms (
id text not null,
participants jsonb not null,
message jsonb null,
name text null,
tenant_id text null default public.tenant_id(),
constraint chat_rooms_pkey primary key (id),
constraint chat_rooms_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
create table if not exists public.chats (
uuid text not null,
id text not null,
messages jsonb null,
tenant_id text null default public.tenant_id(),
thread_id text null,
constraint chats_pkey primary key (uuid),
constraint chats_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
create table if not exists public.chat_attachments (
id text not null,
file_name text null,
file_path text null,
chat_room_id text null,
user_name text null,
created_at timestamp with time zone not null default now(),
tenant_id text null,
constraint chat_attachments_pkey primary key (id),
constraint chat_attachments_chat_room_id_fkey foreign KEY (chat_room_id) references chat_rooms (id) on update CASCADE on delete CASCADE,
constraint chat_attachments_tenant_id_fkey foreign KEY (tenant_id) references tenants (id)
) TABLESPACE pg_default;
create table if not exists public.calendar (
uid text not null,
data jsonb null,
tenant_id text null default public.tenant_id(),
constraint calendar_pkey primary key (uid)
) tablespace pg_default;
create table if not exists public.user_permissions (
id text not null,
user_id uuid not null,
tenant_id text not null default public.tenant_id(),
proc_def_id text not null,
proc_def_ids jsonb not null,
readable boolean not null default false,
writable boolean not null default false,
constraint user_permissions_pkey primary key (id)
) tablespace pg_default;
create table if not exists public.proc_def_marketplace (
uuid uuid not null default gen_random_uuid (),
id text not null,
name text null,
definition jsonb null,
bpmn text null,
description text null,
category text null,
tags text null,
author_name text null,
author_uid text null,
image text null,
import_count integer not null default 0,
constraint proc_def_marketplace_pkey primary key (uuid)
) tablespace pg_default;
create table if not exists public.form_def_marketplace (
uuid uuid not null default gen_random_uuid (),
id text null default ''::text,
proc_def_id text not null,
activity_id text not null,
html text not null,
author_uid text null,
constraint form_def_marketplace_pkey primary key (uuid)
) tablespace pg_default;
create table public.tenant_oauth (
tenant_id text not null,
client_id text not null,
client_secret text not null,
redirect_uri text null,
drive_folder_id text null,
created_at timestamp with time zone null default now(),
updated_at timestamp with time zone null default now(),
google_credentials jsonb null,
google_credentials_updated_at timestamp with time zone null,
constraint tenant_oauth_pkey primary key (tenant_id),
constraint tenant_oauth_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
create table if not exists public.project (
name character varying null,
start_date date null,
end_date date null,
created_date date null,
status character varying not null,
project_id uuid not null default gen_random_uuid (),
due_date date null,
user_id text null,
updated_at timestamp with time zone default now(),
tenant_id text null default public.tenant_id(),
constraint project_pkey primary key (project_id),
constraint project_tenant_id_fkey foreign key (tenant_id) references tenants (id) on update cascade on delete cascade
) tablespace pg_default;
-- create table if not exists public.milestone (
-- id bigserial,
-- impact_type character varying null,
-- impact_desc text null,
-- created_date date null,
-- task_id uuid null,
-- impact_id uuid null,
-- constraint milestone_pkey primary key (id),
-- constraint fk_milestone_task foreign key (task_id) references worklist (task_id),
-- constraint fk_milestone_impact_id foreign key (impact_id) references worklist (task_id),
-- constraint fk_milestone_impact foreign key (impact_id) references worklist (task_id)
-- ) tablespace pg_default;
create table if not exists public.task_dependency (
id bigserial,
lag_time integer null,
lead_time integer null,
type character varying null,
created_date date null,
task_id uuid null,
depends_id uuid null,
constraint worklist_dependency_pkey primary key (id)
) tablespace pg_default;
create table if not exists public.processed_files (
id uuid not null default uuid_generate_v4 (),
file_id text not null,
tenant_id text not null,
processed_at timestamp with time zone null default now(),
file_name text null,
constraint processed_files_pkey primary key (id),
constraint processed_files_file_id_tenant_id_key unique (file_id, tenant_id)
) tablespace pg_default;
create table if not exists public.documents (
id uuid primary key,
content text,
metadata jsonb,
embedding vector(1536)
);
create table if not exists public.events (
id text not null,
job_id text not null,
todo_id text null,
proc_inst_id text null,
event_type event_type_enum not null,
status event_status null,
crew_type text null,
data jsonb not null,
timestamp timestamp with time zone null default now(),
constraint events_pkey primary key (id)
) TABLESPACE pg_default;
create or replace function match_documents(
query_embedding vector(1536),
filter jsonb default '{}'::jsonb,
match_count int default 5
)
returns table (
id uuid,
content text,
metadata jsonb,
similarity float
)
language sql
as $$
select
documents.id,
documents.content,
documents.metadata,
1 - (documents.embedding <=> query_embedding) as similarity
from documents
where documents.metadata @> filter
order by documents.embedding <=> query_embedding
limit match_count;
$$;
------------------ 결제시스템 ---------------------------
-- payment(결제 이력)
CREATE TABLE public.payment (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- 고유ID
payment_key TEXT, -- 결제 KEY(PG 관리)
order_id TEXT UNIQUE, -- 주문 ID(난수)
order_name TEXT, -- 사용자 표시용 상품명
status TEXT DEFAULT 'READY' CHECK (
status IN (
'READY', -- 생성 직후
'IN_PROGRESS', -- 인증 완료 (승인 전)
'AUTH_FAILED', -- 인증 실패
'DONE', -- 결제 승인 완료
'CANCELED', -- 전체 취소
'PARTIAL_CANCELED', -- 부분 취소
'ABORTED', -- 승인 실패
'EXPIRED', -- 유효시간 만료
'WAITING_FOR_DEPOSIT' -- 가상계좌 대기
)
),
receipt_url TEXT, -- PG 영수증 링크
amount DECIMAL(10,2) NOT NULL, -- 결제 금액
approved_at TIMESTAMPTZ, -- 결제 완료 시간
method TEXT, -- 카드, 가상계좌 등
user_id TEXT, -- 결제자
created_at TIMESTAMPTZ DEFAULT now(), -- 생성 날짜
ref_type TEXT, -- 상품 타입(subscription, credit)
ref_id TEXT, -- 상품 ID(subscription.id, credit.id)
tenant_id TEXT REFERENCES public.tenants(id) -- 테넌트
);
-- service(개별 서비스 식별)
CREATE TABLE public.service (
id TEXT NOT NULL, -- 서비스 ID
name TEXT, -- 서비스 이름
created_at TIMESTAMPTZ DEFAULT NOW(), -- 생성일
tenant_id TEXT REFERENCES public.tenants(id), -- 테넌트
CONSTRAINT service_pkey PRIMARY KEY (id, tenant_id)
);
-- service_rate(각 서비스별 과금 단위·크레딧 정의)
CREATE TABLE IF NOT EXISTS public.service_rate (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 고유 ID
service_id text NOT NULL, -- 서비스 ID
tenant_id text NOT NULL, -- 테넌트
model text NOT NULL, -- 모델명
available_from TIMESTAMPTZ NOT NULL DEFAULT now(), -- 적용 시점
created_at TIMESTAMPTZ DEFAULT now(), -- 생성일
dimension jsonb NOT NULL DEFAULT '{}'::jsonb, -- 가격 및 unit 정보
CONSTRAINT unique_service_dimension
UNIQUE (service_id, tenant_id, model, available_from),
CONSTRAINT service_rate_tenant_id_fkey
FOREIGN KEY (tenant_id) REFERENCES public.tenants (id),
-- 핵심: service(id, tenant_id) 복합키 참조 + 서비스 삭제 시 함께 삭제
CONSTRAINT service_rate_service_tenant_fk
FOREIGN KEY (service_id, tenant_id)
REFERENCES public.service (id, tenant_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_service_rate_service_tenant ON public.service_rate (service_id, tenant_id);
-- usage(사용량)
CREATE TABLE public.usage (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY, -- 사용량 ID
tenant_id TEXT NOT NULL REFERENCES public.tenants(id), -- 테넌트
quantity DECIMAL(12,4) NOT NULL, -- 사용 양(토큰 및 호출수..)
amount DECIMAL(12,7), -- 트리거: 크레딧 합계
metadata JSONB, -- 계산용 데이터
service_rate_id UUID REFERENCES public.service_rate(id), -- 트리거: 해당 시점의 가격
group_id UUID, -- 연결된 사용량 ID
model TEXT, -- 사용 모델
service_id TEXT, -- 서비스 ID (LLM, RAG 등)
user_id TEXT, -- 사용자
agent_id TEXT, -- agent ID
process_def_id TEXT, -- 프로세스 정의 ID
process_inst_id TEXT, -- 프로세스 인스턴스 ID
usage_start_at TIMESTAMPTZ NOT NULL, -- 사용 시작
usage_end_at TIMESTAMPTZ DEFAULT NOW(), -- 사용 종료(자동 생성)
CONSTRAINT usage_service_fk
FOREIGN KEY (service_id, tenant_id)
REFERENCES public.service (id, tenant_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
CREATE INDEX idx_usage_service_id ON public.usage(service_id);
CREATE INDEX idx_usage_process_def_id ON public.usage(process_def_id);
CREATE INDEX idx_usage_process_inst_id ON public.usage(process_inst_id);
CREATE INDEX idx_usage_tenant_master_date ON public.usage (tenant_id, service_id, usage_start_at);
-- credit(크레딧 정의)
CREATE TABLE public.credit (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- 크레딧 ID
name TEXT NOT NULL, -- 크레딧 명
description TEXT, -- 크레딧 설명
type TEXT NOT NULL, -- 크레딧 타입
price DECIMAL(10,2) NOT NULL DEFAULT 0, -- 결제 금액
credit DECIMAL(12,3) NOT NULL DEFAULT 0, -- 제공 크레딧
badge JSONB NOT NULL DEFAULT '{}'::jsonb, -- 크레딧 특징
status TEXT NOT NULL DEFAULT 'active' -- 크레딧 상태
CHECK (status IN ('active', 'inactive', 'hidden')),
created_at TIMESTAMPTZ DEFAULT NOW(), -- 생성 날짜
validity_months INT DEFAULT 12, -- 크레딧 만료 개월(기본 12개월)
tenant_id TEXT REFERENCES public.tenants(id) -- 테넌트
);
-- credit_purchase(테넌트의 '충전 크래딧' 구매이력)
CREATE TABLE public.credit_purchase (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY, -- 크레딧 구매ID
tenant_id TEXT NOT NULL REFERENCES public.tenants(id), -- 테넌트
added_credit DECIMAL(12,7) NOT NULL, -- 추가된 크레딧
source_type TEXT NOT NULL -- 충전 방식 구분
CHECK (source_type IN ('purchase'))
DEFAULT 'purchase',
source_id TEXT NOT NULL, -- 충전 ID (credit.id 또는 promo code)
payment_id UUID REFERENCES public.payment(id), -- 결제 ID 정보(구매자 추적용)
expires_at TIMESTAMPTZ , -- 만료일(생성일 기준 + validity_months)
created_at TIMESTAMPTZ DEFAULT NOW(), -- 생성일(자동생성)
CONSTRAINT added_credit_ch CHECK (added_credit >= 0)
);
-- credit_usage(크레딧 차감 이력 테이블)
CREATE TABLE public.credit_usage (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY, -- 크레딧 이력 ID
tenant_id TEXT NOT NULL REFERENCES public.tenants(id), -- 테넌트ID
usage_id UUID NOT NULL REFERENCES public.usage(id), -- 사용량 ID
credit_purchase_id UUID REFERENCES public.credit_purchase(id), -- 연결된 구매 크레딧 ID
used_credit DECIMAL(12,7) NOT NULL, -- 실제로 이만큼 소진
created_at TIMESTAMPTZ DEFAULT NOW(), -- 생성 날짜
CONSTRAINT used_credit_ch CHECK (used_credit >= 0)
);
-- Create indexes
create index if not exists idx_processed_files_tenant_id on public.processed_files using btree (tenant_id) tablespace pg_default;
create index if not exists idx_processed_files_file_id on public.processed_files using btree (file_id) tablespace pg_default;
CREATE UNIQUE INDEX IF NOT EXISTS unique_proc_def_id_per_tenant ON proc_def (id, tenant_id);
CREATE UNIQUE INDEX IF NOT EXISTS unique_form_def_id_per_tenant ON form_def (id, tenant_id);
CREATE UNIQUE INDEX IF NOT EXISTS unique_config_key_per_tenant ON configuration (key, tenant_id);
-- Create views
CREATE OR REPLACE VIEW public.chat_room_chats AS
SELECT
cr.id,
cr.name,
cr.participants,
c.uuid,
c.messages
FROM
chat_rooms cr
JOIN chats c ON cr.id = c.id;
CREATE OR REPLACE VIEW public.v_task_dependency AS
SELECT
d.id,
d.lag_time,
d.lead_time,
d.type,
d.created_date,
d.task_id,
d.depends_id,
t.proc_inst_id,
t.project_id
FROM
task_dependency d
JOIN todolist t ON d.task_id = t.id;
-- Create functions
CREATE OR REPLACE FUNCTION set_user_permissions_id()
RETURNS TRIGGER AS $$
BEGIN
NEW.id := NEW.proc_def_id || '_' || NEW.user_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION check_process_permission(p_proc_def_id TEXT, p_user_id UUID DEFAULT NULL)
RETURNS SETOF user_permissions AS $$
BEGIN
RETURN QUERY
SELECT up.*
FROM user_permissions up,
jsonb_array_elements(up.proc_def_ids->'major_proc_list') AS major_proc,
jsonb_array_elements(major_proc->'sub_proc_list') AS sub_proc
WHERE (p_user_id IS NULL OR up.user_id = p_user_id)
AND (
up.proc_def_ids->>'id' = p_proc_def_id OR
major_proc->>'id' = p_proc_def_id OR
sub_proc->>'id' = p_proc_def_id
);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION search_bpm_proc_inst(keyword TEXT, user_email TEXT)
RETURNS SETOF bpm_proc_inst AS $$
BEGIN
RETURN QUERY
SELECT *
FROM bpm_proc_inst
WHERE (proc_def_id ILIKE '%' || keyword || '%'
OR proc_inst_id ILIKE '%' || keyword || '%'
OR proc_inst_name ILIKE '%' || keyword || '%'
OR variables_data::text ILIKE '%' || keyword || '%')
AND user_email = ANY(participants);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION search_chat_room_chats(keyword TEXT)
RETURNS SETOF chat_room_chats AS $$
BEGIN
RETURN QUERY
SELECT *
FROM chat_room_chats
WHERE messages->>'content' ILIKE '%' || keyword || '%'
OR messages->>'name' ILIKE '%' || keyword || '%'
OR messages->>'email' ILIKE '%' || keyword || '%';
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.update_tenant_id_for_first_tenant()
RETURNS TRIGGER AS $$
DECLARE
tenant_count INT;
BEGIN
SELECT COUNT(*) INTO tenant_count FROM public.tenants;
IF tenant_count = 1 THEN
UPDATE public.proc_def
SET tenant_id = NEW.id
WHERE id = 'leave_request_process';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE OR REPLACE FUNCTION handle_todolist_change()
RETURNS TRIGGER AS $$
DECLARE
v_proc_inst_name text;
should_notify boolean := false;
BEGIN
-- INSERT: 새로운 todolist가 추가되고 상태가 'IN_PROGRESS'인 경우
IF (TG_OP = 'INSERT' AND NEW.status = 'IN_PROGRESS') THEN
should_notify := true;
END IF;
-- UPDATE: 기존 todolist가 업데이트되고 상태가 'IN_PROGRESS'로 변경된 경우
IF (TG_OP = 'UPDATE' AND NEW.status = 'IN_PROGRESS' AND (OLD.status IS NULL OR OLD.status != 'IN_PROGRESS')) THEN
should_notify := true;
END IF;
IF should_notify THEN
SELECT proc_inst_name, tenant_id INTO v_proc_inst_name
FROM bpm_proc_inst
WHERE proc_inst_id = NEW.proc_inst_id;
INSERT INTO notifications (id, user_id, title, type, description, is_checked, time_stamp, tenant_id, url)
VALUES (
gen_random_uuid(),
NEW.user_id,
NEW.activity_name,
CASE
WHEN NEW.proc_inst_id IS NOT NULL AND NEW.proc_inst_id <> '' THEN 'workitem_bpm'
ELSE 'workitem'
END,
COALESCE(v_proc_inst_name, NEW.activity_name),
false, -- in_progress 상태이므로 항상 미체크
now(),
NEW.tenant_id,
'/todolist/' || NEW.id
)
ON CONFLICT (id) DO UPDATE
SET
user_id = EXCLUDED.user_id,
title = EXCLUDED.title,
type = EXCLUDED.type,
description = EXCLUDED.description,
is_checked = EXCLUDED.is_checked,
time_stamp = EXCLUDED.time_stamp,
tenant_id = EXCLUDED.tenant_id,
url = EXCLUDED.url;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION update_notification_user_id()
RETURNS TRIGGER AS $$
BEGIN
UPDATE notifications
SET user_id = NEW.user_id,
time_stamp = now()
WHERE url = '/todolist/' || NEW.id;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION delete_notification_on_todolist_delete()
RETURNS TRIGGER AS $$
BEGIN
DELETE FROM notifications
WHERE url = '/todolist/' || OLD.id;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION handle_chat_insert()
RETURNS TRIGGER AS $$
DECLARE
chat_room_participant jsonb;
participant_email text;
participant_record record;
chat_room_name text;
user_is_in_chat_room boolean;
BEGIN
SELECT name INTO chat_room_name FROM public.chat_rooms WHERE id = NEW.id;
FOR participant_record IN
SELECT jsonb_array_elements(participants) as p
FROM public.chat_rooms
WHERE id = NEW.id
LOOP
chat_room_participant := participant_record.p;
IF chat_room_participant->>'username' != 'System' AND chat_room_participant->>'email' != NEW.messages->>'email' THEN
participant_email := chat_room_participant->>'email';
-- 사용자가 현재 해당 채팅방에 있는지 확인
SELECT EXISTS(
SELECT 1 FROM user_devices
WHERE user_email = participant_email
AND access_page = 'chat:' || NEW.id
AND last_access_at > now() - interval '5 minutes'
) INTO user_is_in_chat_room;
-- 사용자가 채팅방에 없을 때만 알림 생성
IF NOT user_is_in_chat_room THEN
INSERT INTO notifications (id, user_id, title, type, description, is_checked, time_stamp, url, from_user_id)
VALUES (
gen_random_uuid(),
participant_email,
NEW.messages->>'content',
'chat',
chat_room_name,
false,
now(),
'/chats?id=' || NEW.id,
NEW.messages->>'name'
)
ON CONFLICT (id) DO UPDATE
SET
user_id = EXCLUDED.user_id,
title = EXCLUDED.title,
time_stamp = EXCLUDED.time_stamp,
is_checked = EXCLUDED.is_checked,
url = EXCLUDED.url,
from_user_id = EXCLUDED.from_user_id;
END IF;
END IF;
END LOOP;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION duplicate_definition_from_marketplace(
p_definition_id TEXT,
p_definition_name TEXT,
p_author_uid TEXT,
p_tenant_id TEXT
)
RETURNS JSONB AS $$
DECLARE
v_proc_def_record RECORD;
v_form_def_record RECORD;
v_result JSONB := '{}';
v_proc_def_uuid UUID;
v_form_def_uuid UUID;
v_new_definition_id TEXT;
v_new_definition JSONB;
BEGIN
-- 프로세스 정의를 marketplace에서 복사
SELECT * INTO v_proc_def_record
FROM proc_def_marketplace
WHERE id = p_definition_id AND name = p_definition_name;
IF NOT FOUND THEN
RETURN jsonb_build_object('error', 'Process definition not found in marketplace');
END IF;
-- 새로운 정의 ID 생성 (기존 ID + UUID)
v_new_definition_id := p_definition_id || '_' || gen_random_uuid()::TEXT;
-- definition JSON에서 processDefinitionId 업데이트
v_new_definition := v_proc_def_record.definition;
v_new_definition := jsonb_set(v_new_definition, '{processDefinitionId}', to_jsonb(v_new_definition_id));
-- proc_def에 복사
INSERT INTO proc_def (
id,
name,
definition,
bpmn,
tenant_id
) VALUES (
v_new_definition_id,
v_proc_def_record.name,
v_new_definition,
v_proc_def_record.bpmn,
p_tenant_id
)
ON CONFLICT (id, tenant_id) DO UPDATE SET
name = EXCLUDED.name,
definition = EXCLUDED.definition,
bpmn = EXCLUDED.bpmn
RETURNING uuid INTO v_proc_def_uuid;
-- form_def_marketplace에서 관련 폼들을 찾아서 form_def로 복사
FOR v_form_def_record IN
SELECT *
FROM form_def_marketplace
WHERE proc_def_id = p_definition_id AND author_uid = p_author_uid
LOOP
INSERT INTO form_def (
html,
proc_def_id,
activity_id,
tenant_id,
id,
fields_json
) VALUES (
v_form_def_record.html,
v_new_definition_id,
v_form_def_record.activity_id,
p_tenant_id,
v_form_def_record.id,
NULL
)
ON CONFLICT (id, tenant_id) DO UPDATE SET
html = EXCLUDED.html,
proc_def_id = EXCLUDED.proc_def_id,
activity_id = EXCLUDED.activity_id
RETURNING uuid INTO v_form_def_uuid;
END LOOP;
-- import_count 증가
UPDATE proc_def_marketplace
SET import_count = import_count + 1
WHERE id = p_definition_id AND name = p_definition_name;
v_result := jsonb_build_object(
'success', true,
'proc_def_uuid', v_proc_def_uuid,
'new_definition_id', v_new_definition_id,
'message', 'Definition duplicated successfully'
);
RETURN v_result;
EXCEPTION
WHEN OTHERS THEN
RETURN jsonb_build_object(
'error', SQLERRM,
'success', false
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- project updated_at 업데이트
CREATE OR REPLACE FUNCTION update_project_updated_at()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.project_id IS NOT NULL THEN
UPDATE public.project
SET updated_at = now()
WHERE project_id = NEW.project_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- bpm_proc_inst updated_at 자동 업데이트
CREATE OR REPLACE FUNCTION update_bpm_proc_inst_updated_at()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.proc_inst_id IS NOT NULL THEN
UPDATE public.bpm_proc_inst
SET updated_at = now()