Sustave Platform
Database Architecture

1.1 Implementing a Hybrid Authorization Model

1.1.1 Leveraging PostgreSQL Row-Level Security (RLS) for Data Isolation

PostgreSQL's Row-Level Security (RLS) is the cornerstone of our security model, ensuring that each user role (admin, investor, farmer, franchise) can only access data relevant to their role. Unlike the previous implementation which had inconsistencies, our revised approach provides granular control with no data leakage.

-- Policies for farmers
CREATE POLICY farmer_self ON farmers
FOR ALL
TO farmer
USING (user_id = current_user_id())
WITH CHECK (user_id = current_user_id());

-- Policies for farm activities
CREATE POLICY farmer_farm_activities ON farm_activities
FOR ALL
TO farmer
USING (farmer_id = current_user_id())
WITH CHECK (farmer_id = current_user_id());

This policy ensures that a farmer can only access their own data, with no possibility of data leakage. The current_user_id() function reliably retrieves the authenticated user ID from the application context.

1.1.2 Wallet-Based Access Control

We've moved beyond simple user ID-based access control to a wallet-centric model that aligns with blockchain principles. This is critical for ensuring that token balances and transactions are properly isolated.

-- Policies for token balances
CREATE POLICY user_token_balances ON token_balances
FOR ALL
TO investor, farmer, franchise
USING (wallet_id IN (
    SELECT wallet_id FROM wallets WHERE user_id = current_user_id()
))
WITH CHECK (wallet_id IN (
    SELECT wallet_id FROM wallets WHERE user_id = current_user_id()
));

This approach ensures that users can only access token balances associated with their wallets, providing a more secure and flexible model that supports multiple wallets across different blockchain networks.

1.2 Designing Granular RLS Policies for Multi-Tenant Access

1.2.1 Cross-Role Data Access Patterns

We've implemented specific policies to handle cross-role data access needs while maintaining security boundaries. For example, investors can view verified farmer profiles but cannot see sensitive financial data.

CREATE POLICY investor_read_farmers ON farmers
FOR SELECT
TO investor
USING (status = 'verified');

1.3 Securing Sensitive Data and Transactions

1.3.1 Wallet Integration and Blockchain Security

We've completely restructured the wallet integration to ensure secure handling of blockchain transactions. The previous model stored wallet addresses as plain text in multiple tables, creating data inconsistency and security risks.

CREATE TABLE wallets (
    wallet_id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    address VARCHAR(42) NOT NULL CHECK (address ~ '^0x[a-fA-F0-9]{40}$'),
    network_id INT NOT NULL REFERENCES blockchain_networks(network_id) ON DELETE CASCADE,
    is_active BOOLEAN DEFAULT TRUE,
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    -- A user cannot have two wallets on the same network
    UNIQUE (user_id, network_id),
    
    -- Only one primary wallet per user
    EXCLUDE (user_id WITH =, is_primary WITH =) 
    WHERE (is_primary = TRUE) 
    DEFERRABLE INITIALLY DEFERRED
);

This structure ensures that:

• Each user can have multiple wallets across different blockchain networks
• Data consistency is maintained through proper foreign key relationships
• Security is enhanced by eliminating duplicate wallet address storage
• Users can designate a primary wallet for key operations

1.3.2 Platform Fee Management

We've implemented a robust system for tracking and processing platform fees across all transaction types, with automated transfers to the platform wallet.

-- Reserved ID for the administrative wallet
CREATE OR REPLACE FUNCTION get_platform_wallet_id()
RETURNS UUID AS $$
BEGIN
    RETURN '00000000-0000-0000-0000-000000000000'::UUID;
END;
$$ LANGUAGE plpgsql;

-- Create user for the administrative wallet
INSERT INTO users (
    id, 
    wallet_address, 
    name, 
    email, 
    role, 
    kyc_status,
    is_verified
) VALUES (
    get_platform_wallet_id(),
    '0x0000000000000000000000000000000000000000',
    'Sustave Platform',
    'platform@sustave.com',
    'admin',
    'verified',
    true
)
ON CONFLICT (id) DO UPDATE
SET 
    wallet_address = EXCLUDED.wallet_address,
    name = EXCLUDED.name,
    email = EXCLUDED.email,
    role = EXCLUDED.role,
    kyc_status = EXCLUDED.kyc_status,
    is_verified = EXCLUDED.is_verified;

3.1 Consolidating and Normalizing User-Related Data

3.1.1 Correcting the Wallet Integration

The previous implementation had a critical flaw: token transfers were linked directly to user addresses as text, rather than to wallet records. This created data inconsistency and performance issues.

CREATE TABLE token_transfers (
    tx_hash VARCHAR(66) PRIMARY KEY CHECK (tx_hash ~ '^0x[a-fA-F0-9]{64}$'),
    from_wallet_id INT NOT NULL REFERENCES wallets(wallet_id) ON DELETE CASCADE,
    to_wallet_id INT NOT NULL REFERENCES wallets(wallet_id) ON DELETE CASCADE,
    amount NUMERIC(20,6) NOT NULL CHECK (amount > 0),
    token_id INT NOT NULL REFERENCES tokens(token_id) ON DELETE CASCADE,
    network_id INT NOT NULL REFERENCES blockchain_networks(network_id) ON DELETE CASCADE,
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    status VARCHAR(20) DEFAULT 'completed' CHECK (status IN ('pending', 'completed', 'failed')),
    gas_fee NUMERIC(20,6),
    gas_price NUMERIC(20,6)
);

This correction provides:

• Integrity referencial completa: Elimina inconsistencias de datos
• Rendimiento optimizado: Consultas directas sin subconsultas costosas
• Soporte para múltiples redes: Permite transacciones en Polygon, BSC, Ethereum
• Seguridad mejorada: Políticas RLS basadas en wallets en lugar de direcciones

3.1.2 Multiple Wallets per User

We've enabled users to have multiple wallets across different blockchain networks, which is essential for the platform's multi-chain strategy.

-- Add column to identify primary wallet
ALTER TABLE wallets
ADD COLUMN IF NOT EXISTS is_primary BOOLEAN DEFAULT FALSE;

-- Add constraint to ensure only one primary wallet per user
ALTER TABLE wallets
ADD CONSTRAINT unique_primary_wallet 
EXCLUDE (user_id WITH =, is_primary WITH =) 
WHERE (is_primary = TRUE) 
DEFERRABLE INITIALLY DEFERRED;

3.2 Enhancing the Carbon Credit Lifecycle Management

3.2.1 Allowing Multiple Credits per Acre

The previous implementation had a critical flaw: a UNIQUE constraint on acre_nft_id in carbon_credits prevented multiple credits per acre. This was a major business limitation as a single acre can generate multiple credits over time.

-- Remove UNIQUE constraint that prevents multiple credits
ALTER TABLE carbon_credits
DROP CONSTRAINT IF EXISTS carbon_credits_acre_nft_id_key;

-- Add a field for the generation period
ALTER TABLE carbon_credits
ADD COLUMN IF NOT EXISTS generation_period DATE NOT NULL;

-- Add a constraint to prevent duplicates in the same period
ALTER TABLE carbon_credits
ADD CONSTRAINT unique_credit_per_period UNIQUE (acre_nft_id, generation_period);

This correction enables:

• Multiple carbon credits per acre across different time periods
• Prevention of duplicate credits within the same period
• Accurate tracking of historical carbon sequestration
• Compliance with carbon credit verification standards

3.2.2 Carbon Credit Distribution Model

We've implemented a transparent distribution model for carbon credit revenue that aligns with the platform's business model.

-- Get percentages from configuration
SELECT 
    (config_value::JSONB)->>'platform_share'::NUMERIC INTO platform_share
FROM system_config
WHERE config_key = 'carbon_reward_distribution';

-- If not found, use default values
IF platform_share IS NULL THEN platform_share := 0.12; END IF; -- 12%
IF investor_share IS NULL THEN investor_share := 0.22; END IF; -- 22%
IF farmer_share IS NULL THEN farmer_share := 0.66; END IF; -- 66%

5.1 Strengthening Data Validation with Constraints and Triggers

5.1.1 Validating Farm Activities and Yields

Farm activity data must be validated for realistic yield values and proper activity types to maintain data integrity.

CREATE OR REPLACE FUNCTION validate_farm_activity()
RETURNS TRIGGER AS $$
DECLARE
    farm_size DECIMAL(10,2);
    max_yield DECIMAL(10,2);
BEGIN
    -- Validate activity type
    IF NEW.activity_type NOT IN ('planting', 'harvest', 'maintenance', 'intercropping') THEN
        RAISE EXCEPTION 'Invalid farm activity type';
    END IF;
    
    -- Validate positive yield
    IF NEW.yield_kg <= 0 THEN
        RAISE EXCEPTION 'Yield must be greater than 0';
    END IF;
    
    -- Validate maximum yield (10,000 kg per hectare)
    SELECT farm_size_hectares INTO farm_size
    FROM farmers
    WHERE user_id = NEW.farmer_id;
    
    max_yield := farm_size * 10000;
    IF NEW.yield_kg > max_yield THEN
        RAISE EXCEPTION 'Yield exceeds maximum capacity (max: % kg)', max_yield;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger for validating farm activities
CREATE TRIGGER validate_farm_activity_trigger
BEFORE INSERT ON farm_activities
FOR EACH ROW
EXECUTE FUNCTION validate_farm_activity();

5.1.2 Preventing Duplicate Carbon Credits

Unique constraints prevent duplicate carbon credit generation for the same acre and time period.

-- Constraint to prevent duplicates in the same period
ALTER TABLE carbon_credits
ADD CONSTRAINT unique_credit_per_period UNIQUE (acre_nft_id, generation_period);

5.2 Audit & Logging

Comprehensive audit logging tracks all changes to critical data and administrative actions. The existing audit_logs and admin_actions tables provide a foundation for this security monitoring.

5.2.1 Audit Log Implementation

-- Trigger for audit logging
CREATE OR REPLACE FUNCTION audit_table()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_logs (
        user_id, action, old_value, new_value, timestamp
    ) VALUES (
        current_user_id(),
        TG_OP,
        CASE TG_OP
            WHEN 'UPDATE', 'DELETE' THEN row_to_json(OLD)::TEXT
            ELSE NULL
        END,
        CASE TG_OP
            WHEN 'UPDATE', 'INSERT' THEN row_to_json(NEW)::TEXT
            ELSE NULL
        END,
        NOW()
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply auditing to critical tables
CREATE TRIGGER audit_users_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_table();

CREATE TRIGGER audit_token_balances_trigger
AFTER INSERT OR UPDATE OR DELETE ON token_balances
FOR EACH ROW EXECUTE FUNCTION audit_table();