302 lines
11 KiB
SQL
302 lines
11 KiB
SQL
-- DocEngine: Complete table creation script
|
|
-- Schema: admin
|
|
-- Database: ocr
|
|
|
|
SET search_path TO admin, public;
|
|
|
|
-- ============================================
|
|
-- Users & Authentication
|
|
-- ============================================
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
username VARCHAR(150) NOT NULL UNIQUE,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
hashed_password VARCHAR(255) NOT NULL,
|
|
full_name VARCHAR(255),
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
is_superuser BOOLEAN NOT NULL DEFAULT FALSE,
|
|
last_login TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.roles (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(50) NOT NULL UNIQUE,
|
|
description TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.user_roles (
|
|
user_id UUID NOT NULL REFERENCES admin.users(id) ON DELETE CASCADE,
|
|
role_id UUID NOT NULL REFERENCES admin.roles(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (user_id, role_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.refresh_tokens (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES admin.users(id) ON DELETE CASCADE,
|
|
token VARCHAR(512) NOT NULL UNIQUE,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
revoked BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.audit_logs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID REFERENCES admin.users(id) ON DELETE SET NULL,
|
|
action VARCHAR(100) NOT NULL,
|
|
resource_type VARCHAR(100) NOT NULL,
|
|
resource_id VARCHAR(255),
|
|
details TEXT,
|
|
ip_address VARCHAR(45),
|
|
user_agent VARCHAR(512),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================
|
|
-- Documents
|
|
-- ============================================
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.documents (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
filename VARCHAR(500) NOT NULL,
|
|
original_filename VARCHAR(500) NOT NULL,
|
|
content_type VARCHAR(100) NOT NULL,
|
|
file_size BIGINT NOT NULL,
|
|
checksum VARCHAR(128) NOT NULL,
|
|
storage_path VARCHAR(1024) NOT NULL,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'pending',
|
|
page_count INTEGER,
|
|
is_scanned BOOLEAN,
|
|
document_metadata JSONB,
|
|
error_message TEXT,
|
|
uploaded_by UUID REFERENCES admin.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.document_pages (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
document_id UUID NOT NULL REFERENCES admin.documents(id) ON DELETE CASCADE,
|
|
page_number INTEGER NOT NULL,
|
|
width DOUBLE PRECISION NOT NULL,
|
|
height DOUBLE PRECISION NOT NULL,
|
|
image_path VARCHAR(1024),
|
|
text_content TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.document_text_blocks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
page_id UUID NOT NULL REFERENCES admin.document_pages(id) ON DELETE CASCADE,
|
|
text TEXT NOT NULL,
|
|
x DOUBLE PRECISION NOT NULL,
|
|
y DOUBLE PRECISION NOT NULL,
|
|
width DOUBLE PRECISION NOT NULL,
|
|
height DOUBLE PRECISION NOT NULL,
|
|
confidence DOUBLE PRECISION,
|
|
font_family VARCHAR(255),
|
|
font_size DOUBLE PRECISION,
|
|
font_color VARCHAR(50),
|
|
font_style VARCHAR(50),
|
|
block_type VARCHAR(50) NOT NULL DEFAULT 'text',
|
|
sequence INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.document_images (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
page_id UUID NOT NULL REFERENCES admin.document_pages(id) ON DELETE CASCADE,
|
|
x DOUBLE PRECISION NOT NULL,
|
|
y DOUBLE PRECISION NOT NULL,
|
|
width DOUBLE PRECISION NOT NULL,
|
|
height DOUBLE PRECISION NOT NULL,
|
|
image_path VARCHAR(1024) NOT NULL,
|
|
image_type VARCHAR(50) NOT NULL DEFAULT 'figure',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.document_tables (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
page_id UUID NOT NULL REFERENCES admin.document_pages(id) ON DELETE CASCADE,
|
|
x DOUBLE PRECISION NOT NULL,
|
|
y DOUBLE PRECISION NOT NULL,
|
|
width DOUBLE PRECISION NOT NULL,
|
|
height DOUBLE PRECISION NOT NULL,
|
|
rows INTEGER NOT NULL,
|
|
columns INTEGER NOT NULL,
|
|
data JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================
|
|
-- Templates
|
|
-- ============================================
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.document_formats (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
page_width DOUBLE PRECISION NOT NULL,
|
|
page_height DOUBLE PRECISION NOT NULL,
|
|
page_count INTEGER NOT NULL DEFAULT 1,
|
|
margin_top DOUBLE PRECISION NOT NULL DEFAULT 72.0,
|
|
margin_right DOUBLE PRECISION NOT NULL DEFAULT 72.0,
|
|
margin_bottom DOUBLE PRECISION NOT NULL DEFAULT 72.0,
|
|
margin_left DOUBLE PRECISION NOT NULL DEFAULT 72.0,
|
|
fingerprint JSONB,
|
|
source_document_id UUID REFERENCES admin.documents(id) ON DELETE SET NULL,
|
|
version INTEGER NOT NULL DEFAULT 1,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_by UUID REFERENCES admin.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.document_cells (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
format_id UUID NOT NULL REFERENCES admin.document_formats(id) ON DELETE CASCADE,
|
|
page_number INTEGER NOT NULL,
|
|
x DOUBLE PRECISION NOT NULL,
|
|
y DOUBLE PRECISION NOT NULL,
|
|
width DOUBLE PRECISION NOT NULL,
|
|
height DOUBLE PRECISION NOT NULL,
|
|
row_no INTEGER NOT NULL DEFAULT 0,
|
|
column_no INTEGER NOT NULL DEFAULT 0,
|
|
data_type VARCHAR(50) NOT NULL DEFAULT 'text',
|
|
font_family VARCHAR(255),
|
|
font_size DOUBLE PRECISION,
|
|
font_style VARCHAR(50),
|
|
font_color VARCHAR(50),
|
|
background_color VARCHAR(50),
|
|
border_top VARCHAR(100),
|
|
border_right VARCHAR(100),
|
|
border_bottom VARCHAR(100),
|
|
border_left VARCHAR(100),
|
|
padding_top DOUBLE PRECISION NOT NULL DEFAULT 0.0,
|
|
padding_right DOUBLE PRECISION NOT NULL DEFAULT 0.0,
|
|
padding_bottom DOUBLE PRECISION NOT NULL DEFAULT 0.0,
|
|
padding_left DOUBLE PRECISION NOT NULL DEFAULT 0.0,
|
|
alignment VARCHAR(20) NOT NULL DEFAULT 'left',
|
|
vertical_alignment VARCHAR(20) NOT NULL DEFAULT 'top',
|
|
rowspan INTEGER NOT NULL DEFAULT 1,
|
|
colspan INTEGER NOT NULL DEFAULT 1,
|
|
static_text TEXT,
|
|
field_name VARCHAR(255),
|
|
sequence INTEGER NOT NULL DEFAULT 0,
|
|
is_dynamic BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.document_regions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
format_id UUID NOT NULL REFERENCES admin.document_formats(id) ON DELETE CASCADE,
|
|
page_number INTEGER NOT NULL,
|
|
region_type VARCHAR(50) NOT NULL,
|
|
x DOUBLE PRECISION NOT NULL,
|
|
y DOUBLE PRECISION NOT NULL,
|
|
width DOUBLE PRECISION NOT NULL,
|
|
height DOUBLE PRECISION NOT NULL,
|
|
content JSONB,
|
|
sequence INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.table_formats (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
format_id UUID NOT NULL REFERENCES admin.document_formats(id) ON DELETE CASCADE,
|
|
page_number INTEGER NOT NULL,
|
|
x DOUBLE PRECISION NOT NULL,
|
|
y DOUBLE PRECISION NOT NULL,
|
|
width DOUBLE PRECISION NOT NULL,
|
|
height DOUBLE PRECISION NOT NULL,
|
|
rows INTEGER NOT NULL,
|
|
columns INTEGER NOT NULL,
|
|
border_style VARCHAR(50) NOT NULL DEFAULT 'solid',
|
|
border_width DOUBLE PRECISION NOT NULL DEFAULT 1.0,
|
|
border_color VARCHAR(50) NOT NULL DEFAULT '#000000',
|
|
header_rows INTEGER NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.table_columns (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
table_format_id UUID NOT NULL REFERENCES admin.table_formats(id) ON DELETE CASCADE,
|
|
column_index INTEGER NOT NULL,
|
|
width DOUBLE PRECISION NOT NULL,
|
|
header_text VARCHAR(500),
|
|
data_type VARCHAR(50) NOT NULL DEFAULT 'text',
|
|
alignment VARCHAR(20) NOT NULL DEFAULT 'left',
|
|
font_family VARCHAR(255),
|
|
font_size DOUBLE PRECISION,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.table_rows (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
table_format_id UUID NOT NULL REFERENCES admin.table_formats(id) ON DELETE CASCADE,
|
|
row_index INTEGER NOT NULL,
|
|
height DOUBLE PRECISION NOT NULL DEFAULT 20.0,
|
|
is_header BOOLEAN NOT NULL DEFAULT FALSE,
|
|
background_color VARCHAR(50),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.watermarks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
format_id UUID NOT NULL REFERENCES admin.document_formats(id) ON DELETE CASCADE,
|
|
page_number INTEGER,
|
|
text VARCHAR(500),
|
|
image_path VARCHAR(1024),
|
|
x DOUBLE PRECISION NOT NULL,
|
|
y DOUBLE PRECISION NOT NULL,
|
|
width DOUBLE PRECISION NOT NULL,
|
|
height DOUBLE PRECISION NOT NULL,
|
|
opacity DOUBLE PRECISION NOT NULL DEFAULT 0.3,
|
|
rotation DOUBLE PRECISION NOT NULL DEFAULT 0.0,
|
|
font_family VARCHAR(255),
|
|
font_size DOUBLE PRECISION,
|
|
font_color VARCHAR(50),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.image_regions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
format_id UUID NOT NULL REFERENCES admin.document_formats(id) ON DELETE CASCADE,
|
|
page_number INTEGER NOT NULL,
|
|
x DOUBLE PRECISION NOT NULL,
|
|
y DOUBLE PRECISION NOT NULL,
|
|
width DOUBLE PRECISION NOT NULL,
|
|
height DOUBLE PRECISION NOT NULL,
|
|
image_path VARCHAR(1024),
|
|
image_type VARCHAR(50) NOT NULL DEFAULT 'figure',
|
|
is_static BOOLEAN NOT NULL DEFAULT TRUE,
|
|
field_name VARCHAR(255),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.template_fingerprints (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
format_id UUID NOT NULL UNIQUE REFERENCES admin.document_formats(id) ON DELETE CASCADE,
|
|
page_dimensions JSONB,
|
|
logo_coordinates JSONB,
|
|
header_coordinates JSONB,
|
|
footer_coordinates JSONB,
|
|
table_coordinates JSONB,
|
|
cell_coordinates JSONB,
|
|
fingerprint_hash VARCHAR(256) NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.template_matches (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
document_id UUID NOT NULL REFERENCES admin.documents(id) ON DELETE CASCADE,
|
|
format_id UUID NOT NULL REFERENCES admin.document_formats(id) ON DELETE CASCADE,
|
|
confidence_score DOUBLE PRECISION NOT NULL,
|
|
match_details JSONB,
|
|
selected BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|