tandem
/
numerus
2
0
Fork 0
Code Issues 39 Pull Requests Packages Projects 1 Releases 1 Wiki Activity
numerus/sqitch.plan

118 lines
17 KiB
Plaintext
Raw Permalink Normal View History

Initialize Sqitch configuration
2023-01-12 18:37:48 +00:00
%syntax-version=1.0.0
Setup authentication schema and user relation User authentication is based on PostgREST’s[0]: There is a noninherit role, authenticator, whose function is only to switch to a different role according to the application’s session. Accordingly, this role has no permission for anything. The roles that this authentication can switch to are guest, invoicer, or admin. Guest is for anonymous users, when they need to login or register; invoicers are regular users; and admin are application’s administrators, that can change other user’s status, when they have to be removed or have they password changed, for example. The user relation is actually inaccessible to all roles and can only be used through a security definer function, login, so that passwords are not accessible from the application. I hesitated on what to use as the user’s primary key. The email seemed a good candiate, because it will be used for login. But something rubs me the wrong way. It is not that they can change because, despite what people on the Internet keeps parroting, they do not need to be “immutable”, PostgreSQL can cascade updates to foreign keys, and people do **not** change email addresses that ofter. What i **do** know is that email addresses should be unique in order to be used for login and password, hovewer i had to decide what “unique” means here, because the domain part is case insensitive, but the local part who knows? I made the arbitrary decision of assuming that the whole address is case sensitive. I have the feeling that this will bite me harder in the ass than using it as the primary key. [0]: https://postgrest.org/en/stable/auth.html
2023-01-13 00:43:20 +00:00
%project=numerus
%uri=https://numerus.tandem.blog/
Initialize Sqitch configuration
2023-01-12 18:37:48 +00:00
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
roles 2023-01-12T18:42:16Z jordi fita mas <jordi@tandem.blog> # Add database roles
schema_auth [roles] 2023-01-12T19:15:55Z jordi fita mas <jordi@tandem.blog> # Add authentication schema
schema_public [roles] 2023-01-12T19:24:29Z jordi fita mas <jordi@tandem.blog> # Set privileges to public schema
schema_numerus [roles] 2023-01-12T22:57:22Z jordi fita mas <jordi@tandem.blog> # Add application schema
extension_citext [schema_public] 2023-01-12T23:03:33Z jordi fita mas <jordi@tandem.blog> # Add citext extension
email [schema_numerus extension_citext] 2023-01-12T23:09:59Z jordi fita mas <jordi@tandem.blog> # Add email domain
Add user_profile view to update the profile with form Since users do not have access to the auth scheme, i had to add a view that selects only the data that they can see of themselves (i.e., no password or cookie). I wanted to use the `request.user.id` setting that i set in check_cookie, but this would be bad because anyone can change that parameter and, since the view is created by the owner, could see and *change* the values of everyone just by knowing their id. Thus, now i use the cookie instead, because it is way harder to figure out, and if you already have it you can just set to your browser and the user is fucked anyway; the database can not help here. I **am** going to use the user id in row level security policies, but not the value coming for the setting but instaed the one in the `user_profile`, since it already is “derived” from the cookie, that’s why i added that column to the view. The profile includes the language, that i do not use it yet to switch the locale, so i had to add a relation of the available languages, for constraint purposes. There is no NULL language, and instead i added the “Undefined” language, with ‘und’ tag’, to represent “do not know/use content negotiation”. The languages in that relation are the same i used to have inside locale.go, because there is no point on having options for languages i do not have the translation for, so i now configure the list of available languages user in content negotiation from that relation. Finally, i have added all font from RemixIcon because that’s what we used in the design and i am going to use quite a lot of them. There is duplication in the views; i will address that in a different commit.
2023-01-22 01:23:09 +00:00
language [schema_numerus] 2023-01-21T20:55:49Z jordi fita mas <jordi@tandem.blog> # Add relation of available languages
Move available_language.sql before user.sql in sqitch.plan This is because when i create the demo users then i can no remove the available languages before users, due the constrain, and i can no use sqitch rebase or revert.
2023-06-06 17:09:48 +00:00
available_languages [schema_numerus language] 2023-01-21T21:11:08Z jordi fita mas <jordi@tandem.blog> # Add the initial available languages
Add the company relation and read-only form to edit I do not have more time to update the update to the company today, but i believe this is already a good amount of work for a commit. The company is going to be used for row level security, as users will only have access to the data from companies they are granted access, by virtue of being in the company_user relation. I did not know how add a row level security policy to the company_user because i needed the to select on the same relation and this is not allowed, because it would create an infinite loop. Had to add the vat, pg_libphonenumber, and uri extensions in order to validate VAT identification numbers, phone numbers, and URIs, repectively. These libraries are not in Debian, but i created packages for them all in https://dev.tandem.ws/tandem.
2023-01-24 20:46:07 +00:00
user [roles schema_auth email language] 2023-01-12T23:44:03Z jordi fita mas <jordi@tandem.blog> # Create user relation
Add a function to set request settings and the role I did not like the idea that it was the Go server who should set values such as request.user or set the role, because this is mostly something that only the database wants for itself, such as when calling logout. I am also planning to use these setings for row security with the user’s id, that the Go application has no need for, but with the current approach i would need to return it from check_cookie so that it can return it back to the database when acquiring the connection. I would have used the same function to set the settings and the role, but security definer functions—obviously in retrospect—can not set the role, because then could switch to any role of the user that defined the function, not the roles they are member of. Thus, a new function. I did not want to do that every time i needed the database connection within the same request, because it would perform the same operations each time—it is the same cookie, afterall—, so new connections are request scoped and passed along in the context.
2023-01-19 12:07:32 +00:00
ensure_role_exists [schema_auth user] 2023-01-12T23:57:59Z jordi fita mas <jordi@tandem.blog> # Add trigger to ensure the user’s role exists
extension_pgcrypto [schema_auth] 2023-01-13T00:11:50Z jordi fita mas <jordi@tandem.blog> # Add pgcrypto extension
encrypt_password [schema_auth user extension_pgcrypto] 2023-01-13T00:14:30Z jordi fita mas <jordi@tandem.blog> # Add trigger to encrypt user’s password
Add the company relation and read-only form to edit I do not have more time to update the update to the company today, but i believe this is already a good amount of work for a commit. The company is going to be used for row level security, as users will only have access to the data from companies they are granted access, by virtue of being in the company_user relation. I did not know how add a row level security policy to the company_user because i needed the to select on the same relation and this is not allowed, because it would create an infinite loop. Had to add the vat, pg_libphonenumber, and uri extensions in order to validate VAT identification numbers, phone numbers, and URIs, repectively. These libraries are not in Debian, but i created packages for them all in https://dev.tandem.ws/tandem.
2023-01-24 20:46:07 +00:00
login_attempt [schema_auth] 2023-01-17T14:05:49Z jordi fita mas <jordi@tandem.blog> # Add relation to log login attempts
Use user’ß email for auth funcs and return cookie on email change This is for security, just in case two users have the same cookie, althought it is unlikely, but nevertheless less guessable. I also need to refresh the cookie when the user changes their email address, because it is liked toghether. It does mean that it will logout from everywhere else, but i can not do anything about that.
2023-01-23 20:18:55 +00:00
current_user_cookie [schema_numerus] 2023-01-21T20:16:28Z jordi fita mas <jordi@tandem.blog> # Add function to get the cookie of the current Numerus’ user
current_user_email [schema_numerus] 2023-01-23T19:11:53Z jordi fita mas <jordi@tandem.blog> # Add function to get the email of the current Numerus’ user
build_cookie [schema_numerus current_user_email current_user_cookie] 2023-01-23T19:46:13Z jordi fita mas <jordi@tandem.blog> # Add function to build the cookie for the current user
Specify build_cookie dependency for login function
2023-06-04 20:59:25 +00:00
login [roles schema_numerus schema_auth extension_pgcrypto email user login_attempt build_cookie] 2023-01-13T00:32:32Z jordi fita mas <jordi@tandem.blog> # Add function to login
Use user’ß email for auth funcs and return cookie on email change This is for security, just in case two users have the same cookie, althought it is unlikely, but nevertheless less guessable. I also need to refresh the cookie when the user changes their email address, because it is liked toghether. It does mean that it will logout from everywhere else, but i can not do anything about that.
2023-01-23 20:18:55 +00:00
check_cookie [schema_public user build_cookie] 2023-01-17T17:48:49Z jordi fita mas <jordi@tandem.blog> # Add function to check if a user cookie is valid
logout [schema_auth current_user_email current_user_cookie user] 2023-01-17T19:10:21Z jordi fita mas <jordi@tandem.blog> # Add function to logout
Allow guest access to user_profile with an empty profile I want this so that the Go application does not need to know the exact details of the settings that the database sets when applying the cookie; it just needs to select from the user_profile that already knows this. Also, that way i can get the user’s language from its profile with a single select, without having to check whether we are guest or authenticated. With that, i can skip the content negotiation if the user already told us what language they want.
2023-01-23 00:18:47 +00:00
set_cookie [schema_public check_cookie] 2023-01-19T11:00:22Z jordi fita mas <jordi@tandem.blog> # Add function to set the role based on the cookie
Use user’ß email for auth funcs and return cookie on email change This is for security, just in case two users have the same cookie, althought it is unlikely, but nevertheless less guessable. I also need to refresh the cookie when the user changes their email address, because it is liked toghether. It does mean that it will logout from everywhere else, but i can not do anything about that.
2023-01-23 20:18:55 +00:00
user_profile [schema_numerus user current_user_email current_user_cookie] 2023-01-21T23:18:20Z jordi fita mas <jordi@tandem.blog> # Add view for user profile
Add function to change the current user’s password This function does not ask for the confirmation because this is an user-facing issue, not for the database. Still missing: validation and proper error messages.
2023-01-23 20:41:14 +00:00
change_password [schema_numerus user] 2023-01-23T20:22:45Z jordi fita mas <jordi@tandem.blog> # Add function to change the current user’s password
Add the company relation and read-only form to edit I do not have more time to update the update to the company today, but i believe this is already a good amount of work for a commit. The company is going to be used for row level security, as users will only have access to the data from companies they are granted access, by virtue of being in the company_user relation. I did not know how add a row level security policy to the company_user because i needed the to select on the same relation and this is not allowed, because it would create an infinite loop. Had to add the vat, pg_libphonenumber, and uri extensions in order to validate VAT identification numbers, phone numbers, and URIs, repectively. These libraries are not in Debian, but i created packages for them all in https://dev.tandem.ws/tandem.
2023-01-24 20:46:07 +00:00
extension_vat [schema_public] 2023-01-24T10:28:17Z jordi fita mas <jordi@tandem.blog> # Add vat extension
extension_pg_libphonenumber [schema_public] 2023-01-24T13:50:14Z jordi fita mas <jordi@tandem.blog> # Add extension for phone numbers
extension_uri [schema_public] 2023-01-24T14:29:29Z jordi fita mas <jordi@tandem.blog> # Add extension for URIs
currency_code [schema_numerus] 2023-01-24T14:36:04Z jordi fita mas <jordi@tandem.blog> # Add the domain for currency code in ISO 4217
currency [schema_numerus currency_code] 2023-01-24T14:45:26Z jordi fita mas <jordi@tandem.blog> # Add the relation for currencies
available_currencies [schema_numerus currency] 2023-01-24T14:54:18Z jordi fita mas <jordi@tandem.blog> # Add the initial list of available currencies
Use a select for company’s country field At first we thought that a regular text field would do, because we were afraid that a dropdown would be worse from the point of view of user experience, but then we realized that we need the country code for VAT and phone validation, and we can not expect users to input that, of course. I had to add the first “i18n table” to the database with the name of all countries in both Catalan and Spanish and Catalan; English is the default. For now i think i do not need a view that would select the name based on the locale of the current request, because currently i do not plan on adding any other such table —the currency uses the code and the symbol, thus no need for localization. However, now i need the language tag from the locale in order to get the correct translation, and gotext does not give me any way to access the inner language. Thus the need for our Locale type.
2023-01-27 20:30:14 +00:00
country_code [schema_numerus] 2023-01-27T18:33:26Z jordi fita mas <jordi@tandem.blog> # Add domain for country codes
country [schema_numerus country_code] 2023-01-27T18:39:44Z jordi fita mas <jordi@tandem.blog> # Add the relation for countries
country_i18n [schema_numerus country_code language country] 2023-01-27T19:20:43Z jordi fita mas <jordi@tandem.blog> # Add table for localization of country names
Add the invoice_status relation and its i18n
2023-02-07 15:45:27 +00:00
available_countries [schema_numerus country country_i18n] 2023-01-27T18:49:28Z jordi fita mas <jordi@tandem.blog> # Add the list of available countries
Use a select for company’s country field At first we thought that a regular text field would do, because we were afraid that a dropdown would be worse from the point of view of user experience, but then we realized that we need the country code for VAT and phone validation, and we can not expect users to input that, of course. I had to add the first “i18n table” to the database with the name of all countries in both Catalan and Spanish and Catalan; English is the default. For now i think i do not need a view that would select the name based on the locale of the current request, because currently i do not plan on adding any other such table —the currency uses the code and the symbol, thus no need for localization. However, now i need the language tag from the locale in order to get the correct translation, and gotext does not give me any way to access the inner language. Thus the need for our Locale type.
2023-01-27 20:30:14 +00:00
company [schema_numerus extension_vat email extension_pg_libphonenumber extension_uri currency_code currency country_code country] 2023-01-24T15:03:15Z jordi fita mas <jordi@tandem.blog> # Add the relation for companies
Add the company relation and read-only form to edit I do not have more time to update the update to the company today, but i believe this is already a good amount of work for a commit. The company is going to be used for row level security, as users will only have access to the data from companies they are granted access, by virtue of being in the company_user relation. I did not know how add a row level security policy to the company_user because i needed the to select on the same relation and this is not allowed, because it would create an infinite loop. Had to add the vat, pg_libphonenumber, and uri extensions in order to validate VAT identification numbers, phone numbers, and URIs, repectively. These libraries are not in Debian, but i created packages for them all in https://dev.tandem.ws/tandem.
2023-01-24 20:46:07 +00:00
company_user [schema_numerus user company] 2023-01-24T17:50:06Z jordi fita mas <jordi@tandem.blog> # Add the relation of companies and their users
Add company’s default payment method I had to use a deferrable foreign key because the payment methods have a reference to the company, and the company now a circular reference to payment method.
2023-03-04 21:15:52 +00:00
payment_method [schema_numerus company] 2023-03-03T15:00:41Z jordi fita mas <jordi@tandem.blog> # Add relation of payment method
company_default_payment_method [schema_numerus company payment_method] 2023-03-04T20:25:20Z jordi fita mas <jordi@tandem.blog> # Add the company’s default payment method property
Introduce the concept of tax class We want to show the percentage of the tax as columns in the invoice, but until now it was not possible to have a single VAT column when products have different VAT (e.g., 4 % and 10 %), because, as far as the application is concerned, these where ”different taxes”. We also think it would be hard later on to compute the tax due to the government. So, tax classes is just a taxonomy to be able to have different names and rates for the same type of tax, mostly VAT and retention in our case.
2023-02-28 11:02:27 +00:00
tax_class [schema_numerus company] 2023-02-28T10:13:14Z jordi fita mas <jordi@tandem.blog> # Add the relation for tax classes
Add the tax relation with very rough form and handler
2023-01-28 13:18:58 +00:00
tax_rate [schema_numerus] 2023-01-28T11:33:39Z jordi fita mas <jordi@tandem.blog> # Add domain for tax rates
Introduce the concept of tax class We want to show the percentage of the tax as columns in the invoice, but until now it was not possible to have a single VAT column when products have different VAT (e.g., 4 % and 10 %), because, as far as the application is concerned, these where ”different taxes”. We also think it would be hard later on to compute the tax due to the government. So, tax classes is just a taxonomy to be able to have different names and rates for the same type of tax, mostly VAT and retention in our case.
2023-02-28 11:02:27 +00:00
tax [schema_numerus company tax_rate tax_class] 2023-01-28T11:45:47Z jordi fita mas <jordi@tandem.blog> # Add relation for taxes
Replace tag relations with array attributes It all started when i wanted to try to filter invoices by multiple tags using an “AND”, instead of “OR” as it was doing until now. But something felt off and seemed to me that i was doing thing much more complex than needed, all to be able to list the tags as a suggestion in the input field—which i am not doing yet. I found this article series[0] exploring different approaches for tagging, which includes the one i was using, and comparing their performance. I have not actually tested it, but it seems that i have chosen the worst option, in both query time and storage. I attempted to try using an array attribute to each table, which is more or less the same they did in the articles but without using a separate relation for tags, and i found out that all the queries were way easier to write, and needed two joins less, so it was a no-brainer. [0]: http://www.databasesoup.com/2015/01/tag-all-things.html
2023-04-07 19:31:35 +00:00
tag_name [schema_numerus] 2023-03-10T11:06:11Z jordi fita mas <jordi@tandem.blog> # Add domain for tag names
contact [schema_numerus company extension_vat email extension_pg_libphonenumber extension_uri country_code country tag_name] 2023-01-29T12:59:18Z jordi fita mas <jordi@tandem.blog> # Add the relation for contacts
product [schema_numerus company tax tag_name] 2023-02-04T09:17:24Z jordi fita mas <jordi@tandem.blog> # Add relation for products
Convert from cents to “price” and back I do not want to use floats in the Go lang application, because it is not supposed to do anything with these values other than to print and retrieve them from the user; all computations will be performed by PostgreSQL in cents. That means i have to “convert” from the price format that users expect to see (e.g., 1.234,56) to cents (e.g., 123456) and back when passing data between Go and PostgreSQL, and that conversion depends on the currency’s decimal places. At first i did everything in Go, but saw that i would need to do it in a loop when retrieving the list of products, and immediately knew it was a mistake—i needed a PL/pgSQL function for that. I still need to convert from string to float, however, when printing the value to the user. Because the string representation is in C, but i need to format it according to the locale with golang/x/text. That package has the information of how to correctly format numbers, but it is in an internal package that i can not use, and numbers.Digit only accepts numeric types, not a string.
2023-02-05 12:55:12 +00:00
parse_price [schema_public] 2023-02-05T11:04:54Z jordi fita mas <jordi@tandem.blog> # Add function to convert from price to cents
to_price [schema_numerus] 2023-02-05T11:46:31Z jordi fita mas <jordi@tandem.blog> # Add function to format cents to prices
Add the invoice_status relation and its i18n
2023-02-07 15:45:27 +00:00
invoice_status [schema_numerus] 2023-02-07T14:50:26Z jordi fita mas <jordi@tandem.blog> # A relation of invoice status
invoice_status_i18n [schema_numerus invoice_status language] 2023-02-07T14:56:18Z jordi fita mas <jordi@tandem.blog> # Add relation for invoice status’ translatable texts
available_invoice_status [schema_numerus invoice_status invoice_status_i18n] 2023-02-07T15:07:06Z jordi fita mas <jordi@tandem.blog> # Add the list of available invoice status
Allow multiple taxes, and even not tax, for products It seems that we do not agree en whether the IRPF tax should be something of the product or the contact, so we decided to make the product have multiple taxes, just in case, and if only one is needed, then users can just select one; no need to limit to one.
2023-02-08 12:47:36 +00:00
product_tax [schema_numerus product tax] 2023-02-08T11:36:49Z jordi fita mas <jordi@tandem.blog> # Add relation of product taxes
Replace tag relations with array attributes It all started when i wanted to try to filter invoices by multiple tags using an “AND”, instead of “OR” as it was doing until now. But something felt off and seemed to me that i was doing thing much more complex than needed, all to be able to list the tags as a suggestion in the input field—which i am not doing yet. I found this article series[0] exploring different approaches for tagging, which includes the one i was using, and comparing their performance. I have not actually tested it, but it seems that i have chosen the worst option, in both query time and storage. I attempted to try using an array attribute to each table, which is more or less the same they did in the articles but without using a separate relation for tags, and i found out that all the queries were way easier to write, and needed two joins less, so it was a no-brainer. [0]: http://www.databasesoup.com/2015/01/tag-all-things.html
2023-04-07 19:31:35 +00:00
invoice [schema_numerus company contact invoice_status payment_method currency tag_name] 2023-02-09T09:52:21Z jordi fita mas <jordi@tandem.blog> # Add relation for invoice
Add discount_rate domain and invoice_product relation I store again the product’s name, description, and prices, because they are bound to change, but the invoice should remain the same always. That makes me wonder if i should do the same for seller’s and buyer’s data, but that should be a different commit. I’ve added the discount_rate domain because then i can test it independently of the invoice_product relation, moreover i am sure i will need it for simplified invoices too.
2023-02-10 18:02:04 +00:00
discount_rate [schema_numerus] 2023-02-10T17:22:40Z jordi fita mas <jordi@tandem.blog> # Add domain for discount rates
invoice_product [schema_numerus invoice discount_rate] 2023-02-10T17:07:08Z jordi fita mas <jordi@tandem.blog> # Add relation for invoice product
Move the product_id field from invoice_product to a separate table We are going to allow invoices with products that are not (yet) inserted into the products table. We always allowed to have products in invoices with a totally different name, description, price, and whatnot, but until now we had the product id in these invoice lines for statistics purposes. However, Oriol raised the concern that this requires for the products to be inserted before we can create an invoice with them, and we do not plan to have a “create product while invoicing” feature, thus it would mean that people would need to cancel the new invoice, create the new product, and then start the invoice again from scratch. The compromise is to allow products in the invoice that do not have a product_id, meaning that at the time the invoice was created they were not (yet) in the products table. Oriol sees this stop-invoice-create- product issue more important than the accurate statistics of product sales, as it will probably be only one or two units off, anyway. I did not want to allow NULL values to the invoice product’s product_id field, because NULL means “dunno” instead of “no product”, so i had to split that field to a separate table that relates an invoice product with a registered product.
2023-04-19 17:30:12 +00:00
invoice_product_product [schema_numerus invoice_product product] 2023-04-19T16:06:30Z jordi fita mas <jordi@tandem.blog> # Add relation of invoice products and registered products
Replace tag relations with array attributes It all started when i wanted to try to filter invoices by multiple tags using an “AND”, instead of “OR” as it was doing until now. But something felt off and seemed to me that i was doing thing much more complex than needed, all to be able to list the tags as a suggestion in the input field—which i am not doing yet. I found this article series[0] exploring different approaches for tagging, which includes the one i was using, and comparing their performance. I have not actually tested it, but it seems that i have chosen the worst option, in both query time and storage. I attempted to try using an array attribute to each table, which is more or less the same they did in the articles but without using a separate relation for tags, and i found out that all the queries were way easier to write, and needed two joins less, so it was a no-brainer. [0]: http://www.databasesoup.com/2015/01/tag-all-things.html
2023-04-07 19:31:35 +00:00
add_product [schema_numerus product product_tax parse_price company currency tag_name] 2023-02-14T10:32:18Z jordi fita mas <jordi@tandem.blog> # Add function to add new products
edit_product [schema_numerus product product_tax parse_price company currency tag_name] 2023-02-14T11:06:03Z jordi fita mas <jordi@tandem.blog> # Add function to edit products
Add invoice product tax relation
2023-02-15 13:49:06 +00:00
invoice_product_tax [schema_numerus invoice_product tax tax_rate] 2023-02-15T13:20:30Z jordi fita mas <jordi@tandem.blog> # Add relation for taxes in invoice products
Add function to edit invoices
2023-03-11 19:58:20 +00:00
new_invoice_product [schema_numerus discount_rate] 2023-02-16T21:06:01Z jordi fita mas <jordi@tandem.blog> # Add type for passing products to new invoices
Add the function to get the next invoice number I can not use a PostgreSQL sequence because invoices need to be gapless, and sequences are designed to not rollback, for performance reasons. In this case, the performance is secondary because the law does not care.
2023-02-17 13:48:24 +00:00
invoice_number_counter [schema_numerus company] 2023-02-17T13:04:48Z jordi fita mas <jordi@tandem.blog> # Add relation to count invoice numbers
next_invoice_number [schema_numerus invoice_number_counter] 2023-02-17T13:21:48Z jordi fita mas <jordi@tandem.blog> # Add function to retrieve the next invoice number
Move the product_id field from invoice_product to a separate table We are going to allow invoices with products that are not (yet) inserted into the products table. We always allowed to have products in invoices with a totally different name, description, price, and whatnot, but until now we had the product id in these invoice lines for statistics purposes. However, Oriol raised the concern that this requires for the products to be inserted before we can create an invoice with them, and we do not plan to have a “create product while invoicing” feature, thus it would mean that people would need to cancel the new invoice, create the new product, and then start the invoice again from scratch. The compromise is to allow products in the invoice that do not have a product_id, meaning that at the time the invoice was created they were not (yet) in the products table. Oriol sees this stop-invoice-create- product issue more important than the accurate statistics of product sales, as it will probably be only one or two units off, anyway. I did not want to allow NULL values to the invoice product’s product_id field, because NULL means “dunno” instead of “no product”, so i had to split that field to a separate table that relates an invoice product with a registered product.
2023-04-19 17:30:12 +00:00
add_invoice [schema_numerus invoice company currency parse_price new_invoice_product tax invoice_product invoice_product_product invoice_product_tax next_invoice_number tag_name] 2023-02-16T21:12:46Z jordi fita mas <jordi@tandem.blog> # Add function to create new invoices
Add views to compute taxes and total amount of invoices They are not functions because i need to join them with the main invoice relation, and although possible is a bit more awkward with functions. The taxes have their own relation because i will need them grouped by their name in the PDF, so it will probably be a select for that relation.
2023-02-22 13:39:38 +00:00
invoice_tax_amount [schema_numerus invoice_product invoice_product_tax] 2023-02-22T12:08:35Z jordi fita mas <jordi@tandem.blog> # Add view for invoice tax amount
Add discount and tax classes columns to invoice This was actually the (first) reason we added the tax classes: to show them in columns on the invoice—without the class we would need a column for each tax rate, even though they are the same tax. The invoice design has the product total with taxes at the last column, above the tax base, that i am not so sure about, but it seems that it has not brought any problem whatsoever so far, so it remains as is. Had to reduce the invoice’s font size to give more space to the table or the columns would be right next to each other. Oriol also told me to add more vertical spacing to the table’s footer.
2023-03-01 13:08:12 +00:00
invoice_product_amount [schema_numerus invoice_product invoice_product_tax] 2023-03-01T11:18:05Z jordi fita mas <jordi@tandem.blog> # Add view for invoice product subtotal and total
invoice_amount [schema_numerus invoice_product invoice_product_amount] 2023-02-22T12:58:46Z jordi fita mas <jordi@tandem.blog> # Add view to compute subtotal and total for invoices
Show the invoice subtotal, taxes, and total when creating it
2023-02-23 14:31:57 +00:00
new_invoice_amount [schema_numerus] 2023-02-23T12:08:25Z jordi fita mas <jordi@tandem.blog> # Add type to return when computing new invoice amounts
compute_new_invoice_amount [schema_numerus company currency tax new_invoice_product new_invoice_amount] 2023-02-23T12:20:13Z jordi fita mas <jordi@tandem.blog> # Add function to compute the subtotal, taxes, and total amounts for a new invoice
Add function to edit invoices
2023-03-11 19:58:20 +00:00
edited_invoice_product [schema_numerus discount_rate] 2023-03-11T19:22:24Z jordi fita mas <jordi@tandem.blog> # Add typo for passing products to edited invoices
Require invoice_product_product for edit_invoice
2023-06-07 13:09:35 +00:00
edit_invoice [schema_numerus invoice currency parse_price edited_invoice_product tax invoice_product invoice_product_product invoice_product_tax tag_name] 2023-03-11T18:30:50Z jordi fita mas <jordi@tandem.blog> # Add function to edit invoices
Add tags for contacts too With Oriol we agreed that contacts should have tags, too, and that the “tag pool”, as it were, should be shared with the one for invoices (and all future tags we might add). I added the contact_tag relation and tag_contact function, just like with invoices, and then realized that the SQL queries that Go had to execute were becoming “complex” enough: i had to get not only the slug, but the contact id to call tag_contact, and all inside a transaction. Therefore, i opted to create the add_contact and edit_contact functions, that mirror those for invoice and products, so now each “major” section has these functions. They also simplified a bit the handling of the VATIN and phone numbers, because it is now encapsuled inside the PL/pgSQL function and Go does not know how to assemble the parts.
2023-03-26 00:32:53 +00:00
add_contact [schema_numerus extension_vat email extension_pg_libphonenumber extension_uri country_code tag_name contact] 2023-03-25T22:32:37Z jordi fita mas <jordi@tandem.blog> # Add function to create new contacts
Replace tag relations with array attributes It all started when i wanted to try to filter invoices by multiple tags using an “AND”, instead of “OR” as it was doing until now. But something felt off and seemed to me that i was doing thing much more complex than needed, all to be able to list the tags as a suggestion in the input field—which i am not doing yet. I found this article series[0] exploring different approaches for tagging, which includes the one i was using, and comparing their performance. I have not actually tested it, but it seems that i have chosen the worst option, in both query time and storage. I attempted to try using an array attribute to each table, which is more or less the same they did in the articles but without using a separate relation for tags, and i found out that all the queries were way easier to write, and needed two joins less, so it was a no-brainer. [0]: http://www.databasesoup.com/2015/01/tag-all-things.html
2023-04-07 19:31:35 +00:00
edit_contact [schema_numerus email extension_uri country_code tag_name contact extension_vat extension_pg_libphonenumber] 2023-03-25T23:20:27Z jordi fita mas <jordi@tandem.blog> # Add function to edit contacts
Add the expense relation
2023-04-30 14:06:16 +00:00
expense [schema_numerus contact company currency_code currency tag_name] 2023-04-30T13:46:36Z jordi fita mas <jordi@tandem.blog> # Add the expense relation
Add the relation of expense taxes
2023-05-01 14:17:36 +00:00
expense_tax [schema_numerus expense tax tax_rate] 2023-05-01T14:08:33Z jordi fita mas <jordi@tandem.blog> # Add relation of expense taxes
Add function to create expenses
2023-05-02 09:29:57 +00:00
add_expense [schema_numerus expense expense_tax tax company currency parse_price tag_name] 2023-05-02T08:00:26Z jordi fita mas <jordi@tandem.blog> # Add function to create new expenses
Add SQL function to edit expenses
2023-05-04 10:34:47 +00:00
edit_expense [schema_numerus expense currency parse_price tax tag_name] 2023-05-04T09:53:04Z jordi fita mas <jordi@tandem.blog> # Add function to edit expenses
Add the relation to store the expense’s attachment files It is a separate table because we allow expenses to not have such an attachment, although we allow only an attachment per expense, and i do not want to have a bunch of nullable columns for that. I decided to keep the files in the database, contrary to “conventional wisdom” of storing files in the filesystem, because these attachments are invoices and such documets that are an integral part of the expense relation. In other words, losing these files would render the expense (almost) useless. Thus, the ACID guarantees of the database are the most appropriate place for them.
2023-05-13 19:23:24 +00:00
expense_attachment [schema_numerus expense] 2023-05-13T19:11:21Z jordi fita mas <jordi@tandem.blog> # Add relation of expense attachments
Add attach_to_expense SQL function Just to avoid SQL “logic” in Go source.
2023-05-15 10:38:40 +00:00
attach_to_expense [schema_numerus expense expense_attachment] 2023-05-15T10:18:31Z jordi fita mas <jordi@tandem.blog> # Add function to attach documents to expenses
Add expense_tax_amount to properly compute the net income
2023-05-18 10:35:56 +00:00
expense_tax_amount [schema_numerus expense expense_tax] 2023-05-18T10:15:40Z jordi fita mas <jordi@tandem.blog> # Add vier for expense tax amount
Add relations for sales quotations and their products They are mostly the same as invoices, but the contact and payment method are optional, thus, like other optionals fields, i created relations to link these that have payment method or contact, to avoid NULL columns in quote. Still missing functions to add and edit quotations, and views to compute their tax and total amount.
2023-06-06 19:08:31 +00:00
quote_status [roles schema_numerus] 2023-06-06T17:05:53Z jordi fita mas <jordi@tandem.blog> # Add relation for quote status
quote_status_i18n [roles schema_numerus quote_status language] 2023-06-06T17:13:50Z jordi fita mas <jordi@tandem.blog> # Add relation for quote status’ translatable texts
available_quote_status [schema_numerus quote_status quote_status_i18n] 2023-06-06T17:18:48Z jordi fita mas <jordi@tandem.blog> # Add the list of available quote status
quote [roles schema_numerus company quote_status currency tag_name] 2023-06-06T17:46:27Z jordi fita mas <jordi@tandem.blog> # Add relation for sales quotations
quote_contact [roles schema_numerus quote contact] 2023-06-06T18:17:50Z jordi fita mas <jordi@tandem.blog> # Add relation of quote contact
quote_product [roles schema_numerus quote discount_rate] 2023-06-06T18:25:05Z jordi fita mas <jordi@tandem.blog> # Add relation for quote products
quote_product_product [roles schema_numerus quote_product product] 2023-06-06T18:38:26Z jordi fita mas <jordi@tandem.blog> # Add relation of quote products and registered products
quote_product_tax [roles schema_numerus quote_product tax tax_rate] 2023-06-06T18:46:33Z jordi fita mas <jordi@tandem.blog> # Add relation of quotation product tax
quote_payment_method [roles schema_numerus quote payment_method] 2023-06-06T18:59:12Z jordi fita mas <jordi@tandem.blog> # Add relation for the payment method of quotes
Add function to create new quotes I had to add the quote_number_format to company, similar to how we do with invoices.
2023-06-07 12:14:48 +00:00
quote_number_counter [roles schema_numerus company] 2023-06-07T11:05:51Z jordi fita mas <jordi@tandem.blog> # Add relatin to keep a counter of quote numbers
next_quote_number [roles schema_numerus quote_number_counter] 2023-06-07T11:20:54Z jordi fita mas <jordi@tandem.blog> # Add function to retrieve the next quote number
new_quote_product [schema_numerus discount_rate] 2023-06-07T11:36:37Z jordi fita mas <jordi@tandem.blog> # Add type for passing products to new quotes
add_quote [roles schema_numerus quote company currency parse_price new_quote_product tax quote_product quote_payment_method quote_contact quote_product_product quote_product_tax next_quote_number tag_name] 2023-06-07T11:39:45Z jordi fita mas <jordi@tandem.blog> # Add function to create new quotes
Added SQL views to compute computations amounts and edit them
2023-06-07 13:31:20 +00:00
quote_tax_amount [roles schema_numerus quote_product quote_product_tax] 2023-06-07T12:45:17Z jordi fita mas <jordi@tandem.blog> # Add add view for quote tax amount
quote_product_amount [roles schema_numerus quote_product quote_product_tax] 2023-06-07T12:48:58Z jordi fita mas <jordi@tandem.blog> # Add view for quote product subtotal and total
quote_amount [roles schema_numerus quote_product quote_product_amount] 2023-06-07T12:52:51Z jordi fita mas <jordi@tandem.blog> # Add view to compute subtotal and total for quotes
new_quote_amount [schema_numerus] 2023-06-07T12:57:45Z jordi fita mas <jordi@tandem.blog> # Add type to return when computing new quote amounts
compute_new_quote_amount [roles schema_numerus company tax new_quote_product new_quote_amount] 2023-06-07T13:00:07Z jordi fita mas <jordi@tandem.blog> # Add function to compute the subtotal, taxes, and total amounts for a new quotation
edited_quote_product [schema_numerus discount_rate] 2023-06-07T13:03:23Z jordi fita mas <jordi@tandem.blog> # Add type for passing products to edit quotations
edit_quote [roles schema_numerus quote currency parse_price edited_quote_product tax quote_contact quote_payment_method quote_product quote_product_tax quote_product_product tag_name] 2023-06-07T13:08:10Z jordi fita mas <jordi@tandem.blog> # Add function to edit quotations
Tag sqitch.plan with first version
2023-06-12 14:05:50 +00:00
@v0 2023-06-12T14:05:34Z jordi fita mas <jordi@tandem.blog> # Tag version 0
Split contact relation into tax_details, phone, web, and email We need to have contacts with just a name: we need to assign freelancer’s quote as expense linked the government, but of course we do not have a phone or email for that “contact”, much less a VATIN or other tax details. It is also interesting for other expenses-only contacts to not have to input all tax details, as we may not need to invoice then, thus are useless for us, but sometimes it might be interesting to have them, “just in case”. Of course, i did not want to make nullable any of the tax details required to generate an invoice, otherwise we could allow illegal invoices. Therefore, that data had to go in a different relation, and invoice’s foreign key update to point to that relation, not just customer, or we would again be able to create invalid invoices. We replaced the contact’s trade name with just name, because we do not need _three_ names for a contact, but we _do_ need two: the one we use to refer to them and the business name for tax purposes. The new contact_phone, contact_web, and contact_email relations could be simply a nullable field, but i did not see the point, since there are not that many instances where i need any of this data. Now company.taxDetailsForm is no longer “the same as contactForm with some extra fields”, because i have to add a check whether the user needs to invoice the contact, to check that the required values are there. I have an additional problem with the contact form when not using JavaScript: i must set the required field to all tax details fields to avoid the “(optional)” suffix, and because they _are_ required when that checkbox is enabled, but i can not set them optional when the check is unchecked. My solution for now is to ignore the form validation, and later i will add some JavaScript that adds the validation again, so it will work in all cases.
2023-06-30 19:32:48 +00:00
contact_phone [roles schema_numerus extension_pg_libphonenumber] 2023-06-28T11:04:19Z jordi fita mas <jordi@tandem.blog> # Add relation to keep contacts’ phone numbers
contact_email [roles schema_numerus email contact] 2023-06-28T11:47:19Z jordi fita mas <jordi@tandem.blog> # Add relation to keep contacts’ emails
contact_web [roles schema_numerus extension_uri contact] 2023-06-28T12:01:07Z jordi fita mas <jordi@tandem.blog> # Add relation to keep contacts’ websites
contact_tax_details [roles schema_numerus contact extension_vat country_code country] 2023-06-23T09:14:03Z jordi fita mas <jordi@tandem.blog> # Add relation of contact’s tax details
tax_details [schema_numerus extension_vat country_code] 2023-06-29T10:57:57Z jordi fita mas <jordi@tandem.blog> # Add composite type for contacts’ tax details
invoice_contact_id_fkey [schema_numerus invoice contact_tax_details] 2023-06-30T16:50:45Z jordi fita mas <jordi@tandem.blog> # Update invoice’s contact_id foreign key to point to tax sales
Add IBAN and BIC fields to contacts These two fields are just for information purposes, as Numerus does not have any way to wire transfer using these, but people might want to keep these in the contact’s info as a convenience. Since not every contact should have an IBAN, e.g., customers, and inside SEPA (European Union and some more countries) the BIC is not required, they are in two different relations in order to be optional without using NULL. For the IBAN i found an already made PostgreSQL module, but for BIC i had to write a regular expression based on the information i gathered from Wikipedia, because the ISO standard is not free. These two parameters for the add_contact and edit_contact functions are TEXT because i realized that these functions are intended to be used from the web application, that only deals with texts, so the ValueOrNil() function was unnecessarily complex and PostreSQL’s functions were better suited to “convert” from TEXT to IBAN or BIC. The same is true for EMAIL and URI domains, so i changed their parameter types to TEXT too. Closes #54.
2023-07-02 00:08:45 +00:00
extension_iban [schema_numerus] 2023-07-01T22:20:38Z jordi fita mas <jordi@tandem.blog> # Add IBAN extension
contact_iban [schema_numerus roles contact extension_iban] 2023-07-01T22:27:38Z jordi fita mas <jordi@tandem.blog> # Add relation for contact iban
bic [schema_numerus] 2023-07-01T22:46:30Z jordi fita mas <jordi@tandem.blog> # Add BIC domain
contact_swift [schema_numerus roles contact bic] 2023-07-01T23:03:13Z jordi fita mas <jordi@tandem.blog> # Add relation for contact’s SWIFT-BIC
add_contact [add_contact@v0 tax_details contact_web contact_email contact_phone contact_iban contact_swift] 2023-06-29T11:10:15Z jordi fita mas <jordi@tandem.blog> # Change add contact to accept a tax_detail parameter and use the new relations for web, email, phone, iban, and swift
edit_contact [edit_contact@v0 tax_details contact_web contact_email contact_phone contact_iban contact_swift] 2023-06-29T11:50:41Z jordi fita mas <jordi@tandem.blog> # Change edit_contact to require tax_details parameter and to use new relations for web, email, phone, iban, and swift
Create validation function for SQL domains and for phones When i wrote the functions to import contact, i already created a couple of “temporary” functions to validate whether the input given from the Excel files was correct according to the various domains used in the relations, so i can know whether i can import that data. I realized that i could do exactly the same when validating forms: check that the value conforms to the domain, in the exact same way, so i can make sure that the value will be accepted without duplicating the logic, at the expense of a call to the database. In an ideal world, i would use pg_input_is_valid, but this function is only available in PostgreSQL 16 and Debian 12 uses PostgreSQL 15. These functions are in the public schema because initially i wanted to use them to also validate email, which is needed in the login form, but then i recanted and kept the same email validation in Go, because something felt off about using the database for that particular form, but i do not know why.
2023-07-03 09:31:59 +00:00
input_is_valid [schema_public roles] 2023-07-03T08:42:46Z jordi fita mas <jordi@tandem.blog> # add function to check if input is valid for a domain
input_is_valid_phone [schema_public roles extension_pg_libphonenumber] 2023-07-03T08:59:36Z jordi fita mas <jordi@tandem.blog> # add function to validate phone number inputs
import_contact [schema_numerus roles contact contact_web contact_phone contact_email contact_iban contact_swift contact_tax_details input_is_valid input_is_valid_phone] 2023-07-02T18:50:22Z jordi fita mas <jordi@tandem.blog> # Add functions to massively import customer data
Tag Sqitch with version 1
2023-07-03 09:33:09 +00:00
@v1 2023-07-03T09:32:51Z jordi fita mas <jordi@tandem.blog> # Tag version 1