Tidy Tax Table (ttaxt)

Purpose

This repository contains SQL scripts to set up a database for managing VAT, sales tax, and country-related data. The database is designed to help businesses, especially those operating internationally, manage tax compliance for multiple jurisdictions, including:

• VAT management (including reverse charge mechanisms for EU transactions).
• U.S. sales tax compliance, based on state-specific nexus thresholds.
• Country and currency tracking, to support proper tax calculations based on customer and vendor location.

The system helps in tracking when sales thresholds are reached for U.S. states, managing VAT for EU customers and vendors, and ensuring tax compliance across different jurisdictions.

Inspiration

This project was inspired by the discussions with chatgpt around simplifying tax compliance for businesses based abroad, particularly in the EU, that need to handle VAT, U.S. sales tax, and payments to global vendors.

The goal is to create a flexible, lightweight solution that minimizes manual tax management while integrating with existing systems like WooCommerce and Stripe for payments and tax reporting.

This should make it easier to work on projects that are community-based where value is created by a network of people and shared in a vendor marketplace. A similar approach has been used by Minecraft where the community of players produced ideas, content, and tools, including mods, plugins, and themes, that are then sold at times in the marketplace.

Solution Providers Discussed

Some of the best solutions discussed for managing taxes and payments include:

• Avalara: A comprehensive service for handling U.S. state sales tax compliance, filing, and reporting.
• TaxJar: A simpler alternative to Avalara, also providing sales tax calculation and reporting.
• Stripe Tax: An option for businesses using Stripe Connect, allowing for automated sales tax and VAT calculation.

This setup would be used as a set up to those solutions before there is sufficient income or investment to justify a full-service solution. It would rather need to be integrated with WooCommerce (+ Dokan, etc.) and Stripe and Stripe Connect and Xero or similar as a lower cost solution.

Later on, Avalara could be used if thresholds set in the us_sales_tax_compliance table are reached as it would justify the investment. Similar thresholds could be set for other countries or globally to reach similar conclusions.

Example

So far the only code that works is the example-filltable.py which scrapes data from the web and populates the tables with data for VAT rates and reverse charge mechanisms but does not yet include U.S. sales tax compliance. Open to suggestions on how to improve this to include U.S. sales tax compliance.

Setup

The SQL script provided in this repository will create the following tables:

1. vat_rules: To manage VAT rates and reverse charge mechanisms.
2. customer_rules: Tracks VAT registration status and applicable VAT rates for customers.
3. vendor_rules: Similar to customer rules but applies to vendors.
4. us_sales_tax_compliance: Manages U.S. sales tax nexus thresholds and rates.
5. countries: Stores country-specific data such as ISO country code, continent, and currency.

Usage

1. Run the SQL script to create the tables in your preferred database system (e.g., SQLite, PostgreSQL).
2. Populate the tables with relevant data (e.g., VAT rates, U.S. state sales tax thresholds, country data).
3. Use the tables to track tax compliance, apply appropriate VAT rates, and manage sales tax obligations.

Example Queries for Ttaxt (Tidy Tax Table):

1. Query to Find VAT Rate for a Specific Country: This query retrieves the VAT rate and reverse charge status for a specific country (e.g., Netherlands - "NL").

Copy code
SELECT vat_rate, reverse_charge 
FROM vat_rules 
WHERE country_code = 'NL';
Expected Result:
vat_rate: 21.0%, reverse_charge: TRUE

2. Query to Get U.S. Sales Tax Rate and Thresholds for a State: This query returns the sales tax rate, sales threshold, and transaction threshold for a specific U.S. state (e.g., California - "CA").

Copy code
SELECT sales_tax_rate, sales_threshold, transaction_threshold 
FROM us_sales_tax_compliance 
WHERE state_code = 'CA';
Expected Result:
sales_tax_rate: 7.25%, sales_threshold: $100,000, transaction_threshold: 200

3. Query to Track Total Sales in a U.S. State: This query helps track the total sales and transactions in a specific state (e.g., New York - "NY") to monitor whether the nexus threshold has been met.

Copy code
SELECT total_sales, total_transactions 
FROM us_sales_tax_compliance 
WHERE state_code = 'NY';
Expected Result:
total_sales: $80,000, total_transactions: 150

4. Query to Identify Countries with Reverse Charge VAT: This query retrieves all countries where the reverse charge mechanism applies for VAT.

SELECT country_code, vat_rate 
FROM vat_rules 
WHERE reverse_charge = TRUE;
Expected Result:
country_code: 'NL', vat_rate: 21.0
country_code: 'GB', vat_rate: 20.0

5. Query to Check Whether a State Has Reached Sales Tax Threshold: This query checks if sales in a particular U.S. state (e.g., Texas - "TX") have reached the sales or transaction threshold.

SELECT threshold_reached 
FROM us_sales_tax_compliance 
WHERE state_code = 'TX';
Expected Result:
threshold_reached: FALSE

6. Query to Block Sales Once Threshold is Reached: Once a state has reached the sales or transaction threshold, this query can be used to block further sales until compliance is established.

Copy code
UPDATE us_sales_tax_compliance 
SET sales_block = TRUE 
WHERE state_code = 'CA' AND threshold_reached = TRUE;
Expected Result:
Sales in California will be blocked until compliance is managed.

7. Query to Get VAT-Registered Customers and Their Rates: This query returns a list of VAT-registered customers and the applicable VAT rate.

Copy code
SELECT country_code, vat_rate 
FROM customer_rules 
WHERE customer_vat_registered = TRUE;
Expected Result:
country_code: 'FR', vat_rate: 20.0
country_code: 'DE', vat_rate: 19.0

These queries are examples generated by chatgpt atm and not yet tested or based on any real data.

Feel free to modify or extend the script to suit your specific business needs.

Some depedencies

• pandas
• requests
• pyvat
• sqlite3
• SPARQLWrapper
• os
• openpyxl
• xlrd