Supabase · PostgreSQL

Database Layer

Supabase PostgreSQL with Row Level Security, atomic RPC functions, and GDPR-compliant EU data residency.

6
Tables
5
Migrations
5
RPC Functions
14
Indexes
Infrastructure

Database Overview

EU-hosted PostgreSQL with enterprise-grade security and compliance.

🗄️
Supabase (PostgreSQL)
Managed database provider
🌍
EU (Frankfurt)
GDPR-compliant data residency
📊
6 Tables, 5 Migrations
4 RPC functions, 14 indexes
🔒
RLS Enabled
Row Level Security on all tables
Schema Design

Entity Relationship Diagram

Table relationships and data flow across the database schema.

shops
30+ columns
PRIMARY TABLE
1 N
1 N
1 N
vat_validations
18 columns
FK → shops(id)
customer_vat_profiles
10 columns
FK → shops(id)
webhook_logs
9 columns
FK → shops(id)
Standalone Tables (No FK)
vat_cache
7 columns
STANDALONE
sessions
10 columns
STANDALONE
Data Models

Table Schemas

Detailed column definitions, types, constraints, and defaults for every table.

🏪
shops
001 + 002 RLS ON
Column Type Default Constraints Notes
id UUID uuid_generate_v4() PK
shop_domain TEXT - UNIQUE NOT NULL
shop_name TEXT -
access_token TEXT - Encrypted in prod
is_enabled BOOLEAN true
vat_field_required BOOLEAN false
required_countries TEXT[] '{}'
show_company_info BOOLEAN true
custom_error_message TEXT -
plan TEXT 'free' CHECK free / starter / pro / enterprise
monthly_validations INTEGER 0 Analytics counter
validations_reset_at TIMESTAMPTZ -
monthly_vies_calls INTEGER 0 Billing counter 004
vies_calls_reset_at TIMESTAMPTZ - 004
cart_enabled BOOLEAN true 002
cart_required BOOLEAN false 002
cart_block_checkout BOOLEAN false 002
auto_exemption_enabled BOOLEAN true 002
exemption_same_country BOOLEAN false 002
checkout_enabled BOOLEAN true 002
thankyou_enabled BOOLEAN true 002
account_enabled BOOLEAN true 002
account_auto_populate BOOLEAN true 002
is_plus BOOLEAN false 002
settings_json JSONB '{}' 002
reverse_charge_enabled BOOLEAN true 002
reverse_charge_note_template TEXT 'VAT Reverse Charge...' 002
seller_country_code VARCHAR(2) NULL 002
cart_drawer_enabled BOOLEAN true 002
cart_drawer_selector TEXT NULL 002
installed_at TIMESTAMPTZ NOW()
uninstalled_at TIMESTAMPTZ -
created_at TIMESTAMPTZ NOW()
updated_at TIMESTAMPTZ NOW() Auto-trigger on update
✅
vat_validations
001 + 002 RLS ON FK → shops
Column Type Constraints Notes
id UUID PK
shop_id UUID FK → shops(id) CASCADE
vat_number TEXT NOT NULL
country_code CHAR(2) NOT NULL
vat_number_formatted TEXT With country prefix
is_valid BOOLEAN NOT NULL
validation_source TEXT CHECK vies / cache / format_only
company_name TEXT From VIES response
company_address TEXT From VIES response
order_id TEXT
checkout_token TEXT
customer_id TEXT Numeric string
customer_email TEXT
ip_address INET
error_code TEXT
error_message TEXT
surface TEXT CHECK cart / checkout / thank_you / account / api 002
validated_at TIMESTAMPTZ DEFAULT NOW()
created_at TIMESTAMPTZ DEFAULT NOW()
💾
vat_cache
001 RLS ON STANDALONE
Column Type Constraints Notes
id UUID PK
vat_number TEXT UNIQUE NOT NULL
country_code CHAR(2) NOT NULL
is_valid BOOLEAN NOT NULL
company_name TEXT
company_address TEXT
cached_at TIMESTAMPTZ DEFAULT NOW()
expires_at TIMESTAMPTZ DEFAULT NOW() + 24h
👤
customer_vat_profiles
002 RLS ON FK → shops
Column Type Constraints Notes
id UUID PK
shop_id UUID FK → shops(id) CASCADE
customer_id TEXT NOT NULL
vat_number TEXT NOT NULL
country_code CHAR(2) NOT NULL
company_name TEXT
is_valid BOOLEAN DEFAULT true
last_validated_at TIMESTAMPTZ
created_at TIMESTAMPTZ
updated_at TIMESTAMPTZ
UNIQUE(shop_id, customer_id)
📨
webhook_logs
001 + 005 RLS ON FK → shops
Column Type Constraints Notes
id UUID PK
shop_id UUID FK → shops(id) CASCADE
topic TEXT NOT NULL
order_id TEXT 005
payload JSONB
processed BOOLEAN DEFAULT false
processed_at TIMESTAMPTZ
error TEXT
created_at TIMESTAMPTZ DEFAULT NOW()
UNIQUE INDEX on (order_id, topic) WHERE order_id IS NOT NULL 005
🔑
sessions
001 RLS ON STANDALONE
Column Type Constraints Notes
id TEXT PK
shop TEXT NOT NULL
state TEXT
is_online BOOLEAN DEFAULT false
scope TEXT
access_token TEXT
expires_at TIMESTAMPTZ
online_access_info JSONB
created_at TIMESTAMPTZ
updated_at TIMESTAMPTZ
Schema Evolution

Migrations

Incremental database migrations tracking schema changes across development phases.

001
Phase 1B
001_initial_schema.sql
shops, vat_validations, vat_cache, webhook_logs, sessions
002
Phase 1B
002_multi_surface.sql
surface column, surface settings, customer_vat_profiles table
003
Phase 2B
003_improved_increment.sql
Enhanced increment_validation_count RPC function
004
Phase 2B
004_abuse_protection.sql
monthly_vies_calls, vies_calls_reset_at, analytics indexes
005
Phase 2D2
005_webhook_idempotency.sql
webhook_logs.order_id column + unique index for idempotency
Server Functions

RPC Functions

Atomic PostgreSQL functions for cache management, counter incrementing, and scheduled maintenance.

fn clean_expired_cache()
Removes expired entries from the VAT cache table. Runs daily via pg_cron.
DELETE FROM vat_cache WHERE expires_at < NOW()
fn reset_monthly_validations()
Batch resets monthly validation counters on the 1st of each month. pg_cron backup to the RPC self-check mechanism.
-- Monthly batch reset (pg_cron backup)
fn increment_validation_count(p_shop_id)
Atomically increments the validation counter with automatic month boundary detection and reset.
RETURNS TABLE(new_count INT, plan TEXT, was_reset BOOL)
fn increment_vies_call_count(p_shop_id)
Atomically increments the VIES API call counter with row-level locking for billing accuracy.
RETURNS TABLE(new_vies_count INT, plan TEXT, was_reset BOOL)
fn update_updated_at()
Trigger function that automatically sets the updated_at timestamp on row modification for the shops table.
NEW.updated_at = NOW(); RETURN NEW;
Query Performance

Indexes

14 carefully designed indexes optimizing lookups, filtering, and composite queries across all tables.

IDX
idx_shops_domain
ON shops(shop_domain)
IDX
idx_validations_shop
ON vat_validations(shop_id)
IDX
idx_validations_vat
ON vat_validations(vat_number)
IDX
idx_validations_date
ON vat_validations(validated_at)
IDX
idx_validations_order
ON vat_validations(order_id)
IDX
idx_validations_surface
ON vat_validations(surface)
IDX
idx_validations_shop_vat_date
ON vat_validations(shop_id, vat_number, validated_at DESC)
IDX
idx_validations_shop_surface
ON vat_validations(shop_id, surface, validated_at DESC)
IDX
idx_cache_vat
ON vat_cache(vat_number)
IDX
idx_cache_expires
ON vat_cache(expires_at)
IDX
idx_webhooks_shop
ON webhook_logs(shop_id)
IDX
idx_webhooks_topic
ON webhook_logs(topic)
IDX
idx_sessions_shop
ON sessions(shop)
UNQ
idx_webhook_logs_idempotency
UNIQUE ON webhook_logs(order_id, topic)
Security Layer

Row Level Security

Every table has RLS enabled with service_role bypass for backend access.

🛡️
RLS Enabled
Row Level Security is enabled on all 6 tables. No row can be accessed without a matching policy.
🔑
service_role Access
Backend uses the service_role key which bypasses RLS. Full CRUD access for server-side operations.
🚫
No Public Access
No anon or authenticated role policies exist. Direct client-side database access is completely blocked.
Automation

Scheduled Jobs

pg_cron jobs for automated cache cleanup and counter resets.

⏰ Daily 03:00 UTC
Cache Cleanup
Calls clean_expired_cache() to remove stale VAT validation cache entries older than 24 hours. Keeps the cache table lean and ensures fresh validations.
📅 Monthly 1st
Validation Counter Reset
Calls reset_monthly_validations() as a backup to the RPC self-check mechanism. Ensures all monthly counters are properly reset at billing boundaries.
Shopify Data

Metafields

App-owned metafields for persisting VAT data on Shopify entities.

Entity Namespace Key Description
Order vat_validator vat_number Validated VAT number
Order vat_validator company_name Company name from VIES
Order vat_validator is_valid VIES validation result (boolean)
Order vat_validator validated_at Validation timestamp
Order vat_validator reverse_charge Reverse charge applicable (boolean)
Order vat_validator reverse_charge_note Legal reverse charge note
Customer vat_validator vat_number Customer profile VAT number
Customer vat_validator company_name Customer company name
Shop vat_validator cart_blocking_config Function configuration JSON